I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Performance Troubleshooting Using Waits and Latches
Instructor: Paul Randal

• Keep historical snapshots of wait stats
• See 2014 whitepaper on using wait stats
  • Or older, more in-depth 2005 whitepaper [.doc]
• dm_os_schedulers; interesting columns
  • visible_online, visible_online (dac), hidden_online
  • context_switch: number of context switches
  • loads
  • quantum_length_us: length of the scheduler’s quantum in microseconds (time until the thread must yield)
• Waiter list is completely unordered. Each thread will be notified when its resource is available, and can be notified in any order.  It is then moved to the runnable list
• No limit to how many threads are on the waiter list
• No limit to how long a thread can be on the list
• When a thread’s quantum is exhausted, it is automatically moved to the runnable list
  • “Check for abort or yield” function called by thread – SQLOS will notify if quantum is exhausted or if the user has cancelled
  • NON_YIELDING_THREAD error is always a bug. Must bounce SQL to kill it
  • Must yield even if no other threads are on the scheduler
• Threads on the waiter list do not periodically wake up to see if resource is available
• When a thread owning a lock releases the lock and another thread is waiting on the resource, it transfers ownership to the second thread and moves it off the waiter list. That is, the thread is “signaled.”
• Time that a thread sits on the runnable queue is called signal wait time. This is usually very small and generally not of interest.
• Runnable queue is ordered as first-in, first-out
  • Special case: Resource governor allows for threads to be given priority. However, this is hard to set up and nobody uses it
• dm_os_schedulers.runnable_tasks_count. If this amount is large (perhaps >= 10 per scheduler) this indicates CPU pressure
• Waits happen. It is simply a part of SQL Server.
• Wait time = resource wait time + signal wait time
• dm_os_waiting_tasks – What is happening right now?
• dm_os_wait_stats – What has happened
  • Aggregate view of wait stats since they were last cleared
  • Capture snapshots over time. Every 15-30 minutes should be sufficient
  • Figure out how long to retain
• Coming by about the end of the year: sqlskills.com will have a repository to wait types! (and latch types)
• Capture waits via extended events: sqlos.wait_info event
• A couple of demos on collecting XEvent, including SQL Server call stacks!
• Latch whitepaper [.pdf]
• Superlatches – efficiency mechanism on NUMA nodes
  • Can produce benign error message in log: “Failure to allocate super-latch promotion threshold” – nothing to do about it
• dm_os_latch_stats: Ignore unless wait stats show latch waits
• Clearing wait/latch stats: DBCC SQLPERF
  • When were they last cleared? See here
• Spinlock – synch mechanism that is lighter than a latch
  • Uses CPU-level instruction to get a bit
  • Tries a number of times (500 to 1000); if still cannot acquire will backoff and sleep on the CPU, then try again
  • Does not yield thread, so runnable threads have to wait –high signal wait times will be the result
  • In practice, this does not result in a SOS_NONYIELDING thread scenario (which takes about 60 seconds to come up)
  • Rare to see, but can happen
  • Diagnosing requires lots of internals knowledge; see this whitepaper [.pdf]
• Common wait types
  • CXPACKET – parallelization
  • OLEDB – Linked servers or DMVs
  • ASYNC_NETWORK_IO – TDS packets sent to client; awaiting ack.
• Common latch types
  • ACCESS_METHODS_DATASET_PARENT – multithreading where worker thread is getting next set of work
  • ACCESS_METHODS_SCAN_RANGE_GENERATION
  • ACCESS_METHODS_HOBT_COUNT
• Wait types
  • PAGEIOLATCH_XX – Waiting for data to be read into memory
    • XX = SH – Page to be read
    • XX = EX – Page to be written
  • Determining which table a page belong to
• dm_os_buffer_descriptors – one row per page in the buffer pool
• dm_os_virtual_file_stats
  • For each DB file – IOs, etc.
  • Snapshot this table from time to time
• PAGE_LATCH_XX
  • Waiting for access to memory page
  • Often indicates tempdb contention
  • TF 1118 – Removes SGAM contention
    • Also, TF 3226 – Don’t write backup success messages to the log
    • TF 4199 – changes query opt., not necessarily recommended for all systems
  • For PFS contention, add move tempdb files
    • If logical cores < 8, tempdb files = number of logical cores
    • Else use 8 tempdb files; if contention in tempdb not alleviated, add 4 more at a time until problem is alleviated
  • May also indicate excessive page splits or an insert hotspot
• LCK_M_XX wait
  • Analyzing – see this Michael Swart blog post
  • Especially look for LCK_M_RS_XX; indicates serializable isolation level
  • On SharePoint systems consider using snapshot isolation
• Row locks escalate to table locks
• Page locks escalate to table locks
• Row locks never escalate to page locks
• WRITELOG wait
  • LOGBUFFER wait – copying log block to log cache
  • Once log block is written, waits for SPID 1 (the log writer) to asynchronously flush the log to disk; when done, signals the original thread
  • Limit on how many log writes per database can be enqueued at once
    • Pre-2012: 32
    • 2012+: 112
  • Can be symptomatic of page splits or many small txns
  • Delayed durability
    • Only enable is SLA has data loss permissible
    • Can significantly improve throughput on small transactions
  • CXPACKET
    • Completely normal on thread 0 (execution_context_id = 0) (controller thread)
    • Only of concern if wait is high on a thread other than 0
    • See Bob Door’s blog post on how to track foreign memory access (foreign pages in performance monitor)
    • Consider increasing cost threshold for parallelism
  • SOS_SCHEDULER_YIELD
    • Have to use XEvents to view call stacks to really identify source!
  • LATCH_XX
    • Latch class largely controls understanding of root cause; see this resource page
  • FGCB_ADD_REMOVE latches
    • Often auto-growth related
  • DBCC_XX latches
    • Related to CHECKDB
    • Nothing to do about these
    • Note that persisted computed columns can significantly increase latching (DBCC_OBJECT_METADATA)
  • PREEMPTION_OS_XX waits
    • Call-out to Windows
    • XX = name of Win API method; Google for “MSDN XX”
  • ASYNC_NETWORK_IO waits
    • Client is likely doing RBAR on the resultset rather than caching the results client-side

Many, many other wait types