SQL – Fragen & AntwortenNeuerstellen von Indexen, Warteschlangenlänge des Datenträgers und mehr

Herausgegeben von Nancy Michell

F: Wie werden SQL Server-Indexe neu erstellt? Ich möchte wissen, warum DBCC DBREINDEX meinen ganzen Speicherplatz in Anspruch nimmt und der Platz nicht freigegeben wird, wenn der Befehl fehlschlägt. Meine Datenbank hat 90 GB, und die größte Tabelle ist 70 GB groß.

Wenn ich DBCC DBREINDEX ausführe und dabei 10 Prozent Speicherplatz frei lasse, beansprucht das Programm den gesamten verfügbaren Speicherplatz, und der Befehl schlägt fehl. Wenn bei einer anfänglichen Größe der Datenbankdatei von 90 GB der DBCC-Befehl fehlschlägt, wächst die Datenbankdatei auf 160 GB, und diese zusätzlichen 70 GB werden nie wieder freigegeben. Ich muss die Datenbank mehrmals manuell verkleinern, um den Speicher freizugeben.

Wenn der Befehl funktioniert, ist die Datenbankdatei ebenfalls 160 GB groß, aber der Speicherplatz wird automatisch nach ein paar Stunden durch die automatische Verkleinerung freigegeben. Wäre es akzeptabel, anstelle von DBREINDEX eine Defragmentierung durchzuführen, um weniger Speicherplatz in Anspruch zu nehmen? Ich habe keine gebündelten Indexe und verwende den einfachen Wiederherstellungsmodus.

A: Im einfachsten Fall werden Indexe neu erstellt, indem eine neue Kopie des Index erstellt und dann die alte gelöscht wird. Für eine kurze Zeitspanne gibt es also praktisch zwei Kopien des Index. Die Erstellung des neuen Index erfordert möglicherweise genauso viel Platz in der Datenbankdatei wie der ursprüngliche Index, und wenn die Neuerstellung einen Sortiervorgang erfordert, sind zusätzliche 20 Prozent der Indexgröße allein für die Sortierung erforderlich.

Deshalb erfordert die Neuerstellung eines Index im schlimmsten Fall den 1,2fachen Speicherplatz des alten Index. Hat die Datenbankdatei nicht genug freien Speicherplatz, muss sie sich entsprechend dem Fortschritt des Vorgangs vergrößern. Wenn die automatische Vergrößerung nicht aktiviert ist oder es auf dem Datenträgervolume nicht genug Speicherplatz gibt, schlägt der Neuerstellungsvorgang möglicherweise wegen unzureichendem Speicher fehl.

Unabhängig davon, ob der Vorgang fehlschlägt oder nicht, wird der zusätzliche Speicherplatz, der der Datenbankdatei zugeordnet worden ist, nach Abschluss des Neuerstellungsvorgangs nicht wieder freigegeben. Es wird davon ausgegangen, dass der Speicherplatz für reguläre Datenbankvorgänge verwendet wird.

Ausführen von Verkleinern (entweder manuell oder automatisch) führt durch die Art, wie der Algorithmus funktioniert, so gut wie sicher zu Indexfragmentierung. Weitere Informationen finden Sie unter: SQL Server-Speichermodul. Automatische Verkleinerung kann sich besonders nachteilig auf die Leistung auswirken, wenn die Datenbank freien Speicherplatz für reguläre Vorgänge benötigt, denn Sie können in einen Kreislauf von fortwährender automatischer Vergrößerung und anschließender automatischer Verkleinerung hineingeraten, der sich verheerend auf Fragmentierung und Leistung auswirken kann.

Die Verwendung von DBCC INDEXDEFRAG (oder ALTER INDEX ... REORGANIZE in SQL Server™ 2005) hat den Vorteil, dass fast kein zusätzlicher Speicherplatz in der Datenbankdatei belegt wird, aber dieser Vorgang dauert u. U. länger und erstellt eine viel größere Menge an Protokolleinträgen als eine Indexneuerstellung. DBCC INDEXDEFRAG wird unabhängig vom verwendeten Wiederherstellungsmodus immer vollständig protokolliert, während die Neuerstellung eines Index im einfachen Wiederherstellungsmodus massenprotokolliert wird. Beide Methoden haben verschiedene Vor- und Nachteile. Ausführlichere Erläuterungen dazu finden Sie im Whitepaper Bewährte Methoden zur Indexdefragmentierung in SQL Server.

Bevor Sie sich für eine Methode zur Indexdefragmentierung entscheiden, sollten Sie sich zunächst fragen, ob dieser Prozess überhaupt erforderlich ist. Je nach Art der Vorgänge, für die der Index verwendet wird, hat die Fragmentierung möglicherweise keine Auswirkung auf die Leistung, sodass deren Behebung eine Verschwendung von Ressourcen darstellt. Das Whitepaper stellt hierzu sehr ausführliche Informationen bereit.

Fazit: Stellen Sie sicher, dass Sie die für Ihre Umgebung am besten geeignete Methode zur Defragmentierung wählen und hierdurch die Abfrageleistung wirklich erhöht wird.

F: Ich habe zwischen zwei Instanzen von SQL Server 2005 erfolgreich die Datenbankspiegelung konfiguriert. Meine Anwendung stellt mithilfe einer SQL Server-Anmeldung eine Verbindung zu SQL Server her und wird mithilfe von ADO und SQL Native Client erstellt. Verbindungszeichenfolge und Verbindungseinstellungen geben die richtigen Informationen an, einschließlich des passenden Failoverpartners. Ich habe auch auf dem Spiegelserver durchgängig dieselben Anmeldungen erstellt, wie sie auf dem Prinzipalserver vorhanden sind. Bei Prüfung eines Datenbankfehlers übernimmt der Spiegel erfolgreich die Hauptrolle, und alles erscheint auf der SQL Server-Instanz korrekt. (Ich kann sogar mithilfe meiner Windows®-Anmeldung eine Verbindung zum Spiegel herstellen.) Die Wiederherstellung der Verbindung schlägt jedoch mit folgendem Fehler fehl:

Cannot open database "<db name>" requested by the login. The login failed. 

Es wird angezeigt, dass die Anmeldung keinem Benutzer in der neuen Prinzipaldatenbank (dem ursprünglichen Spiegel) zugeordnet ist. Ich führe sp_change_users_login aus, um die Benutzer und die Anmeldungen für die Datenbank zu synchronisieren, und ich erhalte eine Nachricht, dass das Problem mit mehreren verwaisten Benutzern behoben wurde. Meine Anwendung stellt dann erneut erfolgreich eine Verbindung zum neuen Prinzipalserver her. Ich habe mehrere Failovers versucht, und jedes Mal erlebe ich dasselbe Verhalten, nämlich, dass die Zuordnung zwischen der Anmeldung und dem Benutzer verloren geht.

Gibt es eine Möglichkeit, das Spiegelsetup so zu konfigurieren, dass dieses Problem nicht auftritt?

A: Ja. Das Problem liegt in der Tatsache, dass die Sicherheits-IDs (SIDs) für die SQL Server-Anmeldungen auf den einzelnen Servern nicht übereinstimmen, auch wenn die Namen für die Anmeldungen gleich sind. Dieses Problem besteht nicht bei Windows/Domänenbenutzer/Gruppenanmeldungen, da die SIDs für diese Anmeldungen aufgrund der Domänen-SID für den Benutzer/die Gruppe erstellt werden und deshalb für denselben bestimmten Benutzer/dieselbe bestimmte Gruppe gleich sind, unabhängig davon, welchem SQL-Server der Benutzer/die Gruppe hinzugefügt wird.

Um den Synchronisierungsschritt sp_change_users_login unnötig zu machen, müssen Sie die SQL Server-Anmeldungen auf dem Spiegelserver nicht nur mit demselben Namen, sondern auch mit derselben SID wie auf dem Prinzipalserver erstellen. Dies erreichen Sie mithilfe der SID-Spezifikation in der Anweisung CREATE LOGIN (ANMELDUNG ERSTELLEN), wenn Sie die Anmeldungen auf dem Spiegelserver erstellen, und zwar folgendermaßen:

CREATE LOGIN <loginname> WITH PASSWORD = <password>, 
SID = <sid for 
same login on principal server>,...

Sie können die SID für jede Anmeldung vom Prinzipalserver abrufen, indem Sie die Katalogansicht sys.sql_logins abfragen. Ein Beispiel für eine Abfrage, die eine tatsächliche CREATE LOGIN-Anweisung für jede SQL Server-/Windows-Anmeldung auf einem bestimmten Server generiert, ist in Abbildung 1 dargestellt.

Figure 1 Erstellen einer CREATE LOGIN-Anweisung

select 'create login [' + p.name + '] ' + 
case when p.type in('U','G') then 'from windows ' else '' end + 
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + 
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + 
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'

F: Wie groß sollte die durchschnittliche Warteschlangenlänge meines Datenträgers sein? Wenn ich z B. 20 separate physische Spindles außerhalb des SAN (Storage Area Network) mit RAID 01-Konfiguration habe, wie berechne ich dann die durchschnittliche Warteschlangenlänge des Datenträgers? Ist es die durchschnittliche Warteschlangenlänge des Datenträgers/20 oder die durchschnittliche Warteschlangenlänge des Datenträgers/2?

A: Bevor Sie Zeit auf die durchschnittliche Warteschlangenlänge in einer SAN-Umgebung aufwenden, sollten Sie zunächst die Datenträgerwartezeit überprüfen. Aber eigentlich hängt es davon ab, was Sie herausfinden möchten. Warum, werden Sie gleich sehen.

Die Erklärung dieses Indikators (von Perfmon) ist: „Die durchschnittliche Warteschlangenlänge des Datenträgers ist die durchschnittliche Anzahl der Lese- und der Schreibanforderungen, die sich für den ausgewählten Datenträger während des Samplingintervalls in der Warteschlange befanden.“ Dies ist ein Leistungsindikator entweder für physische oder logische Datenträger, sodass die resultierende Länge davon abhängt, wie der zugrunde liegende Speicher dem Betriebssystem vorgelegt wird.

Schauen wir uns Ihren Fall an. Sie haben 20 Spindles in einer RAID 01-Konfiguration, was bedeutet, dass sie gestriped und gespiegelt sind (oder gespiegelt und gestriped, je nachdem, ob Sie 01 oder 10 lesen). Das Entscheidende an diesem Speicherarray ist, dass es 10 Spindles im Stripeset gibt.

Aber mir fehlen jetzt wichtige Informationen, wie z. B. die Stripegröße, wie groß die Schreibvorgänge sind und welche Art von E/A Sie veranlassen (lesen, schreiben, sequenziell oder zufällig).

Wenn man die fehlenden Informationen vorläufig ignoriert, bedeutet eine durchschnittliche Warteschlangenlänge des Datenträgers von 10, dass das Betriebssystem 10 E/A für das Festplattenarray in die Warteschlange gestellt hat. Theoretisch könnte das eine E/A zu jedem der 10 gespiegelten Sätze im Stripe sein, oder es könnten 10 E/A sein, die alle zu einem Datenträger gehören. Es kann nicht festgestellt werden, welche der beiden Möglichkeiten vorliegt.

An dieser Stelle kommen die fehlenden Informationen ins Spiel. Angenommen, die Stripegröße beträgt 64 KB, die Größe der Schreibvorgänge 8 KB, und Sie wollen ein ganzes Segment von sequenziellen Schreibvorgängen durchführen. Dies ist ein typisches Szenario für SQL Server-Speicheraktivität. In diesem Fall besteht eine hohe Wahrscheinlichkeit, dass acht der E/A auf dem ersten Datenträger und die nächsten zwei E/A auf dem nächsten Datenträger gespeichert wurden. Wenn Sie also für dieses Szenario die Warteschlangenlänge pro Datenträger herausfinden wollen, so beträgt diese 8 für den ersten Datenträger, 2 für den zweiten Datenträger und 0 für die übrigen acht Datenträger im Array.

Wechseln wir jetzt zu einem Szenario mit einer theoretischen Stripegröße von 8 KB und einer Blockgröße der Schreibvorgänge von 64 KB, bei einer gleichbleibenden Warteschlangenlänge des Datenträgers von 10. In diesem Fall wird jeder 64 KB-Block auf 8 Datenträger verteilt, sodass eine einzige E/A auf 8 Datenträger geschrieben wird und die 10 in der Warteschlange befindlichen E/A auf 80 Schreibvorgänge über alle 10 Datenträger im Array verteilt werden. Bei einer Berechnung der Warteschlangenlänge pro Datenträger im Array erhalten Sie 8 für jeden Datenträger im Array.

Seien wir realistisch, und gehen wir von einem höheren Grad an Unsicherheit aus. In den meisten Fällen stellt der SAN-Speicher eine Verbindung zum Server her, und zwar mithilfe eines oder mehrerer Hostbusadapter (HBA) im Server, einer Reihe von Glasfasern, um den HBA mit dem SAN zu verbinden, einer Reihe von Ports auf dem Front-End des SAN und vielleicht eines Fiber-Switch in der Struktur zwischen dem Server und dem SAN. Dann gelangen wir in die interne Architektur der Busse innerhalb des SAN und sehen, wie die Verbindung der Datenträger zu den Ports auf der Vorderseite des SAN hergestellt ist.

Jede in Perfmon gemeldete Warteschlange kann ein Symptom für hohe Wartezeit oder für Warteschlangen an irgendeinem dieser Punkte sein, zwischen denen das Betriebssystem die Warteschlangenlänge seiner Datenträger und die Oberfläche der Datenträger misst. Daher sollten Sie auf die Wartezeit achten und eventuelle Entscheidungen eher aufgrund dieses Indikators fällen als aufgrund von durchschnittlichen Warteschlangenlängen des Datenträgers.

F: Ich verwende Transaktionsreplikation, und ich weiß, dass viele Zeilen in einer Tabelle beim Abonnenten manuell geändert wurden. Deshalb erhalte ich Fehler, wenn der Herausgeber versucht, eine Zeile zu aktualisieren, die beim Abonnenten nicht mehr existiert.

Ich will wissen, ob es durch Replikation eine Möglichkeit gibt, nur diese eine Tabelle vom Herausgeber neu zu initialisieren, anstatt einen ganzen Snapshot erneut auszuführen. Ich habe mir die Funktion TableDiff angeschaut, die so aussieht, als ob sie diese Funktion erfüllen könnte, aber ich frage mich, wie sie mit der Replikation interagiert.

Erstellt z. B. TableDiff zu einem bestimmten Zeitpunkt einen Snapshot von der Tabelle des Herausgebers und vergleicht ihn mit der Entsprechung des Abonnements? Muss ich die Replikation beenden, wenn ich das Dienstprogramm TableDiff anwende, damit die Konsistenz der Daten sichergestellt ist? Was muss ich sonst noch wissen?

A: Erstens erstellt TableDiff weder von der Tabelle des Herausgebers noch der des Abonnements einen wirklichen Snapshot. Hinsichtlich Ihres speziellen Szenarios gibt es verschiedene Optionen.

Die erste wäre eine vorübergehende Unterbrechung der Replikation, um das Dienstprogramm auszuführen. Wenn Sie sich Sorgen machen, das Benutzer Daten ändern könnten, verwenden Sie die Parameter -sourcelocked und -destinationlocked, die beide Tabellen während der Ausführung des Dienstprogramms exklusiv sperren. Wenn dies unannehmbar ist, bestände eine andere Option in der Verwendung der Parameter -rc und -ri, während Sie die Replikation weiterlaufen lassen. Hierdurch wird TableDiff zunächst einmal ausgeführt und anschließend erneut nach jedem Fehler, der erkannt wird. Dabei werden Fehler beseitigt, die möglicherweise aufgrund von Verzögerungen bei der Weitergabe der Replikation entstanden sind. Beachten Sie jedoch, dass Sie je nach Replikationsverzögerung mit dieser Option nicht unbedingt alle Zeilen erfassen, die sich beim Abonnenten geändert haben.

Unser Dank gilt den folgenden Microsoft-IT-Experten für ihre fachliche Unterstützung: Sunil Agarwal, Chad Boyd, David Browne, Gilles Comeau, Emmanuel Dreux, Amanda Foote, Matt Hollingsworth, Paul Mestemaker, Uttam Parui, Paul Randal, Dennis Tighe und Steven Wort.

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