SQL Q & A: Größe spielt sehr wohl eine Rolle

  • Artikel

Zu den Problemen, mit denen sich SQL-Administratoren in diesem Monat auseinandersetzen müssen, gehören Datenbankgröße, Indexfragmentierung und Verfügbarkeit nach Failovern.

Paul S. Randal

Angst vor der Fragmentierung

F: Ich habe ein paar Blog-Posts, die scheinen zu implizieren, wir brauchen nicht zu besorgt über Index-Fragmentierung, wenn unsere Datenbanken auf Solid-State-Speicher gehostet werden — die Theorie, dass Solid-State-Laufwerke (SSDs) sind so viel schneller als Festplatten Spinnerei. Verstehe ich die Leistungseinbußen reduziert würden, aber können wir wirklich nur vollständig ignorieren Indexfragmentierung?

**A.**Ob Sie Spinnen Festplatten oder .ssds. verwenden, müssen Sie achten, Index-Fragmentierung. Index-Fragmentierung umfasst zwei Phänomene — Seitendichte Fragen und Out-of-Order-Index-Seiten. Erstere verhindert effizient lesen vor während Index-Bereichsscans und letzteres reduziert Datendichte.

Es stimmt, dass Lese-und Schreibzugriff Wartezeiten mit SSDs sehr klein sind. Daher die Notwendigkeit häufiger, kleinere Vorauslese-e/As beim Scannen eines fragmentierten Indexes Bereich nicht annähernd so viel Leistung Wirkung als in der gleichen Situation haben, wenn Spinnerei Platten mit.

Der Rückgang der Datendichte von Index-Fragmentierung kann jedoch immer noch ein großes Problem. Die meisten Index-Fragmentierung tritt aus einer Operation namens ein "split Seite." Dies geschieht, wenn freier Speicherplatz auf einer Seite in einem Index erstellt wird, indem Sie verschieben die Hälfte die Indexzeilen auf eine neue Seite. Dies lässt die alten und neuen Seiten mit etwa 50 Prozent leeren Raum. Mit einem stark fragmentierten Index ist es nicht ungewöhnlich, dass Indizes mit eine durchschnittliche Seitendichte von 70 Prozent oder weniger (mit 30 Prozent freien Speicherplatz).

Mit diesem im Verstand wenn eine große Anzahl von Indizes in der Datenbank gespeichert auf Ihrem SSDs niedrigen Seitendichte, bedeutet dies, dass Ihre teuer SSDs eine große Menge an Leerraum gespeichert werden konnte. Dies ist eindeutig keine optimale Situation. Auch, obwohl die zusätzliche e/As erforderlich, in den unteren-Dichte-Seiten zu lesen auf der SSDs niedrige Latenzzeiten haben, sie mehr Platz in der SQL Server-Pufferpool (die speicherinternen Daten-Datei Seitencache) nehmen. Dies bedeutet auch, dass Ihre wertvollen Serverarbeitsspeicher nicht optimal verwendet wird.

Die andere Sache zu prüfen, neben der Indexfragmentierung selbst ist die Ursache der Fragmentierung: Seite teilt. Dies sind kostspielige Operationen, die eine Menge von Transaktionsprotokoll-Datensätzen erzeugen (werfen Sie einen Blick auf meine Blog-Eintrag zu sehen, wie schlecht es sein kann). Diese zusätzliche Protokolldatensätze bedeutet zusätzliche Verarbeitung durch nichts, die das Transaktionsprotokoll liest (z. B. Transaktionsreplikation, Sicherungen, Datenbankspiegelung, Protokollversand). Dies kann zu Performance-Einbußen für die gleichen Prozesse führen. So ignorieren Sie nicht Indexfragmentierung, nur weil Sie SSDs verwenden.

Schauen Sie nicht auf den Spiegel

F: Wir sind der Neugestaltung unserer Strategie für Verfügbarkeit, sondern haben stecken wie ein paar der Transaktionsreplikation Abonnementdatenbanken mehr hoch verfügbar zu machen. Wir konnten nicht verwenden Sie Datenbankspiegelung in SQL Server 2005, da hätten wir das Abonnement nach einem Failover erneut initialisiert. Gibt es eine bessere Lösung, jetzt, da wir auf SQL Server 2008 R2 sind?

**A.**Sie sind richtig fest, dass eine Abonnementdatenbank auf SQL Server 2005-Spiegelung nur eine redundante Kopie der Daten enthält, das bereits auf die Spiegelkopie der Abonnementdatenbank gespiegelt wurde. Es gibt keine Möglichkeit, die Replikationsabonnements ohne eine vollständige Reinitialisierung neu zu erstellen. Dadurch wird natürlich eine schlechte Wahl für SQL Server 2005 für die Datenbankspiegelung.

Mit SQL Server 2008 wurde ein neuer Mechanismus eingeführt Transaktionsreplikation, die teilweise Reinitialisierung eines Abonnements zulässt. Die Option heißt "initialize from Lsn." Es wird als @ Sync_type-Parameter beim Aufrufen von Sp_addsubscription angegeben.

Peer-to-Peer-Transaktionsreplikation in SQL Server 2008 wurde verbessert, um ermöglichen es Ihnen, hinzufügen und Entfernen von Knoten in einer Peer-to-Peer-Topologie ohne alle Aktivitäten in der Topologie komplett ruhige zuerst machen. Dies war ein wichtiger Impuls für die Daten Verfügbarkeit Peer-to-Peer-Topologie bietet. Die Option "Initialize from Lsn" wurde hinzugefügt, wie diese Erweiterungen vorhanden waren.

Für die Datenbankspiegelung ist keine zusätzliche Unterstützung für die Spiegelung Abonnierbare Datenbanken (wie es in der Protokolllese-Agent für die Spiegelung Publikationsdatenbanken). Die Methode "Initialize from Lsn" können Sie jedoch eine schnelle Reinitialisierung eines Abonnements nach einem Spiegelungsfailover bieten.

Diese Methodik stützt sich auf die Bestimmung der Protokollfolgenummer (LSN — eine eindeutige Nummer zur Identifizierung eines Protokolldatensatzes Transaktion) der letzten repliziert Betrieb auf die Abonnementdatenbank angewendet, bevor das Datenbankspiegelung Failover aufgetreten ist. Wir nennen diese LSN2.

Einige dieser Verfahren werden auch gespiegelt wurde haben, die Spiegelkopie der Datenbank vor dem Failover setzen. Dies könnte zu LSN3, beispielsweise ein bisschen weiter zurück in der Zeit als LSN2 steigen. Es werden auch einige Operationen, die überhaupt auf die Abonnementdatenbank angewendet wurde noch nicht. Dies sind die jüngsten in der Zeit als LSN2 oder LSN3. Wir nennen diese LSN1.

Alle Operationen bis zu LSN2 wurden auf die wichtigsten Abonnementdatenbank angewendet. Alle Operationen bis zu LSN3 wurden auf die wichtigsten Abonnementdatenbank angewendet und auf der Spiegeldatenbank Abonnement gespiegelt. Um eine "Initialize from Lsn" Initialisierung eines neuen Abonnements nach einem Spiegelungsfailover ausführen zu können, muss der Aufruf von Sp_addsubscription LSN3 als Ausgangspunkt verwenden.

Die Verteilungsbeibehaltung muss Zeitraum ebenfalls festgelegt werden, damit Vorgänge für einige Zeit, nachdem sie auf die Abonnementdatenbank angewendet wurde, haben in der Verteilungsdatenbank beibehalten werden. Kurz gesagt, jetzt können Sie die Datenbankspiegelung bieten höheren Verfügbarkeit einer Abonnement-Datenbank mit nur eine minimale Reinitialisierung nach einem Spiegelungsfailover erforderlich. Für eine mehr ausführlichere Erklärung zu diesem download im White Paper "SQL Server-Replikation: Bereitstellung hoher Verfügbarkeit mithilfe von Datenbankspiegelung. "

Zu groß, um Griff

F: Unserer Hauptdatenbank erreicht fast besteht. Wir finden, dass wir einfach nicht haben die Möglichkeit, regelmäßige Wartung Aufgaben ohne ernsthaft unsere regelmäßigen Arbeitslasten. Wir sind sehr besorgt über die Möglichkeit zum Sichern der Datenbank um Disaster-Recovery zu ermöglichen. Haben Sie Tipps?

**A.**Dies ist ein Fall, wo die Datenbank in mehr überschaubare Einheiten aufspalten nützlich sein würde. Dies in einer Reihe von Möglichkeiten, können Sie die häufigsten verwenden Sie den SQL Server-Tabelle/Index Partitionieren Funktion (in Enterprise Edition) oder manuell aufteilen, Dinge in separate Tabellen.

In beiden Fällen ist der entscheidende Punkt auf mehrere Dateigruppen in der Datenbank erstellen. Mit der Partitionierung, befindet sich jede Partition der größten Tabellen/Indizes in einer separaten Dateigruppe. Mit manuelle aufteilen, befindet sich in einer separaten Dateigruppe (evtl. mit all seiner Indizes sowie) jeder großen Tisch.

Mithilfe von separate Dateigruppen müssen Sie eine feiner abgestufte Einheiten der Datenbank, die Sie sichern und wiederherstellen können. Sie müssen nicht auf die gesamte besteht jedes Mal angewendet werden. Hätten Sie eine Verkaufsdatenbank, könnte beispielsweise mit Daten ab 2012 zurück zu 2008, Sie die verschiedenen Tabellen von Datenbereich in Kalenderjahr Partitionen partitionieren. Jede Partition Jahr wäre in einer separaten Dateigruppe.

Mit nur die 2012 Dateigruppe in Änderungen könnte Sie es häufig sichern. Sie können anderen unveränderlichen Dateigruppen viel weniger häufig sichern. Dies spart backup-Speicherplatz und die Zeitspanne, die der i/zusätzliche O Aufwand durchführen der Sicherung anfällt das Produktionssystem.

Mit solch einem Arrangement, Notfallwiederherstellung wird auch schneller (mit Enterprise Edition). Sie müssen nur die Dateigruppen benötigt, um den Online Transaction Processing (OLTP) Teil der Arbeitsauslastung online bringen schnell wiederherstellen. Sie können diese mit eine Teilwiederherstellung, dann verwenden Sie partielle Datenbankverfügbarkeit, um die Datenbank online zu schalten. Sie können wiederherstellen die Dateigruppen, die die älteren Daten später mit schrittweisen Onlinewiederherstellung, während die OLTP-Aktivitäten in den Dateigruppen bereits online ausgeführt werden.

Lesen Sie mehr dazu in diese White Papers Ansatz:

Unter Druck

F: Eine Sache, die unsere DBA Team verwirrt ist, wie zu sagen, ob der Puffer-Pool unter Druck gesetzt ist. Es gibt eine Menge von widersprüchliche Informationen über die PerfMon-Zähler und welche Schwellenwerte zu verwenden. Die meisten von was ich gelesen habe, sagt zu Page Life Expectancy (PLE) und 300 als Schwellenwert verwendet. Können Sie etwas Licht in diese Schuppen?

**A.**Du bist nicht allein in Ihre Verwirrung. Die Anzahl 300 wurde zuerst in einem Microsoft-Whitepaper veröffentlicht vor fünf Jahren verwiesen. Das ist schlecht jetzt veraltet.

PLE ist der richtige Zähler verwenden, aber Sie müssen verstehen, was es bedeutet, und wenn besorgt zu sein. Diese Zahl stellt eine sofortige Maßnahme wie aggressiv der Puffer-Pool ist, machen Platz für Seiten erforderlichen Daten vom Datenträger in den Arbeitsspeicher gelesen werden. Es ist kein gleitender Durchschnitt. Es ist die erwartete Zeit in Sekunden, die eine Seite Lesen vom Datenträger bleiben im Speicher, bevor du musst es ausspülen also eine andere Seite ihrer stattfinden können.

Als solche sagen nicht Blick auf einen einzigen PLE-Wert Sie, viel. Sie müssen sich auf Wert Trends. Es ist durchaus möglich, für gültige SQL Server-Operationen zu verursachen, die PLE zu drastisch sinken. Es wird häufig dann auf den früheren Wert wiederherstellen. Wenn PLE Tropfen und bleibt niedrig, das ist ein Grund zur Besorgnis.

Der Schwellenwert für den Zeitpunkt der betroffenen ist ein fester Wert, nicht, wie viele Menschen beschreiben. Die 300 Wert bedeutet den gesamten Puffer-Pool wird alle 300 Sekunden ersetzt. Haben Sie einen Pufferpool 100 GB, zum Beispiel bedeutet dies, dass 100 GB neuer Daten alle fünf Minuten in den Arbeitsspeicher gelesen wird. Das ist eindeutig ein Performance-Problem. Allerdings wird es eine massive Leistung Problem weg bevor PLE Hits 300. Sie können berechnen ein vernünftiger Wert verwendet (Puffer Poolspeicher in GB / 4) * 300, wie in diesem Blog-Eintrag.

Sie müssen auch von der non-Uniform Memory Access (NUMA)-Konfiguration des Servers berücksichtigen. Der PLE-Leistungsindikator im Puffer-Manager-Leistungsobjekt ist tatsächlich der Durchschnitt der PLEs für jeden NUMA-Knoten, wenn Sie NUMA konfiguriert haben. Dies bedeutet, dass die Überwachung der Puffer verwalten PLE ist kein echter Indikator für die Arbeitsspeicherauslastung auf dem Server. In diesem Fall sollten Sie den PLE-Leistungsindikator in jedem die Pufferpartition-Objekte messen. Sie können lesen mehr über PLE und NUMA in diesem Blog-Eintrag.

PLE ist der richtige Zähler zu überwachen, aber Sie sollten nur betroffen sein, wenn der Wert deutlich niedriger als Normal sinkt und dort für eine lange Zeit bleibt. Das ist eine allgemeine Richtlinie, aber leider gibt es keine Besonderheiten, die auf alle Situationen anwenden.

Paul S. Randal

**Paul S. Randal**ist der Geschäftsführer von SQLskills.com, Microsoft regional Director und ein SQL Server-MVP. Er arbeitete an der SQL Server-Speicher-Engine Team bei Microsoft von 1999 zu 2007. Er schrieb DBCC CHECKDB/Repair für SQL Server 2005 und war verantwortlich für das Kernspeichermodul während Entwicklung von SQL Server 2008. Randal ist Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung und ein regelmäßiger Referent bei Konferenzen weltweit. Er Blogs auf SQLskills.com/blogs/paul, und Sie finden ihn auf Twitter bei twitter.com/PaulRandal.

Verwandte Inhalte