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

How to change your default transaction isolation level in SSMS

It’s not uncommon for me to see support professionals running SELECT queries against production databases that cause major performance issues due to shared locks.  By default SQL Server Management Studio (SSMS) has its transaction isolation level set to READ COMMITTED.  If your support users only have read access to your database I would suggest having […]

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

How to see what is in your buffer cache

If you have ever wondered what database or tables/indexes were consuming the most memory/buffer in your database, this post can help with that. How do I see this? The key DMV’s to view this  are sys.dm_os_buffer_descriptors, sys.allocation_units, and sys.partitions.  The key piece to identifying what is currently in the buffer cache is sys.dm_os_buffer_descriptors.  The use […]

Read More