1 / 30

Inside SQL Server Wait Types

Inside SQL Server Wait Types. SQL 2005 and SQL 2008. Bob Ward Microsoft Corporation. Microsoft CSS at PASS 2009. Welcome to My World. =. All scripts will be available. Not the best of docs. What is a wait type?. 485 in SQL Server 2008. We created this to help us find bottlenecks

rcarlos
Download Presentation

Inside SQL Server Wait Types

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Inside SQL Server Wait Types SQL 2005 and SQL 2008 Bob Ward Microsoft Corporation

  2. Microsoft CSS at PASS 2009

  3. Welcome to My World = All scripts will be available

  4. Not the best of docs What is a wait type? 485 in SQL Server 2008 We created this to help us find bottlenecks In a galaxy, far, far, away we had locks, I/O and network But as time has moved on… we went a bit overboard The name of the type is up to the developer

  5. How does a wait type work?

  6. Let’s look at an example We know we need to wait Common for a SELECT Understands SQLOS scheduling Wait() results in SignalObjectAndWait() Ultimately it always comes down to WaitForSingleObject() or SignalObjectAndWait() • SOS_EventAuto is a wrapper for Windows Kernel Event object

  7. Where do wait types show up? Historical stats Live state legacy Tracing in 2008 In the tools Wait Statistics Counter

  8. Dive into Wait Types

  9. BUF latch - sync Common Wait Types Hint: System table or allocation Locks - sync Hint: Your app Hint: I/O delay Andrew Kelly’s talk on Capturing and Analyzing File & Wait Stats Resource Make up ~50 of the wait types Hint: Network or your app

  10. Some Waits may not be bottlenecks Should be called “not waiting” BOL calls these Queue Waits Normal for SQL CLR

  11. Busting the Myth of CXPACKET Sync Craig Freedman Talk is a must read Don’t jump to these

  12. The non BUFFER Latch Sync Same modes as BUFs (KP, SH, UP, EX, DT) Latch class

  13. FGCB_ADD_REMOVE latch Sync SQL Server Engine INSERT “I need to grow” LATCH_EX: FGCB_ADD_REMOVE Need space INSERT mydb.mdf Need space FGCB Autogrow Need space INSERT Moral of the story: Use instant file initialization but…it doesn’t work for the tlog Need space INSERT LATCH_SH: FGCB_ADD_REMOVE

  14. SOS_SCHEDULER_YIELD Forced I/O, Lock, Latch ************************ * * BEGIN STACK DUMP: * 10/17/09 15:51:52 spid 0 * * Non-yielding Scheduler * ************************ Could be preemptive thread(s)

  15. Resource THREADPOOL Applies to any task TDS Login Login Timeout Request = task + worker

  16. What about I/O Waits? Sync Log Writer COMMIT TRAN Flush Log Buffer WRITELOG Mylog.ldf Log Cache Log Buffer INSERT LOGBUFFER Request Log Buffer All buffers in use Copy model Resource Mylog.ldf and .mdf SQLTrace File Resource IO_COMPLETION Page I/O Sort I/O DISKIO_SUSPEND File VDI App Create database files Engine Workers ASYNC_IO_COMPLETION BACKUP WITH SNAPSHOT Zero Log Files Backup media Sync Resource

  17. Queries, Memory, and RESOURCE semaphores Resource Hashes and sorts compiles sys.dm_os_memory_brokers DBCC MEMORYSTATUS

  18. Pre-emptive Waits External May wrap more code than just the API ************************ * * BEGIN STACK DUMP: * 10/17/09 15:51:52 spid 0 * * Non-yielding Scheduler * ************************

  19. What are some I might see? ~190 of these

  20. Extended Events and Waits dm_xe_map_values Begin and End Duration, Total, Max

  21. There are other “waits” “Why can’t I truncate the log” PRECONNECT status A poorly written DLL You decide to throttle backoffs in sys.dm_os_spinlock_stats

  22. Where is THE LIST? The Wait Type Repository Blog

  23. Resources

  24. Appendix

  25. What does MDW tell you about I/O Waits sync reads, sorts, SQLTrace I/O, load CLR assembly Buffer Pool I/O for pages Backups, Recovery, DBM WRITELOG wait time = Log Flush Wait (perfmon) LOGBUFFER is just waiting on folks waiting on WRITELOG

  26. The mapping has changed KB 822101 wrong for 2005 and 2008

  27. What’s About These? Forced Resource External Sync Hot stored proc in SQL Server 2005

  28. Complete the Evaluation Form & Win! Sponsored by Dell • You could win a Dell Mini Netbook– every day – just for handing in your completed form! Each session form is another chance to win! Pick up your Evaluation Form: • Within each presentation room • At the PASS Booth near registration area Drop off your completed Form: • Near the exit of each presentation room • At the PASS Booth near registration area

  29. Visit the Microsoft Technical Learning Center Located in the Expo Hall Microsoft Ask the Experts Lounge Microsoft Chalk Talk Theater Presentations Microsoft Partner Village

  30. Thank you for attending this session and the 2009 PASS Summit in Seattle

More Related