the importance of wait statistics in sql server
TRANSCRIPT
The Importance of Wait Statistics in Monitoring SQL Server
Grant Fritchey
What is a Wait?
• SQL Server does work• All the work doesn’t occur simultaneously• When a unit of work stops• Threads
– RUNNING– SUSPENDED– RUNNABLE
• Time spent between RUNNING and RUNNING
Why Are Waits Important?
• Why is the server slow• Where is the server slow• Which resources are waiting frequently• Which resources are waiting long• Direct pointer to the cause of poor performance
Where Do I Get Waits
• Sys.dm_os_wait_stats• Sys.dm_db_wait_stats• sys.dm_os_latch_stats
Are Some Waits More Important?
• LATCH_*, PAGELATCH_*• PAGEIOLATCH_*, IO_COMPLETION, WRITELOG• CXPACKET• ASYNC_NETWORK_IO• RESOURCE_SEMAPHORE• BACKUPIO• Lots and lots more• signal_wait_time_ms
Are Some Waits Less Important?
• CXPACKET• WAITFOR• CLR_SEMAPHORE• BROKER_RECEIVE_WAITFOR• ONDEMAND_TASK_QUEUE• Lots and lots more
How Do I Use This Information?
• Filter out the unimportant waits• You know where your system bottlenecks are• Is it system related?• Is it query related?• Is it structure related?
How Do I Know Which Queries Are Waiting?• Sys.dm_os_waiting_tasks
How Do I Know Which Waits A Query Has?• Sys.dm_exec_requests• Extended Events: wait_info
What Happens To Waits Over Time?• DBCC SQLPERF(“sys.dm_os_wait_stats”,CLEAR)• DBCC SQLPERF(“sys.dm_os_latch_stats”,CLEAR)• Instance restart
DEMO: Wait Stats in SQL Monitor
SQL Monitor• Clear insights into SQL Server performance
in real time• Embedded expertise• Alerting• Baselines• Wait stats & expensive queries• Custom Metrics• SQL Monitor
SQL Monitor
• Read more experiences of SQL Monitor from real customers at: www.red-gate.com/products/dba/sql-monitor
• We use it to monitor SQLServerCentral, if you want to see how it works: http://monitor.red-gate.com
Its very simple to use and doesn’t drown me in unnecessary information. It came out on top for being reasonably priced and also functionality. We now have a stable SQL Estate due to SQL Monitor.
Nic Hopper, Senior DBA, Atrium Underwriters Ltd
Documents
• SQL Server Performance Tuning Using Wait Statistics• Performance Tuning Using Waits and Queues (old, but still good)• Wait statistics, or please tell me where it hurts – Paul Randal• What Queries Are Causing My Waits – John Sterret
Thank you for your attention
Q & A
