SQL SERVER – What are Wait Types, Wait Stats and its Importance

Earlier last month Solid Quality India announced SQL Server Health Check Service and since then, it has got very good response from the industry. However, the only question we are be asked all the time is: “What is “SQL Server Resource Wait Stats Analysis” and how can it be useful?”What caught my attention is that it seems everyone understood what the other details on the page mean, but most of them have a query regarding Wait Stats and their importance.

For such a long time, even I wasn’t sure what Wait Stats are. Later on, I learned Wait Stats from Andrew Kelly of Solid Quality Mentors and now I feel very comfortable using them. Basically, wait types are nothing but wait performed to execute any task created by SQL Server. As per BOL, there are three types of wait types, namely:

Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker, or it’s not yet available.
Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
External Waits. External waits occur when an SQL Server worker is waiting for an external event.
To check the wait types for any server, just run the following simple statistics:

SELECT *
FROM sys.dm_os_wait_stats

You can get the Wait Stats and identify which of the Wait Stats is causing the issue that troubles you.

The link to Book On Line where you can read all the Wait Stats is over here: SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from .NET Info