How important is it to understand SQL Server Wait Statistics??

最新推荐文章于 2017-06-10 14:10:00 发布

envykok

最新推荐文章于 2017-06-10 14:10:00 发布

阅读量177

点赞数

分类专栏： SQL 性能优化文章标签： sql server signal statistics application server thread

SQL 性能优化专栏收录该内容

35 篇文章 0 订阅

订阅专栏

http://www.orcsweb.com/blog/desiree/how-important-is-it-to-understand-sql-server-wait-statistics/

To be clear, it is very important to understand wait statistics. It is a key part of tracking down application and server related performance issues. If your application is having a performance issues or your server resource usage is high, this is will prove to be an important part of troubleshooting and resolving the issue.

About Wait Statistics

Sys.dm_os_wait_stats is a DMV that allows DBAs to track and troubleshoot requests that could not execute immediately. When a request comes into SQL Server that can't immediately execute, the system puts it into a wait state. The SQL Server engine internally tracks the time spent waiting, totals it, and retains it in memory. The sys.dm_os_wait_stats DMV displays information on waits that have completed. This DMV does not show current waits.

View definition (Column names and descriptions)

wait_type - Name of the wait type.

waiting_tasks_count - Number of waits on this wait type. This counter is incremented at the start of each wait.

wait_time_ms - Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.

max_wait_time_ms - Maximum wait time on this wait type.

signal_wait_time_ms - Difference between the time the waiting thread was signaled and when
it started running.

The resultset of the Sys.dm_os_wait_stats DMV is the values of the wait statistics that signifies the total waits for all processes that have finished since the counters were last reset. SQL Server resets the counters to 0 each time the SQL Server service is restarted. The command below will allow a manual reset of the values.

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

Each time the DMV is queried, a snapshot at that point in time is the resultset, which includes the total waits on the SQL server instance.

Understanding the Columns

The waiting_tasks_count column is an indication of how many times the processes have waited for that particular wait type.

In general, the most important column is the wait_time_ms column. It is an indication how many total milliseconds passed for each wait type. The longer the wait time, the less efficient the process becomes.

The max_wait_time_ms is self explanatory and gives more insight into the issue. It can help measure the severity of a particular wait type.

Another aspect of waits that is equally important is signal_wait_time. The time signal_wait_time is the elapse time from the time the resource is available to the actual use of the resource. Further, it is the time it took for the thread to be loaded from the queue onto the processor for processing. Note that it is also included in the overall wait time, which appears in the DMV's wait_time_ms column. A lot of signal waits indicate excessive CPU pressure, while fewer signal waits put the bottleneck on a specific wait type.

Find Bottlenecks

Run the DMV periodically to view the statistics. It is best to order the report in descending order of most to least waits. That will allow a quick view as to where the largest bottlenecks are and where to investigate problems to determine how to minimize waits.

Additional information on how to read the wait statistics:

Specific types of wait times while a query is executing can indicate bottlenecks or stall points within the query. Also, high wait times and/or wait counts on the server as a whole can indicate bottlenecks or hot spots in within query interactions on the server. For instance, lock waits indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout.