SQL Server: Datenbanken und Indizes
Die Verwaltung der Indizes und das Vorhandensein korrekter Indizes stellt einen wesentlichen Bestandteil der Verwaltung der SQL Server-Auslastung insgesamt dar.
Auszug aus "SQL Server DMV Starter Pack," veröffentlicht von Red Gate Books (2010).
Glenn Berry, Louis Davidson und Tim Ford
Microsoft ist logisch Database Management Objects (DMOs) auf Datenbank-/File-Ebene in zwei Kategorien unterteilt:
- Datenbank-bezogenen. Diese enthalten Datenbank Verwaltungssichten (DMVs), die uns die Tabelle und der Index-Seite und Zeile zählt für eine bestimmte Datenbank, sowie die Seitenzuordnung auf Dateiebene untersuchen lassen. Ein paar der DMVs sind auch gewidmet untersuchen Verwendung der TempDB-Datenbank.
- Index-bezogene. Diese enthalten DMVs speziell auf Indizes, ihre Merkmale, wie sie es gewohnt sind und identifizieren potentiell nützliche Indizes für Ihre Arbeit.
Alle Ansichten in diesen zwei Kategorien haben am Anfang die Bezeichnungen "sys.dm_db_". Diese Typen der DMVs helfen Ihnen eine wirksame Indizierung Strategie definieren, wie dies eine der besten Möglichkeiten ist, um sicherzustellen, dass die bedeutendsten und häufigen Abfragen sind in der Lage, die Daten, die sie, in einer logischen, geordnete Weise benötigen lesen, und so vermeiden unnötige I/O. Finden das richtige Gleichgewicht zwischen zu viele Indizes und Indizes zu wenig — und mit den "richtigen" Satz von Indizes im Ort — ist äußerst wichtig für die beste Leistung von SQL Server.
Sie müssen auch die TempDB-Datenbank zu überwachen. TempDB ist eine globale Ressource, die temporäre Daten für Benutzer und interne Objekte für alle mit einer bestimmten Instanz von SQL Server verbundenen Benutzer speichert. Dazu gehören z. B. interne Arbeitstabellen zum Speichern der Ergebnisse von Cursorn, und Benutzer Objekte wie z. B. temporäre Tabellen und Table-Variablen.
Suchen fehlender Indizes
Um herauszufinden, welche Indizes eine bestimmte Datenbank möglicherweise fehlen, müssen Sie drei eng miteinander verbundenen DMVs verwenden. Die erste ist sys. dm_db_missing_index_group_stats, die wie folgt beschrieben wird:
"Gibt Zusammenfassungsinformationen zu Gruppen fehlender Indizes, außer räumliche Indizes zurück. Von sys. dm_db_missing_index_group_stats zurückgegebenen Informationen werden aktualisiert, nach jeder Ausführung der Abfrage, nicht nach jeder Abfragekompilierung oder Neukompilierung. Nutzungs-Statistiken werden nicht beibehalten, und nur bis zum Neustart von SQL Server gehalten werden. Datenbankadministratoren sollten regelmäßig Sicherungskopien der Informationen zu fehlenden Indizes machen, wenn sie die Nutzungsstatistiken nach dem Wiederverwenden des Servers beibehalten möchten."
Die zweite DMV ist sys. dm_db_missing_index_groups, wie folgt beschrieben:
"Liefert Informationen über fehlende Indizes in einer bestimmten Gruppe, außer räumliche Indizes enthalten sind."
Dies ist im Grunde nur eine Join-Tabelle sys. dm_db_missing_index_group_stats bis unsere dritten DMV, die sys. dm_db_missing_index_details, wie folgt beschrieben ist:
"Liefert detaillierte Informationen zu fehlenden Indizes, außer räumliche Indizes."
Durch den Beitritt dieser drei DMVs, erhalten Sie eine nützliche fehlenden Index-Abfrage (siehe Abbildung 1).
Abbildung 1 Identifizierung potentiell sinnvolle Indizes.
-- Missing Indexes in current database by Index Advantage SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] , migs.last_user_seek , mid.[statement] AS [Database.Schema.Table] , mid.equality_columns , mid.inequality_columns , mid.included_columns , migs.unique_compiles , migs.user_seeks , migs.avg_total_user_cost , migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK ) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle WHERE mid.database_id = DB_ID() ORDER BY index_advantage DESC ;
Diese Abfrage verwendet die Datenzugriffs-Muster-Statistiken für eine bestimmte Tabelle zur Berechnung des möglichen Vorteil einen bestimmten Index hinzufügen. Indizes mit einem höheren Index_advantage sind diejenigen, die SQL Server haben die größte positive Auswirkungen betrachtet auf die Verringerung der Arbeitsbelastung, basierend auf die Verringerung der Kosten für Abfrage und die voraussichtliche Anzahl von Zeiten, die sie den Index verwenden.
Denken Sie daran, dass wenn Sie einen Index für eine bestimmte Tabelle ändern, dann alle fehlenden Index-Statistiken für diese Tabelle gelöscht und erneut im Laufe der Zeit aufgefüllt. Wenn Sie diese Abfrage kurz nach einem Index ändern ausführen, wird es wahrscheinlich — und ungenau – informieren Sie, dass keine fehlenden Indizes für diese Tabelle vorhanden sind.
Es hat einige Einschränkungen, die Sie benötigen, zu prüfen. Es angeben nicht zuerst, immer die beste Reihenfolge von Spalten für einen Index. Wenn mehrere Spalten Equality_columns oder Inequality_columns aufgeführt sind, sollten Sie betrachten die Selektivität der jede dieser Spalten innerhalb der Gleichheit und Ungleichheit Ergebnisse die beste Spaltenreihenfolge für den potenziellen neuen Index zu bestimmen. Zweitens berücksichtigt es nicht gefilterte Indizes, die neu in SQL Server 2008 sind. Schließlich ist es im Allgemeinen darauf enthaltenen Spalten und neue Indizes gespannt.
Sie sollten nie nur Blind hinzufügen jeden Index, der diese Abfrage schlägt vor, vor allem, wenn Sie einem online Transaction processing (OLTP)-Workload. Stattdessen müssen Sie sorgfältig prüfen, die Ergebnisse der Abfrage und die Ergebnisse, die nicht Teil Ihrer regelmäßigen Arbeitsbelastung manuell herausfiltern.
Beginnen Sie mit der Untersuchung der Last_user_seek-Spalte. Wenn die Last_user_seek Zeit ist sind ein paar Tage oder sogar Wochen, dann Abfragen, die SQL Server wollen diesen Index verursacht wahrscheinlich aus einer zufälligen, ad-hoc-Abfrage oder Teil einer Berichtsabfrage selten laufen. Auf der anderen Seite, wenn das Last_user_seek mal ein paar Sekunden oder ein paar Minuten war, dann es ist wohl Teil Ihrer regelmäßigen Arbeitsbelastung und berücksichtigen Sie, dass mögliche Index genauer.
Unabhängig davon, was diese Abfrage empfiehlt immer Blick auf die vorhandenen Indizes für eine Tabelle, einschließlich ihre Nutzungsstatistiken, bevor Sie Änderungen vornehmen. Denken Sie daran, dass eine volatile Tabelle im Allgemeinen weniger Indizes als eine weitere statische Tabelle verfügen soll. Sie sollten sehr zögerlich eine Tabelle (für eine OLTP-Workload) einen neuen Index hinzu, wenn die Tabelle mehr als fünf oder sechs bereits effektive Indizes.
Vergessen Sie nicht, dass die gespeicherte Systemprozedur Sp_helpindex, eingeschlossene Spalteninformationen nicht angezeigt werden. Das bedeutet, Sie sollten einen Ersatz verwenden oder einfach die CREATE INDEX-Anweisung für Ihre vorhandenen Indizes Skript.
Verhören Indexverwendung
Ist eines der nützlichsten DMVs in der Kategorie Indizierung sys. dm_db_index_usage_stats, die wie folgt beschrieben wird:
"Gibt zählt der verschiedenen Arten von Indexvorgängen und die Zeit, die jede Art von Vorgang zuletzt ausgeführt wurde. Jeder einzelne Such-, Scan, Lookup oder Aktualisierung am angegebenen Index durch eine Abfrageausführung zählt als Verwendung dieses Indexes und der entsprechende Zähler in dieser Sicht inkrementiert. Informationen wird berichtet, sowohl für Operationen von Benutzern übermittelten Abfragen verursacht und für Operationen verursacht durch intern generierte Abfragen, wie z. B. Scans zum Sammeln von Statistiken."
Diese DMV enthält wertvolle Informationen über If und wie oft Ihre Indizes, für Lese- und Schreibvorgänge verwendet werden. Sie können auch Abfragen diese DMV zu informieren:
- Die Verteilung der Arbeitsauslastung in Ihrem definierten Indizes
- Indizes, die durch Ihre Arbeit nicht zugegriffen werden, und so sind prime Kandidaten für löschen
- Indizes mit einer großen Anzahl von Schreibvorgänge und NULL oder weniger Lesevorgänge (diese sind auch Kandidaten für Entfernung, nach weiteren Untersuchungen).
Die erste dieser drei Skripts (siehe Abbildung 2) Listet alle Ihre Heap-Tabellen, gruppierten Indizes und nicht gruppierte Indizes, zusammen mit der Anzahl der Lesevorgänge, Schreibvorgänge und den Füllfaktor für jeden Index.
Abbildung 2 können Sie bestimmen, wie Ihre Indizes verwendet werden.
--- Index Read/Write stats (all tables in current DB) SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] , i. name AS [IndexName] , i.index_id , user_seeks + user_scans + user_lookups AS [Reads] , user_updates AS [Writes] , i.type_desc AS [IndexType] , i.fill_factor AS [FillFactor] FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1 AND i.index_id = s.index_id AND s.database_id = DB_ID() ORDER BY OBJECT_NAME(s.[object_id]) , writes DESC , reads DESC ;
Dies ist eine nützliche Abfrage für besseres Verständnis Ihrer Arbeitsauslastung. Damit können Sie die Volatilität einen bestimmten Index, und das Verhältnis der Lesevorgänge, Schreibvorgänge bestimmen. Dadurch können Sie verfeinern und optimieren Ihre Indizierung Strategie. Beispielsweise hätten Sie eine Tabelle, die ziemlich wurde aufgeführt, statische (sehr wenige schreibt auf einer der Indizes), Sie könnten mehr zuversichtlich über das Hinzufügen von mehr Indizes in Ihren fehlenden Index Abfragen.
Wenn Sie SQL Server 2008 Enterprise Edition haben, könnte diese Abfrage helfen Sie entscheiden, ob es eine gute Idee, die Datenkomprimierung (Seite oder Zeile) zu aktivieren. Ein Index mit sehr wenig Schreibaktivität ist wahrscheinlich eine bessere Kandidat für Datenkomprimierung als ein volatiler Index.
Das nächste Skript (siehe Abbildung 3) verwendet sys.indexes und sys.objects, um Tabellen und Indizes in der aktuellen Datenbank, die nicht in sys. dm_db_index_usage_stats bis zu finden. Dies bedeutet, dass diese Indizes keine Lese- oder Schreibvorgänge seit SQL Server letzten begonnen hatte oder seit die aktuelle Datenbank geschlossen wurde oder getrennt (je nachdem, was kürzer ist).
Abbildung 3 Suchen nicht verwendeter Indizes.
-- List unused indexes SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] , i. name FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] WHERE i.index_id NOT IN ( SELECT s.index_id FROM sys.dm_db_index_usage_stats AS s WHERE s.[object_id] = i.[object_id] AND i.index_id = s.index_id AND database_id = DB_ID() ) AND o.[type] = ‘U’ ORDER BY OBJECT_NAME(i.[object_id]) ASC ;
Wenn SQL Server lange genug, dass Sie eine vollständige, repräsentative Arbeitsauslastung, besteht eine gute Chance ausgeführt wurde, sind diese nicht verwendete Indizes (und vielleicht Tabellen) "tot". Das bedeutet, Ihre Datenbank nicht mehr verwendet und Sie können potenziell ablegen, nachdem ich einige weiteren Untersuchungen.
Unsere endgültige sys. dm_db_index_usage_stats Abfrage filtert nach der aktuellen Datenbank (siehe Abbildung 4). Dies schließt nur nicht gruppierte Indizes. Damit können Sie entscheiden, ob die Kosten für die Wartung eines bestimmten Indexes den Vorteil überwiegt, dass es im Ort.
Abbildung 4 finden nur selten verwendeten Indizes.
-- Possible Bad NC Indexes (writes > reads) SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , i. name AS [Index Name] , i.index_id , user_updates AS [Total Writes] , user_seeks + user_scans + user_lookups AS [Total Reads] , user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference] FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1 AND s.database_id = DB_ID() AND user_updates > ( user_seeks + user_scans + user_lookups ) AND i.index_id > 1 ORDER BY [Difference] DESC , [Total Writes] DESC , [Total Reads] ASC ;
Diese Abfrage sucht alle Indizes, die große von Schreibvorgängen mit NULL mal gelesen Anzahl. Jeder Index, der in diese Kategorie fällt ist ein guter Kandidat für die Löschung (bei der vollständigen Untersuchung). Sie möchten sicherstellen, dass Ihre SQL Server-Instanz lange genug gelaufen ist, dass Sie Ihre komplette, typische Arbeitsauslastung enthalten haben.
Vergessen Sie nicht, über regelmäßige Berichterstattung Arbeitsauslastungen, die nicht in Ihrer täglichen Arbeit auftauchen könnte. Auch wenn die Indizes, die diese Arbeitslasten zu erleichtern, die häufig werden nicht verwendet, ihre Anwesenheit wird entscheidend sein.
Sie sollten auch einen Blick auf Zeilen wo gibt es eine große Anzahl von Schreibvorgängen und eine kleine Anzahl von Lesevorgängen. Diese Indizes werden weitere eines Aufrufs Urteil abhängig von der Tabelle und wie vertraut sind Sie mit Ihrer Arbeitsauslastung.
.png)
.png)
.png)
Glenn Berry arbeitet als ein Datenbankarchitekt bei NewsGator Technologies Inc. in Denver. 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.
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
Timothy Ford Ichs ein SQL Server-MVP und arbeitet seit mehr als 10 Jahren mit SQLServer. 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.