Inside SQL Server Wait Types Bob Ward Microsoft Corporation SQL 2005 and SQL 2005 and SQL 2008 SQL 2008 Microsoft CSS at PASS 2009 Pre-Conference Seminar Tackling Top Reporting Services Issues Mon 11/2 8:30am-4:30pm Adam Saxton Main Conference Talks (DBA-500-SC) Inside SQL Server Wait Types Tues 11/3 10:15 11:45am 3AB Bob Ward SQL Server Clinic Room 611 11/3 11/5/2009 After Keynote 6:00pmish (DBA-X69-C) Implementing and Supporting SQL 2008 Failover Clustering Tues 11/3 1:30-2:45pm 4C1-2
Shon Hauck (BIA-X45-C) Top customer support issues in Analysis Services Wed 11/4 1:30-2:45pm 2AB John Sirmon (AD-X43-C ) Troubleshooting applications accessing SQL Server Thurs 11/5 1:00-2:15pm 613-614 Abirami Iyer and Lakshmi Jonnakuti 2 3 All scripts will be available Stay for questions as Stuff you can use long as you want This means your brain may hurt =
We will move DMVs, Code, fast and Debugger, and furious APIs I assume SQL knowledge This is a 500 level talk Welcome to My World What is a wait type? Not the best Not the best of docs of docs 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 485 in SQL Server 2008 The name of the type is up to the developer Background tasks
Queue Preemption External Yield or Sleep Forced Locks, Latches, and bunch of others Synchronization I/O, Network, Thread, Memory Resource 4 How does a wait type work? Developer writes code that runs Anyone querying the DMVS sees the wait type and accumulated wait time Code is signaled to
wake-up Developer knows they might execute code that waits Code saves last wait type Code clears wait type, time, and last wait type Developer sets a wait type Developer calls SQLOS routines to wait 5 Lets look at an example Common for a Common for a SELECT SELECT Request LCK_M_IS (Shared Intent) lock
Understands SQLOS scheduling Wait() results in SignalObjectAndWait() We know we need to wait A conflict exists Setup a SOS_WaitInfo with LCK_M_IS Use SOS_EventAuto class to wait Call LockOwner::Sleep Ultimately it always comes down to WaitForSingleObject() or SignalObjectAndWait() SOS_EventAuto is a SOS_EventAuto is a wrapper for Windows wrapper
forEvent Windows Kernel object Kernel Event object 6 Where do wait types show up? sys.dm_os_wait_stats sys.dm_exec_requests sys.dm_os_waiting_tasks sys.sysprocesses Extended Events Management Data Warehouse Activity Monitor Performance Monitor Counters Historical stats Historical stats Live state Live state legacy legacy Tracing in 2008 Tracing in 2008 In the tools In the tools Wait Statistics Wait Statistics Counter Counter
7 Dive into Wait Types 8 Common Wait Types Hint: Your app Locks sync LCK_XX Hint: System table or allocation BUF latch - sync PAGELATCH and PAGEIOLATCH Hint: I/ O delay Andrew Kellys talk on Capturing and Analyzing File & Wait Stats Resource
Make up ~50 of the wait types ASYNC_NETWORK_IO Hint: Network or your app 9 Some Waits may not be bottlenecks MISCELLANEOUS Should be called not Should be called not waiting waiting Background Task Waits LAZYWRITER_SLEEP BOL calls these BOL calls these SQLTRACE_BUFFER_FLUSH Queue Waits Queue Waits CHECKPOINT_QUEUE
REQUEST_FOR_DEADLOCK_SEARCH CLR_AUTO_EVENT Normal for SQL Normal for SQL CLR CLR 10 Busting the Myth of CXPACKET Craig Freedman Talk Craig Freedman Talk is a must read is a must read Used to synchronize parallel query workers Just means you have a parallel query Do you expect parallel queries? Do you have high wait times? wait_resource shows coordination Sync
High wait times mean long running parallel queries What Should I Do? Look at the Tasks You may not need to do anything Which one is not CXPACKET? Some other wait may be the issue Find queries and tune them Use the MAXDOP query hint Modify max degree of parallelism Dont jump to these 11 12 sys.dm_os_latch_stats sys.dm_exec_requests.wait_resource
Latch class Same modes as BUFs (KP, SH, UP, EX, DT) As opposed to PAGELATCH or PAGEIOLATCH Not just for BUFs Thread sync of memory structure How many are there? Appears as LATCH_XX Latch can be generic The non BUFFER Latch Sync FGCB_ADD_REMOVE latch Sync SQL Server Engine INSERT Need space INSERT
Need space INSERT Need space INSERT Need space I need to grow I need to grow LATCH_EX: LATCH_EX: FGCB_ADD_REMOVE FGCB_ADD_REMOVE FGCB LATCH_SH: LATCH_SH: FGCB_ADD_REMOVE FGCB_ADD_REMOVE Autogrow mydb.mdf
Moral of the Moral of the story: Use story: Use instant file initi instant file initi alization alization butit doesnt butit doesnt work for the work for the tlog tlog 13 SOS_SCHEDULER_YIELD Forced I/O, Lock, Latch A task that does not naturally wait must yield
What if we dont do this right? ************************ * * BEGIN STACK DUMP: * 10/17/09 15:51:52 spid 0 * * Non-yielding Scheduler * ************************ Examples No I/O needed for pages T-SQL variables only or just expressions Query compile Small hashes and sorts Indicators High count CPU intensive query High wait time CPU intensive queries competing or someone not yielding very well Could be
Could be preemptive preemptive thread(s) thread(s) 14 THREADPOOL Resource Applies to any task TDS Login Receive TDS packet Engine creates SQLOS Task Find available worker on scheduler If none, set THREADPOOL wait type Next available worker runs
task Login Timeout These are pure victims Only seen in stats and tasks Request Request = task + = task + worker worker You may need DAC to see it live PENDING tasks and work_queue_count in schedulers > 0 Look for other waits Often a long blocking chain DO NOT assume you need more worker threads 15 What about I/O Waits?
COMMIT COMMIT TRAN TRAN INSERT INSERT Copy model SQLTrace File Sort I/O Create database files Zero Log Files Sync Flush Log Buffer Log Writer WRITELOG LOGBUFFER Request Log Buffer Resource IO_COMPLETION File ASYNC_IO_COMPLETION Resource Log Log
Buffer Buffer Log Cache Mylog.ldf All buffers in use Resource Page I/O Mylog.ldf and .mdf DISKIO_SUSPEND Engine Workers VDI App BACKUP WITH SNAPSHOT Backup Sync media 16
17 Why are you compiling so much? Factor of limited memory or memory hungry compiles Throttled on a system of levels (gateways) with thresholds High Query Memory lowers thresholds Not often seen on 64bit systems sys.dm_os_memory_brokers sys.dm_os_memory_brokers DBCC MEMORYSTATUS DBCC MEMORYSTATUS Limited memory or too many concurrent users MEMORYCLERK_SQLQUERYEXEC and MEMORYCLERK_SQLQERESERVATIONS clerks dm_exec_query_resource_semaphores dm_exec_query_memory_grants RESOURCE_SEMAPHORE_SMALL_QUERY waits RESOURCE_SEMAPHORE_ QUERY_COMPILE compiles RESOURCE_SEMAPHORE (Query Memory) Hashes and sorts
Resource Queries, Memory, and RESOURCE semaphores Pre-emptive Waits May wrap more Maycode wrapthan more just code than just the API the API ************************ * * BEGIN STACK DUMP: * 10/17/09 15:51:52 spid 0 * * Non-yielding Scheduler * ************************ Windows API Xproc Workers go preemptive when calling external APIs that may take some time
External What does this look like pre-SQL 2008? Status = RUNNING Wait_type = NULL What does this look like in SQL 2008? Status = RUNNING Wait_type = PREEMPTIVE_XXXX 18 What are some I might see? Type Description Scenario PREEMPTIVE_OS_GETPROCADDRESS Wraps calls to GetProcAddress() and xproc function Measure of xproc execution time
PREEMPTIVE_OS_WRITEFILEGATHER Wraps calls to WriteFileGather() to zero out a section of a file Long autogrow for tlog file or database files (if not using instant file init) PREEMPTIVE_OS_LOOKUPACCOUNTSID Wrapped calls to LookupAccountSid() Mostly used during login authentication. Long waits could indicate DC issues. PREEMPTIVE_OLEDBOPS Wrapped around various code fragments that will call OLE-DB methods for linked server queries.. Helps fill in gaps where OLEDB wait not set. ~190 of ~190 of
these these 19 Extended Events and Waits wait_info wait_info_externa l Normal waits Preemptive waits dm_xe_map_values dm_xe_map_values wait_type opcode Timings Begin and End Begin and End Duration, Total, Max Duration, Total, Max Get query, session, or stack dump
On by Default System_Health Session Has These SQLCAT Waits Stats Per Session Project 20 There are other waits Why cant I Why cant I truncate the truncate the log log log_reuse_wait A poorly A poorly written DLL written DLL PRECONNECT PRECONNECT status status loader lock wait Spinlocks backoffs in backoffs in
sys.dm_os_spinlock_stats sys.dm_os_spinlock_stats sessions Resource Governor You decide to You decide to throttle throttle 21 Where is THE LIST? In a header file in the source code and in sys.dm_xe_map_values for SQL Server 2008 The BOL list KB article on waittypes is only for SQL 2000 and prior The plan The Wait Type Repository The Wait Type Repository Blog Blog Post new findings on this blog post Comment on the blog or send email to [email protected]
Use the blog to update the BOL Blog may contain scenarios and more details 22 Resources Our CSS Escalation Blog The Wait Type Repository Blog Post BOL reference on sys.dm_os_wait_stats SQLCAT Waits Stats Per Session CodePlex Craig Freedman blog posts on Parallelism CLR Wait Types blog post SQL Server 2005 Waits and Queues Whitepaper The System_Health XEvent Session Blog 23 Appendix 24 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 25 The mapping has changed sys.sysprocesses.waittype is a binary value Binary to string mapping changed in SQL 2005 KB 822101 wrong for 2005 and 2008 lastwaittype may NOT be current mapping if wait_type != NULL sys.dm_xe_map_values has the correct mapping.kind of 26 Whats About These? Forced SLEEP_TASK Fixed time Hard to figure out scenario Resource DBMIRROR_DBM_EVENT Log shipping delayed to secondary External
OLEDB Wrapped around linked server OLE-DB API calls Wait time will fluctuate since set and cleared for each call wait_resource is remote server and remote SPID PREEMPTIVE_XX type can now also show up Sync CMEMTHREAD Thread synchronization for memory allocation Hot stored proc in SQL Serv High wait times = A likely bug Hot stored proc in SQL Serv er 2005 er 2005 27 Complete the Evaluation Form & Win! 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 Sponsored by Dell Drop off your completed Form: Near the exit of each presentation room At the PASS Booth near registration area 28 Visit the Microsoft Technical Learning Center Located in the Expo Hall Microsoft Ask the Experts Lounge Microsoft Chalk Talk Theater Presentations Microsoft Partner Village 29 Thank you for attending this session and the 2009 PASS Summit in Seattle
