Wait Types

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Monitoring & Tuning - Wait Events

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!

Contents

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:

  • Resource waits are caused by a particular resource, perhaps a specific lock that is unavailable when the requested is submitted. Resource waits are the ones you should focus on for troubleshooting the large majority of performance issues.
  • External waits occur when SQL Server worker thread is waiting on an external process, such as extended stored procedure to be completed. External wait does not necessarily mean that the connection is idle; rather it might mean that SQL Server is executing an external code which it cannot control. Finally the queue waits occur if a worker thread is idle and is waiting for work to be assigned to it.
  • Queue waits normally apply to internal background tasks, such as ghost cleanup, which physically removes records that have been previously deleted. Normally you don't have to worry about any performance degradation due to queue waits.

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

  • Current SQL Server Activity - a replacement for SP_Who2 that checks active queries, waits one second, then checks again. For all active queries, it shows their command and what wait type is holding them up.

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.

Related Reading

Retrieved from "http://sqlserverpedia.com/wiki/Wait_Types"