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 MoreHow 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 MoreWhere did that execution plan come from?
I’ve had a lot of cases where I have a developer send over an execution plan and ask me to look at their query. I’m really hesitant to waste time looking at an estimated execution plan rather than a (real) plan retrieved from the plan cache. For a variety of reasons it is best to […]
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 MoreHow to remove a hint from a query without code changes
On occasion I’ve been in situations where I’ve identified a query with a hint applied that was causing degraded performance. In the case of vendor code this can be a problem since removal or changes to hints would require a code change. This can also be a problem in shops where there are strict processes […]
Read MoreHow 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 MoreTempdb caching – What is it and how to take advantage of it
There is overhead associated with creating objects in tempdb. Sometimes this overhead surfaces as pagelatch contention – most commonly in PFS, GAM, or SGAM pages. Tuning to alleviate PFS, GAM, or SGAM contention isn’t so much the topic I’ll be writing about; however, this will help alleviate the pressure on tempdb. What is tempdb caching? […]
Read MoreWhat happens with parameterized wildcards?
I’ve been curious of how the optimizer behaves when using parameterized SQL with a LIKE clause. Wildcard values that have a wildcard character in front generally do not seek on indexes. I was curious how the optimizer deals with this from a cached plan perspective – would placement of the wildcard character cause recompilations? I […]
Read MoreHow SPLIT_STRING row estimates can affect performance
Recently someone brought a tweet to my attention regarding SPLIT_STRING and how performance of it was faster than table valued parameters (TVP). As you may have read in one of my prior posts TVP’s have issues related to lack of good row estimates. This can be fixed with a little extra work. SPLIT_STRING caught my […]
Read MoreIndex seeks and NULL values
A long time ago I heard or read that you couldn’t index a NULL value – I didn’t think that was correct. The other day I saw a missing index (as identified by the optimizer) where the first column was looking for a NULL value. I was working on optimizing a process that was pretty […]
Read More