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 More

What’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 More

Something 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 More

Why 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