Inside SQL Server Wait Types Bob Ward Microsoft

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

Microsoft CSS at PASS 2009 Pre-Conference Seminar • Tackling Top Reporting Services Issues • Mon 11/2 8: 30 am-4: 30 pm • Adam Saxton Main Conference Talks • (DBA-500 -SC) Inside SQL Server Wait Types • Tues 11/3 10: 15 – 11: 45 am 3 AB • Bob Ward SQL Server Clinic • Room 611 • 11/3 – 11/5/2009 • After Keynote – 6: 00 pm…ish • (DBA-X 69 -C) Implementing and Supporting SQL 2008 Failover Clustering • Tues 11/3 1: 30 -2: 45 pm 4 C 1 -2 • Shon Hauck • (BIA-X 45 -C) Top customer support issues in Analysis Services • Wed 11/4 1: 30 -2: 45 pm 2 AB • John Sirmon • (AD-X 43 -C ) Troubleshooting applications accessing SQL Server • Thurs 11/5 1: 00 -2: 15 pm 613 -614 • Abirami Iyer and Lakshmi Jonnakuti 2

Welcome to My World This is a “ 500” level talk I assume SQL knowledge DMVs, Code, Debugger, and APIs We will move fast and furious = This means your brain may hurt Stuff you can use Stay for questions as long as you want All scripts will be available 3

What is a wait type? Not the best of docs • We created this to help us find bottlenecks • In a galaxy, 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 Resource Synchronization Forced External Queue • I/O, Network, Thread, Memory • Locks, Latches, and “bunch of others” • Yield or Sleep • Preemption • Background tasks 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

Let’s look at an example Common for a SELECT Request LCK_M_IS (Shared Intent) lock Understands SQLOS scheduling Wait() results in Signal. Object. And. Wait() We know we need to wait A conflict exists Setup a SOS_Wait. Info with LCK_M_IS Use SOS_Event. Auto class to wait Call Lock. Owner: : Sleep Ultimately it always comes down to Wait. For. Single. Object() or Signal. Object. And. Wait() SOS_Event. Auto is a wrapper for Windows Kernel Event object 6

Where do wait types show up? sys. dm_os_wait_stats sys. dm_exec_requests Historical stats Live state sys. dm_os_waiting_tasks sysprocesses Extended Events Management Data Warehouse legacy Tracing in 2008 In the tools Activity Monitor Performance Monitor Counters Wait Statistics 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 Kelly’s 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 waiting” Background Task Waits • LAZYWRITER_SLEEP • SQLTRACE_BUFFER_FLUSH • CHECKPOINT_QUEUE • REQUEST_FOR_DEADLOCK_SEARCH CLR_AUTO_EVENT BOL calls these Queue Waits Normal for SQL CLR 10

Busting the Myth of CXPACKET Craig Freedman Talk 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’ Don’t jump to these 11

The non BUFFER Latch Sync Latch can be generic • Not just for BUFs • Thread sync of memory structure Appears as LATCH_XX • As opposed to PAGELATCH or PAGEIOLATCH How many are there? Same modes as BUFs (KP, SH, UP, EX, DT) Latch class • sys. dm_os_latch_stats • sys. dm_exec_requests. wait_resource 12

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

SOS_SCHEDULER_YIELD Forced I/O, Lock, Latch A task that does not “naturally” wait must yield • What if we don’t do this right? ************ * * BEGIN STACK DUMP: * 10/17/09 15: 51: 52 spid 0 * * Non-yielding Scheduler * ************ Examples Indicators • No I/O needed for pages • T-SQL variables only or just “expressions” • Query compile • Small hashes and sorts • High count CPU intensive query • High wait time CPU intensive queries competing or someone not yielding very well Could be preemptive 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 = task + 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 TRAN 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 Buffer Mylog. ldf Log Cache 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

Queries, Memory, and RESOURCE semaphores Hashes and sorts RESOURCE_SEMAPHORE (Query Memory) compiles RESOURCE_SEMAPHORE_ QUERY_COMPILE Resource • 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 sys. dm_os_memory_brokers DBCC MEMORYSTATUS • 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 64 bit systems 17

Pre-emptive Waits Workers go preemptive when calling “external” APIs that may take “some time” May wrap more code than just the API • Windows API • Xproc External ************ * * BEGIN STACK DUMP: * 10/17/09 15: 51: 52 spid 0 * * Non-yielding Scheduler * ************ 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 Get. Proc. Address() and xproc function Measure of xproc execution time PREEMPTIVE_OS_WRITEFILEGATHER Wraps calls to Write. File. Gather() 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 Lookup. Account. Sid() 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 these 19

Extended Events and Waits wait_info_external “Normal” waits Preemptive waits wait_type opcode Timings dm_xe_map_values Begin and End 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 can’t I truncate the log” log_reuse_wait A poorly written DLL “loader lock wait” Spinlocks backoffs in sys. dm_os_spinlock_stats PRECONNECT status sessions Resource Governor You decide to 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 Blog • Post new findings on this blog post • Comment on the blog or send email to psssql@microsoft. com • 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 Code. Plex 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 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

What’s 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 • High wait times = A likely bug Hot stored proc in SQL Server 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 Theater Presentations Microsoft Partner Village 29

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