waits and queues and you€¦ · what about dba? 7 . learning to dba 8 . ... what does all that...
TRANSCRIPT
![Page 1: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/1.jpg)
1
Waits and Queues and You Thomas LaRock
Senior DBA, Confio Software
![Page 2: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/2.jpg)
Who Am I?
@SQLRockstar http://thomaslarock.com
![Page 3: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/3.jpg)
Learning to Drive
3
![Page 4: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/4.jpg)
Learning to Drive
Gas gauge
4
![Page 5: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/5.jpg)
Learning to Drive
Gas gauge
Oil light
5
![Page 6: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/6.jpg)
Learning to Drive
Gas gauge
Oil light
Odd engine noises
6
![Page 7: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/7.jpg)
Learning to Drive
Gas gauge
Oil light
Odd engine noises
What about DBA?
7
![Page 8: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/8.jpg)
Learning to DBA
8
![Page 9: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/9.jpg)
Learning to DBA
Red/green icons
9
![Page 10: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/10.jpg)
Learning to DBA
Red/green icons
Phone ringing
10
![Page 11: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/11.jpg)
SQL Server is a Black Box
11
INPUT OUTPUT
![Page 12: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/12.jpg)
Car Engine
Oil changes
Tune ups
Alignments
Brakes
12
![Page 13: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/13.jpg)
SQLOS
13
![Page 14: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/14.jpg)
14
![Page 15: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/15.jpg)
SQLOS
15
![Page 16: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/16.jpg)
Agenda
Describe Execution Model
16
![Page 17: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/17.jpg)
Agenda
Describe Execution Model
Use some DMVs
17
![Page 18: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/18.jpg)
Agenda
Describe Execution Model
Use some DMVs
Show why we measure waits (use case)
18
![Page 19: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/19.jpg)
Execution Model Example
19
![Page 20: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/20.jpg)
Your Tax Dollars at Work
20
![Page 21: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/21.jpg)
Your Tax Dollars at Work
21
![Page 22: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/22.jpg)
22
![Page 23: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/23.jpg)
23
![Page 24: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/24.jpg)
24
![Page 25: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/25.jpg)
25
![Page 26: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/26.jpg)
26
Execution Model (TSA)
Passport checked
Assigned a lane
Put stuff on belt
Head to gate
Freedom Pat
![Page 27: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/27.jpg)
27
Execution Model (SQLOS)
Init
Runnable
Running
Done
Waiting
![Page 28: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/28.jpg)
28
CPU 1
SPID 77 – Running
CPU 1 Queue
SPID 81 – Runnable
SPID 91 – Runnable
Waiter Queue
SPID 62 – ASYNC_NETWORK_IO
SPID 63 – CXPACKET
SPID 67 – PAGEIOLATCH_SH
SPID 74 – LCK_M_S
SPID 79 – LATCH_EX
SPID 87 – WRITELOG
Execution Model (SQLOS)
![Page 29: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/29.jpg)
29
CPU 1
SPID 77 – Running
CPU 1 Queue
SPID 81 – Runnable
SPID 91 – Runnable
SPID 67 – Runnable
Execution Model (SQLOS)
Waiter Queue
SPID 62 – ASYNC_NETWORK_IO
SPID 63 – CXPACKET
SPID 67 – PAGEIOLATCH_SH
SPID 74 – LCK_M_S
SPID 79 – LATCH_EX
SPID 87 – WRITELOG
![Page 30: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/30.jpg)
30
CPU 1
SPID 77 – Running
CPU 1 Queue
SPID 81 – Runnable
SPID 91 – Runnable
SPID 67 – Runnable
Execution Model (SQLOS)
Waiter Queue
SPID 62 – ASYNC_NETWORK_IO
SPID 63 – CXPACKET
SPID 74 – LCK_M_S
SPID 77 – LCK_M_IX
SPID 79 – LATCH_EX
SPID 87 – WRITELOG
![Page 31: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/31.jpg)
31
CPU 1
SPID 81 – Running
CPU 1 Queue
SPID 81 – Runnable
SPID 91 – Runnable
SPID 67 – Runnable
Execution Model (SQLOS)
Waiter Queue
SPID 62 – ASYNC_NETWORK_IO
SPID 63 – CXPACKET
SPID 74 – LCK_M_S
SPID 77 – LCK_M_IX
SPID 79 – LATCH_EX
SPID 87 – WRITELOG
![Page 32: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/32.jpg)
32
CPU 1
SPID 77 – Running
CPU 1 Queue
SPID 81 – Runnable
SPID 91 – Runnable
Waiter Queue
SPID 62 – ASYNC_NETWORK_IO
SPID 63 – CXPACKET
SPID 74 – LCK_M_S
SPID 77 – LCK_M_IX
SPID 79 – LATCH_EX
SPID 87 – WRITELOG
Execution Model (SQLOS)
![Page 33: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/33.jpg)
33
CPU 1
SPID 77 – Running
CPU 1 Queue
SPID 81 – Runnable
SPID 91 – Runnable
Waiter Queue
SPID 62 – ASYNC_NETWORK_IO
SPID 63 – CXPACKET
SPID 74 – LCK_M_S
SPID 77 – LCK_M_IX
SPID 79 – LATCH_EX
SPID 87 – WRITELOG
Execution Model (SQLOS)
![Page 34: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/34.jpg)
34
CPU 1
SPID 77 – Running
CPU 1 Queue
SPID 81 – Runnable
SPID 91 – Runnable
Waiter Queue
SPID 62 – ASYNC_NETWORK_IO
SPID 63 – CXPACKET
SPID 74 – LCK_M_S
SPID 77 – LCK_M_IX
SPID 79 – LATCH_EX
SPID 87 – WRITELOG
Execution Model (SQLOS)
![Page 35: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/35.jpg)
35
Execution Model (DMVs)
Init
Runnable
Running
Done
Waiting
![Page 36: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/36.jpg)
36
Execution Model (DMVs)
sys.dm_exec_sessions
sys.dm_exec_requests
= ‘runnable’
sys.dm_exec_requests
= ‘running’
sys.dm_os_waiting_tasks
sys.dm_exec_connections
![Page 37: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/37.jpg)
Demo
37
DEMO
![Page 38: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/38.jpg)
38
1.
2.
3.
4.
5.
Sample Wait Types
![Page 39: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/39.jpg)
39
1.
2.
3.
4.
5. LCK_M_S, LCK_M_U, LCK_M_X…
Sample Wait Types
![Page 40: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/40.jpg)
40
1.
2.
3.
4.
5. LCK_M_S, LCK_M_U, LCK_M_X…
• Waiting to acquire locks
Sample Wait Types
![Page 41: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/41.jpg)
41
1.
2.
3.
4. PAGEIOLATCH_SH, PAGEIOLATCH_EX…
5. LCK_M_S, LCK_M_U, LCK_M_X…
• Waiting to acquire locks
Sample Wait Types
![Page 42: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/42.jpg)
42
1.
2.
3.
4. PAGEIOLATCH_SH, PAGEIOLATCH_EX…
• Physical disk
5. LCK_M_S, LCK_M_U, LCK_M_X…
• Waiting to acquire locks
Sample Wait Types
![Page 43: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/43.jpg)
43
1.
2.
3. WRITELOG
4. PAGEIOLATCH_SH, PAGEIOLATCH_EX…
• Physical disk
5. LCK_M_S, LCK_M_U, LCK_M_X…
• Waiting to acquire locks
Sample Wait Types
![Page 44: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/44.jpg)
44
1.
2.
3. WRITELOG
• Waiting for a log flush to complete
4. PAGEIOLATCH_SH, PAGEIOLATCH_EX…
• Physical disk
5. LCK_M_S, LCK_M_U, LCK_M_X…
• Waiting to acquire locks
Sample Wait Types
![Page 45: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/45.jpg)
45
1.
2. CXPACKET
3. WRITELOG
• Waiting for a log flush to complete
4. PAGEIOLATCH_SH, PAGEIOLATCH_EX…
• Physical disk
5. LCK_M_S, LCK_M_U, LCK_M_X…
• Waiting to acquire locks
Sample Wait Types
![Page 46: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/46.jpg)
46
1.
2. CXPACKET
• Parallelism
3. WRITELOG
• Waiting for a log flush to complete
4. PAGEIOLATCH_SH, PAGEIOLATCH_EX…
• Physical disk
5. LCK_M_S, LCK_M_U, LCK_M_X…
• Waiting to acquire locks
Sample Wait Types
![Page 47: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/47.jpg)
47
1. ASYNC_NETWORK_IO, NETWORKIO
2. CXPACKET
• Parallelism
3. WRITELOG
• Waiting for a log flush to complete
4. PAGEIOLATCH_SH, PAGEIOLATCH_EX…
• Physical disk
5. LCK_M_S, LCK_M_U, LCK_M_X…
• Waiting to acquire locks
Sample Wait Types
![Page 48: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/48.jpg)
48
1. ASYNC_NETWORK_IO, NETWORKIO
• Waiting on the network
2. CXPACKET
• Parallelism
3. WRITELOG
• Waiting for a log flush to complete
4. PAGEIOLATCH_SH, PAGEIOLATCH_EX…
• Physical disk
5. LCK_M_S, LCK_M_U, LCK_M_X…
• Waiting to acquire locks
Sample Wait Types
![Page 49: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/49.jpg)
Why Measure Waits?
Think about how you currently resolve performance issues, does it look like this…?
49
![Page 50: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/50.jpg)
Why Measure Waits?
Think about how you currently resolve performance issues, does it look like this…?
50
![Page 51: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/51.jpg)
Why Wait Time Analysis Rocks
Allows you to focus on *the* problem
• Four possible resource bottlenecks
51
![Page 52: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/52.jpg)
Why Wait Time Analysis Rocks
Allows you to focus on *the* problem
• Four possible resource bottlenecks
Waits help you to answer the BIG question:
• “Is this a problem?”
52
![Page 53: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/53.jpg)
Why Wait Time Analysis Rocks
Allows you to focus on *the* problem
• Four possible resource bottlenecks
Waits help you to answer the BIG question:
• “Is this a problem?”
Helps to prioritize
• Fix the ones you can fix now
53
![Page 54: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/54.jpg)
Why Wait Time Analysis Rocks
Allows you to focus on *the* problem
• Four possible resource bottlenecks
Waits help you to answer the BIG question:
• “Is this a problem?”
Helps to prioritize
• Fix the ones you can fix now
Does not rely on Health Stats alone
• Perfmon counters only?
54
![Page 55: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/55.jpg)
55
Use Case (or, My Story)
![Page 56: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/56.jpg)
How To Measure?
3rd party tools
Native tools
• DMVs
• Perfmon
• xEvents
• MDW/UCP
56
![Page 57: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/57.jpg)
Know What You Are Measuring
Know difference between “wait” and “queue”
• Wait – Any time a session is waiting
• Queue – A measure of system resource and utilization (think perfmon)
57
![Page 58: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/58.jpg)
Know What You Are Measuring
Know difference between “wait” and “queue”
• Wait – Any time a session is waiting
• Queue – A measure of system resource and utilization (think perfmon)
Difference between OLTP and OLAP
• Which one has more ad-hoc queries?
• Which one is more likely to see parallelism?
• Which one is likely to see more blocking/locking?
58
![Page 59: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/59.jpg)
Know What You Are Measuring
Know difference between “wait” and “queue”
• Wait – Any time a session is waiting
• Queue – A measure of system resource and utilization (think perfmon)
Difference between OLTP and OLAP
• Which one has more ad-hoc queries?
• Which one is more likely to see parallelism?
• Which one is likely to see more blocking/locking?
What does all that mean to the DBA?
59
![Page 60: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/60.jpg)
Operating With Precision
60
![Page 61: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/61.jpg)
Thanks!
THANK YOU CLEVELAND!
NEXT STOP: ST. LOUIS!
61
![Page 62: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/62.jpg)
Questions?
62
![Page 63: Waits and Queues and You€¦ · What about DBA? 7 . Learning to DBA 8 . ... What does all that mean to the DBA? 59 . Operating With Precision 60 . Thanks! THANK YOU CLEVELAND! NEXT](https://reader033.vdocuments.us/reader033/viewer/2022042805/5f62b1a4c7aef001c93a1a2b/html5/thumbnails/63.jpg)
For More Information
http://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx
http://technet.microsoft.com/en-us/sqlserver/gg508897.aspx
http://sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
https://sqlserverperformance.wordpress.com/
http://speakerrate.com/speakers/3000-sqlrockstar
63