SQL – Fragen und Antworten: Verkleinern, wachsende und Neuentwicklung von Datenbanken
SQL-Datenbanken sind in allen Formen und Größen und Schemas. In diesem Monat kann unsere Experten SQL mit 2° bis 60° kondensierend, Wachstum und Neuentwicklung von Datenbanken.
Paul S. Randal
Die unglaubliche Datenbank verkleinern
Frage: Wir sind gelegentlich gezwungen, verkleinern auf unsere Datenbanken aufgrund von fehlendem Speicherplatz, ausgeführt, obwohl ich weiß, dass dies zu Leistungsproblemen führen kann. Wir kümmern danach alle Indexfragmentierung. Können Sie erklären, warum scheint verkleinern, viel langsamer für einige Datenbanken als andere, ausgeführt werden, auch wenn Sie ähnliche Größe sind?
A: Ich freue mich, dass Sie cognizant der Nebeneffekte eine Verkleinerung der Datenbank ausführen können. Ich klar auch, dass in einigen Fällen nur unvermeidlich ist.
Gleichzeitige Datenbankaktivität und dem Schema der Tabellen in der Datenbank können die Common Language Runtime eine Verkleinerung der Datenbank zu beeinflussen. Dies bedeutet, dass zwei Datenbanken mit gleicher Größe aber mit verschiedenen Schemas deutlich unterschiedlich viel Zeit zum Verkleinern erlangen können.
Datenzugriffsseiten-Datei navigieren, zum Konsolidieren von freien Speicherplatzes am Ende der Datei, der Sie dann an das Dateisystem zurückgegeben (durch Reduzieren der Größe der Datendatei) arbeitet, um Sie zu verkleinern. Um eine Datenseite-Datei zu verschieben, muss SQL Server eine exklusive Sperre auf der Seite erhalten. Dies bedeutet, dass niemand sperren oder alle Datensätze auf der Seite verfügen. Wenn Sie gleichzeitige Aktivitäten in der Datenbank die umfasst das Sperren erwerben, Verkleinerung konnte blockiert werden und dann warten Sie, bis die Sperre benötigt. Dies führt dazu, dass Verkleinerung länger dauern als ausgeführt werden soll, wenn keine andere Aktivität in der Datenbank vorhanden ist.
Ein weiterer Faktor beim Verkleinern eine Datei Datenseite verschoben wird, wenn alle anderen Datenbankstrukturen physischen Zeiger auf Daten auf der Seite verschoben haben, diese physische Zeiger mit dem neuen Speicherort der Seite aktualisieren müssen. Dies ist ein Problem aufgetreten, außer wenn eine Tabelle einen Heap ist nicht (hat kein gruppierten Index) und/oder bei eine Tabelle verfügt über eine oder mehr large Object (LOB) Spalten gespeicherte ab Zeile (getrennt von den Datensatz der Tabelle).
Wenn eine Tabelle einen Heap ist, enthalten alle nicht gruppierten Indizes auf dem Heap physischen Zeiger auf die Datensätze der Tabelle. Beim verkleinern eine Datenseite mit aus der Tabelle verschoben wird, müssen die nicht gruppierten Indizes aktualisiert werden. SQL Server wird durch Aufrufen der Abfrageprozessor Indexwartung für die nicht gruppierten Indizes 100 Zeilen gleichzeitig durchführen.
Wenn Sie eine Tabelle aus der Zeile LOB-Daten enthält, zeigen die Datensätze aus der Zeile LOB-Daten. Es ist jedoch keine Back-Zeigers von LOB-Daten zu den Datensätzen. Dies bedeutet, dass beim Verkleinern eine Textseite (enthaltenden ab Zeile LOB-Daten), alle Verschiebt die Datensätze, die auf LOB-Daten auf dieser Seite aktualisiert werden müssen. Es gibt keine Back-Zeiger, führen Sie einen Tabellenscan, um die richtigen Datensätze aktualisieren finden. Wie Sie sich vorstellen können, kann dieser Vorgang für eine Tabelle mit einer großen Menge von LOB-Daten sehr langsam sein.
Obwohl Verkleinerung langsam sein kann, stellt von SQL Server 2005 an Fortschritt über die Spalte Percent_complete der Ansicht sys.dm_exec_requests Verwaltung dynamischer Berichte. Sie können auch den Leistungsindikator Größe Data Bewegung pro Sekunde im Leistungsobjekt Datenbanken zu sehen, wie schnell verkleinern Fortschritt überwachen.
Können Sie automatische Vergrößerung
Frage: Ich bin ein neues DBA und ich habe wurde Lesen sehr viele Informationen online zu bewährten Methoden für Datenbankeinstellungen. Ich bin Unklarheiten durch in Konflikt stehende Ansichten, ob automatische Vergrößerung oder nicht aktiviert sein soll. Kann ich einfach deaktivieren ohne Probleme verursacht?
A: Die einfache Antwort ist, dass Sie immer aktivieren sollten automatische Vergrößerung, aber sich nicht darauf verlassen. Die allgemeine Vorgehensweise besteht darin, Überwachen von Daten- und Transaktionsprotokolldateien Protokoll Größe/Dateiverwendung und proaktiv vergrößert diese (oder plötzlichen, unerwarteten Wachstum zu untersuchen). Enable automatische Vergrößerung für Notfälle Fälle bei jeder verantwortlich für das Überwachen der Dateiverwendung/Größe ist nicht sofort zur Verwaltung der Dateien zur Verfügung steht.
Wenn automatische Vergrößerung ist nicht aktiviert für das Transaktionsprotokoll-Datei und der Datei dieses nach oben, dann schreiben Sie alle Aktivitäten in der Datenbank wird verhindert, bis mehr Speicherplatz im Transaktionsprotokoll zur Verfügung gestellt wird. Wenn automatische Vergrößerung ist für Einfügevorgänge oder Datenbank Wartungsoperationen wie das erneute Erstellen eines Indexes möglicherweise fehlschlagen, Datendateien nicht aktiviert.
Der schwierige Teil wird aus der Einstellung "Automatische Vergrößerung herauszufinden. Von SQL Server 2005 an ist die standardmäßige automatische Vergrößerung für Transaktionsprotokolldateien 10 Prozent und 1 MB für Datendateien. Ein Prozentsatz basierende automatische Vergrößerung bedeutet jedoch, wie Ihre Dateien erweitern, also auch die Ebene der automatische Vergrößerung ist. Dies bedeutet auch, dass die benötigte Zeit kann auch erhöhen, wenn Sie Don ’t die sofortige Dateiinitialisierung aktiviert haben. Daher sollten beide Dateitypen einen festen automatische Vergrößerung automatisch so vergrößert haben handelt es sich um ein vorhersagbares Verhalten.
Müssen eine sehr große oder Prozentsatz basierende automatische Vergrößerung kann für Transaktionsprotokolldateien besonders problematisch sein. Sofortige Dateiinitialisierung ist keine Option aus, damit alle neu zugewiesenen Speicherplatz 0 (null) initialisiert werden muss. Zwar, der NULL-Initialisierung erfolgt, Schreiben alle Aktivitäten im Transaktionsprotokoll blockiert wird. So, dass es sinnvoll ist, die Datei Auto-Vergrößerung der Transaktionsprotokolle zu verteilen, so groß ist können genug, dass Operationen für eine Weile, aber nicht zu groß weiterhin, dass der Fluss von Vorgängen zu lange unterbrochen wird.
Für Datendateien einem 1 MB automatische Vergrößerung ist übertrieben klein, aber es ist schwer, den richtigen Wert zu bestimmen. Dies hängt davon ab, ob Sie die automatische Vergrößerung zu einem Notfall Notlösung Measure oder manuelle Daten Größe Dateiverwaltung ersetzen möchten. Es auch abhängig ist, wie viel neuer Speicherplatz auf täglich für Daten, die in die Datenbank eingefügten benötigen. Die Quintessenz ist: Sie sollten automatische Vergrößerung aktivieren, und legen Sie ihn auf eine entsprechende Menge nicht Prozentsatz.
Schema speichern
Frage: Ich bin unserer Datenbankschema Neuentwicklung, sodass Abfragen effizienter sind. Einige der beteiligten Tabellen haben einer große Datenmenge Zeichen, und ich möchte sicherstellen, dass es auf die effizienteste Weise Speichern von bin. Gibt es Richtlinien oder empfohlenen Vorgehensweisen, die Sie gemeinsam nutzen können?
A: LOB-Daten gespeichert werden sollen wie haben einem großen Einfluss auf die Abfrageleistung, daher ist es wichtig, dass Sie das richtige Verfahren auswählen. Eine detaillierte Analyse aller Optionen ist nicht Gegenstand dieses Artikels, aber hier finden Sie einige Richtlinien:
Zunächst werden die Daten immer weniger als 8.000 Byte? Ist dies der Fall ist, versuchen Sie, einen Datentyp, der (n) Char oder Varchar (n), aber nicht einer der LOB-Datentypen wie XML, true (n)varchar(max), varbinary(max), Text (n) oder das Bild, wenn es unbedingt erforderlich ist. Wenn einen true LOB-Datentyp aufgrund der Datengröße der erforderlich ist, verwenden Sie nicht (n) Text oder das Bild als diese Typen in SQL Server 2005 als veraltet markiert wurden. Sie sind nicht als die anderen, neueren LOB-Datentypen wie funktionsfähig.
Zweitens: Wenn einen true LOB-Typ erforderlich ist, entscheiden Sie, ob zum Speichern der Daten in der Zeile (in der gleichen Tabellendatensatz der anderen Spalten in der Tabelle) oder Deaktivieren der Zeile (gespeichert in separaten Datei Datenseiten mit einer Verknüpfung aus dem Datensatz der Tabelle). Wenn Sie häufig LOB-Daten verwenden, wäre es besser, speichern es in der Zeile als Abfragen effizienter abgerufen werden können. Wenn dies nicht der Fall ist, ist in der Regel besser ab Zeile zu speichern. Gelegentliche Abfragen bezahlen einen etwas höheren Kosten LOB-Daten abrufen, aber die Datensätze werden kleinere, zu dichter Datenspeicherung und insgesamt eine bessere Abfrageleistung. Beachten Sie, dass können Sie nur Speicher LOB-Daten in Zeilen bis zu 8.000 Byte oder den gewünschten Betrag möglich ist, die anderen Spalten in dem Datensatz Daten angegeben, nach dem hat es automatisch aus der Zeile übertragen.
Wenn eine Tabelle eine LOB-Spalte enthält, werden Onlineindexvorgänge Drittens für alle Indizes verhindert, die die LOB-Spalte enthalten. Dies betrifft definitionsgemäß gruppierten Index der Tabelle. Aus diesem Grund einige Personen Speichern von LOB-Daten in eine völlig separaten Tabelle (vertikal Partitionieren von außerhalb dieses LOB-Spalte), und führen Sie dann eine JOIN-Operation zwischen der Haupttabelle und der LOB-Tabelle, wenn LOB-Daten von einer Abfrage benötigt werden. Dies erfordert etwas mehr Speicher aufgrund der Komplexität der JOIN, ermöglicht aber weitere Auswahl der Strategie für die Wartung des Index.
Sie können auch über die feste Breite im Vergleich zu Datentypen mit variabler Länge befassen und schnellen streaming Zugriff auf die Daten möglicherweise sogar erforderlich ist, in diesem Fall sollten Sie den Datentyp von SQL Server 2008 FILESTREAM. Finden Sie für eine detailliertere Analyse aller LOB Speicher Datentypen in meinem Blogbeitrag “ die Wichtigkeit der Auswahl nach rechts LOB-Speicher-Technik ”.
Kritische Überprüfung und Salden
Frage: Ich habe die Datenbank Wartungsverfahren unserer Firma überarbeitet und ich werde die Ausführung von DBCC-Überprüfungen für unseren wichtigen Datenbanken gestartet. Wie oft sollte eine Überprüfung für jede Datenbank werden ausgeführt?
A: Proaktive Konsistenzprüfungen sind ein wesentlicher Bestandteil jeder umfassende Datenbank-Wartungsplanes – für Benutzer- und Systemdatenbanken. Außerdem ist es wichtig, eine Verifizierungsmethode Seite verwenden. Aktivieren Sie für Datenbanken von SQL Server 2005 und höher, Seitenprüfsummen. Verwenden Sie für SQL Server 2000-Datenbanken torn Page Detection.
Konsistenzprüfungen Bezug sind, ist es schwierig, eine absolute Antwort wie oft ausführen erteilen. Es empfiehlt sich im Allgemeinen ausführen so oft wie möglich, mindestens einmal pro Woche. Die optimale Frequenz von Konsistenzprüfungen für Sie ist einem klassischen “ es abhängt. ”
Hier sind einige der Faktoren zu berücksichtigen:
Was ist zunächst Ihr Wartungsfenster? Konsistenzprüfungen beanspruchen große Mengen an CPU, Arbeitsspeicher und e/a-Ressourcen, ist das Wartungsfenster von, wenn Sie diese Ressourcen sparen können kürzer ist als die Zeit zum Ausführen der Konsistenzprüfung, nicht alle Datenbanken gleichzeitig überprüfen können u. u.. Möglicherweise müssen Sie staffeln die Konsistenzprüfungen über eine ganze Woche oder sogar Lagern Sie die Konsistenzprüfung auf einem Produktionssystem (durch das Wiederherstellen einer Sicherung und Ausführung der Konsistenzprüfung auf der wiederhergestellten Datenbank).
Ist Zweitens wie stabil das e/a-Subsystem, in dem die Datenbanken gespeichert sind? Wenn Probleme mit das e/a-Subsystem empfiehlt es sich, Konsistenzprüfungen so oft wie möglich erhalten frühesten möglichen Anzeichen einer Beschädigung führen. Meiner Erfahrung länger, dass eine Beschädigung verlässt, mehr weit verbreitet wird, und desto schwieriger ist es, die Datenbank wiederherzustellen und die Recovery Point Objective und Recovery Time Objective erfüllt.
Die Quintessenz ist, dass für Sie und den Komfort Ebene nach oben. Im August 2009 ich eine Umfrage in meinem Blog durchgeführt und außerhalb des 276 Befragten 37 Prozent führen Sie wöchentlich Konsistenzprüfungen und eine weitere 25 Prozent täglich ausführen. Sie sehen die vollständigen Ergebnisse der Umfrage zusammen mit viel mehr Informationen über die Überprüfung Häufigkeit www.sqlskills.com/BLOGS/PAUL/post/Importance-of-running-regular-consistency-checks.aspx von herauszufinden.
Dank an Kimberly l. Tripp von SQLskills.com für Ihre technischen Überprüfung der Artikel dieses Monats.
.jpg)
Paul S. Randal ist die Verwaltung von Director SQLskills.com, einen regionalen Microsoft-Leiter und SQL Server-MVP. Er gearbeitet, die SQL Server-Speichermodul Team bei Microsoft von 1999 2007. Er schrieb DBCC CHECKDB/Repair für SQL Server 2005 und während der Entwicklung von SQL Server 2008 für das Kernspeichermodul zuständig ist. Paul Randal ist Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung und regelmäßiger Referent bei Konferenzen in aller Welt. Er bloggt auf SQLskills.com/blogs/paul, und Sie können ihn auf Twitter am Twitter.com/PaulRandal finden.