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 monitoring
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 MoreQuery store doesn’t work on read replicas
While attempting to find a query that ran on a read only replica, it got me wondering whether I needed to be querying the secondary, the primary, or the always on listener. Turns out the answer is none of the above. Query store only collects information from the primary. If you query any of these […]
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 MoreHow to view query store id’s related to a sql/plan handle or query/plan hash.
On occasion you will have a sql_handle, plan_handle, query_hash, or query_plan_hash and need to know the query store ID related to it. One such example is with a deadlock. Deadlocks contain sql_handles. A key part of diagnosing deadlocks is viewing the execution plan. Query store is a good place to view the execution plan and […]
Read MoreSomething you should know about query timeouts and sys.dm_exec_query_stats
If you’re looking for your worst performers by querying sys.dm_exec_query_stats, be aware queries that are timing out (i.e. “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding”) do not factor into the statistics shown in sys.dm_exec_query_stats. I’ll show an example of this. Example For this example […]
Read MoreWhy do I have multiple plans for one query?
Have you ever been digging in your plan cache with either sys.dm_exec_query_stats or sys.dm_exec_procedure_stats and seen two identical queries with different execution plans? When I say identical I’m referring to having the same sql_handle. A sql_handle is generated based on the query text submitted to sql. If one character is different (even white space) it […]
Read MoreHow to find your busiest database from sys.dm_exec_query_stats
Earlier today I read a blog post detailing how to find your busiest database by querying sys.dm_exec_query_stats. The blog post suggested you use the dbid column from sys.dm_exec_sql_text. There is a problem with this. The dbid is NULL for anything that is not a stored procedure. This is what was suggested in the blog post […]
Read MoreHow to detect query timeout errors with Extended Events
A common error that pops up in application logs as a result of long executions is: When these start showing up in an application log, it is a good indicator that a performance issue may be occurring in SQL Server. So far as SQL Server is concerned, it thinks the application dropped or cancelled the […]
Read MoreWhy do all .Net apps show up as “.Net SqlClient Data Provider” in traces?
Have you ever been asked to get involved in diagnosing a problem for a .Net application only to find that your diagnostic tool of choice shows “.Net SqlClient Data Provider” for all applications running on the database? I’ve encountered this frequently and it makes filtering out activity to the application of interest difficult. Fortunately there is a very […]
Read More