Main Usage of sys.dm_os_wait_stats

Main Usage of sys.dm_os_wait_stats
4.5 (90%) 6 votes

As you know, the main responsibility of the database administrator lies in the monitoring of the SQL Server performance and intervening in determined time. You can find several SQL Server performance monitoring tools in the market but sometimes we need additional information about SQL Server performance to diagnosis and troubleshoot the performance issues. So we must have enough information about SQL Server Dynamic Management Views to handle issues about SQL Server.

Dynamic Management View (DMV) is a concept that helps us to discover SQL Server Engine performance metrics. DMV was first announced in SQL Server 2005 version and it continued in all versions of SQL Server afterward. In this post, we will talk about particular DMV whose database administrator must have enough information. This is sys.dm_os_wait_stats.

sys.dm_os_wait_stats

sys.dm_os_wait_stats supports essential metrics for diagnosing SQL Server performance problems. If you have some issues (CPU, Memory, I/O, Lock, Latch etc.) in SQL Server Engine, sys.dm_os_wait_stats data guides us to define the problem. Activity Monitor in SQL Server Management Studio includes a panel named as “Resource Waits”. “Resource Waits” gets these metrics from a special stored procedure. This temporary stored procedure name is “#am_generate_waitstats” and it uses sys.dm_os_wait_stats. You can find this temporary stored procedure in “tempdb”. At this point, I would like to add some notes about the temporary stored procedure. Because this type of stored procedure does not have a common usage.

 

sys_stats pic1

 

The temporary stored procedure does not differ from permanent stored procedures. It has two types: local and global like temporary tables. Local stored procedure stays active in the current session and is dropped after the session closes. It can be created like this:

The global stored procedure also stays active in all the sessions and dropped after the created session closes. The global stored procedure can be created as:

Tip: When we open the Activity Monitor, it creates the #am_generate_waitstats temporary stored procedure and drops it after the closing.

Now we will look into the main idea and details of sys.dm_os_wait_stats. The below query will return all data about SQL Server “Wait Statistics”. This query is in the purest form. It is inconvenient to detect issues with such form. In the following sections, you will find more useful queries then sys.dm_os_wait_stats. Now we will explain the description of the SQL Server “Wait Statistics” columns:

 

sys_stats pic2

 

The wait_type column contains the definition or name of wait statistics. wait_type column data is significant for us because the definition of wait statistics that indicates the main reason for the problem. waiting_tasks_count indicates the frequency of wait type occurred in SQL Server.

wait_time_ms indicates the total wait time. The unit of measure is a millisecond.

max_wait_time_ms indicates the maximum wait time.

signal_wait_time_ms is described in MSDN as “Difference between the time that the waiting thread was signaled and when it started running”. Especially high value of this column signs to CPU pressure. So the query is waiting in the runnable queue and ready for running but CPU is busy with other queries. For this reason, the query is waiting in the queue. When the CPU resource available, CPU will get a new query from the runnable queue and will start processing. In short, we can describe signal_wait_time_ms as waiting time in the runnable queue which is state turn runnable to running.

Tip: In the best practice, the several wait statistics are most important than the others. These can be listed as:

• PAGEIOLATCH_*
• WRITELOG
• ASYNC_NETWORK_IO
• CXPACKET
• CPU
• LCK_M_*
• PREEMPTIVE_*
• PAGELATCH_*

Take a look at the Pinal Dave or Paul S. Randal wait for statistics queries. They eliminated several wait statistic types in their queries. The below resource wait types can be eliminated in sys.dm_os_wait_stats queries:

• BROKER_EVENTHANDLER
• BROKER_RECEIVE_WAITFOR
• BROKER_TASK_STOP
• BROKER_TO_FLUSH
• BROKER_TRANSMITTER
• CHECKPOINT_QUEUE
• CHKPT
• CLR_AUTO_EVENT
• CLR_MANUAL_EVENT
• CLR_SEMAPHORE
• DBMIRROR_DBM_EVENT
• DBMIRROR_DBM_MUTEX
• DBMIRROR_EVENTS_QUEUE
• DBMIRROR_WORKER_QUEUE
• DBMIRRORING_CMD
• DIRTY_PAGE_POLL
• DISPATCHER_QUEUE_SEMAPHORE
• EXECSYNC
• FSAGENT
• FT_IFTS_SCHEDULER_IDLE_WAIT
• FT_IFTSHC_MUTEX
• HADR_CLUSAPI_CALL
• HADR_FILESTREAM_IOMGR_IOCOMPLETION
• HADR_LOGCAPTURE_WAIT
• HADR_NOTIFICATION_DEQUEUE
• HADR_TIMER_TASK
• HADR_WORK_QUEUE
• LAZYWRITER_SLEEP
• LOGMGR_QUEUE
• MEMORY_ALLOCATION_EXT
• ONDEMAND_TASK_QUEUE
• PREEMPTIVE_HADR_LEASE_MECHANISM
• PREEMPTIVE_OS_AUTHENTICATIONOPS
• PREEMPTIVE_OS_AUTHORIZATIONOPS
• PREEMPTIVE_OS_COMOPS
• PREEMPTIVE_OS_CREATEFILE
• PREEMPTIVE_OS_CRYPTOPS
• PREEMPTIVE_OS_DEVICEOPS
• PREEMPTIVE_OS_FILEOPS
• PREEMPTIVE_OS_GENERICOPS
• PREEMPTIVE_OS_LIBRARYOPS
• PREEMPTIVE_OS_PIPEOPS
• PREEMPTIVE_OS_QUERYREGISTRY
• PREEMPTIVE_OS_VERIFYTRUST
• PREEMPTIVE_OS_WAITFORSINGLEOBJECT
• PREEMPTIVE_OS_WRITEFILEGATHER
• PREEMPTIVE_SP_SERVER_DIAGNOSTICS
• PREEMPTIVE_XE_GETTARGETSTATE
• PWAIT_ALL_COMPONENTS_INITIALIZED
• PWAIT_DIRECTLOGCONSUMER_GETNEXT
• QDS_ASYNC_QUEUE
• QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
• QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
• QDS_SHUTDOWN_QUEUE
• REDO_THREAD_PENDING_WORK
• REQUEST_FOR_DEADLOCK_SEARCH
• RESOURCE_QUEUE
• SERVER_IDLE_CHECK
• SLEEP_BPOOL_FLUSH
• SLEEP_DBSTARTUP
• SLEEP_DCOMSTARTUP
• SLEEP_MASTERDBREADY
• SLEEP_MASTERMDREADY
• SLEEP_MASTERUPGRADED
• SLEEP_MSDBSTARTUP
• SLEEP_SYSTEMTASK
• SLEEP_TASK
• SP_SERVER_DIAGNOSTICS_SLEEP
• SQLTRACE_BUFFER_FLUSH
• SQLTRACE_INCREMENTAL_FLUSH_SLEEP
• SQLTRACE_WAIT_ENTRIES
• UCS_SESSION_REGISTRATIO
• WAIT_FOR_RESULTS
• WAIT_XTP_CKPT_CLOSE
• WAIT_XTP_HOST_WAIT
• WAIT_XTP_OFFLINE_CKPT_NEW_LOG
• WAIT_XTP_RECOVERY
• WAITFOR
• WAITFOR_TASKSHUTDOW
• XE_TIMER_EVENT
• XE_DISPATCHER_WAIT
• XE_LIVE_TARGET_TVF

Tip: SQL Server starts to collect DMV data when it starts or restarts. SQL Server automatically reset the wait statistics when it restarted and the below query forces to reset the wait statistics since SQL Server was last restarted:

Additionally, the measurement accuracy of wait statistics is the key point. At this point we can consider two approaches:

• Reset the wait statistics and recollect the wait statistics.

• Capture two different “time wait statistics” and measure the difference in values.

In my opinion, capturing and storing wait statistics for any history table approach is better than other. In this method, you can measure particularly time interval and do not loose wait statistics of historical data.

Now we will make a sample of the capturing of wait statistics and show captured data in Power BI with graphics.

We will create a history table to store wait statistics:

The below script inserts “wait statistics” to the history table. But you need to schedule this query in SQL Server Agent to store history table:

After the historical data has been stored, we open Power BI and develop our wait statistics dashboard.

Click Get Data and select SQL Server:

sys_stats pic3

 

Set the connection settings and then write the query to SQL statement (optional, requires a database). Click OK.

sys_stats pic4

 

sys_stats pic5

 

Click Import from Marketplace

 

sys_stats pic6

 

Find Sparkline by OKViz visual component and Add Power BI

sys_stats pic7

Add Sparkline to Power BI design panel and drag and drop dataset columns as in the below image:

 

sys_stats pic8

 

Add two table components to filter: SQLStartTime and WaitType. Finally, the dashboard should be like this:

 

sys_stats pic9

 

How to diagnosis resource wait issue:

In this section, we will mention the methodology and discipline of diagnosing wait statistics problems. Particularly, we have to figure out one point about wait statistics: it simply defines the main structure of the problem but never gives details. For this reason, we need to research wait details and reasons. Therefore, “wait statistics” allows us turning our research into this direction. After, we should use other tools (PerfMon, Extended Events, 3rd party monitoring tools, etc.) and other DMVs to define exact problems.

Assuming that, we observe ASYNC_NETWORK_IO in SQL Server, this resource wait is related to slow network connection from a client to server side. But this information does not help to troubleshoot the main reason of the problem because we may have several network configuration between server and client side. For this example we need to look:

• Large result sets queries

• Network Interface Card configurations

• Settings of network environment between server sides to the client side.

• Check network adapter bandwidth.

As you can see in the example, we need to complete some tasks to define the exact problem. Wait statistics do not indicate the target of the problem.

Conclusions

In this post, we talked about the main concept of the sys.dm_os_wait_stats dynamic management view. At the same time, we discussed the simplicity of usage and significant points, at which it is necessary to pay attention.

References

sys.dm_os_wait_stats (Transact-SQL)

CREATE PROCEDURE (Transact-SQL)

Working with tables in Power BI reports and dashboards

 

Useful products:

dbForge Monitoradd-in for Microsoft SQL Server Management Studio that allows you to track and analyse SQL Server performance.

Esat Erkeç

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.
Esat Erkeç

Latest posts by Esat Erkeç (see all)