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 MoreIs your system_health session missing data?
It is fairly common knowledge that the system_health session is a useful tool for resolving issues such as deadlocks. I see a lot of posts on other websites where people report a discrepancy in the number of deadlocks monitored by perfmon or tracing vs what shows up in the system health session. This is the result […]
Read MoreWhat’s better? #Temp tables or @Table variables?
Like most things performance related in SQL Server, it depends. Let me start with a common misconception on table variables. I regularly see developers using @Temp table variables because “they are in memory”. I cringe every time I hear this. Why do I cringe? Because that’s not true. Table variables are persisted just the same […]
Read MoreTuning 3rd party software packages? Yes, it is possible.
You didn’t write it, you can’t modify it, but you’ve been asked to support it. How do you deal with this type of challenge? I love a good performance challenge and the constraints on this one always make these fun to solve. I’ve been in many consulting scenarios where a client implemented a software package, […]
Read MoreWhen does too much parallelism affect performance?
I’ve seen a lot of discussions and blog posts around tuning max degree of parallelism and cost threshold of parallelism. I’ve read lots of general guidance around decreasing max degree of parallelism (MAXDOP) for servers with greater than 8 CPU’s. From my experience, even when following the guidance on MAXDOP and cost threshold of parallelism […]
Read MoreHow To Fix Your Deadlocks
A common problem I see in systems is deadlocks. In a lot of cases the DBA isn’t totally aware of them because applications use retry logic for deadlocked transactions. I consider this to be a hack for a real problem that can be addressed in most cases. In this post I will explain what a […]
Read More