When your SQL Server is not performing as well as you would like, the fastest way to find out what’s wrong is to look at the wait statistics available in the dynamic management view sys.dm_os_wait_stats. At the bottom of this article we will give you the code to query this DMV to see what events are causing your SQL Server to wait too long or too frequently. But first we will explain a bit about wait states and wait statistics
SQL Server is always waiting for something, a disk io, a place in the processor queue, a lock, etc. The SQL Server engine keeps track of the duration and type of these waits and makes the aggregated results available through sys.dm_os_wait_stats. It is simple to query this data management view to find which wait types are causing SQL Server to wait the most.
Identifying the wait types that have caused SQL Server to slow down is the first step in solving your performance problems. Unfortunately, the second step is more difficult. This step requires learning what these wait states mean and how the problems they cause can be fixed.
This step is difficult because there are 639 different wait states in SQL Server 2012. That is over a hundred more than were in the previous SQL Server version and we can assume that there will be new ones added in future versions.
Fortunately, there are only a relatively few wait states that commonly cause performance problems. If you learn something about the eight most common wait states, you will be well on your way to using wait statistics for performance tuning. These common waits are the ones that we will discuss in this article. We will describe what causes them and where possible, how to fix them. However, we also include a link below to a full reference on wait states for the times when you run into one that you don’t understand.
Here is our summary of common wait states:
This wait state is associated with query parallelism . You will always see some number of these waits unless you have disabled query parallelism. Unless CXPACKET constitutes a very high percentage of total wait time, it usually does not mean it is a performance problem. If necessary, you can play with the max degree of parallelism setting. In rare cases you might even want to turn query parallelism off by setting max degree of parallelism to 1.
All the variations of PAGEIOLATCH waits usually indicate a problem with the disk subsystem. However, we should not assume that a lot of these waits mean your disk subsystem is inadequate for the load. It might well be that poorly written code, poor indexing or lack of sufficient memory to cache data is causing the io subsystem to work much harder than it should normally have to.
The name is deceptive. It is rare that you find this to be a network problem. Most often it is a problem with the application consuming data very slowly. This can be triggered by poor code or by queries that return an enormous amount of data for the application to process. Often the fix is to optimize your poorly performing code.
In most cases this is an indication that the disk subsystem serving the log file is inadequate for the task.
Most often this wait is triggered by communication between linked servers. However, linked servers are not the only things that use OLEDB to communicate. Since data management views also use OLEDB the problem could be a 3rd party monitoring tool that is making heavy use of DMVs to gather performance data.
This wait is often a symptom of blocking. However the blocking itself can be caused by any number of different things. High on that list is inefficient code and indexing.
Without going into a great deal of complex explanation, it is probably enough to say that this is an indication of pressure on your cpu resources. You need to find and fix code that is using too much cpu as well as other conditions that use a lot of cpu. If high values for this wait state persist, you might consider more or faster processors.
This wait occurs when SQL Server is attempting to latch a page in memory. Often this is caused by a well-known issue in tempdb when a high number of processes are trying to access the allocation pages in the tempdb file at the same time. If your tempdb is heavily used, this may be the reason. Check out this link for a possible fix
If you need to know something about a wait state that does not appear above, Bob Ward at Microsoft maintains a web page that includes descriptions of many more wait states. You should be able to find what you need there.
Paul Randal has written my favorite wait state query. It removes irrelevant waits like internal processes and benign waits that are not a cause for concern. This query makes it easy to see the true state of affairs on your server.
Now that you are armed with the proper tools you should be able to diagnose problems in SQL Server very rapidly.