After writing a post on SQL Server’s new parameter sniffing option and how it helps performance of parameterized wildcard queries, I started to wonder if we can get similar benefits with filtered indexes. Filtered indexes come with the gotcha of requiring that the predicates of the index be hardcoded (non parameterized). I can see the […]
Read MorePerformance Tuning
Negative blocking session ids
On occasion while examining lock scenarios, I’ve seen a lead blocker with a negative session ID. After looking in the documentation for the blocking_session_id, it explained why I am seeing this (taken directly from Microsoft’s sys.dm_exec_requests documentation): -2 = The blocking resource is owned by an orphaned distributed transaction. -3 = The blocking resource is […]
Read MorePerformance of parameterized LIKE queries with new parameter sniffing option
A while back I wrote a blog post on how parameterized queries with a LIKE in the predicate (WHERE) performed slower than the non-parameterized version. In a nutshell, the parameterized version of the query can’t tell for certain what parameters will be passed and does not generate an optimal plan. While attempting to set up […]
Read MorePerformance implications of using DATEDIFF function in WHERE
These might be narrow cases I’m describing, but as I’ve seen it happen often enough I figured it is worth writing a post about. In this post I’ll describe two different scenarios where the DATEDIFF function caused a query to perform poorly due to DATEDIFF not being sargable. The first scenario I’ll be describing is […]
Read MorePerformance implications of using ISNULL vs IS NULL
Quite often I see queries that are not sargable due to the use of the ISNULL function in the query’s predicate (WHERE clause). If you’re not familiar with sargable it means whether or not a condition in the predicate can take advantage of an index. I won’t dive too deep into sargability here, but I’ll […]
Read MoreDealing With “Bad” Plans
SQL Server does the best it can to generate an efficient execution plan when a query is executed. This process can be expensive. To avoid the expense, SQL Server attempts to cache a plan once it is generated. This eliminates the need for subsequent calls to the same query to generate the plan. In an […]
Read MoreWhat’s filling up tempdb?
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 MoreJoin 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 More