SQLServer: SQL Server-Verzögerungen kein Rätsel mehr
Sie können die Nachverfolgungsdaten für SQL Server-Transaktionswartezeiten verwenden, um übermäßig genutzte Ressourcen zu finden und Engpässe auszuschalten.
Auszug aus "SQL Server DMV Starter Pack," veröffentlicht von Red Gate Books (2010).
Glenn Berry, Louis Davidson und Tim Ford
Jedesmal, wenn eine SQL Server-Sitzung hat aus irgendeinem Grund zu warten, bevor die angeforderte Arbeit fortgesetzt werden kann, speichert SQL Server lange gewartet. Es zeichnet auch die Ressource, auf die sich die Transaktion wartet.
Die dynamische Verwaltungssicht sys. dm_os_wait_stats anzeigen (DMV) macht, die diese warten, Statistik, aggregiert über alle Sitzungen bieten eine zusammenfassende Bewertung der großen wartet auf bestimmte Instanz befinden. Diese gleichen DMV stellt außerdem (PerfMon) Leistungsindikatoren, die bestimmte Ressource Verwendung Messungen (Disk Transfer Preise, Menge an CPU-Zeit verbraucht und so weiter) zur Verfügung stellen.
Durch Korrelation von Wartestatistik mit Ressource-Messungen, können schnell suchen die meisten "angefochtenen" Ressourcen auf Ihrem System, und markieren Sie potenzielle Engpässe.
SQL Server 2005 wartet und Warteschlangen
Die Verwendung von "Wartezeiten und Warteschlangen" als Grundlage für eine Leistungsoptimierung Methodik erläutert wird ein hervorragende Whitepaper von Tom Davidson. Im wesentlichen führt jede Anforderung an SQL Server die Einleitung einer Reihe von "Arbeitstasks."
Ein SQL Server-Zeitplanungsmodul ein Arbeitsthread jede Aufgabe zugewiesen. Normalerweise gibt es ein SQL OS Scheduler pro CPU, und nur eine Sitzung pro Zeitplanungsmodul ausgeführt zu jeder Zeit. Es ist der Scheduler-Jobs, die Arbeitslast gleichmäßig zwischen verfügbaren Arbeitsthreads zu verbreiten.
Wenn eine Sitzung Arbeitsthread auf dem Prozessor, der Sitzung Status "ausgeführt wird," ausgeführt wird, wie von der Status-Spalte der sys. dm_exec_requests DMV verfügbar gemacht. Wenn ein Thread bereit ist zu gehen, aber der Scheduler, den es derzeit zugewiesen ist, hat einer anderen Sitzung ausgeführt, dann wird es in der Warteschlange "Runnable" platziert werden. Dies bedeutet, dass es in der Warteschlange, auf den Prozessor zu erhalten ist. Dies wird als ein Signal warten bezeichnet.
Die Signal-Wartezeit nach der Spalte Signal_wait_time_ms ausgesetzt ist, und ist ausschließlich CPU-Wartezeit. Wenn eine Sitzung auf eine andere Ressource zur Verfügung, um fortzufahren, z. B. eine gesperrte Seite, wartet oder eine laufende Sitzung I/O durchführen muss, wird es in der Warteliste verschoben. Dies ist eine Ressource warten und die wartende Sitzung Status aufgezeichnet als "Angehalten."
Der Grund für die Wartezeit ist aufgezeichnet und in der Wait_type-Spalte der sys. dm_os_wait_stats-DMV ausgesetzt. Insgesamt verbrachte Zeit warten wird von der Spalte Wait_time_ms verfügbar gemacht, damit können Sie die Ressource-Wartezeit wie folgt berechnen:
Ressourcenwartevorgänge = Total wartet – Signal wartet = (Wait_time_ms)-(Signal_wait_time_ms)
Signal wartet sind unvermeidlich in online Transaction processing (OLTP) Systeme, die eine große Anzahl von kurzen Transaktionen enthalten sind. Die wichtigste Metrik, in Bezug auf potenzielle CPU-Überlastung, ist das Signal warten als Prozentwert der Summe wartet. Ein hoher Prozentsatz-Signal ist ein Zeichen von übermäßiger CPU-Druck. Die Literatur neigt zu zitieren, "hoch" wie etwa mehr als 25 Prozent, aber es hängt von Ihrem System.
Auf unseren Systemen behandeln wir als ein beunruhigenden Zeichen Werte, die größer als 10 bis 15 Prozent. Insgesamt stellt die Verwendung der Wartestatistik ein sehr wirksames Mittel zur Diagnose von Reaktionszeiten in Ihrem System. In einfachen Worten warten Sie arbeiten oder Sie. Reaktionszeit ist Service-Zeit sowie die Wartezeit.
Wenn Reaktionszeiten langsam sind und Sie keine erheblichen Wartezeiten oder hauptsächlich Signal wartet finden, dann müssen Sie sich auf CPU. Wenn Sie Antwortzeit finden besteht vor allem aus Zeit beim warten andere Ressourcen (z. B. das Netzwerk, i/O und So weiter), dann Sie genau, wohin wissen Sie Ihre Optimierungsmaßnahmen zu konzentrieren.
Nehmen Sie das Rätselraten
Mario Broodbakker geschrieben hat eine ausgezeichnete einleitende Serie von Artikeln auf Verwendung warten Ereignissen für Leistungsprobleme zu diagnostizieren. Unser erste Skript in der Kategorie OS verwendet die sys. dm_os_wait_stats-DMV, die folgendermaßen definiert ist: Gibt Informationen zu allen Wartevorgängen Threads, die ausgeführt. In dieser aggregierten Sicht können Sie Leistungsprobleme bei SQL Server sowie bei bestimmten Abfragen und Batches diagnostizieren.
Diese Abfrage berechnet Signal wartet und Ressource wartet als Prozentsatz der gesamten Wartezeit, um potenzielle CPU-Überlastung zu diagnostizieren:
-- Total waits are wait_time_ms (high signal waits indicates CPU pressure) SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] , CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]FROM sys.dm_os_wait_stats ;
Diese Abfrage ist nützlich zu CPU-Überlastung zu bestätigen. Weil Signal wartet Mal eine CPU zu einen Thread warten wenn Sie Summe Signal wartet, die etwa größer als 10 bis 15 Prozent aufnehmen, dann dies ein ziemlich guter Indikator für CPU-Überlastung ist.
Diese warten Statistiken sind kumulativ, da SQL Server zuletzt neu gestartet wurde, müssen Sie wissen, Ihre Grundlinie Wert für Signal wartet, und beobachten Sie die Entwicklung im Laufe der Zeit. Sie können manuell die Wartestatistik ohne Neustart des Servers durch die Ausgabe eines Befehls DBCC SQLPERF wie folgt löschen:
-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;
Wenn Ihre SQL Server-Instanz seit geraumer Zeit läuft, und Sie erhebliche Änderungen wie das Hinzufügen eines neuen Indexes, machen dann Sie löschen sollten warten die alten Statistiken. Andernfalls werden die alte kumulierte Wartezeit-Statistik welche Auswirkungen Maske die Änderung auf die Wartezeiten hat.
Unsere zweite Beispielskript unter Verwendung der DMV sys. dm_os_wait_stats helfen Ihnen zu bestimmen, dass die Ressourcen auf denen SQL Server die meisten Ausgaben ist Zeit warten:
-- Isolate top waits for server instance since last restart -- or statistics clear 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 WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN' ) ) SELECT W1.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 ; -- percentage threshold
Dieses Skript hilft Ihnen den größten Engpass auf der Ebene der Serverinstanz zu suchen. Dies hilft Ihnen Ihre Optimierungsmaßnahmen auf eine bestimmte Art von Problem zu konzentrieren. Zum Beispiel, wenn kumulative oben warten Typen sind Disk I/O-bezogenen, dann würden Sie wollen dieses Problem weiter mithilfe von datenträgerbezogenen DMV Abfragen und PerfMon-Leistungsindikatoren zu untersuchen.
Expose-Leistungsindikatoren
Die DMV, die die Leistungsindikatoren verfügbar macht ist dm_os_performance_counters, die als beschrieben wird: "Gibt eine Zeile pro Leistungsindikator vom Server verwaltet." Dies ist eine nützliche DMV, aber kann es frustrierend zu verwenden. Je nach dem Wert für Cntr_type für eine bestimmte Zeile musst du einige interessante turbulente um sinnvolle Informationen aus dieser DMV durchlaufen. Es ist ein Ersatz für die alte sys.sysperfinfo von SQL Server 2000.
Dieses Skript hilft Ihnen ungewöhnliche Bedingungen füllt sich das Transaktionsprotokoll zu untersuchen. Es gibt, dass das Wiederherstellungsmodell, Protokoll Wiederverwendung warten Beschreibung, Größe des Transaktionsprotokolls, Protokollspeicherplatz verwendet, Prozentsatz des verwendeten Protokolls, Kompatibilitätsgrad und Seite Option für jede Datenbank in der aktuellen SQL Server-Instanz überprüfen:
-- Recovery model, log reuse wait description, log file size, -- log usage size and compatibility level for all databases on instance SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] , db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] , lu.cntr_value AS [Log Used (KB)] , CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] , db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]FROM sys.databases AS db INNER JOIN sys.dm_os_performance_counters AS lu ON db. name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls ON db. name = ls.instance_nameWHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’ AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;
Diese Abfrage hilft einen unbekannten Datenbank-Server ausgewertet. Es eignet sich auch aus Sicht der Überwachung. Beispielsweise wenn Ihre Wiederverwendung Protokoll warten Beschreibung ist etwas ungewöhnliches und das Transaktionsprotokoll ist 85 Prozent voll, die einen Alarm auslösen sollen.
.png)
Glenn Berry arbeitet als ein Datenbankarchitekt bei NewsGator Technologies in Denver, Colorado Er ist ein SQL Server-MVP und hat eine ganze Sammlung von Microsoft-Zertifizierungen, einschließlich MCITP, MCDBA, MCSE, MCSD, MCAD und MCTS, was beweist, dass er gerne Tests machen.
.png)
Louis Davidson in der IT-Branche wurde seit 16 Jahren als Unternehmensdatenbank Entwickler und Architekt. Er ist seit sechs Jahren SQL Server Microsoft MVP und schrieb vier Bücher über Datenbankdesign. Er ist derzeit die Datenarchitekt und manchmal DBA für das Christian Broadcasting Network, Unterstützung von Büros in Virginia Beach, VA., und Nashville, Tennessee
.png)
Timothy Ford ist ein SQL Server-MVP und arbeitet seit mehr als 10 Jahren mit SQL Server. Er ist der primäre DBA und Fachexperten für die SQL Server-Plattform für Spektrum Gesundheit. Er wurde schriftlich über Technologie seit 2007 für eine Vielzahl von Websites und verwaltet seinen eigenen Blog unter thesqlagentman.com, SQL als auch Telearbeit und professionelle Entwicklungsthemen abdeckt.
Erfahren Sie mehr über "SQL Server DMV Starter Pack" bei red-gate.com/our-company/about/book-store.