1 / 18

Understanding SQL Server Waitstats for Performance Troubleshooting

Learn about waitstats in SQL Server, how they impact performance, common wait types, interpreting waitstats, and troubleshooting steps. Gain insights from expert Ronald Bijl.

ting
Download Presentation

Understanding SQL Server Waitstats for Performance Troubleshooting

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. Waitstats 2005/2008 21 april 2012

  2. Watgaan we doen? • Even voorstellen • Performance problemen • Eenveelgebruikteaanvliegroute • Watzijnwaitstats • Execution model • De DMV’s • Hoe langduurteen query • Waarzijn de stats te vinden • Indieneen performance probleemzichaandoet • Hoe de waitstats DMV uit te vragen • Veelvoorkomendewaittypes • Demo • Ask

  3. Evenvoorstellen Ronald Bijl Werkzaam met SQL Server vanafversie 6.0 Beheer Ontwikkeling BI analyse/ontwerp/bouw SQL Server Docent r.bijl@xmsnet.nl http://axeprofit.blogspot.com

  4. Performanceproblemen Enigeoorzaken van performance problemen • Hardware voldoet (niet) meer • Netwerk • Verkeerdeconfiguratie SQL Server • Verkeerdeconfiguratie Windows Server • Code ontwikkeld op kleine datasets • Inefficiente code

  5. Eenveelgebruikteaanvliegroute • Perfmon PLE Buffer cache hit ratio Disk latency (avg disk queue length) Page faults Total memory vs Target memory Full scans/sec • Resource monitor • Profiler • ProfilerTSql duration • TSQL SPs

  6. Watzijnwaitstats • “Statistieken” die wordenbijgehouden • hoe langprocessen (queries) binnen SQL Server moetenwachtenom de verschillendefasenbinnen het executie model te doorlopen • per wait type • cumulatief

  7. Execution model (1)

  8. Execution model (2)

  9. Execution model (3)

  10. De DMV’s • Running list (cpu time) • -> sys.dm_exec_requests (where status = ‘running’) query(s) welke word(en) uitgevoerd (CPU execution) • Runnable queue (signal wait time (< 25%)) • -> sys.dm_exec_requests (where status = ‘runnable’) query(s) die uitgevoerd kunnen worden maar wachten op CPU-tijd • Waiter list (query wait time) • -> sys.dm_os_waiting_tasks query(s) die wachten op een resource

  11. Hoe langduurteen query? Query execution time = Query Wait Time + Query Signal Time + CPU Time

  12. Waarzijn de stats te vinden • DMV: SYS.DM_OS_WAIT_STATS • Erzijngeenstandaard reports vooraanwezig in de management studio • Omdatzecumulatiefzijn: delta’s berekenen en interpreteren

  13. Indieneen performance probleemzichvoordoet Kan de situatiegereproduceerd en geisoleerdworden (test-instance) • Clear waitstats (DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)) • Draai de vertragendeprocessen (indienbekend) • Interpreteer de waitstats Indienditnietmogelijk is (zeerwaarschijnlijk): • Clear waitstats (DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)) • Insert de waitstats frequent (bijvoorbeeldiedereminuut) in eenusertable • Interpreteernaenigetijd de waitstats

  14. Hoe de waitstats DMV uit te vragen • WITH Waits AS • ( • SELECT • wait_type, • wait_time_ms / 1000. AS wait_time_s, • 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, • ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn • FROM sys.dm_os_wait_stats recompile • WHERE wait_type • NOT IN • ('BROKER_TASK_STOP','Total','SLEEP','BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TRANSMITTER', • 'CHECKPOINT_QUEUE','CHKPT','CLR_AUTO_EVENT','CLR_MANUAL_EVENT','KSOURCE_WAKEUP','LAZYWRITER_SLEEP', • 'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK', • 'SLEEP_BPOOL_FLUSH','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK', • 'SLEEP_TASK', 'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SQLTRACE_BUFFER_FLUSH','TRACEWRITE', • 'WAIT_FOR_RESULTS','WAITFOR_TASKSHUTDOWN','XE_DISPATCHER_WAIT','XE_TIMER_EVENT','WAITFOR', • 'CLR_SEMAPHORE','BROKER_TO_FLUSH', 'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT', • 'FT_IFTSHC_MUTEX', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP') -- filter out additional irrelevant waits • )

  15. SELECT convert(varchar(40),W1.wait_type) wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct < 95

  16. Veelvoorkomende wait types • ASYNC_IO_COMPLETION • Wachten op IO • ASYNC_NETWORK_IO • Vertraging in netwerk of in verwerking aanroepende applicatie • CXPACKET • Parallelle processor afhandelingen • PAGEIOLATCH_EX • Exclusive latches (kan niet locken in memory (buffer) door vertraagde IO) • PAGEIOLATCH_SH • Shared latches (kan niet locken in memory (buffer) door vertraagde IO) • SOS_SCHEDULER_YIELD • Executing task staat zijn plek af -> onderaan in de runnable queue (non-preemptive (cooperative)) • Resource_Semaphore • Memory pressure • BackupIO of BackupBuffer • Backup loopt vertraging op, mogelijk door langzame backupmedia • WRITELOG • Logflush na een commit of een checkpoint

  17. Demo

  18. Questions?

More Related