SQL Server

Optimieren der SQL Server-CPU-Leistung

Zach Nichter

Kurz zusammengefasst:

• Behandlung von Problemen mit der Datenbankleistung
• Überprüfen der Hardwareursachen
• Verwenden von PerfMon zum Nachverfolgen von Datenbankengpässen
• Auswerten der Abfrageleistung

Die Behandlung von Leistungsproblemen auf einem Datenbanksystem kann sehr aufwendig sein. Es ist wichtig zu wissen, wo nach den Fehlern zu suchen ist. Noch wichtiger ist es jedoch zu wissen, warum Ihr System auf diese Weise auf eine bestimmte Anforderung reagiert. Eine Anzahl von Faktoren kann sich auf die CPU-Nutzung

auf einem Datenbankserver auswirken: unter anderem Kompilierung und Neukompilierung von SQL-Anweisungen, fehlende Indexe, Multithreadvorgänge, Datenträger- und Speicherengpässe, Routinenwartung und ETL-Aktivität (Extract, Transform, Load). Die CPU-Nutzung ist an und für sich keine schlechte Sache, denn Arbeit auszuführen, ist der Zweck der CPU. Für eine stabile CPU-Nutzung muss vor allem sichergestellt werden, dass die CPU ihre Zeit damit verbringt, das zu verarbeiten, was Sie angeben, statt die Zyklen mit schlecht optimiertem Code oder schwerfälliger Hardware zu vergeuden.

Zwei Wege zum selben Ziel

Von einer höheren Ebene betrachtet, gibt es zwei Wege zum Erkennen von CPU-Leistungsproblemen. Die erste Möglichkeit ist, die Hardwareleistung des Systems zu überprüfen. Dadurch können Sie erkennen, wo Sie suchen müssen, wenn Sie den zweiten Weg wählen und die Abfrageeffizienz des Servers überprüfen. Dieser zweite Weg ist in der Regel effektiver, wenn es um das Erkennen von SQL Server™-Leistungsproblemen geht. Wenn Sie nicht genau wissen, wo Ihre Abfrageleistungsprobleme liegen, sollten Sie immer mit einer Bewertung der Systemleistung beginnen. Letzten Endes werden Sie in der Regel beide Wege gehen. Im Folgenden soll eine Grundlage gebildet werden, um beide Wege untersuchen zu können.

Bilden der Grundlage

Hyper-Threading

Hyper-Threading ist ein Thema, dessen ausführlichere Behandlung sich aufgrund der Art und Weise lohnt, in der es sich auf SQL Server auswirkt. Hyper-Threading bietet dem Betriebssystem genau genommen zwei logische Prozessoren für jeden physischen Prozessor. Im Grunde leiht es sich auf den physischen Prozessoren Zeit aus, sodass jeder Prozessor im Endeffekt besser genutzt wird. Die Intel-Website (intel.com/technology/platform-technology/hyper-threading/index.htm) beschreibt die Funktionsweise des Hyper-Threading weit ausführlicher.

Auf SQL Server-Systemen behandelt der DBMS eigentlich sein eigenes, äußerst effizientes Queuing und Threading zum Betriebssystem. Hyper-Threading dient also nur der Überlastung der physischen CPUs auf Systemen mit bereits starker CPU-Nutzung. Wenn SQL Server mehrere Anforderungen in die Warteschlange einreiht, um an mehreren Zeitplanungsmodulen zu arbeiten, muss das Betriebssystem genau genommen den Kontext der Threads auf den physischen Prozessoren hin- und herschalten, um die eingehenden Anforderungen zu erfüllen, selbst wenn sich die beiden logischen Prozessoren auf dem gleichen physischen Prozessor befinden. Wenn für „Kontextwechsel/s“ ein höherer Wert als 5000 pro physischen Prozessor angegeben wird, sollten Sie dringend erwägen, Hyper-Threading auf Ihrem System auszuschalten und die Leistung erneut zu testen.

Anwendungen mit einer starken CPU-Nutzung auf SQL Server können in seltenen Fällen Hyper-Threading effektiv verwenden. Testen Sie Ihre Anwendungen immer anhand von SQL Server, sowohl mit aktiviertem als auch deaktiviertem Hyper-Threading, bevor Sie auf Ihren Produktionssystemen Änderungen implementieren.

Ein High-End-Zweikernprozessor übertrifft leicht den RAM in einem Computer, der dadurch schneller wird als ein angeschlossenes Speichergerät. Eine gute CPU kann einen Durchsatz bewältigen, der ungefähr sechs Mal so groß ist wie der Durchsatz eines aktuellen Top-End-DDR2-Speichers und ungefähr zwei Mal so groß wie der eines Top-End-DDR3-Speichers. Der typische Speicherdurchsatz übertrifft den Durchsatz des schnellsten Fiber-Channel-Laufwerks um mehr als das Zehnfache. Festplatten hingegen können nur eine begrenzte Anzahl von IOPS (input/output operations per second, Eingabe/Ausgabevorgänge pro Sekunde) ausführen. Dies ist ein Wert, der völlig von der Anzahl der Suchen pro Sekunde abhängt, die ein Laufwerk durchführen kann. Es ist jedoch nicht typisch, dass nur ein einziges Speicherlaufwerk verwendet wird, um alle Speicheranforderungen auf Unternehmensdatenbanksystemen zu behandeln. Die meisten Setups von heute verwenden SANs (Storage Area Networks) auf Unternehmensdatenbankservern oder größere RAID-Gruppen, die das Problem mit dem Datenträger-E/A-Prozessor beseitigen oder minimieren können. Am wichtigsten ist es, daran zu denken, dass unabhängig davon, wie Ihr Setup aussieht, Datenträger- und Speicherengpässe die Leistung Ihrer Prozessoren beeinträchtigen können.

Aufgrund der E/A-Geschwindigkeitsunterschiede ist das Abrufen von Daten vom Datenträger viel teurer als das Abrufen von Daten vom Speicher. Eine Datenseite in SQL Server hat eine Grüße von 8 KB. Eine Erweiterung in SQL Server besteht aus acht 8 KB-Seiten, was 64 KB entspricht. Dieses Verständnis ist wichtig, denn wenn SQL Server eine bestimmte Datenseite vom Datenträger anfordert, wird nicht nur die Datenseite abgerufen, sondern die gesamte Erweiterung, in der sich die Datenseite befindet. Es gibt Gründe, warum dies eigentlich für SQL Server kostengünstiger ist, doch das soll hier nicht im Einzelnen behandelt werden. Eine bereits zwischengespeicherte Datenseite aus dem Pufferpool abzurufen, und das mit einer Höchstleistung, dauert in der Regel nicht länger als eine halbe Millisekunde. Das Abrufen einer einzigen Erweiterung vom Datenträger dauert in einer idealen Umgebung in der Regel zwischen 2 und 4 Millisekunden. Ich erwarte normalerweise, dass ein funktionstüchtiges stabiles Datenträgersubsystem zum Lesen zwischen 4 und 10 Millisekunden benötigt. Das Abrufen einer Datenseite vom Speicher ist gewöhnlich 4 bis 20 Mal schneller als das Abrufen einer Datenseite vom Datenträger.

Wenn SQL Server eine Datenseite anfordert, prüft es den im Speicher befindlichen Arbeitsspeicherpuffercache, bevor es die Datenseite auf dem Datenträgersubsystem sucht. Wenn die Datenseite im Pufferpool gefunden wird, ruft der Prozessor die Daten ab und führt dann die nötige Arbeit durch. Dies wird ein Softwareseitenfehler genannt. Softwareseitenfehler sind für SQL Server ideal, weil die Daten, die als Teil einer Anforderung abgerufen werden, sich im Puffercache befinden müssen, bevor sie verwendet werden können. Eine Datenseite, die im Puffercache nicht gefunden werden kann, muss vom Datenträgersubsystem des Servers abgerufen werden. Wenn das Betriebssystem die Datenseite vom Datenträger abrufen muss, wird dies als ein Hardwareseitenfehler bezeichnet.

Bei der Korrelation von Speicherleistung, Datenträgerleistung und CPU-Leistung ist ein gemeinsamer Nenner nützlich, um alles in die richtige Perspektive zu rücken: Durchsatz. In einem nicht so wissenschaftlichen Sinne gibt der Durchsatz an, wie viele Daten Sie in einer begrenzten Pipe unterbringen können.

1. Weg: Systemleistung

Es gibt wirklich nur ein paar Methoden, mit denen bestimmt werden kann, ob ein Server einen CPU-Engpass aufweist, und es gibt nicht viele mögliche Ursachen für eine starke CPU-Nutzung. Einige dieser Probleme können mithilfe von PerfMon oder einem ähnlichen Systemüberwachungstool nachverfolgt werden, während andere mithilfe von SQL Profiler oder ähnlichen Tools nachverfolgt werden. Eine andere Methode ist, SQL-Befehle über Query Analyzer oder SSMS (SQL Server Management Studio) zu verwenden.

Meine Philosophie beim Bewerten einer Systemleistung ist „zuerst allgemein anfangen, dann tiefer gehen“. Schließlich können Sie sich erst dann auf Problembereiche konzentrieren, wenn Sie sie identifiziert haben. Nachdem Sie die Gesamt-CPU-Nutzung mit einem Tool wie PerfMon ausgewertet haben, können Sie damit einige sehr einfache und leicht verständliche Leistungsindikatoren untersuchen.

Einer der bekanntesten Leistungsindikatoren ist „Prozessorzeit (%)“. Wenn Sie sich in PerfMon befinden, wird er hervorgehoben, sobald Sie das Fenster „Leistungsindikatoren hinzufügen“ öffnen. Prozessorzeit (%) ist die Zeitdauer, die die Prozessoren mit der Ausführung von Arbeit beschäftigt sind. Bei Prozessoren wird die Nutzung in der Regel als hoch eingestuft, wenn der Wert für den Großteil Ihrer Höchstbetriebsdauer 80 Prozent oder höher ist. Es ist ganz normal, und Sie sollten davon ausgehen, dass Sie in den Zeiten, wenn der Server nicht mit einer Nutzung von 80 Prozent arbeitet, Spitzen bis zu 100 Prozent erhalten.

Ein anderer Leistungsindikator, den Sie auswerten sollten, ist die Prozessor-Warteschlangenlänge. Sie finden ihn unter dem System-Leistungsobjekt in PerfMon. Die Prozessor-Warteschlangenlänge zeigt, wie viele Threads darauf warten, Arbeit auf der CPU durchzuführen. SQL Server verwaltet seine Arbeit durch Zeitplanungsmodule im Datenbankmodul, wo es seine eigenen Anforderungen einreiht und verarbeitet. Da SQL Server seine eigene Arbeit verwaltet, verwendet es nur einen einzigen CPU-Thread für jeden logischen Prozessor. Das heißt, es sollte nur eine kleine Anzahl von Threads vorhanden sein, die in der Prozessorwarteschlange darauf warten, Arbeit auf einem System durchzuführen, das für SQL Server vorgesehen ist. In der Regel sollte der Wert, den Sie erhalten, nicht mehr als fünf Mal so hoch wie die Anzahl physischer Prozessoren auf einem dedizierten SQL Server sein. Meiner Meinung nach ist jedoch mehr als zwei Mal bereits problematisch. Auf Servern, auf denen der DBMS ein System gemeinsam mit anderen Anwendungen verwendet, werden Sie dies zusammen mit den Leistungsindikatoren „Prozessorzeit (%)“ und „Kontextwechsel/s“ überprüfen wollen (später mehr zu Kontextwechsel), um zu bestimmen, ob Ihre anderen Anwendungen oder der DBMS auf einen anderen Server verschoben werden müssen.

Wenn das Einreihen von Prozessoren in Warteschlangen zusammen mit starker CPU-Nutzung auftritt, untersuche ich die Leistungsindikatoren „SQL-Kompilierungen/Sekunde“ und „Erneute SQL-Kompilierungen/Sekunde“ in SQL Server: SQL-Statistik-Leistungsobjekt (siehe Abbildung 1). Kompilieren und erneutes Kompilieren von Abfrageplänen erhöht die CPU-Nutzung eines Systems. Die Werte, die Sie erhalten, sollten nahezu Null sein, was die erneuten Kompilierungen angeht. Achten Sie jedoch auf Trends innerhalb Ihrer Systeme, um das Standardverhalten Ihres Servers und die Anzahl der Standardkompilierungen zu bestimmen. Erneute Kompilierungen können nicht immer vermieden werden, doch Abfragen und gespeicherte Prozeduren können optimiert werden, sodass erneute Kompilierungen verringert und Abfragepläne wiederverwendet werden können. Vergleichen Sie diese Werte mit den tatsächlichen SQL-Anweisungen, die in das System kommen, mithilfe von „Batchanforderungen/Sekunde“, einem ebenfalls in SQL Server verfügbaren Leistungsindikator: SQL-Statistik-Leistungsobjekt. Wenn die Kompilierungen und erneuten Kompilierungen pro Sekunde eine hohen prozentualen Anteil der in das System kommenden Batchanforderungen darstellen, dann sollte dieser Bereich überprüft werden. In einigen Situationen kann es vorkommen, dass SQL-Entwickler nicht verstehen, wie oder warum ihr Code zu dieser Art von Systemressourcenproblemen beiträgt. Weiter unten werde ich einige Verweise bereitstellen, um Ihnen zu helfen, diese Art von Aktivität einzuschränken.

Abbildung 1** Auswählen der zu überwachenden Leistungsindikatoren **(Klicken Sie zum Vergrößern auf das Bild)

Überprüfen Sie in PerfMon außerdem den Leistungsindikator „Kontextwechsel/s“ (siehe Abbildung 2). Er teilt Ihnen mit, wie oft Threads aus den Zeitplanungsmodulen des Betriebssystems (nicht den SQL-Zeitplanungsmodulen) herausgenommen werden müssen, um andere wartende Threads zu bearbeiten. Oft sind Kontextwechsel viel häufiger auf Datenbanksystemen zu finden, die gemeinsam mit anderen Anwendungen wie IIS oder anderen Herstelleranwendungs-Serverkomponenten verwendet werden. Der Schwellenwert, den ich für Kontextwechsel/s verwende, ist ungefähr 5000 Mal so groß wie die Anzahl der Prozessoren im Server. Dieser Wert kann auch auf Systemen hoch sein, auf denen Hyper-Threading aktiviert ist und die gleichzeitig einen mittleren bis hohen Wert für die CPU-Nutzung aufweisen. Wenn die CPU-Nutzung und der Kontextwechsel regelmäßig ihre Schwellenwerte überschreiten, zeigt dies einen CPU-Engpass an. Falls dies regelmäßig vorkommt und Ihr System veraltet ist, benötigen Sie mehr oder schnellere CPUs. Weitere Informationen finden Sie in der Randleiste „Hyper-Threading“.

Figure 2 Zu beobachtende Leistungsindikatoren

Der SQL Server Lazy Writer (in SQL Server 2000) bzw. der Ressourcen-Monitor (wie er in SQL Server 2005 genannt wird) ist ein weiterer Bereich, der bei einer starken CPU-Nutzung überprüft werden muss. Das Leeren der Puffer und der Prozedurcaches kann die CPU-Zeit durch den Ressourcenthread namens „Ressourcen-Monitor“ verlängern. Der Ressourcen-Monitor ist ein SQL Server-Prozess, der bestimmt, welche Seiten behalten und welche vom Pufferpool auf den Datenträger geleert werden müssen. Jede Seite im Puffer und die Prozedurcaches werden von Anfang an mit Kosten versehen, die für die Ressourcen stehen, die beim Platzieren der Seite in den Cache verbraucht werden. Dieser Kostenwert wird bei jedem Scannen des Ressourcen-Monitors verringert. Wenn eine Anforderung Cacheplatz benötigt, werden die Seiten auf Grundlage der Kosten, die mit den einzelnen Seiten verbunden sind, aus dem Speicher geleert. Dabei werden die Seiten mit den niedrigsten Werten als Erstes geleert. Die Aktivität des Ressourcen-Monitors kann mithilfe des Leistungsindikators „Verzögerte Schreibvorgänge/Sekunde“ in SQL Server nachverfolgt werden. Puffer-Manager-Objekt in PerfMon. Sie sollten nachverfolgen, wie dieser Wert dazu neigt zu bestimmen, welcher Schwellenwert auf Ihrem System typisch ist. Dieser Leistungsindikator wird in der Regel zusammen mit den Leistungsindikatoren „Lebenserwartung von Seiten“ und „Prüfpunkte/Sekunde“ überprüft, um zu bestimmen, ob der Arbeitsspeicher ungenügend ist.

Der Leistungsindikator „Lebenserwartung von Seiten“ (Page Life Expectancy, PLE) hilft beim Ermitteln der Speicherauslastung. Der PLE-Leistungsindikator zeigt an, wie lange sich eine Datenseite im Puffercache aufhält. 300 Sekunden ist der in der Branche anerkannte Schwellenwert für diesen Leistungsindikator. Jeder Durchschnittswert, der längere Zeit unter 300 Sekunden liegt, zeigt an, dass die Seiten zu häufig aus dem Speicher geleert werden. Wenn dies geschieht, führt es dazu, dass der Ressourcen-Monitor stärker belastet wird, was wiederum mehr Aktivität auf die Prozessoren verlagert. Der PLE-Leistungsindikator sollte zusammen mit dem Prüfpunkte/Sekunde-Leistungsindikator bewertet werden. Wenn im System ein Prüfpunkt auftritt, werden die problematischen Datenseiten im Puffercache auf den Datenträger geleert. Dadurch fällt der PLE-Wert. Im Grunde ist der Ressourcen-Monitor-Prozess der Mechanismus, der diese Seiten auf den Datenträger leert. Während diese Prüfpunkte ausgeführt werden, müssen Sie also damit rechnen, dass der Wert für Verzögerte Schreibvorgänge/Sekunde steigt. Wenn Ihr PLE-Wert sofort nach Abschluss eines Prüfpunkts steigt, können Sie dieses vorläufige Symptom ignorieren. Wenn Sie jedoch feststellen, dass der PLE-Schwellenwert regelmäßig unterschritten wird, sieht es sehr danach aus, dass der zusätzliche Speicher Ihre Probleme verringern und dabei einige Ressourcen für die CPU freigeben wird. All diese Leistungsindikatoren sind in SQL Server verfügbar: Puffer-Manager-Leistungsobjekt.

2. Weg: Abfrageleistung

SP-Verfolgung

Bei der Ablaufverfolgung Ihrer SQL Server-Anwendung lohnt es sich, die gespeicherten Prozeduren kennenzulernen, die für die Verfolgung verwendet werden. Das Verwenden der grafischen Benutzeroberfläche (SQL Server Profiler) für die Verfolgung kann die Systemauslastung um 15 bis 25 Prozent erhöhen. Wenn Sie gespeicherte Prozeduren in Ihrer Verfolgung verwenden können, kann dies zu einer Verringerung um etwa die Hälfte führen.

Wenn ich weiß, dass in meinem System ein Engpass aufgetreten ist, und herausfinden will, welche der aktuellen SQL-Anweisungen Probleme auf meinem Server verursachen, führe ich die unten angeführte Abfrage aus. Diese Abfrage hilft mir, einzelne Anweisungen und die von ihnen genutzten Ressourcen sowie die Anweisungen anzuzeigen, die für die Verbesserungen der Leistung überprüft werden müssen. Weitere Informationen zu SQL-Ablaufverfolgungen finden Sie unter msdn2.microsoft.com/ms191006.aspx.

SELECT 
    substring(text,qs.statement_start_offset/2
        ,(CASE    
            WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 
            ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) 
    ,qs.plan_generation_num as recompiles
    ,qs.execution_count as execution_count
    ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
    ,qs.total_worker_time as cpu_time
    ,qs.total_logical_reads as reads
    ,qs.total_logical_writes as writes
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    LEFT JOIN sys.dm_exec_requests r 
        ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC

Abfragepläne werden ausgewertet, optimiert, kompiliert und im Prozedurcache platziert, wenn eine neue Abfrage an SQL Server gesendet wird. Jedes Mal, wenn eine Abfrage zum Server gesendet wird, wird der Prozedurcache überprüft, um so einen Abfrageplan mit einer Anforderung zu paaren. Wenn keiner gefunden wird, erstellt SQL Server einen neuen Plan dafür, was einen möglicherweise sehr aufwendigen Vorgang darstellt.

Hier sind einige Überlegungen für die Optimierung der T-SQL-CPU:

• Wiederverwenden des Abfrageplan
• Verringern von Kompilierungen und erneuten Kompilierungen
• Sortieren von Vorgängen
• Fehlerhafte Verknüpfungen
• Fehlende Indexe
• Tabellen-/Indexscans
• Funktionsverwendung in Select- und Where-Klauseln
• Multithreadvorgänge

Im Folgenden soll alles wieder perspektivisch zurechtgerückt werden. SQL Server ruft in der Regel Daten sowohl aus dem Speicher als auch vom Datenträger ab. Oft wird dabei mit mehr als nur einer Seite gearbeitet. In den meisten Fällen arbeiten mehrere Teile einer Anwendung an einem Datensatz und führen dabei mehrere kleinere Abfragen aus oder verknüpfen Tabellen, um eine vollständige Ansicht relevanter Daten bereitzustellen. In OLAP-Umgebungen kann es sein, dass Ihre Anwendungen Millionen von Zeilen aus einer oder zwei Tabellen abrufen, damit Sie die Daten für einen regionalen Verkaufsbericht zusammentragen und zusammenfassen können. In solchen Situationen kann das Zurückgeben von Daten in Millisekunden gemessen werden, falls sich die Daten im Speicher befinden. Doch aus diesen Millisekunden können Minuten werden, wenn dieselben Daten von der Festplatte und nicht aus dem Arbeitsspeicher abgerufen werden.

Das erste Beispiel ist eine Situation mit einer hohen Zahl von Transaktionen, in der die Planwiederverwendung von der Anwendung abhängt. Eine geringe Planwiederverwendung führt zu einer großen Anzahl von Kompilierungen von SQL-Anweisungen, die wiederum die CPU-Verarbeitung stark ankurbeln. Im zweiten Beispiel kann die übermäßige Systemressourcennutzung dazu führen, dass die CPU eines Systems zu aktiv ist, da vorhandene Daten ständig vom Puffercache geleert werden müssen, um für die große Menge neuer Datenseiten Platz zu machen.

Stellen Sie sich ein Transaktionssystem vor, in dem eine SQL-Anweisung, wie unten angeführt, innerhalb von 15 Minuten 2000 Mal ausgeführt wird, um Kartonversandinformationen abzurufen. Ohne die Wiederverwendung von Abfrageplänen könnte dies theoretisch zu individuellen Ausführungszeiten von etwa 450 Millisekunden pro Anweisung führen. Falls nach der anfänglichen Ausführung der gleiche Abfrageplan verwendet wird, könnte jede nachfolgende Abfrage wahrscheinlich in ungefähr 2 Millisekunden ausgeführt werden, was die gesamte Ausführungszeit auf ungefähr 5 Sekunden reduziert.

USE SHIPPING_DIST01;
SELECT 
    Container_ID
    ,Carton_ID
    ,Product_ID
    ,ProductCount
    ,ModifiedDate
FROM Container.Carton
WHERE Carton_ID = 982350144;

Das Wiederverwenden von Abfrageplänen ist für die ideale Leistung auf Transaktionssystemen wichtig und wird in den meisten Fällen durch das Parametrisieren Ihrer Abfragen oder gespeicherten Prozeduren erreicht. Hier sind einige ausgezeichnete Ressourcen, in denen Sie Informationen zum Wiederverwenden von Abfrageplänen finden:

• Batchkompilierung, erneute Kompilierung und Probleme mit dem Zwischenspeichern von Abfrageplänen in SQL Server 2005 (microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)
• Optimieren der gespeicherten Prozeduren von SQL Server zur Vermeidung erneuter Kompilierungen (sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
• Erneute Kompilierung von Abfragen in SQL Server 2000 (msdn2.microsoft.com/aa902682.aspx)

Eine weitere nützliche Ressource voller Informationen sind die SQL Server 2005-DMVs (dynamische Verwaltungssichten, Dynamic Management Views). Bei starker CPU-Nutzung gibt es einige DMVs, die ich verwende, um zu bestimmen, ob die CPU korrekt verwendet wird.

Einer der dynamischen Verwaltungssichten, die ich überprüfe, ist sys.dm_os_wait_stats. Sie ermöglicht den DBAs das Bestimmten der einzelnen Ressourcentypen oder -funktionen, die SQL Server verwendet. Außerdem misst sie die Dauer der durch diese Ressource verursachten Systemwartezeit. Die Leistungsindikatoren in diesem DMV sind kumulativ. Das heißt, dass Sie für eine neu ansetzende Untersuchung, welche Ressourcen sich möglicherweise auf verschiedene Systembereiche auswirken, zuerst den Befehl „DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)“ ausführen müssen, um alle Leistungsindikatoren nach dem Überprüfen aller ungeklärten Probleme zurückzusetzen. Die DMV „sys.dm_os_wait_stats“ entspricht dem Befehl zur Überprüfung der Datenbankkonsistenz „DBCC SQLPERF(WAITSTATS)“ in SQL Server 2000. Weitere Informationen zu den verschiedenen Wartezeitentypen erhalten Sie in der SQL Server-Onlinedokumentation unter msdn2.microsoft.com/ms179984.aspx.

Es ist wichtig zu wissen, dass in einem System Wartezeiten auch bei optimalem Betrieb normal sind. Sie müssen nur bestimmen, ob die Wartezeiten von einem CPU-Engpass bedingt werden. Was die Gesamtwartezeit betrifft, sollten die Signalwartezeiten so kurz wie möglich sein. Die Zeitdauer, die eine bestimmte Ressource auf eine Prozessorressource wartet, kann einfach dadurch bestimmt werden, dass die Signalwartezeit von der gesamten Wartezeit abgezogen wird. Dieser Wert sollte nicht größer als ungefähr 20 Prozent der gesamten Wartezeit sein.

Die DMV „sys.dm_exec_sessions“ zeigt alle offenen Sitzungen auf dem SQL Server an. Diese dynamische Verwaltungssicht stellt eine Zusammenfassung der Leistung der einzelnen Sitzungen und der gesamten Arbeit bereit, die jede Sitzung von Anfang an durchgeführt hat. Dazu gehört die Gesamtzeit, die eine Sitzung gewartet hat, die gesamte CPU-Nutzung, die Speichernutzung und eine Zählung der Lese- und Schreibvorgänge. Die DMV informiert Sie außerdem über die Anmeldung, die Anmeldezeit, den Hostcomputer und das letzte Mal, dass die Sitzung eine Anforderung an SQL Server gesendet hat.

Mithilfe der DMV „sys.dm_exec_sessions“ sind Sie in der Lage, nur die aktiven Sitzungen zu bestimmen. Wenn Sie also einen hohen Wert für die CPU-Nutzung erhalten, sollten Sie zuerst hier nachsehen. Überprüfen Sie zuerst die Sitzungen, die eine hohe CPU-Zahl aufweisen. Bestimmen Sie die Anwendung und den Benutzer, der die Arbeit durchgeführt hat. Gehen Sie dann ins Detail. Das Koppeln der DMV „sys.dm_exec_sessions“ mit der DMV „sys.dm_exec_requests“ kann viele der Informationen bereitstellen, die anhand der gespeicherten Prozeduren „sp_who“ und „sp_who2“ verfügbar sind. Wenn Sie diese Daten zusammen mit der dynamischen Verwaltungsfunktion (dynamic management function, DMF) namens „sys.exec_sql_text“ über die Spalte „sql_handle“ verknüpfen, können Sie die von der Sitzung derzeit ausgeführte Abfrage abrufen. Der Ausschnitt in Abbildung 3 illustriert, wie diese Daten in einen Zusammenhang gebracht werden, um zu bestimmen, was derzeit auf einem Server geschieht.

Figure 3 Bestimmen der Serveraktivität

SELECT es.session_id
    ,es.program_name
    ,es.login_name
    ,es.nt_user_name
    ,es.login_time
    ,es.host_name
    ,es.cpu_time
    ,es.total_scheduled_time
    ,es.total_elapsed_time
    ,es.memory_usage
    ,es.logical_reads
    ,es.reads
    ,es.writes
    ,st.text
FROM sys.dm_exec_sessions es
    LEFT JOIN sys.dm_exec_connections ec 
        ON es.session_id = ec.session_id
    LEFT JOIN sys.dm_exec_requests er
        ON es.session_id = er.session_id
    OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
WHERE es.session_id > 50    -- < 50 system sessions
ORDER BY es.cpu_time DESC

Meiner Meinung nach hilft diese Anweisung zu bestimmen, auf welche Anwendungen der Schwerpunkt gelegt werden sollte. Wenn ich die CPU, den Speicher und die Lese- und Schreibvorgänge für alle Sitzungen innerhalb einer Anwendung vergleiche und erkenne, dass die CPU-Ressource viel stärker beansprucht wird als andere genutzte Ressourcen, setze ich als Erstes bei diesen SQL-Anweisungen an.

Um SQL-Anweisungen dem Verlauf entsprechend für eine Anwendung nachzuverfolgen, verwende ich SQL Server-Ablaufverfolgungen. Sie erhalten diese entweder durch das Tool „SQL Server Profiler“ oder durch das Ablaufverfolgungssystem der gespeicherten Prozeduren. So können Sie bewerten, was vor sich geht. (Siehe die Randleiste „SP-Verfolgung“ für weitere Informationen zu diesem Thema.) Profiler sollte bei Anweisungen mit starker CPU-Nutzung sowie bei Hash- und Sortierwarnungen, Cachefehlern und anderen Warnzeichen überprüft werden. Dies kann Ihnen helfen, die Fehlersuche auf bestimmte SQL-Anweisungen oder einen bestimmten Zeitraum, der für eine starke Ressourcennutzung verantwortlich ist, einzuengen. Mit Profiler können Sie Folgendes nachverfolgen: den SQL-Anweisungstext, Ausführungspläne, die CPU-Nutzung, die Speichernutzung, logische Lese- und Schreibvorgänge, das Zwischenspeichern von Abfrageplänen, erneute Kompilierungen, die Entfernung von Abfrageplänen aus dem Cache, Cachefehler, Scans von Tabellen und Indexen, fehlende Statistiken und viele andere Ereignisse.

Nachdem ich entweder von den gespeicherten sp_trace-Prozeduren oder vom SQL Server Profiler Daten gesammelt habe, verwende ich gewöhnlich eine Datenbank, die mit den Ablaufverfolgungsdaten gefüllt wird – entweder nachträglich oder durch die entsprechende Einstellung der Ablaufverfolgung, sodass sie in die Datenbank schreibt. Das nachträgliche Auffüllen der Datenbank kann mithilfe der SQL Server-Systemfunktion „fn_trace_getinfo“ erfolgen. Dieser Ansatz hat den Vorteil, dass ich die Daten auf verschiedene Art und Weise abfragen und sortieren kann, um zu sehen, welche SQL-Anweisungen die CPU am meisten beansprucht haben oder welche die meisten Lesevorgänge aufweisen. Außerdem kann ich zählen, wie viele erneute Kompilierungen stattgefunden haben und vieles andere. Hier ist ein Beispiel, in dem veranschaulicht wird, wie diese Funktion verwendet wird, um eine Tabelle mit einer Profilerablaufverfolgungsdatei zu laden. Die Standardeinstellung gibt an, dass alle Ablaufverfolgungsdateien für diese Ablaufverfolgung in der Reihenfolge geladen werden, in der sie erstellt wurden:

SELECT * INTO trc_20070401
FROM fn_trace_gettable('S:\Mountpoints\TRC_20070401_1.trc', default);
GO

Zusammenfassung

Wie Sie gesehen haben, deutet eine starke CPU-Nutzung nicht unbedingt darauf hin, dass ein CPU-Engpass vorliegt. Hinter einer starken CPU-Nutzung kann sich eine Reihe von anderen Anwendungs- oder Hardwareengpässen verbergen. Nachdem Sie erkannt haben, dass eine starke CPU-Nutzung vorliegt, obwohl Ihre anderen Leistungsindikatoren stabile Werte aufweisen, können Sie anfangen, die Ursache innerhalb des Systems zu suchen, und eine Lösung herausarbeiten (sei es der Erwerb weiterer CPUs oder das Optimieren Ihres SQL-Codes). Welchen Weg Sie auch einschlagen, geben Sie nicht auf. Mit den Tipps, die in diesem Artikel bereitgestellt werden, sowie etwas Praxis und Recherche ist das Optimieren der CPU-Nutzung in SQL Server ein erreichbarer Ausführungsplan.

Zach Nichter ist SQL Server-Experte und verfügt über mehr als 10 Jahre Erfahrung in diesem Bereich. Er hat bereits mehrere SQL Server-Supportrollen innegehabt, einschließlich DBA, Teamleiter, Manager und Berater. Derzeit ist Zach Nichter bei Levi Strauss & Co. als DBA-Architekt tätig. Der Schwerpunkt seiner Tätigkeit liegt im Bereich der Leistung, Überwachung und Architektur von SQL Server und in anderen strategischen Initiativen. Darüber hinaus ist er der Autor eines Videoblogs, der unter www.sqlcatch.com

© 2008 Microsoft Corporation und CMP Media, LLC. Alle Rechte vorbehalten. Die nicht genehmigte teilweise oder vollständige Vervielfältigung ist nicht zulässig.