1 / 15

SQL Server Wait Statistics Capture, Report, Analyse

SQL Server Wait Statistics Capture, Report, Analyse. Rob Risetto Principal Consultant with StrataDB ( rob@stratadb.com ). About Rob . SQL Server consultant and cofounder of StrataDB StrataDB - Brisbane based SQL Server Consultancy covering SQL Engine and Business Intelligence

kira
Download Presentation

SQL Server Wait Statistics Capture, Report, Analyse

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. SQL Server Wait Statistics Capture, Report, Analyse Rob RisettoPrincipal Consultant with StrataDB (rob@stratadb.com)

  2. About Rob • SQL Server consultant and cofounder of StrataDB • StrataDB - Brisbane based SQL Server Consultancy covering SQL Engine and Business Intelligence • Worked with SQL Server for 14 years • My focus area • Performance Tuning • High Availability • SQL Engine Architecture and Design • SQL Automation via Powershell

  3. Agenda • Quick theory refresher on Wait Statistics • How/Why • Common Wait Types • Wait Statistic Analysis Guidelines • Capture, Report & Analyse Demos using • DMVs • SQL Server 2012 Performance Dashboard • Powershell Charting

  4. Wait Statistics – How/Why Rather than reinventing the wheel… Let’s use Joe Sack (Microsoft Corporation) slides http://www.digitalconcourse.com/dropzone/MSCOMM/PASSMN/PASSEVT20090616/Joe%20Sack%20Performance%20Troubleshooting%20with%20Wait%20Stats.pdf

  5. Wait Types – Common Ones SOS_SCHEDULER_YIELD Represents a SQLOS worker (thread) that has voluntarily yielded the CPU to another worker. May indicate CPU pressure if very high percentage of all waits (> 80%). CXPACKET A query is parallelized and the parallel threads are not given equal amounts of work to do, or one thread blocks. High number for OLTP system is not good. PAGEIOLATCH_XX This is where SQL Server is waiting for a data page to be read from disk into memory. May indicate Disk IO or Memory pressure.

  6. Wait Statistics – Common Ones PAGELATCH_XX Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. PFS, SGAM, and GAM contention that can occur in tempdb Index hot-spot with concurrent inserts into an index with an identity value key WRITELOG This is the log management system waiting for a log flush to disk. May indicate Disk IO LCK_M_XX This is simply the thread waiting for a lock to be granted and indicates blocking problems.

  7. Wait Statistics – Common Ones IO_COMPLETION This is SQL Server waiting for IOs to complete. May indicate Disk IO bottleneck ASYNC_NETWORK_IO SQL Server is waiting for a client to finish consuming data. Bad programming or network issue. RESOURCE_SEMAPHORE Queries waiting for execution memory (the memory used to process the query operators - like a sort) May indicate memory pressure or a very high concurrent workload.

  8. Paul Randal’s Wait Stats Survey

  9. Guidelines for Analysis Look at the Accumulated Waits - Use to focus investigation effort on performance issue - Is a wait type a high percentage of all waits - Is Signal time > 15% - 20% (may indicate CPU pressure) - Is there a high average wait time for a wait type - egPageIOLatchaverage > 20 ms

  10. Guidelines for Analysis Correlate Waits with related DMVs and Perfmon - If IO wait compare IO File Stats, Avg Disk Secs/Read, Avg Disk Secs/Write - If CPU waits, check Processor Time %, sys.dm_os_schedulers (runnable_task_count) - If Memory waits, check Page Life Expectancy, Pending Memory Grants (query workspace)

  11. Guidelines for Analysis Look at Delta Waits to determine current wait statistics Capture Waits history for troubleshooting - Review history to identify high wait periods - Combine with other DMV and Perfmon history capture to correlate and diagnose Use sys.dm_os_waiting_tasks to drilldown current waiting tasks - see interesting patterns - see the query associated with the wait

  12. Real World Workloads & Demos

  13. Useful links Paul Randal (SQL Skills) Waits Stats blog http://www.sqlskills.com/BLOGS/PAUL/category/Wait-Stats.aspx#p9 Glenn Berry 911 Emergency DMVs http://dl.dropbox.com/u/13748067/911%20DMV%20Emergency%20Queries%20September%202012.sql Hammerora tool article http://www.sqlservercentral.com/blogs/aschenbrenner/2011/11/23/running-a-tpc_2D00_c-workload-on-sql-server/ Response Time Analysis using Extended Events http://sqlcat.codeplex.com/wikipage?title=ExtendedEventsWaitstats&referringTitle=Home

  14. Useful links SQL Server 2012 Performance Dashboard Download http://www.microsoft.com/en-au/download/details.aspx?id=29063 SQL Server 2005 Waits and Queues http://sqlcat.com/sqlcat/b/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx Powershell Charting with MS Charting Controls http://cmille19.wordpress.com/2009/07/09/powershell-charting-with-ms-chart-controls/ Powershell LibraryChart.ps1 download http://poshcode.org/1205

  15. Rob’s Contact Details Email: rob@stratadb.com Mobile: 0417 322 000

More Related