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 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 MoreASYNC_NETWORK_IO Wait, What is it? What causes it?
You may occasionally see ASYNC_NETWORK_IO waits surface in your database . This wait can be caused by an unhealthy network connection; however, more often than not I see it caused for different reasons. What are causes of ASYNC_NETWORK_IO wait? There’s a few common scenarios I see as a cause for this. Queries retrieving large result […]
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 More