Why you should not WAIT to fix Query WAITS

If you are a SQL Server DBA or a Database enthusiast I am sure you have heard about Wait Statistics.Wait Statistics are the most important concept in SQL Server when it comes to performance troubleshooting.

You may wondering why any query has to wait? But you may be surprised to know every query has to wait! Yes, that is true when you execute a query, the query always has to wait.There are two reasons why a query has to wait

• Resource Waits

A resource wait happens when the query is waiting for a resource such as I/O or may be on some table data which is currently locked by some other process.

• Cooperative Scheduling Waits

SQL Server bypasses the Scheduling of Windows OS to scaling the thread management efficiency. To do this SQL Server manages CPU resources by managing the CPU time by splitting it between queries. SQL Server has a time slice of 4 milliseconds, by doing this SQL Server takes a query off the CPU when it exceeds this value and let another query the ability to use CPU. This is the main reason we said each query has to wait even there are no resource waits.

In a query life cycle of SQL Server it can go in to three states. Which are;

• Running
• Runnable
• Suspended

When a query is actually doing something it is in Running state, if the query in a Resource wait then it is in Suspended state. A query in Runnable state means, it has all the resources it needs to execute but waiting for a CPU thread released from SQL Server.

SQL Server reports all the details about above mentioned query waits through Wait Statistics. SQL Server exposes the Wait Statistics through the Dynamic Management View sys.dm_os_wait_stats. The data returned from this DMV is about all the wait reasons in SQL Server.

Now the interesting part is in SQL Server 2014 we are getting total of 771 different reasons why a query can wait. Yes that is a lot but there are very specific wait types we normally worry about.

There is a very good survey done on what are the most worrying wait types in SQL Server here

However purpose of this article is not to go deep in to wait types. But what is more important is understanding what wait statistics are and when to use them.

When its come to query optimization the first step we can take is have a good look at the wait statistics, because they can tell you why queries in SQL Server were waiting and if there is a problem or not, if there is a problem then you can dig deep and find out whether it is a missing index or blocking issue or something else.

In conclusion wait statistics will only show you the symptoms not the root cause. But when you see the symptoms you must take actions to cure the root cause. This is why you should not wait to fix Query waits. We hope now you have clear idea about Query waits and what wait statistics are. We will talk more about trouble shooting with wait statistics in a future article.

Reading an Execution Plan - Part 1

Execution plan is something similar to the Bible, everyone know it is there but very few reads and understand what is in there. We here lot of people saying "Why this query is slow" but if you ask them "Have you look at the execution plan?" you rarely get the answer "Yes".

So first of all what is Execution plan,  in simple terms, it is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. In other words it says how the query was or will be executed in side SQL Server.So when you know how to read it you can find the places which are making the query slow.

Execution Plan Types

When you look at a execution plan of a particular query SQL Server presents two types of plans;

Estimated Execution Plan

When estimated execution plans are generated, the Transact-SQL queries or batches do not execute. Instead, the execution plan that is generated displays the query execution plan that SQL Server Database Engine would most probably use if the queries were actually executed.

Actual Execution Plan

When actual execution plans are generated, the Transact-SQL queries or batches execute. The execution plan that is generated displays the actual query execution plan that the SQL Server Database Engine uses to execute the queries.

Execution Plan Formats

In SQL Server execution plan can be viewed in three different formats;

• Graphical Plan
• Text Plan
• XML Plan

Graphical Plan

These are quick and easy to read but the detailed data for the plan is masked. Both Estimated and Actual execution plans can be viewed in graphical format.

Text Plans

These are a bit harder to read, but more information is immediately available. There are three text plan formats:

• SHOWPLAN_ALL : a reasonably complete set of data showing the Estimated execution plan for the query
• SHOWPLAN_TEXT : provides a very limited set of data for use with tools like osql.exe. It too only shows the Estimated execution plan
• STATISTICS PROFILE: similar to SHOWPLAN_ALLexcept it represents the data for the Actual execution plan

XML Plans

XML plans present the most complete set of data available on a plan, all on display in the structured XML format. There are two varieties of XML plan:

• SHOWPLAN_XML : The plan generated by the optimizer prior to execution.
• STATISTICS_XML : The XML format of the Actual execution plan.

Now let's look at a query and how  it's execution plan looks like. I am using the AdventureWorks sample database for the examples.

Here I am writing a full select on person table. And you can see the Actual Execution Plan below in which it does a full Clustered Index Scan. This is because SQL Server has to return all the values in the table using the clustered index.

Let's write a query which will do a restrict (WHERE filter) to the primary key, now you can see it is using a Seek to the clustered index to get the value.

Scan operations are very costly and Seek operations are performance friends since they use Indexes to get information. Simply a Clustered index scan means a full table scan which is the most costly operation to retrieve data.

Now let's look at how a non-clustered index being used is shown in an execution plan. Below query is asking for the people who has PersonType attribute is set to 'EM'. In the table PersonType column does not have an index so the Query Optimizer has no any other option but to go for a full clustered index scan to get the results.

Now let's ask SQL Server to return people who has first name Alan. Person table has a composite index for FirstName, MiddleName and LastName. As you can see it uses a index scan to on that composite index first and then do a Key_Lookup on the clustered index to present the data.

Let's ask SQL Server to get the people who has name Dylan A Millar. In this scenario it uses an Index Seek on the composite index. Which is fully utilizing the index for optimal performance. In simple terms Index Seek is the most optimal way to access the data but in the previous scenario it had to scan through the whole index since the first name didn't have a  proper index.

In conclusion you must look at the execution plan when optimizing a bad performing query. If a Index is missing you can simply identify it by looking at the execution plan as explained above. We will be discussing more about reading different type of execution plans in the next chapter of this article series.

View SQL Server Plan Cache

The major reason we use Stored Procedures is because it allows SQL Server to cache the Execution plan so it can be reused. This gives a performance boost as the Query Optimizer doesn't have to go through the expensive steps of choosing the right query plan for the query.

But have you ever actually seen whether your stored procedure is actually has a plan cached in the server or whether it is been reused? There are quite a lot of scenarios Query Optimizer is forced to do a recompile of a Procedure which can be a huge performance hit. It is entirely a different topic for another article which I will write in the future. 

In this article I am going to show you how you can view whether a procedure is cached or not, how many times it has been reused, etc.This is the query which you can use to view these information;

SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'MissingDB') AS DBName, TEXT AS SQLSTMT 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

This will return below columns;

UseCounts	Number of times the plan has reused since caching
RefCounts	Number of times the other cached objects reused this plan
Cacheobjtype	Object type out of one of the following;                                                        Compiled Plan Parse Tree Extended Proc CLR Compiled Functions CLR Compiled Procedures
Objtype	Object type with ragards to the database (SP, View, Trigger,  etc)
DBName	Database name which the cached object belongs to
SQLSTMT	SQL Statement Cached

However execution plans are not kept in the cache forever. They removed from the cache if one of the following criteria's met;

• More memory is required by the system
• The "age" of the plan has reached zero
• The plan isn't currently being referenced by an existing connection

Aging is happens using a formula that multiplies the estimated cost of the plan by the number of times it has been used (UseCounts). There is a SQL Process called "Lazywriter" (Love the name) which is responsible for removing plans from the cache.


You can see this at work, first select a particular SP. Then simply restart the SQL Server Service and then run the above script. You wont see your SP in the cache, then run the SP. Then run the above script again, then you can see your SP is cached. Running again you will see the UseCounts column increases if your SP is not recompiling at each run.

This is a good way to be sure your SP is not recompiling as there are lot of situations where it does.