This article is the Collaboration of the Month for February 2010. Find out how it can be improved, read how to edit articles, then jump in to make this an article we can be proud of!
What Are SQL Server Waits?
Instead of measuring activity of CPU, storage, or memory, why not ask what SQL Server has been waiting on when executing queries? Starting with SQL Server 2005, some of SQL Server's Dynamic Management Views (DMVs) return wait data - measurements of what the database engine has been waiting on.
In general there are three categories of waits that could affect any given request:
You should expect some waits on a busy system. This is completely normal and doesn't necessarily translate into a performance issue. Wait events become a problem if they tend to be consistently long over a significant period of time. For example, waits that take few milliseconds over a 2 hour monitoring window are not concerning. Those waits taking over 15 minutes over a 2 hour monitoring window should be investigated more closely.
Queries to Check SQL Server Waits
Want to add more queries here? Go to the Transact SQL Code Library, click Edit, and add a new link on that page to describe your query. Just copy/paste one of the other links and edit it. After you save the page, your newly created link will appear red. You can click on it to edit a new page. Then come back here and add a link to it.
Explanations of SQL Server Wait Types
Some of these waits occur for internal operations and no tuning is necessary to avoid such waits - we identify those as well. Some of the following have more than one wait type. If you're looking for QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX, for example, click on the QUERY_NOTIFICATION_* group and each of the underlying waits will be listed there.