If you’ve ever received an alert that tempdb was running out of space, you probably want to know what session/query is consuming tempdb. There are two ways tempdb gets consumed. User tempdb usage and system tempdb usage. User temdpb usage is related to creating and populating @table variables or #temporary tables and explicitly populating them. […]
Read MorePerformance Tuning
Join Hints – Careful, They Force Order!
Recently I was looking at a query generating deadlocks as a result of a clustered index scan. I saw that someone forced a LOOP JOIN on one of the offending queries. At first glance it appeared as if the LOOP JOIN should have made an index seek more likely; however, after remembering a side effect […]
Read MoreHave you ever used sp_create_plan_guide_from_handle?
On occasion you may have a query that starts picking up a bad plan as a result of an execution plan regression. This can be for a variety of reasons (stats, parameter sniffing, etc). If this occurs on a regular basis and you don’t have the luxury of query store, you can use sp_create_plan_guide_from_handle in […]
Read MoreHow to find the source of connection leaks
In a previous post, I detailed how to determine if you have connection leaks. In this post I will show you how to determine which query executions are likely the source of the connection leaks. The approach we will use for finding the connection leaks is to look for sessions that have been in a […]
Read MoreTuning 3rd party software packages? Yes, it is possible.
You didn’t write it, you can’t modify it, but you’ve been asked to support it. How do you deal with this type of challenge? I love a good performance challenge and the constraints on this one always make these fun to solve. I’ve been in many consulting scenarios where a client implemented a software package, […]
Read More