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 […]

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 […]

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 […]

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 […]

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 […]

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). […]

Having the proper indexes in place is important for performance; however, on occasion indexes are created that are never used. Unnecessary indexes incur overhead for any DML. Fortunately this is a simple thing to detect. CREATE TABLE #UnusedIndexes (database_name sysname, table_name sysname, index_id int, object_id int, index_name sysname NULL, user_updates bigint, last_user_update datetime, user_seeks bigint, […]