Muutke küpsiste eelistusi

E-raamat: Pro SQL Server 2019 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server

Teised raamatud teemal:
  • Formaat - PDF+DRM
  • Hind: 67,91 €*
  • * hind on lõplik, st. muud allahindlused enam ei rakendu
  • Lisa ostukorvi
  • Lisa soovinimekirja
  • See e-raamat on mõeldud ainult isiklikuks kasutamiseks. E-raamatuid ei saa tagastada.
Pro SQL Server 2019 Wait Statistics: A Practical Guide to Analyzing Performance in SQL ServerSuurem pilt
Teised raamatud teemal:

DRM piirangud

  • Kopeerimine (copy/paste):

    ei ole lubatud

  • Printimine:

    ei ole lubatud

  • Kasutamine:

    Digitaalõiguste kaitse (DRM)
    Kirjastus on väljastanud selle e-raamatu krüpteeritud kujul, mis tähendab, et selle lugemiseks peate installeerima spetsiaalse tarkvara. Samuti peate looma endale  Adobe ID Rohkem infot siin. E-raamatut saab lugeda 1 kasutaja ning alla laadida kuni 6'de seadmesse (kõik autoriseeritud sama Adobe ID-ga).

    Vajalik tarkvara
    Mobiilsetes seadmetes (telefon või tahvelarvuti) lugemiseks peate installeerima selle tasuta rakenduse: PocketBook Reader (iOS / Android)

    PC või Mac seadmes lugemiseks peate installima Adobe Digital Editionsi (Seeon tasuta rakendus spetsiaalselt e-raamatute lugemiseks. Seda ei tohi segamini ajada Adober Reader'iga, mis tõenäoliselt on juba teie arvutisse installeeritud )

    Seda e-raamatut ei saa lugeda Amazon Kindle's. 

Here is a practical guide for analyzing and troubleshooting SQL Server performance using wait statistics. Learn to identify precisely why your queries are running slowly. Measure the amount of time consumed by each bottleneck so that you can focus attention on making the largest improvements first. This edition is updated to cover analysis of wait statistics inside Query Store, the CXCONSUMER wait event, and to be current with SQL Server 2019. Whether you are new to wait statistics, or already familiar with them, this book provides a deeper understanding on how wait statistics are generated and what they can mean for your SQL Server instances performance. 

Pro SQL Server 2019 Wait Statistics goes beyond the most common wait types into the more complex and performance-threatening wait types. Youll learn about per-query wait statistics and session-based wait statistics, and the types of problems they each can help you solve. The different wait types are categorized by their area of impact, including CPU, IO, Lock, and many more.

The book presents clear examples to help you gain practical knowledge of why and how specific wait times increase or decrease, and how they impact your SQL Servers performance. After reading this book you wont want to be without the valuable information that wait statistics provide regarding where you should be spending your limited tuning time to maximize performance and value to your business. 







What You'll Learn





Identify resource bottlenecks in a running SQL Server instance

Locate wait statistics information inside DMVs and Query Store

Analyze the root cause of sub-optimal performance

Diagnose I/O contention and locking contention

Benchmark SQL Server performance

Lower the wait time of the most popular wait types

















Who This Book Is For

Database administrators who want to identify and resolve performance bottlenecks, those who want to learn more about how the SQL Server engine accesses and uses resources inside SQL Server, and administrators concerned with achievingand knowing they have achievedoptimal performance
About the Author xv
About the Technical Reviewers xvii
Acknowledgments xix
Introduction xxi
Part I Foundations of Wait Statistics Analysis
1(100)
Chapter 1 Wait Statistics Internals
3(22)
A Brief History of Wait Statistics
4(2)
The SQLOS
6(3)
Schedulers, Tasks, and Worker Threads
9(10)
Sessions
10(1)
Requests
11(1)
Tasks
12(1)
Worker Threads
13(3)
Schedulers
16(1)
Putting It All Together
17(2)
Wait Statistics
19(4)
Summary
23(2)
Chapter 2 Querying SQL Server Wait Statistics
25(38)
Sys.dm_os_wait_stats
26(3)
Sys.dm_os_waiting_tasks
29(4)
Understanding sys.dm_os_waiting_tasks
29(2)
Querying sys.dm_os_waiting_tasks
31(2)
Sys.dm_exec_requests
33(3)
Understanding sys.dm_exec_requests
33(2)
Querying sys.dm_exec_requests
35(1)
Sys.dm_exec_session_wait_stats
36(2)
Combining DMVs to Detect Waits Right Now
38(5)
Viewing Wait Statistics Using Perfmon
43(2)
Capturing Wait Statistics Using Extended Events
45(12)
Capture Wait Statistics Information for a Specific Query
46(11)
Analyzing Wait Statistics on a Per-Query Basis Using Execution Plans
57(4)
Summary
61(2)
Chapter 3 The Query Store
63(12)
What Is the Query Store?
63(1)
Query Store Architecture
64(1)
How Wait Statistics Are Processed in the Query Store
65(3)
Accessing Wait Statistics Through the Query Store Reports
68(2)
Accessing Wait Statistics Through the Query Store DMVs
70(3)
Summary
73(2)
Chapter 4 Building a Solid Baseline
75(26)
What Are Baselines?
76(5)
Visualizing Your Baselines
78(1)
Baseline Types and Statistics
79(2)
Baseline Pitfalls
81(1)
Too Much Information
81(1)
Know Your Metrics
81(1)
Focus on the Big Measurement Changes
81(1)
Use Fixed Intervals
82(1)
Building a Baseline for Wait Statistics Analysis
82(9)
Reset Capture Method
86(1)
Delta Capture Method
87(2)
Using SQL Server Agent to Schedule Measurements
89(2)
Wait Statistics Baseline Analysis
91(8)
Summary
99(2)
Part II Wait Types
101(272)
Chapter 5 CPU-Related Wait Types
103(36)
Cxpacket
103(11)
What Is the Cxpacket Wait Type?
104(3)
Lowering Cxpacket Wait Time by Tuning the Parallelism Configuration
107(4)
Lowering Cxpacket Wait Time by Resolving Skewed Workloads
111(1)
Introduction of the Cxconsumer Wait Type in SQL Server 2016 SP2 and 2017
112(1)
Cxpacket Summary
113(1)
Sos_Scheduler_Yield
114(9)
What Is the Sos_Scheduler_Yield Wait Type?
114(3)
Lowering Sos_Scheduler_Yield Waits
117(5)
Sos_Scheduler_Yield Summary
122(1)
Threadpool
123(16)
What Is the Threadpool Wait Type?
123(3)
Threadpool Example
126(4)
Gaining Access to Our SQL Server During Threadpool Waits
130(2)
Lowering Threadpool Waits Caused by Parallelism
132(2)
Lowering Threadpool Waits Caused by User Connections
134(3)
Threadpool Summary
137(2)
Chapter 6 IO-Related Wait Types
139(50)
Async_Io_CoMpletion
139(8)
What Is the Async_Io_Completion Wait Type?
140(1)
Async_Io_Completion Example
141(1)
Lowering Async_Io_Completion Waits
142(5)
Async_Io_Completion Summary
147(1)
Async_Networkjo
147(4)
What Is the Async_Network_Io Wait Type?
147(1)
Async_Network_Io Example
148(1)
Lowering Async_Network_Io Waits
149(2)
Async_Network_Io Summary
151(1)
Cmemthread
151(3)
What Is the Cmemthread Wait Type?
151(2)
Lowering Cmemthread Waits
153(1)
Cmemthread Summary
154(1)
Io_Completion
154(3)
What Is the Io_Completion Wait Type?
155(1)
Io_Completion Example
155(2)
Lowering Io_Completion Waits
157(1)
Io_Completion Summary
157(1)
Logbuffer and Writelog
157(6)
What Are the Logbuffer and Writelog Wait Types?
158(2)
Logbuffer and Writelog Example
160(2)
Lowering Logbuffer and Writelog Waits
162(1)
Logbuffer and Writelog Summary
163(1)
Resource_Semaphore
163(8)
What Is the Resource_Semaphore Wait Type?
163(2)
Resource_Semaphore Example
165(5)
Lowering Resource_Semaphore Waits
170(1)
Resource_Semaphore Summary
171(1)
Resource_Semaphore_Query_Compile
171(8)
What Is the Resource_Semaphore_Query_Compile Wait Type?
172(2)
Resource_Semaphore_Query_Compile Example
174(3)
Lowering Resource_Semaphore_Query_Compile Waits
177(1)
Resource_Semaphore_Query_Compile Summary
178(1)
Sleep_Bpool_Flush
179(7)
What Is the Sleep_Bpool_Flush Wait Type?
179(3)
Sleep_Bpool_Flush Example
182(3)
Lowering Sleep_Bpool_Flush Waits
185(1)
Sleep_Bpool_Flush Summary
185(1)
Write_Completion
186(3)
What Is the Write_Completion Wait Type?
186(1)
Write_Completion Example
186(1)
Lowering Write_Completion Waits
187(1)
Write_Completion Summary
187(2)
Chapter 7 Backup-Related Wait Types
189(14)
Backupbuffer
190(5)
What Is the Backupbuffer Wait Type?
190(3)
Backupbuffer Example
193(1)
Lowering Backupbuffer Waits
194(1)
Backupbuffer Summary
195(1)
Backupio
195(3)
What Is the Backupio Wait Type?
196(1)
Backupio Example
196(1)
Lowering Backupio Waits
197(1)
Backupio Summary
198(1)
Backupthread
198(5)
What Is the Backupthread Wait Type?
198(1)
Backupthread Example
199(1)
Lowering Backupthread Waits
200(1)
Backupthread Summary
201(2)
Chapter 8 Lock-Related Wait Types
203(32)
Introduction to Locking and Blocking
205(1)
Lock Modes and Compatibility
205(2)
Locking Hierarchy
207(1)
Isolation Levels
208(4)
Querying Lock Information
212(4)
LCK_M_S
216(4)
What Is the LCK_M_S Wait Type?
216(1)
LCK_M_S Example
217(1)
Lowering LCK_M_S Waits
218(1)
LCK_M_S Summary
219(1)
LCK_M_U
220(3)
What Is the LCK_M_U Wait Type?
220(2)
LCK_M_U Example
222(1)
Lowering LCK_M_U Waits
223(1)
LCK_M_U Summary
223(1)
LCK_M_X
223(3)
What Is the LCK_M_X Wait Type?
224(1)
LCK_M_X Example
224(1)
Lowering LCK_M_X Waits
225(1)
LCK_M_X Summary
226(1)
LCK_M_I[ xx]
226(4)
What Is the LCK_M_I[ xx] Wait Type?
226(1)
LCK_M_I[ xx] Example
227(2)
Lowering LCK_M_I[ xx] Waits
229(1)
LCK_M_I[ xx] Summary
229(1)
LCK_M_SCH_S and LCK_M_SCH_M
230(5)
What Are the LCK_M_SCH_S and LCK_M_SCH_M Wait Types?
230(1)
LCK_M_SCH_S and LCK_M_SCH_M Example
231(2)
Lowering LCK_M_SCH_S and LCK_M_SCH_M Waits
233(1)
LCK_M_SCH_S and LCK_M_SCH_M Summary
233(2)
Chapter 9 Latch-Related Wait Types
235(44)
Introduction to Latches
235(12)
Latch Modes
237(1)
Latch Waits
238(2)
Sys.dm_os_latch_stats
240(1)
Page-Latch Contention
241(6)
Pagelatch_[ xx]
247(11)
What Is the Pagelatch_[ xx] Wait Type?
247(1)
Pagelatch_[ xx] Example
248(4)
Lowering Pagelatch_[ xx] Waits
252(6)
Pagelatch_[ xx] Summary
258(1)
Latch_[ xx]
258(8)
What Is the LATCH_[ xx] Wait Type?
259(1)
Latch_[ xx] Example
259(6)
Lowering LATCH_[ xx] Waits
265(1)
Latch_[ xx] Summary
266(1)
Pageiolatch_[ xx]
266(13)
What Is the Pageiolatch_[ xx] Wait Type?
267(2)
Pageiolatch_[ xx] Example
269(1)
Lowering Pageiolatch_[ xx] Waits
270(7)
Pageiolatch_[ xx] Summary
277(2)
Chapter 10 High-Availability and Disaster-Recovery Wait Types
279(22)
Dbmirror_Send
280(7)
What Is the Dbmirror_Send Wait Type?
283(1)
Dbmirror_Send Example
283(2)
Lowering Dbmirror_Send Waits
285(1)
Dbmirror_Send Summary
286(1)
Hadr_Logcapture_Wait and Hadr_Work_Queue
287(3)
What Are the Hadr_Logcapture_Wait and Hadr_Work_Queue Wait Types?
287(3)
Hadr_Logcapture_Wait and Hadr_Work_Queue Summary
290(1)
Hadr_Sync_Commit
290(7)
What Is the Hadr_Sync_Commit Wait Type?
290(1)
Hadr_Sync_Commit Example
291(3)
Lowering Hadr_Sync_Commit Waits
294(3)
Hadr_Sync_Commit Summary
297(1)
Redo_Thread_Pending_Work
297(4)
What Is the Redo_Thread_Pending_Work Wait Type?
298(2)
Redo_Thread_Pending_Work Summary
300(1)
Chapter 11 Preemptive Wait Types
301(26)
SQL Server on Linux
302(3)
Preemptive_Os_Encryptmessage and Preemptive_Os_Decryptmessage
305(8)
What Are the Preemptive_Os_Encryptmessage and Preemptive_Os_Decryptmessage Wait Types?
305(1)
Preemptive_Os_Encryptmessage and Preemptive_Os_Decryptmessage Example
306(6)
Lowering Preemptive_Os_Encryptmessage and Preemptive_Os_Decryptmessage Waits
312(1)
Preemptive_Os_Encryptmessage and Preemptive_Os_Decryptmessage Summary
313(1)
Preemptive_Os_Writefilegather
313(3)
What Is the Preemptive_Os_Writefilegather Wait Type?
313(1)
Preemptive_Os_Writefilegather Example
314(1)
Lowering Preemptive_Os_Writefilegather Waits
315(1)
Preemptive_Os_Writefilegather Summary
316(1)
Preemptive_Os_Authenticationops
316(5)
What Is the Preemptive_Os_Authenticationops Wait Type?
317(1)
Preemptive_Os_Authenticationops Example
318(1)
Lowering Preemptive_Os_Authenticationops Waits
319(2)
Preemptive_Os_Authenticationops Summary
321(1)
Preemptive_Os_Getprocaddress
321(6)
What Is the Preemptive_Os_Getprocaddress Wait Type?
322(2)
Preemptive_Os_Getprocaddress Example
324(1)
Lowering Preemptive_Os_Getprocaddress Waits
325(1)
Preemptive_Os_Getprocaddress Summary
325(2)
Chapter 12 Background and Miscellaneous Wait Types
327(28)
Checkpoint_Queue
328(4)
What Is the Checkpoint_Queue Wait Type?
328(3)
Checkpoint_Queue Summary
331(1)
Dirty_Page_Poll
332(3)
What Is the Dirty_Page_Poll Wait Type?
332(3)
Dirty_Page_Poll Summary
335(1)
Lazywriter_Sleep
335(2)
What Is the Lazywriter_Sleep Wait Type?
335(2)
Lazywriter_Sleep Summary
337(1)
MSQL_XP
337(3)
What Is the MSQL_XP Wait Type?
337(1)
MSQL_XP Example
338(1)
Lowering MSQL_XP Waits
339(1)
MSQL_XP Summary
340(1)
OLEDB
340(2)
What Is the OLEDB Wait Type?
340(1)
OLEDB Example
340(1)
Lowering OLEDB Waits
341(1)
OLEDB Summary
342(1)
Tracewrite
342(9)
What Is the Tracewrite Wait Type?
343(1)
Tracewrite Example
343(5)
Lowering Tracewrite Waits
348(3)
Tracewrite Summary
351(1)
Waitfor
351(4)
What Is the Waitfor Wait Type?
352(1)
Waitfor Example
352(1)
Waitfor Summary
353(2)
Chapter 13 In-Memory OLTP-Related Wait Types
355(18)
Introduction to In-Memory OLTP
355(6)
CFPs
356(4)
Isolation
360(1)
Transaction Log Changes
360(1)
WAIT_XTP_HOST_WAIT
361(6)
What Is the WAIT_XTP_HOST_WAIT Wait Type?
361(5)
WAIT_XTP_HOST_WAIT Summary
366(1)
WAIT_XTP_CKPT_CLOSE
367(3)
What Is the WAIT_XTP_CKPT_CLOSE Wait Type?
367(3)
WAIT_XTP_CKPT_CLOSE Summary
370(1)
WAIT_XTP_OFFLINE_CKPT_NEW_LOG
370(3)
What Is the WAIT_XTP_OFFLINE_CKPT_NEW_LOG Wait Type?
370(2)
WAIT_XTP_OFFLINE_CKPT_NEW_LOG Summary
372(1)
Appendix I Example SQL Server Machine Configurations
373(4)
Default Test Machine
373(1)
HA/DR Test Machines
374(3)
Appendix II Spinlocks
377(4)
Appendix III Latch Classes
381(10)
Index 391
Enrico van de Laar has been working with data in various formats and sizes for over 15 years. He is a data and advanced analytics consultant for Dataheroes where he helps organizations optimize their data platform environment and helps them with their first steps in the world of advanced analytics. He is a Data Platform MVP since 2014 and a frequent speaker on various data-related events throughout the world. He frequently blogs about technologies such as Microsoft SQL Server and Azure Machine Learning on his blog at enricovandelaar.com. You can contact Enrico on Twitter @evdlaar.
Lisainfo e-raamatute kohta Lisainfo e-raamatute kohta
Püsilink: https://www.kriso.ee/db/97814842491612e.html
Märksõnad: