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 MoreDo you have unused indexes?
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, […]
Read MoreHow to find the source of connection leaks
In a previous post, I detailed how to determine if you have connection leaks. In this post I will show you how to determine which query executions are likely the source of the connection leaks. The approach we will use for finding the connection leaks is to look for sessions that have been in a […]
Read MoreHow to detect query timeout errors with Extended Events
A common error that pops up in application logs as a result of long executions is: When these start showing up in an application log, it is a good indicator that a performance issue may be occurring in SQL Server. So far as SQL Server is concerned, it thinks the application dropped or cancelled the […]
Read MoreWhat are wait events and why are they important for performance analysis?
While trying to get to the bottom of a SQL Server performance issue, on many occasions I’ve seen engineering teams and in some cases DBA’s start focussing on perfmon counters to determine where a SQL performance issue is coming from. Performance counters quite often will report symptoms of a performance issue and can many times […]
Read More