analyzing sql server wait stats, hands-on!
TRANSCRIPT
![Page 1: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/1.jpg)
Analyzing SQL Server wait
stats
Discover why SQL Server is
waiting
![Page 3: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/3.jpg)
The Microsoft Certified Solutions Master (MCSM) and Microsoft Certified Master (MCM) programs recognize the most experienced IT professionals who demonstrate and validate their deep technical skills to successfully design and implement technical solutions in a complex business environment.
Microsoft Certified Solutions Expert (MCSE) certifications are designed to recognize IT professionals who can design and build solutions across multiple technologies, both on-premises and in the cloud.
The Microsoft Most Valuable Professional (MVP) Award is our way of saying thank you to exceptional, independent community leaders who share their passion, technical expertise, and real-world knowledge of Microsoft products with others.
Uwe Rickendb Berater GmbH
![Page 4: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/4.jpg)
AGENDA
• what is a WAIT?
• what is a QUEUE?
• when SQL Server waits
• common Wait Stats and reasons
![Page 5: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/5.jpg)
When SQL Server waits
depiction: http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/
![Page 6: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/6.jpg)
What is a „Wait“?
• A „wait“ occurs when a thread needs a resource or runs out of time
– A lock on a page prevents further access
– A data page must be read from the disk
– Results from a parallel query
– …
• A waiting thread runs into a “queue”
![Page 7: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/7.jpg)
What is a „Queue“?
• A „queue“ holds one or more waiting threads
• The “suspended” queue holds threads which are waiting for resources
• The “runnable” queue holds threads which are waiting for CPU time
• SQL Server provides DMV’s for the monitoring of the status
![Page 8: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/8.jpg)
When SQL Server waits
• sys.dm_os_wait_stats
– Information about waits which have been
executed!
• sys.dm_os_waiting_tasks
– Information about threads which are currently
waiting for resources.
![Page 9: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/9.jpg)
When SQL Server waitsrunning
suspendedrunnable
60: RUNNING
54: WAITFOR56: ASYNC_...68: LCK_M_S70: PAGELA..
55: RUNNABLE59: RUNNABLE72: RUNNABLE51: RUNNABLE
![Page 10: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/10.jpg)
When SQL Server waitsrunning
suspendedrunnable
55: RUNNING>4ms
54: WAITFOR56: ASYNC_...70: PAGELA..
59: RUNNABLE72: RUNNABLE51: RUNNABLE68: RUNNABLE
![Page 11: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/11.jpg)
When SQL Server waitsrunning
suspendedrunnable
59: RUNNING
54: WAITFOR56: ASYNC_...70: PAGELA..
72: RUNNABLE51: RUNNABLE68: RUNNABLE55: RUNNABLE
Quantum: 4msSOS_SCHEDULER_YIELD
![Page 12: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/12.jpg)
Most common wait types• CXPACKET
• ASYNC_NETWORK_IO
• ASYNC_IO_COMPLETION
• PAGELATCH_XX
• PAGEIOLATCH_XX
• SOS_SCHEDULER_YIELD
• WRITELOG
• THREADPOOL
![Page 13: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/13.jpg)
CXPACKET
• occurs when a parallel query is executing
– Examine the long-running parallel query and
tune it if necessary.
– To ensure “efficient” parallelism, check that the
statistics on tables and indexes used by the
query are up to date.
![Page 14: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/14.jpg)
CXPACKET
Transaction
1.000 records
1.000 records
1.000 records
1.000 records
CONTROLLER THREAD
![Page 15: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/15.jpg)
Demo#SQLintheCityUS
![Page 16: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/16.jpg)
ASYNC_NETWORK_IO
• Your application code may not be
retrieving data correctly!
• There are very large data sets being
requested by the client!
• An application is requesting large amounts
of data!
![Page 17: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/17.jpg)
Demo#SQLintheCityUS
![Page 18: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/18.jpg)
ASYNC_IO_COMPLETION
• This occurs when SQL Server is waiting for asynchronous I/O operations to finish
• Look for additional waits in the top ranking
– IO_COMPLETION
– LOGMGR
– WRITELOG
– PAGEIOLATCH_*
![Page 19: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/19.jpg)
Demo#SQLintheCityUS
![Page 20: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/20.jpg)
PAGEIOLATCH_??
• Buffer Latch for a page object on the disk
– DT (destroy)
– EX (exclusive)
– KP (keep)
– SH (shared)
– UP (update)
![Page 21: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/21.jpg)
Demo#SQLintheCityUS
![Page 22: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/22.jpg)
PAGELATCH_??
• PAGELATCH_* latches are lightweight,
non-configurable internal locks used by
SQL Server’s storage engine to protect
concurrent access to pages in the
BUFFER POOL
![Page 23: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/23.jpg)
Demo#SQLintheCityUS
![Page 24: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/24.jpg)
SOS_SCHEDULER_YIELD
• This common wait isn’t necessarily a
problem
• It indicates that SQL Server CPU
scheduling is working efficiently.
– RUNNING | SUSPENDED | RUNNABLE
![Page 25: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/25.jpg)
Demo#SQLintheCityUS
![Page 26: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/26.jpg)
WRITELOG
• This wait occurs when data in the log cache is
being flushed to disk.
• Whenever a data page is updated, it’s written
to the buffer cache and the log cache.
• Data in the log cache is then written to the
transaction log file on the physical disk once
the transaction is complete.
![Page 27: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/27.jpg)
Demo#SQLintheCityUS
![Page 28: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/28.jpg)
THREADPOOL• SQL Server assigns a scheduler to each CPU
core to manage internal threads.
• Some of these threads are worker threads that are assigned to query requests on a one-to-one basis.
• If every worker thread is busy, additional requests can't be assigned straight away.
• SQL Server attempts to manage requests more efficiently by creating a pool of worker threads, so the next available worker thread from the pool will pick up the task.
• If every worker thread remains busy, SQL Server can't accept new requests and will hang.
Cores 32 bit 64 bit
<=4 256 512
5 – 8 288 576
9 – 16 352 704
32 480 960
![Page 29: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/29.jpg)
Demo#SQLintheCityUS
![Page 30: Analyzing SQL Server wait stats, hands-on!](https://reader033.vdocuments.us/reader033/viewer/2022042706/589a5f421a28abc3438b59fd/html5/thumbnails/30.jpg)
Want to try the tools you’ve just seen?Head to the Hands-on Labs
#SQLintheCityUS