MSDN Magazin > Home > Ausgaben > 2008 > January >  SQL Server: Entdecken Sie versteckte Daten...
SQL Server
Entdecken Sie versteckte Daten, und optimieren Sie die Anwendungsleistung
Ian Stirk
Codedownload verfügbar unter: DMVsinSQLServer2008_01.exe (155 KB)
Browse the Code Online

Themen in diesem Artikel:
  • Dynamische Verwaltungsansichten und -funktionen
  • Ursachen für Serverwartezeiten
  • Häufige Indexprobleme
  • Probleme im Zusammenhang mit logischen E/A-Vorgängen
In diesem Artikel werden folgende Technologien verwendet:
SQL Server
Viele Probleme mit der Anwendungsleistung lassen sich auf schlecht funktionierende Datenbankabfragen zurückführen, doch es gibt eine Reihe von Möglichkeiten zum Verbessern der Datenbankleistung. SQL ServerTM 2005 sammelt viele Informationen, mit denen Sie die Ursachen für solche Leistungsprobleme identifizieren können.
SQL Server 2005 sammelt Daten, die sich auf das Ausführen von Abfragen beziehen. Diese Daten werden im Speicher festgehalten und mit jedem Serverneustart erneut gesammelt. Sie können verwendet werden, um zahlreiche Probleme und Metriken einschließlich jener im Zusammenhang mit Tabellenindizes, Abfrageleistung und Server-E/A-Vorgängen zu identifizieren. Sie können diese Daten über die SQL Server Dynamic Management Views (DMVs) und die zugehörigen Dynamic Management Functions (DMFs) abfragen. Dabei handelt es sich um systembasierte Ansichten und Funktionen, die Serverstatusinformationen anzeigen, mit denen sich Probleme diagnostizieren und die Datenbankleistung optimieren lassen.
In diesem Artikel werden Bereiche vorgestellt, in denen die Leistung mithilfe von Informationen verbessert werden kann, die bereits von SQL Server 2005 gesammelt werden. Dieser Ansatz ist größtenteils nichtintrusiv, da vorhandene Daten gesammelt und untersucht werden, wobei in der Regel zugrunde liegende Systemdaten abgefragt werden.
Es wird aufgezeigt, wie Sie diese Informationen erhalten können. Zudem werden die zugrunde liegenden DMVs erörtert, Einschränkungen identifiziert, die beim Interpretieren der Daten beachtet werden sollten, und zusätzliche Bereiche vorgestellt, in denen Sie möglicherweise Leistungsverbesserungen vornehmen können. Zu diesem Zweck werden eine Reihe von SQL-Skripts vorgestellt und untersucht, die die verschiedenen Aspekte der von SQL Server 2005 gesammelten Daten ausführlich beschreiben. Eine vollständige und umfassend kommentierte Version dieses Skripts kann von der MSDN® Magazin-Website heruntergeladen werden.
Einige der hier erörterten Schritte beziehen sich auf den Server als Ganzes, einschließlich aller auf einem bestimmten Server gehosteten Datenbanken. Es ist möglich, sich bei Bedarf durch Hinzufügen der entsprechenden Filterung auf eine bestimmte Datenbank zu konzentrieren, beispielsweise durch Hinzufügen des Datenbanknamens zu einer Abfrage.
Umgekehrt sind einige Abfragen mit der sys.indexes-DMV verbunden, bei der es sich um eine datenbankspezifische Ansicht handelt, die Ergebnisse nur für die aktuelle Datenbank meldet. In diesen Fällen wurden die Abfragen ergänzt, damit sie mithilfe der gespeicherten Systemprozedur „sp_MSForEachDB“ alle Datenbanken durchlaufen, sodass Sie serverweite Ergebnisse erhalten.
Um auf die relevantesten Datensätze für eine bestimmte Leistungsmetrik abzuzielen, wird die Anzahl der mithilfe der SQL TOP-Funktion zurückgegebenen Datensätze eingeschränkt.

Ursachen für Serverwartezeiten
Für den Benutzer drückt sich schlechte Leistung in der Regel als eine Reihe von Wartezeiten aus. Immer wenn eine SQL-Abfrage ausgeführt werden kann, aber auf eine andere Ressource wartet, zeichnet sie Informationen über die Ursache der Wartezeit auf. Auf diese Informationen kann mithilfe der sys.dm_os_wait_stats-DMV zugegriffen werden. Sie können die kumulierte Ursache aller Wartezeiten mithilfe des in Abbildung 1 dargestellten SQL-Skripts untersuchen.
SELECT TOP 10
 [Wait type] = wait_type,
 [Wait time (s)] = wait_time_ms / 1000,
 [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 
               / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' 
ORDER BY wait_time_ms DESC;

Abbildung 1 Die SQL-Abfrage zeichnet Ursachen von Wartezeiten auf 
Bei Ausführung dieses Skripts wird als Ergebnis der Wartezeittyp sortiert nach der gesamten Wartezeit aufgeführt. In den Beispielergebnissen sehen Sie, dass E/A-Vorgänge als Ursache für Wartezeiten verhältnismäßig weit oben stehen. Beachten Sie, dass es dabei nur um E/A-Vorgänge (zum Lesen/Schreiben von Daten im Speicher) und nicht um physische E/A-Vorgänge geht, da sich die Daten nach dem ersten Laden in der Regel im Speicher befinden.

Lese- und Schreibvorgänge
Hohe Auslastung durch E/A-Vorgänge kann ein Indikator schlechter Datenzugriffsmechanismen sein. SQL Server 2005 verfolgt die Gesamtzahl von Lese- und Schreibvorgängen für die Anforderungen aller Abfragen. Durch Summieren dieser Zahlen können Sie bestimmen, welche Datenbanken insgesamt die meisten Lese- und Schreibvorgänge durchführen.
Die sys.dm_exec_query_stats-DMV enthält aggregierte Leistungsstatistiken für zwischengespeicherte Abfragepläne. Dazu zählen Informationen über die Anzahl der logischen Lese- und Schreibvorgänge und darüber, wie oft die Abfrage ausgeführt wurde. Wenn Sie diese DMV mit der sys.dm_exec_sql_text-DMF verbinden, können Sie die Anzahl von Lese- und Schreibvorgängen nach Datenbank summieren. Beachten Sie, dass der neue SQL Server 2005 CROSS APPLY-Operator für diese Verbindung verwendet wird. Das Skript, das zur Identifizierung der Datenbanken dient, die die meisten Lese- und Schreibvorgänge verwenden, ist in Abbildung 2 dargestellt.
SELECT TOP 10 
        [Total Reads] = SUM(total_logical_reads)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

SELECT TOP 10 
        [Total Writes] = SUM(total_logical_writes)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;

Abbildung 2 Identifizieren der meisten Lese- und Schreibvorgänge 
Die Ergebnisse zeigen an, welche Datenbanken die meisten logischen Seiten lesen und schreiben. Der obere Satz von Daten ist nach Gesamtlesevorgängen, der untere Satz nach Gesamtschreibvorgängen sortiert.
Es ist klar ersichtlich, dass DatabaseName bei einigen Instanzen auf NULL festgelegt ist. Diese Einstellung identifiziert Ad-hoc- und vorbereitete SQL-Anweisungen. Diese Information ist nützlich, um den Nutzungsgrad der systemeigenen SQL zu identifizieren, allein schon eine potenzielle Ursache vieler verschiedener Probleme. (Hierdurch wird beispielsweise angezeigt, dass Abfragepläne oder Code nicht wiederverwendet werden und im Sicherheitsbereich potenzielle Probleme bestehen.)
Ein hoher Wert für „tempdb“ kann auf die übermäßige Verwendung temporärer Tabellen, übermäßige Neukompilierungen oder ein ineffizientes Gerät hinweisen. Mithilfe dieser Ergebnisse könnte z. B. identifiziert werden, welche Datenbanken hauptsächlich für die Berichterstellung (ein hohes Maß an Datenauswahl) im Gegensatz zu Transaktionen (viele Updates) verwendet werden. Jeder Datenbanktyp (Berichterstellung oder Transaktionen) hat verschiedene Indizierungsanforderungen. Darauf wird gleich noch näher eingegangen.

Fehlende Indizes nach Datenbank
Wenn SQL Server eine Abfrage verarbeitet, speichert der Optimierer eine Aufzeichnung der Indizes, die zum Erfüllen der Abfrage verwendet werden müssen. Wenn diese Indizes nicht gefunden werden, erstellt SQL Server einen Datensatz des fehlenden Index. Diese Informationen können mithilfe der sys.dm_db_missing_index_details-DMV angezeigt werden.
Sie können mithilfe des in Abbildung 3 dargestellten Skripts anzeigen, bei welchen Datenbanken auf einem bestimmten Server fehlende Indizes vorliegen. Das Ermitteln dieser fehlenden Indizes ist wichtig, da die Indizes oft einen idealen Pfad zum Abrufen von Abfragedaten bereitstellen. Dies kann wiederum E/A-Vorgänge verringern und die Gesamtleistung verbessern. Mit dem von mir geschriebenen Skript wird „sys.dm_db_missing_index_details“ untersucht und die Anzahl fehlender Indizes pro Datenbank summiert, sodass Sie leicht bestimmen können, welche Datenbanken näher untersucht werden müssen.
SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

Abbildung 3 Identifizieren fehlender Datenbanken 
Datenbanken sind recht oft in Transaktions- und Berichtssysteme unterteilt. Es dürfte verhältnismäßig einfach für Sie sein, die vorgeschlagenen fehlenden Indizes auf die Berichtsdatenbanken anzuwenden. Bei den Transaktionsdatenbanken andererseits muss in der Regel die Wirkung der zusätzlichen Indizes auf die zugrunde liegenden Tabellendaten weiter untersucht werden.

Kostspielige fehlende Indizes
Indizes wirken sich unterschiedlich auf die Abfrageleistung aus. Sie können bis zu den kostspieligsten fehlenden Indizes über alle Datenbanken auf dem Server hinweg vordringen, um herauszufinden, welche fehlenden Indizes sich wahrscheinlich am meisten auf die Leistung auswirken.
Die sys.dm_db_missing_index_group_stats-DMV stellt fest, wie oft SQL versucht hat, einen bestimmten fehlenden Index zu verwenden. Die sys.dm_db_missing_index_details-DMV zeigt die fehlende Indexstruktur im Einzelnen, beispielsweise die für die Abfrage erforderlichen Spalten. Diese beiden DMVs sind über die sys.dm_db_missing_index_groups-DMV miteinander verknüpft. Die Leistungseinbußen durch den fehlenden Index (die Gesamtkostenspalte) wird berechnet als das Produkt der durchschnittlichen Gesamtbenutzerkosten und der durchschnittlichen Benutzerauswirkung, multipliziert mit der Summe der Suchvorgänge und Scans durch die Benutzer.
Sie können das in Abbildung 4 dargestellte Skript zum Identifizieren der kostspieligsten fehlenden Indizes verwenden. Die Ergebnisse dieser Abfrage, die nach Gesamtkosten geordnet sind, zeigen die Leistungseinbußen der wichtigsten fehlenden Indizes zusammen mit Einzelheiten zu Datenbank/Schema/Tabelle und den in den fehlenden Indizes erforderlichen Spalten an. Dieses Skript identifiziert insbesondere, welche Spalten in Gleichheits- und Ungleichheits-SQL-Anweisungen verwendet werden. Zusätzlich gibt es an, welche anderen Spalten als einbezogene Spalten in einem fehlenden Index verwendet werden sollten. Mit einbezogenen Spalten können Sie mehr abgedeckte Abfragen erfüllen, ohne die Daten von der zugrunde liegenden Seite zu erhalten, sodass weniger E/A-Vorgänge verwendet werden und die Leistung verbessert wird.
SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

Abbildung 4 Leistungseinbußen durch fehlende Indizes (Klicken Sie zum Vergrößern auf das Bild)
Beachten Sie, dass die Ergebnisse nicht die Reihenfolge angeben, in der die Spalten im erforderlichen Index erstellt werden sollten. Um dies festzulegen, sollten Sie die gesamte SQL-Codebasis prüfen. Allgemein gilt, dass die am häufigsten ausgewählten Spalten zuerst im Index erscheinen sollten.
Es sollte auch beachtet werden, dass nur die Benutzerspalten (beispielsweise „user_seeks“ und „user_scans“) beim Berechnen der Leistungseinbußen durch den fehlenden Index in Betracht gezogen werden. Der Grund dafür ist , dass Systemspalten meistens Verwendungsstatistiken, Datenbankkonsistenzüberprüfung (Database Consistency Checking, DBCC) und Datendefinitionssprachbefehle (Data Definition Language, DDL) darstellen und diese zum Erfüllen der Geschäftsfunktionen weniger wichtig sind, als die Datenbankadministratorfunktionen.
Sie sollten unbedingt daran denken, dass Sie die potenziellen Leistungseinbußen durch den zusätzlichen Index besonders berücksichtigen müssen, wenn es in der zugrunde liegenden Tabelle zu Datenänderungen kommt. Daher sollte die zugrunde liegende SQL-Codebasis zusätzlich untersucht werden.
Wenn empfohlen wird, zahlreiche Spalten aufzunehmen, sollten Sie die zugrunde liegende SQL untersuchen, da dies ein Hinweis darauf sein könnte, dass die umfassende „SELECT*“-Anweisung verwendet wird. Wenn dies der Fall ist, überarbeiten Sie am besten Ihre Auswahlabfragen.

Nicht verwendete Indizes
Nicht verwendete Indizes können sich negativ auf die Leistung auswirken. Dies ist darauf zurückzuführen, dass der Index ebenfalls aktualisiert werden muss, wenn die zugrunde liegenden Tabellendaten geändert werden. Dies beansprucht natürlich zusätzliche Zeit und kann sogar die Wahrscheinlichkeit einer Blockierung erhöhen.
Wenn ein Index zum Erfüllen einer Abfrage verwendet wird und infolge von Updates der zugrunde liegenden Tabellendaten aktualisiert wird, aktualisiert SQL Server die entsprechenden Einzelheiten zur Indexverwendung. Diese Verwendungsdetails können zum Anzeigen nicht verwendeter Indizes angezeigt werden.
Die sys.dm_db_index_usage_stats-DMV zeigt an, wie oft und in welchem Umfang Indizes verwendet werden. Sie wird mit der sys.indexes-DMV verknüpft, die beim Erstellen des Index verwendete Informationen enthält. Sie können die verschiedenen Benutzerspalten auf einen Wert von 0 überprüfen, um nicht verwendete Indizes zu identifizieren. Die Auswirkung der Systemspalten wird erneut aus den vorstehend erörterten Gründen ignoriert. Mithilfe des in Abbildung 5 dargestellten Skripts können Sie die nicht verwendeten Indizes identifizieren, die zu den größten Leistungseinbußen führen.
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
        -- Useful fields below:
        --, *
INTO #TempUnusedIndexes
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND s.[object_id] = -999  -- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempUnusedIndexes 
SELECT TOP 10    
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND i.name IS NOT NULL    -- Ignore HEAP indexes.
ORDER BY user_updates DESC
;
'

-- Select records.
SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
-- Tidy up.
DROP TABLE #TempUnusedIndexes

Abbildung 5 Identifizieren der kostspieligsten nicht verwendeten Indizes (Klicken Sie zum Vergrößern auf das Bild)
Die Ergebnisse dieser Abfrage zeigen die Indizes, die nicht zum Abrufen von Daten verwendet, sondern aufgrund von Änderungen in der zugrunde liegenden Tabelle aktualisiert wurden. Diese Updates werden in den user_updates- und system_updates-Spalten angezeigt. Die Ergebnisse werden nach der Anzahl von Benutzerupdates sortiert, die auf den Index angewendet wurden.
Sie müssen ausreichende Informationen sammeln, um sicherzustellen, dass der Index nicht verwendet wird. Schließlich wollen Sie einen Index, der möglicherweise für eine vierteljährlich oder jährlich ausgeführte Abfrage kritisch ist, nicht versehentlich entfernen. Beachten Sie außerdem, dass einige Indizes zum Einschränken von Einfügungen doppelter Datensätze oder zum Sortieren von Daten verwendet werden. Diese Faktoren müssen ebenfalls vor Entfernen unbenutzter Indizes berücksichtigt werden.
Die grundlegende Form der Abfrage wird nur auf die aktuelle Datenbank angewendet, da diese mit der sys.indexes-DMV verbunden ist, für die nur die aktuelle Datenbank relevant ist. Sie können Ergebnisse für alle Datenbanken auf dem Server mithilfe der gespeicherten Systemprozedur „sp_MSForEachDB“ extrahieren. Das dazu verwendete Muster wird in der Randleiste „Durchlaufen aller Datenbanken“ erklärt. Dieses Muster wird auch in anderen Abschnitten des Skripts verwendet, wenn alle Datenbanken auf dem Server durchlaufen werden sollen. Außerdem wurden Indizes des Typs „Heap“ herausgefiltert, da diese die systemeigene Struktur einer Tabelle ohne formellen Index darstellen.

Kostspielige verwendete Indizes
Es kann auch hilfreich sein, die Indizes (unter den verwendeten) zu identifizieren, die in Bezug auf Änderungen an den zugrunde liegenden Tabellen zu den größten Leistungseinbußen führen. Diese Leistungseinbußen wirken sich negativ auf die Leistung aus, doch der Index selbst könnte für den Datenabruf wichtig sein.
Die sys.dm_db_index_usage_stats-DMV zeigt an, wie oft und in welchem Umfang Indizes verwendet werden. Diese DMV wird mit der sys.indexes-DMV verknüpft, die beim Erstellen des Index verwendete Details enthält. Eine Prüfung der user_updates- und system_updates-Spalte zeigt die wartungsaufwändigsten Indizes an. Abbildung 6 stellt das zum Identifizieren der kostspieligsten Indizes verwendete Skript bereit und zeigt die Ergebnisse.
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempMaintenanceCost
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_updates + system_updates) > 0 -- Only report on active rows.
    AND s.[object_id] = -999  -- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempMaintenanceCost 
SELECT TOP 10
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_updates + system_updates) > 0 -- Only report on active rows.
ORDER BY [Maintenance cost]  DESC
;
'

-- Select records.
SELECT TOP 10 * FROM #TempMaintenanceCost 
ORDER BY [Maintenance cost]  DESC
-- Tidy up.
DROP TABLE #TempMaintenanceCost

Abbildung 6 Identifizieren der kostspieligsten Indizes (Klicken Sie zum Vergrößern auf das Bild)
Als Ergebnis werden die wartungsaufwändigsten Indizes zusammen mit Details über die beteiligte Datenbank/Tabelle angezeigt. Die Wartungskostenspalte wird als Summe der user_updates- und system_updates-Spalte berechnet. Die Nützlichkeit des Index (die in der Abrufnutzungsspalte angezeigt wird) errechnet sich aus der Summe der verschiedenen user_*-Spalten. Es ist wichtig, bei der Entscheidung, ob ein Index entfernt werden kann, dessen Nützlichkeit in Betracht zu ziehen.
Bei umfassenden Änderungen an den Daten können Ihnen diese Ergebnisse beim Identifizieren der Indizes helfen, die vor der Anwendung von Updates entfernt werden sollten. Sie können diese Indizes dann wiederverwenden, nachdem alle Updates durchgeführt wurden.

Oft verwendete Indizes
Durchlaufen aller Datenbanken
Die sys.indexes-DMV ist eine datenbankspezifische Ansicht. Daher zeigen Abfragen, die mit sys.indexes verbunden sind, nur Ergebnisse für die aktuelle Datenbank an. Sie können jedoch die gespeicherte Systemprozedur „sp_MSForEachDB“ verwenden, um alle Datenbanken auf dem Server zu durchlaufen und so serverweite Ergebnisse anzuzeigen. Dies ist das in diesen Fällen von mir verwendete Muster.
  1. Dabei wird eine temporäre Tabelle mit der erforderlichen Struktur erstellt, die dem Hauptteil des Codes ähnelt. Sie erhält einen Datensatz, der nicht existiert („object_id von -999“), sodass die temporäre Tabellenstruktur erstellt werden kann.
  2. Der Hauptteil des Codes wird ausgeführt und durchläuft alle Datenbanken auf dem Server. Beachten Sie, dass die Anzahl der (mithilfe der TOP-Anweisung) für jede Datenbank abgerufenen Datensätze der Anzahl von Datensätzen entsprechen sollte, die angezeigt werden sollen. Andernfalls sind die Ergebnisse für die TOP n-Datensätze über alle Datenbanken auf dem Server hinweg möglicherweise nicht wirklich repräsentativ.
  3. Die Datensätze werden aus der temporären Tabelle extrahiert und nach der jeweils interessanten Spalte sortiert (in diesem Fall ist es die user_updates-Spalte).

Sie können DMVs verwenden, um die am häufigsten verwendeten Indizes zu identifizieren, bei denen es sich um die häufigsten Pfade zu zugrunde liegenden Daten handelt. Dies sind Indizes, die durch Verbesserung bzw. Optimierung erhebliche Verbesserungen der Gesamtleistung bereitstellen könnten.
Die sys.dm_db_index_usage_stats-DMV enthält Details dazu, wie oft die Indizes zum Abrufen von Daten über Suchen, Scans und Nachschlagen verwendet werden. Diese DMV wird mit der sys.indexes-DMV verknüpft, die beim Erstellen des Index verwendete Details enthält. Die Spalte „Usage“ berechnet sich aus der Summe aller user_*-Felder. Dies kann mithilfe des in Abbildung 7 dargestellten Skripts geschehen. Die Ergebnisse dieser Abfrage zeigen sortiert nach Verwendung an, wie oft der Index verwendet wurde.
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the -- following step.
SELECT TOP 1
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempUsage
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 
-- Only report on active rows.
    AND s.[object_id] = -999  -- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempUsage 
SELECT TOP 10
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 -- Only report on active rows.
ORDER BY [Usage]  DESC
;
'

-- Select records.
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC
-- Tidy up.
DROP TABLE #TempUsage

Abbildung 7 Identifizieren der am häufigsten verwendeten Indizes (Klicken Sie zum Vergrößern auf das Bild)
Die am häufigsten verwendeten Indizes stellen die wichtigsten Zugriffswege zu den zugrunde liegenden Daten dar. Natürlich sollen diese Indizes nicht entfernt werden, doch eine nähere Untersuchung lohnt sich, um sicherzustellen, dass sie optimal sind. Sie sollten beispielsweise sicherstellen, dass die Indexfragmentierung (besonders bei Daten, die nacheinander abgerufen werden) gering ist und dass die zugrunde liegenden Statistiken auf dem neuesten Stand sind. Außerdem sollten Sie nicht verwendete Indizes in den Tabellen entfernen.

Logisch fragmentierte Indizes
Die logische Indexfragmentierung zeigt den Prozentsatz von Einträgen im Index an, die in der falschen Reihenfolge vorliegen. Dies ist nicht dasselbe wie die Fragmentierung der Seitenauslastung. Die logische Fragmentierung wirkt sich auf Reihenfolgescans aus, bei denen ein Index verwendet wird. Diese Fragmentierung sollte möglichst entfernt werden. Dies kann mit einer Neuerstellung oder Neuorganisation des Index erreicht werden.
Sie können die am logischsten fragmentierten Indizes mithilfe der folgenden DMVs identifizieren. Mithilfe der sys.dm_db_index_physical_stats-DMV können Sie Details über die Größe und Fragmentierung von Indizes anzeigen. Diese DMV wird mit der sys.indexes-DMV verknüpft, die beim Erstellen des Index verwendete Details enthält.
Abbildung 8 zeigt das zum Identifizieren der am logischsten fragmentierten Indizes verwendete Skript. Die Ergebnisse, die nach dem Fragmentierungsprozentsatz sortiert sind, zeigen die am logischsten fragmentierten Indizes über alle Datenbanken hinweg zusammen mit der betreffenden Datenbank/Tabelle an. Beachten Sie, dass diese Ausführung anfänglich einige Zeit (mehrere Minuten) in Anspruch nehmen kann. Daher wurde sie im Skriptdownload auskommentiert.
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the -- following step.
SELECT TOP 1 
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE s.[object_id] = -999  -- Dummy value just to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempFragmentation 
SELECT TOP 10
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE s.database_id = DB_ID() 
      AND i.name IS NOT NULL    -- Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC
;
'

-- Select records.
SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
-- Tidy up.
DROP TABLE #TempFragmentation

Abbildung 8 Identifizieren logisch fragmentierter Indizes (Klicken Sie zum Vergrößern auf das Bild)

Kostspielige Abfragen in Bezug auf E/A-Vorgänge
E/A-Vorgänge sind ein Maß für die Anzahl von Lese-/Schreibvorgängen, die von einer Abfrage durchgeführt werden. Dies kann ein Indikator für die Effizienz einer Abfrage sein. Abfragen, bei denen viele E/A-Vorgänge verwendet werden, sind oft gut für Leistungsverbesserungen geeignet.
Die sys.dm_exec_query_stats-DMV stellt die Gesamtleistungsstatistik für zwischengespeicherte Abfragepläne einschließlich Details über physische und logische Lese-/Schreibvorgänge und über die Häufigkeit der Abfrageausführung bereit. Sie enthält Offsets, die zum Extrahieren der eigentlichen SQL aus ihrer enthaltenen übergeordneten SQL verwendet werden. Diese DMV wird mit der sys.dm_exec_sql_text-DMF verknüpft, die Informationen zum SQL-Batch enthält, auf den sich die E/A-Vorgänge beziehen. Die verschiedenen Offsets werden auf diesen Batch angewendet, um die zugrunde liegenden einzelnen SQL-Abfragen zu erhalten. Das Skript ist in Abbildung 9 dargestellt. Die Ergebnisse, die nach durchschnittlichen E/A-Vorgängen sortiert sind, zeigen die durchschnittlichen E/A-Vorgänge, die gesamten E/A-Vorgänge, die einzelne Abfrage, die übergeordnete Abfrage (wenn die einzelne Abfrage Teil eines Batch ist) sowie den Datenbanknamen an.
SELECT TOP 10 
 [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) 
        ,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

Abbildung 9 Identifizieren der kostspieligsten Abfragen in Bezug auf E/A-Vorgänge (Klicken Sie zum Vergrößern auf das Bild)
Da E/A-Vorgänge die Datenmenge widerspiegeln, kann die in der Spalte für individuelle Abfragen dargestellte Abfrage beim Bestimmen von Bereichen helfen, in denen sich E/A-Vorgänge reduzieren lassen und somit die Leistung verbessert wird. Es ist möglich, die Abfrage in den Datenbankoptimierungsratgeber einzugeben, um zu bestimmen, ob Indizes/Statistiken zur Verbesserung der Abfrageleistung hinzugefügt werden sollten. Statistiken umfassen Details über die Verteilung und Dichte der zugrunde liegenden Daten. Sie dienen dem Abfrageoptimierer beim Festlegen eines optimalen Abfragezugriffsplans.
Es könnte auch nützlich sein zu prüfen, ob eine Verknüpfung zwischen der Tabelle in diesen Abfragen und den im Abschnitt über fehlende Indizes aufgeführten Indizes besteht. (Beachten Sie dabei jedoch Folgendes: Es ist wichtig, die Auswirkung beim Erstellen von Indizes in Tabellen zu untersuchen, bei denen es zu vielen Updates kommt, da zusätzliche Indizes die zum Aktualisieren der zugrunde liegenden Tabellendaten erforderliche Zeit erhöhen.)
Das Skript könnte so verändert werden, dass nur Lese- oder Schreibvorgänge gemeldet werden, was für Berichtsdatenbanken beziehungsweise Transaktionsdatenbanken nützlich ist. Sie könnten auch Berichte zum Gesamt- oder Durchschnittswert erstellen und eine entsprechende Sortierung vornehmen. Hohe Werte für Lesevorgänge können auf fehlende oder unvollständige Indizes oder schlecht gestaltete Abfragen oder Tabellen hinweisen.
Beim Interpretieren von Ergebnissen, bei denen die sys.dm_exec_query_stats-DMV verwendet wird, sollten Sie etwas vorsichtig sein. Ein Abfrageplan kann beispielsweise jederzeit aus dem Prozedurcache entfernt werden, und nicht alle Abfragen werden zwischengespeichert. Obwohl sich dies auf die Ergebnisse auswirkt, sollten diese dennoch auf die Abfragen mit den höchsten Leistungseinbußen hindeuten.

Kostspielige Abfragen in Bezug auf CPU-Nutzung
Ein weiterer, recht nützlicher Ansatz ist die Analyse der Abfragen mit den höchsten Leistungseinbußen in Bezug auf die CPU-Nutzung. Dieser Ansatz kann Abfragen mit schlechter Leistung sehr gut aufzeigen. Die hier verwendeten DMVs sind dieselben, die zur Untersuchung von Abfragen in Bezug auf E/A-Vorgänge verwendet wurden. Die Abfrage in Abbildung 10 ermöglicht Ihnen das Identifizieren der Abfragen mit den höchsten Leistungseinbußen gemessen an der CPU-Nutzung.
SELECT TOP 10 
 [Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

Abbildung 10 Die SQL-Abfrage zeichnet Ursachen von Wartezeiten auf (Klicken Sie zum Vergrößern auf das Bild)
Diese Abfrage gibt die durchschnittliche CPU-Nutzung, die gesamte CPU-Nutzung, die einzelne Abfrage und die übergeordnete Abfrage (wenn die einzelne Abfrage Teil eines Batch ist) sowie den entsprechenden Datenbanknamen zurück. Wie bereits erwähnt, könnte es sich lohnen, den Datenbankoptimierungsratgeber bei der Abfrage auszuführen, um festzustellen, ob weitere Verbesserungen möglich sind.

Kostspielige CLR-Abfragen
Zunehmend nutzt SQL Server die CLR. Daher kann es hilfreich sein zu bestimmen, welche Abfragen die CLR, die gespeicherte Prozeduren, Funktionen und Trigger umfasst, am meisten nutzen.
Die sys.dm_exec_query_stats-DMV enthält Details über „total_clr_time“ und die Häufigkeit der Abfrageausführung. Sie enthält zudem Offsets, die zum Extrahieren der eigentlichen Abfrage aus der übergeordneten Abfrage verwendet werden. Diese DMV wird mit der sys.dm_exec_sql_text-DMF verknüpft, die Informationen zum SQL-Batch enthält. Die verschiedenen Offsets werden angewendet, um die zugrunde liegende SQL zu erhalten. Abbildung 11 zeigt die zum Identifizieren der CLR-Abfragen mit den höchsten Leistungseinbußen verwendete Abfrage.
SELECT TOP 10 
 [Average CLR Time] = total_clr_time / execution_count 
,[Total CLR Time] = total_clr_time 
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE total_clr_time <> 0
ORDER BY [Average CLR Time] DESC;

Abbildung 11 Identifizieren der kostspieligsten CLR-Abfragen (Klicken Sie zum Vergrößern auf das Bild)
Diese gibt die durchschnittliche CLR-Zeit, die gesamte CLR-Zeit, die Ausführungsanzahl, die einzelne Abfrage, die übergeordnete Abfrage und den Datenbanknamen zurück. Auch hier lohnt es sich wieder, den Datenbankoptimierungsratgeber bei der Abfrage auszuführen, um festzustellen, ob weitere Verbesserungen möglich sind.

Die am häufigsten ausgeführten Abfragen
Sie können das vorherige Beispiel für CLR-Abfragen mit hohen Leistungseinbußen ändern, um die am häufigsten ausgeführten Abfragen zu identifizieren. Beachten Sie, dass hier dieselben DMVs gelten. Die Verbesserung der Leistung einer sehr häufig ausgeführten Abfrage kann eine bedeutendere Leistungsverbesserung als das Optimieren einer großen, selten ausgeführten Abfrage bereitstellen. (Zur Integritätsprüfung könnten Sie die Abfragen prüfen, die die meisten kumulierten CPU- oder E/A-Vorgänge verwenden.) Ein weiterer Vorteil bei der Verbesserung einer häufig ausgeführten Abfrage besteht darin, dass sie auch eine Gelegenheit zum Reduzieren der Anzahl von Sperren und der Transaktionslänge bereitstellt. Das Endergebnis besteht natürlich darin, dass Sie das Reaktionsvermögen des Systems insgesamt verbessert haben.
Sie können die am häufigsten ausgeführten Abfragen mithilfe der in Abbildung 12 dargestellten Abfrage identifizieren. Bei dieser Ausführung werden die Ausführungsanzahl, die einzelne Abfrage, die übergeordnete Abfrage (wenn die einzelne Abfrage Teil eines Batch ist) und die dazugehörige Datenbank angezeigt. Auch hier lohnt es sich wieder, die Abfrage im Datenbankoptimierungsratgeber auszuführen, um festzustellen, ob weitere Verbesserungen möglich sind.
SELECT TOP 10 
 [Execution count] = execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;

Abbildung 12 Identifizieren der am häufigsten ausgeführten Abfragen (Klicken Sie zum Vergrößern auf das Bild)

Durch Blockieren beeinträchtigte Abfragen
Bei Abfragen, die am meisten durch Blockierungen beeinträchtigt werden, handelt es sich in der Regel um Abfragen mit langer Laufzeit. Nach ihrer Identifizierung können Sie diese Abfragen analysieren, um zu ermitteln, ob sie zur Verringerung von Blockierungen neu geschrieben werden können und sollten. Ursachen für Blockierungen umfassen die Verwendung von Objekten in inkonsistenter Reihenfolge, widersprüchliche Transaktionsbereiche und die Aktualisierung nicht verwendeter Indizes.
Die sys.dm_exec_query_stats-DMV, die bereits erörtert wurde, enthält Spalten, die zum Identifizieren der am meisten durch Blockierungen beeinträchtigten Abfragen verwendet werden können. Die durchschnittlich blockierte Zeit errechnet sich aus der Differenz zwischen „total_elaspsed_time“ und „total_worker_time“, dividiert durch „execution_count“.
Die sys.dm_exec_sql_text-DMF enthält Details über den SQL-Batch, auf den sich die Blockierung bezieht. Die verschiedenen Offsets werden darauf angewendet, um die zugrunde liegende SQL zu erhalten.
Mithilfe der in Abbildung 13 dargestellten Abfrage können Sie die Abfragen identifizieren, die am meisten durch Blockierungen beeinträchtigt werden. Die Ergebnisse zeigen die durchschnittlich blockierte Zeit, die gesamte blockierte Zeit, die Ausführungsanzahl, die einzelne Abfrage, die übergeordnete Abfrage und die dazugehörige Datenbank an. Diese Ergebnisse sind nach der durchschnittlichen blockierten Zeit sortiert. Eine Sortierung nach der gesamten blockierten Zeit kann ebenfalls nützlich sein.
SELECT TOP 10 
 [Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count
,[Total Time Blocked] = total_elapsed_time - total_worker_time 
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) 
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;

Abbildung 13 Identifizieren der am häufigsten blockierten Abfragen (Klicken Sie zum Vergrößern auf das Bild)
Bei einer Untersuchung der Abfrage könnten Sie Entwurfsprobleme (beispielsweise fehlende Indizes), Transaktionsprobleme (in der falschen Reihenfolge verwendete Ressourcen) und so weiter finden. Der Datenbankoptimierungsratgeber weist u. U. ebenfalls auf mögliche Verbesserungen hin.

Die geringste Wiederverwendung des Plans
Einer der Vorteile bei der Verwendung gespeicherter Prozeduren besteht darin, dass der Abfrageplan zwischengespeichert wird und ohne Kompilierung der Abfrage wiederverwendet werden kann. Dies spart Zeit und Ressourcen und verbessert die Leistung. Sie können die Abfragepläne mit der geringsten Wiederverwendung identifizieren, um weiter zu untersuchen, warum die Pläne nicht wiederverwendet werden. Möglicherweise stellen Sie fest, dass einige Pläne zum Optimieren der Wiederverwendung neu geschrieben werden sollten.
Abbildung 14 zeigt das von mir geschriebene Skript zur Identifizierung von Abfragen mit der geringsten Wiederverwendung des Plans. Bei diesem Verfahren werden bereits erörterte DMVs zusammen mit einer noch nicht erwähnten verwendet: „dm_exec_cached_plans“. Diese DMV enthält ebenfalls Details über Abfragepläne, die von SQL Server zwischengespeichert wurden. Die Ergebnisse zeigen an, wie oft ein Plan verwendet wurde (in der Planverwendungsspalte), und geben die einzelne Abfrage, die übergeordnete Abfrage und den Datenbanknamen an.
SELECT TOP 10
 [Plan usage] = cp.usecounts
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), 
qt.text)) * 2 ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
,cp.cacheobjtype
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
ORDER BY [Plan usage] ASC;

Abbildung 14 Identifizieren von Abfragen mit der geringsten Wiederverwendung des Plans (Klicken Sie zum Vergrößern auf das Bild)
Sie können nun die einzelnen Abfragen untersuchen, die der Reihenfolge nach angezeigt werden, um den Grund dafür zu identifizieren, warum diese Pläne nicht öfter bzw. überhaupt nicht wiederverwendet werden. Ein möglicher Grund besteht darin, dass die Abfrage bei jeder Ausführung neu kompiliert wird. Dies kann der Fall sein, wenn die Abfrage verschiedene SET-Anweisungen oder temporäre Tabellen enthält. Eine ausführlichere Diskussion über Neukompilierungen und das Zwischenspeichern von Plänen finden Sie im Whitepaper „Batchkompilierung, Neukompilierung und Probleme mit der Planzwischenspeicherung in SQL Server 2005“ (verfügbar unter microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx).
Sie sollten außerdem sicherstellen, dass ausreichend Gelegenheit für eine mehrmalige Ausführung der Abfrage zur Verfügung stand. Sie können eine zugeordnete SQL-Ablaufverfolgungsdatei verwenden, um dies zu bestätigen.

Weitere Arbeiten
Bedenken Sie, dass die von den verschiedenen DMVs verfügbar gemachten Metriken nicht permanent gespeichert, sondern nur im Speicher gehalten werden. Wenn SQL Server 2005 neu gestartet wird, gehen diese Metriken verloren.
Sie könnten regelmäßig Tabellen auf Grundlage der Ausgabe der DMVs erstellen und die Ergebnisse mit einem Zeitstempel speichern. Anschließend könnten Sie diese Tabellen in der Reihenfolge der Zeitstempel prüfen, um die Auswirkungen von Anwendungsänderungen bzw. die Auswirkungen eines bestimmten Auftrags oder zeitabhängiger Verarbeitung zu identifizieren. Wie wirken sich beispielsweise Verarbeitungsprozeduren am Monatsende aus?
Ähnlich könnten Sie die Arbeitsauslastung einer bestimmten Ablaufverfolgungsdatei mit den Änderungen in den Tabellen in Beziehung setzen, um die Auswirkungen einer bestimmten Arbeitsauslastung (beispielsweise täglich oder am Monatsende) auf fehlende Indizes, die am häufigsten verwendeten Abfragen und so weiter zu bestimmen. Das hier enthaltene Skript kann zum Erstellen dieser Tabellen bearbeitet und regelmäßig als Teil eines laufenden Wartungstasks ausgeführt werden.
Es ist auch möglich, benutzerdefinierte Berichte mit Visual Studio 2005 zu erstellen, die das in diesem Artikel erörterte Skript verwenden. Diese lassen sich leicht in SQL Server Management Studio integrieren und bieten eine ansprechendere Darstellung der Daten.
Wenn sich die Möglichkeit bietet, sollten Sie versuchen, die beschriebenen Methoden mit anderen Methoden zu integrieren, beispielsweise mit der Ablaufverfolgung und der Verhältnisanalyse. Auf diese Weise erhalten Sie ein umfassenderes Bild der zur Verbesserung Ihrer Datenbankleistung erforderlichen Änderungen.
In diesem Artikel wurde die Nützlichkeit der umfangreichen Informationen vorgestellt, die SQL Server 2005 im normalen Arbeitsverlauf sammelt. Das Abfragen dieser Informationen bietet definitive Anhaltspunkte, die sich bei den laufenden Bemühungen zur Verbesserung der Abfrageleistung als nützlich erweisen dürften. Sie können beispielsweise die Ursache für Serverwartezeiten feststellen, nicht verwendete Indizes suchen, die sich negativ auf die Leistung auswirken, sowie die häufigsten und die kostspieligsten Abfragen ermitteln. Wenn Sie erst einmal mit der Untersuchung dieser verborgenen Daten angefangen haben, bieten sich unzählige Möglichkeiten. Es gibt noch viel mehr über DMVs zu lernen, und ich hoffe, dass dieser Artikel Ihnen Appetit auf weitere Untersuchungen gemacht hat.

Ian Stirk ist seit 1987 im IT-Bereich als Entwickler, Designer und Architekt tätig. Er verfügt über folgende Qualifikationen: M.Sc., MCSD, MCDBA und SCJP. Ian Stirk arbeitet als freiberuflicher Berater mit Microsoft-Technologien in London. Er kann unter Ian_Stirk@yahoo.com erreicht werden.

Page view tracker