SQL Server: Tiefgehende Analysen von Leistungsproblemen

Es gibt verschiedene Taktiken zur Bestimmung, was verursacht die meisten Wartezeiten in SQL Server, so dass Sie Ihre Performance-tuning direkt können.

Auszug ausSQL Server DMV Starter Pack," von Red Gate Books (2010) herausgegeben.

Glenn Berry, Louis Davidson und Tim Ford

Let 's get Recht bis auf OS-Ebene. Wir blicken auf die Worker-Threads, die Durchführung der Aufgaben, die von unseren Transaktionen benötigt. Den dynamischen Verwaltungsobjekten (DMOs) in dieser Kategorie — aller, die beginnen mit dem Namen sys.dm_os_—provide äußerst detaillierte Informationen über die Art und Weise SQL Server interagiert mit der Hardware und das Betriebssystem. Diese DMOs können Sie alle Arten von Performance-Fragen beantwortet:

• Die Wait_stats DMO erfahren Sie welche Art von Dingen, die die SQL Server OS-Threads gewartet haben.
• Performance_counters erzählt, die SQL Server -Leistungsindikatorwerte und wie sie entschlüsselt sind.
• Ring_buffers, Os_schedulers oder Wait_stats wird Sie zu einer CPU-Auslastung-Problem hinweisen.
• Sys_info geben Ihnen die Eigenschaften der Maschine auf der SQL Server ausgeführt wird.
• Sys_memory oder Process_memory wird Ihnen sagen, wie Ihr Gedächtnis als Ganzes verwendet wird.
• Memory_cache_counters oder Buffer_descriptors, die Ihnen sagen, wie der Cache-Speicher verwendet wird.

Diese Abfragen, arbeiten mit SQL Server 2005, 2008 und 2008 R2. Sie benötigen auch die View Server State-Berechtigung. Hinweis: Die Objekte sollten gemeinsam als DMOs bezeichnet werden. Einige Verwechslungen mit der völlig unabhängige "Distributed Management Objects," so ist es noch durchaus üblich für Datenbankadministratoren, DMOs kollektiv als "Dynamische Verwaltungssichten oder DMVs." bezeichnen aber tendenziell mit DMOs

SQL Server wartet

Die bedeutendste DMV in der Betriebssystem-Kategorie ist wohl dm_os_wait_stats. Jedes Mal, wenn eine Sitzung hat aus irgendeinem Grund zu warten, bevor die angeforderte Arbeit fortgesetzt werden kann, zeichnet SQL Server die Länge der Zeit gewartet und die Ressource, auf die SQL Server wartet. Die dm_os_wait_stats DMV macht warten diese Statistiken, aggregiert über alle Sitzungs-IDs, um Ihnen einen zusammenfassenden Überblick über geben wo der großen wartet auf eine bestimmte Instanz sind.

Diese gleichen DMV stellt außerdem (PerfMon) Leistungsindikatoren, die spezifische Ressourcenverwendung Messungen (Disk-Übertragungsraten, CPU-Zeit verbraucht und so weiter) zur Verfügung zu stellen. Durch Korrelation von Wartestatistik mit Ressource-Messungen, können Sie schnell suchen die meisten angefochtenen Ressourcen auf Ihrem System und mögliche Engpässe zu markieren.

Waits und Warteschlangen

Mit Wartezeiten und Warteschlangen ist eine hervorragende Grundlage für das Performance-tuning, wie in der White PaperSQL Server 2005 wartet und Warteschlangen," von Tom Davidson. Im wesentlichen führt jede Anforderung an den SQL Server in einer Reihe von "Arbeitstasks" initiiert. Ein SQL Server -Scheduler weist jede Aufgabe einen Worker-Thread. Normalerweise gibt es ein SQL-OS-Scheduler pro CPU, und nur eine Sitzung pro Planer kann jederzeit ausführen.

Es ist der Scheduler Job um die Arbeitsauslastung auf verfügbaren Arbeitsthreads gleichmäßig zu verteilen. Wenn eine Sitzung-Worker-Thread auf dem Prozessor ausgeführt wird der Status der Sitzung ausgeführt werden, wie von der Status-Spalte der sys. dm_exec_requests DMV verfügbar gemacht.

Wenn ein Thread bereit zu gehen, aber den Zeitplan an, ist hat es derzeit zugewiesen ist eine andere Sitzung ausgeführt, es in der "ausführbar" Warteschlange platziert werden. Dies bedeutet, dass es in der Warteschlange für den Prozessor zu bekommen ist. Dies nennt man eine Signal warten.

Signal wartet

Die Signal-Wartezeit ist spaltenweise Signal_wait_time_ms ausgesetzt. Dies bezieht sich ausschließlich auf CPU-Wartezeit. Wenn eine Sitzung auf eine andere Ressource zur Verfügung stehen, z. B. eine gesperrte Seite wartet oder eine laufende Sitzung I/O durchführen muss, wird er in die Warteliste verschoben. Dies ist eine Ressource warten und die wartende Sitzungsstatus wird aufgezeichnet, wie "ausgesetzt." Der Grund für die Wartezeit ist aufgezeichnet und in der Spalte Wait_type dm_os_wait_stats DMV ausgesetzt.

Das warten insgesamt verbrachte Zeit ist nach der Spalte Wait_time_ms ausgesetzt, sodass Sie die Ressource-Wartezeit, wie folgt berechnen können:

Ressourcenwartevorgänge = Gesamt wartet – Signal wartet (oder (Wait_time_ms) - (Signal_wait_time_ms))

Signal wartet sind unvermeidlich in online-Transaktionsverarbeitung (OLTP)-Systeme, wie diese eine große Anzahl von kurzen Transaktionen bestehen. Die wichtigste Metrik im Hinblick auf potenzielle CPU-Überlastung, ist das Signal warten, als Prozentsatz der gesamten wartet.

Ein hoher Prozentsatz-Signal ist ein Zeichen für CPU-Überlastung. "High" oft bezieht sich auf mehr als 25 Prozent, aber es hängt von Ihrem System. Werte größer als 10 bis 15 Prozent können auch ein beunruhigenden Zeichen sein. Insgesamt sind warten Statistiken ein wirksames Mittel, Reaktionszeiten in Ihrem System zu diagnostizieren. In sehr einfachen Worten, die Sie entweder arbeiten oder Sie warten.

Wenn die Reaktionszeiten langsam sind und Sie keine erheblichen Wartezeiten oder hauptsächlich Signal wartet finden, wissen Sie, Sie müssen sich konzentrieren auf CPU. Wenn Sie finden, dass die Antwortzeit besteht hauptsächlich aus Wartezeit für andere Ressourcen (z. B. Netzwerk, i/o und So weiter), wissen Sie, dass Sie Ihre Optimierungsmaßnahmen auf diese Ressourcen konzentrieren müssen.

Performance profiling

Unser erste Skript in der OS-Kategorie verwendet die dm_os_wait_stats DMV, die Informationen zu allen Wartevorgängen ausgeführten Threads zurückgibt. In dieser aggregierten Sicht können Sie Leistungsprobleme mit SQL Server insgesamt und mit bestimmten Abfragen und Batches diagnostizieren.

Diese einfache 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 indicate 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 ;

Dies ist nützlich, um zu helfen, CPU-Überlastung zu bestätigen. Da Signal wartet sind Wartezeit für eine CPU/Service, den ein Thread, wenn die Summe Signal aufzeichnen oben wartet etwa 10 bis 15 Prozent, das ist ein guter Indikator für CPU-Überlastung. Diese warten Statistiken sind kumulativ, da SQL Server zuletzt neu gestartet wurde, musst du wissen des Basisplans Wert für Signal wartet und den Trend im Laufe der Zeit zu sehen.

Sie können manuell aus der Wartestatistik löschen, ohne Neustart des Servers, durch die Ausgabe einer Datenbank Konsistenzüberprüfung (DBCC) SQLPERF-Befehl wie folgt:

-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;

Wenn Ihre SQL Server -Serverinstanz ausgeführt wurde für eine ganze Weile und Sie eine erhebliche Änderung machen, z. B. Hinzufügen eines wichtigen neuen Indexes sollten Sie löschen die alten warten-Statistiken. Andernfalls werden die alten kumulativen warten-Statistiken welche Auswirkungen maskieren, die Änderung auf die Wartezeiten hat.

Unsere zweite Beispielskript (siehe Abbildung 1) zeigt, wie mithilfe der dm_os_wait_stats DMV hilft festzustellen, die Ressourcen für die SQL Server die verbringt Zeit warten.

Abbildung 1 dieses Skript generiert einen Bericht über die oberen Ursachen wartet.

-- 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

Das Skript in Abbildung 1 hilft Ihnen die größten Engpässe auf Instanzebene zu suchen. Dies hilft Ihnen Ihre Optimierungsmaßnahmen auf die besondere Art des Problems zu konzentrieren. Beispielsweise 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 Leistungsindikatoren zu untersuchen.

Leistungsindikatoren

Die DMV, die die Leistungsindikatoren verfügbar macht ist dm_os_performance_counters. Dies gibt eine Zeile pro Leistungsindikator vom Server verwaltet. Dies ist eine nützliche DMV, aber es ist frustrierend, mit zu arbeiten.

Je nach dem Wert für Cntr_type für eine bestimmte Zeile müssen Sie möglicherweise Graben tief um diese DMV sinnvolle Informationen einholen. Es ist ein Ersatz für die alte sys.sysperfinfo von SQL Server 2000.

Das Skript in Abbildung 2 hilft Ihnen ungewöhnliche Bedingungen füllt sich das Transaktionsprotokoll zu untersuchen. Das Wiederherstellungsmodell, Protokoll Wiederverwendung warten Beschreibung, Größe des Transaktionsprotokolls Protokollspeicherplatz verwendet, Prozentsatz des Protokolls verwendet, Kompatibilitätsgrad, überprüfen, und Seite Option für jede Datenbank in der aktuellen SQL Server -Serverinstanz zurückgibt.

Abbildung 2 bestimmen, was Sie das Transaktionsprotokoll mit diesem Skript füllt,

-- 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)%' ;

Die Abfrage in Abbildung 2 ist hilfreich für die Bewertung eines unbekannten Datenbank-Servers. Es ist auch aus Sicht der Überwachung im Allgemeinen sinnvoller. Zum Beispiel wenn Ihre Log-Wiederverwendung warten Beschreibung ist etwas Ungewöhnliches wie ACTIVE_TRANSACTION, und das Transaktionsprotokoll ist 85 Prozent belegt, dann sollte es einige Alarmglocken los.

Mit dieser Art der Überwachung ist äußerst hilfreich für die Ermittlung der Quellen und Ursachen von Wartezeiten und andere Leistungsprobleme. Diese Art der Untersuchung können Sie besser Ihre Leistungsoptimierung Anstrengungen an der richtigen Stelle zu richten.

Glenn Berry arbeitet als ein Datenbankarchitekt bei NewsGator Technologies Inc. in Denver, Colorado Er ist ein SQL Server MVP und hat eine ganze Sammlung von Microsoft-Zertifizierungen, einschließlich MCDBA, MCSE, MCSD, MCAD, MCITP und MCTS, was beweist, dass er gerne Tests machen.

Louis Davidson in der IT-Branche wurde seit 16 Jahren als Unternehmensdatenbank Entwickler und Architekt. Er ist seit sechs Jahren ein 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

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 -Serverplattform für Spektrum Gesundheit. Er hat schriftlich über Technologie seit 2007 für eine Vielzahl von Websites und führt seinen eigenen Blog unter thesqlagentman.com, mit SQL als auch Telearbeit und professionellen Entwicklungsthemen.

Erfahren Sie mehr überSQL Server DMV Starter Pack" unter rot-gate.com.

Verwandte Inhalte

• SQL Server: Die zehn Geheimnisse eines SQL Server -Experten
• SQL – Fragen und Antworten: Sicherungs- und Setup
• SQL Server: Um jeden Preis schützen von Daten