Earlier today I had a conversation with a colleague who thought statistics updates could cause blocking. I have never seen this before, but since I consider this colleague to be pretty competent I second guessed myself. I decided to put together a post as soon as I got home to verify this. To start with […]
Read MoreHave you ever used sp_create_plan_guide_from_handle?
On occasion you may have a query that starts picking up a bad plan as a result of an execution plan regression. This can be for a variety of reasons (stats, parameter sniffing, etc). If this occurs on a regular basis and you don’t have the luxury of query store, you can use sp_create_plan_guide_from_handle in […]
Read MoreHow to fix RESOURCE_SEMAPHORE waits
RESOURCE_SEMAPHORE waits occur when a query is unable to get enough memory to execute. In this post I will describe how to see they are occurring. I will also provide tips on ways to help reduce or eliminate them. How do I see them occurring? You can detect they are occurring by checking wait stats. […]
Read MoreHave you ever wondered what SQL generated that FETCH or FETCH API_CURSOR statement?
Have you ever seen a FETCH statement show up when monitoring via activity monitor, sysprocesses, dm_exec_requests, or name your monitoring tool of choice? These surface when a process is programatically fetching through a cursor. Most of the time the fetch statement isn’t of interest when performance tuning. Instead it is of more use to see […]
Read MoreSitecore on SQL Server? Something you should be aware of…
Sitecore is a .Net based content management system. It uses SQL Server for its backend database. I recently got asked to help investigate a performance issue on a new implementation that was being performance tested. In this post I’ll describe the behavior causing the performance issue, and how through proper configuration this could be […]
Read MoreTable valued parameters and performance
Table valued parameters (TVP) are a great feature that solves the problem of passing a table of values into a stored procedure. In the past problems like this would get solved by complicated approaches such as passing and parsing XML, or passing long streams of dynamically generated SQL. There is a caveat you should be aware […]
Read MoreWhat is a query_hash or query_plan_hash and why is it useful?
If you have ever queried sys.dm_exec_requests or sys.dm_exec_query_stats you may have noticed the query_hash and query_plan_hash columns. If you’re familiar with a sql_handle and plan_handle the query_hash and query_plan_hash should be easy to understand. A query_hash is a computed value that points to a query irrespective of literal values. Similarly a query_plan_hash is a computed […]
Read MoreYour plan is cached but dm_exec_query_plan returns NULL, Why?
On occasion you might query sys.dm_exec_query_plan with a valid plan_handle and it will return NULL for the query_plan column. This is despite seeing the plan_handle in sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, or sys.dm_exec_cached_plans. In this short post I’ll explain how to replicate this behavior and why you might see it. Replicating For this example I’ll be using a […]
Read MoreTempdb contention and this time it’s not on PFS, GAM, SGAM
Tempdb can be a major bottleneck on a busy SQL Server instance. A common source of this is PFS, GAM, or SGAM page contention. Lately I’ve seen on more than one SQL 2016 instance where a different page became a major bottleneck (if you are on SQL 2017 this may apply to you as well). […]
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 More