SQL – Fragen und Antworten: Beheben Sie den Schaden
Überprüfen Sie die Auswahl der Abfragepläne, äußerst langsame Sicherungen und den Vorgang beim Reparieren einer Replikationsdatenbank.
Paul S. Randal
Planen Sie Ihre Abfrage
F: Bei der SQL Server einen Abfrageplan meine Abfragen ausführen Wahl ist, berücksichtigt dauert die Daten, die derzeit im Arbeitsspeicher?
**A.**Die einfache Antwort ist, dass der Abfrageoptimierer nie Puffer Schwimmbad Inhalt berücksichtigt bei der Auswahl der eines Abfrageplans. Der Abfrageoptimierer wertet verschiedene Pläne, wie es sich die Auswahlmöglichkeiten verengt. Es sucht den besten Plan, den es innerhalb eines angemessenen Zeitrahmens feststellen können. Der Abfrageoptimierer identifizieren nicht immer die absolute beste Plan. Der Abfrageoptimierer kann nicht übermäßig viel Zeit verbringen Sie auf Plan-Compilation, aber es wählt immer einen "guten genug" Plan.
Der Abfrageoptimierer muss wissen über die verschiedenen an der Abfrage beteiligten Tabellen. Er findet diese Informationen in die relationale Metadaten für die Tabellen. Die relationale Metadaten beschreiben die Tabellenspalten, Indizes und Einschränkungen, sowie Statistiken über die Wert-Verteilungen in den einzelnen Spalten (wenn diese Statistiken erstellt wurden). Die Speichermetadaten beschreiben, wie die Tabelle und Indizes tatsächlich in den Datendateien gespeichert sind. Der Abfrageoptimierer verwendet nicht diese Informationen bei der Bestimmung eines Plans.
SQL Server nicht verfolgen welche Teile einer Tabelle und ihre Indizes sind im Speicher zu jeder Zeit. Der Pufferpool verfolgt die Daten-Datei-Seiten aus einer Datenbank im Arbeitsspeicher. Jedoch tut nichts in SQL Server jede Art von automatisierten Aggregation. Es wird nicht, z.B. feststellen, dass 50 Prozent des Index zwei der Tabelle X im Speicher, während nur 5 Prozent der drei Index der Tabelle X im Speicher befindet.
Der Abfrageoptimierer geht nichts im Speicher, so dass es wahrscheinlich einen Abfrageplan zu wählen, bei der die niedrigste Menge der physischen e/a. Pläne mit viel physische e/a sind zeitaufwändig und teuer. Betrachten Sie eine Tabelle mit zwei nicht gruppierte Indizes, die eine SELECT-Abfrage erfüllen konnte. Der erste Index hat alle erforderlichen Spalten. Der zweite Index hat die erforderlichen Spalten, plus einige zusätzliche Spalten.
Der erste Index haben kleinere Indexeinträge, damit es mehr Indexzeilen pro Seite der Daten-Datei haben. Der Abfrageoptimierer wählt diesen Index. Über den zweiten Index gegenüber lesen weniger Daten-Datei-Seiten in den Speicher verwenden physische e/a, den Zugriff auf die erforderlichen Indexeinträge erfüllt, dass die Abfrage benötigen. Die zweite hat größere Datensätze und weniger Datensätze pro Seite der Daten-Datei. Diese Begründung heißt Optimierung, und es ist wie der Abfrageprozessor in SQL Server konzipiert ist.
Doch ist was passiert, wenn ein Großteil der zweiten, größeren Index bereits im Speicher und keines der erste Index ist im Speicher? Die Abfrage erfordert physische e/a den gewählten Index in den Speicher gelesen. Dies wird viel langsamer als mit dem größeren Index bereits im Speicher sein. In diesem Fall ist der Abfrageplan eigentlich suboptimal. Jedoch der Abfrageoptimierer hat keine Möglichkeit zu wissen, was im Speicher ist, und dies ist nur ein Beispiel.
Auch mit diesem Gedanken verwendet was passiert, wenn der Abfrageoptimierer erkannt, was im Speicher und generiert einen Plan aber, die weniger effizienten Index da war es schon im Speicher? Dieser Plan wäre optimal, nur so lange, wie diese Situation beibehalten. Wenn der effizientere Index in den Speicher für eine andere Abfrage gelesen wurde, würde die Abfrage dann einen nicht optimalen Plan haben. Wie würde der Plan ungültig werden erklärt, so dass es wieder neu kompiliert werden könnte?
Als ehemaliger Softwareentwickler die SQL Server -Team weiß ich, dass die technische Komplexität halten eine Gesamtansicht, die Tabellen und Indizes im Speicher für Abfrage-Plan-Auswahl und Ungültigerklärung sind, extrem schwierig ist. Dies würde wahrscheinlich eine unerwünschte Leistung Aufwand für lediglich einen gelegentlichen Vorteil hinzufügen und es wird wahrscheinlich nie passieren.
Wenn Sie sehen, des Pufferpools interessiert sind, schauen Sie sich die Dynamic Management View (DMV)-dm_os_buffer_descriptors und die verschiedenen Abfragen, die ich zusammengestellt habe, auf die Puffer-Pool-Abschnitt von meinem SQLskills-Blog.
Lange Sicherungen
F: Wir verwenden Protokollversand um eine sekundäre Datenbank für Berichtszwecke. Jeder so oft, geraten wir in ein Problem, wo weit länger als gewöhnlich dauert eine Protokollsicherung in der sekundären Datenbank anwenden. Haben Sie keine Ahnung, was dies verursachen könnte?
**A.**Ja, habe ich diese Situation ein paar mal gesehen. Wenn Sie eine sekundäre Protokollversanddatenbank für die Berichterstellung verwenden, bedeutet dies, dass Sie die mit STANDBY-Option verwenden, wenn Sie Log-Sicherungen in der sekundären Datenbank wiederherstellen. Dies funktioniert in drei Schritten:
- Schreiben Sie alle Protokolldatensätze aus der Sicherung in die Protokolldatei.
- Führen Sie den REDO-Teil von Recovery (um sicherzustellen, dass alle Operationen von festgeschriebenen Transaktionen in der Datenbank vorhanden sind).
- Führen Sie den rückgängig-Teil von Recovery (sicherstellen, dass alle Vorgänge für Transaktionen in der Datenbank vorhanden sind).
Schritt 3 schreibt alle Protokolldatensätze generiert die rückgängig-Operationen in einer speziellen Datei, die die Undo-Datei aufgerufen. Dies bedeutet, dass die Datenbank im schreibgeschützten Modus befindet. Es ist auch transaktionell konsistent, so dass die Benutzer zugreifen können. Der Grund, die Protokolldatensätze in der Undo-Datei geschrieben werden ist so das Transaktionsprotokoll der Datenbank ist nicht verändert. Dadurch können Sie die nachfolgenden Protokollsicherungen wiederherstellen.
Wenn der Wiederherstellungsprozess auf dem sekundären Server, beginnt wenn eine Undo-Datei vorhanden ist, gibt es einen weiteren Schritt vor den ersten drei Schritten durchgeführt. Dieser erste Schritt nimmt alle Protokolldatensätze in der Rückgängigdatei und macht die Auswirkungen auf sie. Dies setzt im Wesentlichen die Datenbank zurück in den Zustand, den es in am Ende von Schritt 2. Dieser Zustand der Datenbank ist dasselbe, als ob die vorherigen Protokollsicherung mit WITH NORECOVERY, statt mit STANDBY wiederhergestellt worden waren.
Das Problem, das Sie erlebt haben ist, wenn eine Sicherung wiederhergestellt wird eine lange andauernde Transaktion enthält, die vor dem Ende der Protokollsicherung verpflichten, nicht. Dies bedeutet, dass es vollständig rückgängig gemacht als Teil der Wiederherstellung der Sicherungskopie sein muss. Daraus resultiert eine große Undo-Datei, die an sich machen ein Protokoll wiederherstellen Sicherung dauert länger. Wenn die Log-Sicherung wiederhergestellt wird, auch eine nicht festgeschriebene, lang andauernde Transaktion hat, dann ist es der perfekte Sturm. Schritt 3 wird auch eine Weile dauern.
Dies kann passieren, wenn die Prinzipaldatenbank Index gewartet wird und eine Protokollsicherung nahe dem Ende einer lang andauernden Indexneuerstellung eines großen gruppierten Index endet. Die erste Wiederherstellung Diese Protokollsicherung in der sekundären Datenbank dauert wesentlich länger als üblich wegen Schritt 3 in der Wiederherstellungsvorgang abgeschlossen.
Die nächste Protokollsicherung in der Prinzipaldatenbank wird auch kurz vor eine Indexneuerstellung abgeschlossen. Wenn es auf den sekundären wiederhergestellt wird, muss die gesamte Undo-Datei wieder rückgängig gemacht werden. Dann die Wiederherstellung geschieht, und ein weiterer großer Undo-Datei wird generiert, um die zweite nicht festgeschriebene Indexneuerstellung rückgängig zu machen.
Wenn die sekundäre Datenbank rund um die Uhr Zugriff benötigt, für den Hinweis, müssen Sie von dieser Möglichkeit bewusst sein. In diesem Fall ergänzen Sie sorgfältig die Index-Wartungsarbeiten in der primären Datenbank mit Log-Sicherungen. Dadurch wird sichergestellt, dass nur vollständige und engagierten Index Umbauten in den Protokollsicherungen auf die sekundäre Datenbank wiederhergestellt werden vorhanden sind.
Eine Alternative ist von Protokollversand zur Datenbankspiegelung verschieben. Hier werden Protokolldatensätze kontinuierlich von der Prinzipaldatenbank zur Spiegeldatenbank gesendet. Es gibt keine zusätzliche Schritte mit Log-Vorgänge rückgängig machen mehrere Male. Gibt es ein Komplexität Ausgleich beteiligt, wie der Nachteil dieses Ansatzes ist, dass Berichterstattung müssten Datenbanksnapshots verwenden.
Replizieren zu reparieren
F: Jeder so oft wir am Ende mit beschädigten Dateien. Unsere Sicherungen landen auch beschädigt, so dass wir einen Reparaturvorgang auszuführen. Letzte Woche war eine der Datenbanken zu reparieren, hatte ich eine Replikation-Publikationsdatenbank. In der Onlinedokumentation heißt es, dass alle Abonnenten erneut initialisiert werden müssen, nach der Reparatur einer Datenbank. Können Sie erklären, warum?
**A.**Wenn Sie erwägen, mit der Option REPAIR_ALLOW_DATA_LOSS des Datenbank-Konsistenzüberprüfung (DBCC) CHECKDB (ich werde nur von hier sagen "reparieren"), müssen Sie zweimal überlegen, wenn die Datenbank wirst du zum Reparieren einer Replikationsdatenbank ist. Wenn möglich, verwenden Sie Ihre Backups statt Repair.
Wenn Sie die Mergereplikation verwenden, Data Manipulation Language (DML) löst Erfassung Änderungen auf die Publikationsdatenbank und wandelt sie in logischen Operationen. Wenn Sie Transaktionsreplikation verwenden, fängt Datenbank Transaktion Log-Analyse die Veröffentlichung Datenbank ändern. Dann werden die protokollierten, physikalische Vorgänge in logische Operationen umgewandelt. In beiden Fällen werden die logischen Operationen auf der Replikation Abonnementdatenbanken angewendet.
Weder Mechanismus ermöglicht die Erfassung von Reparaturvorgänge. Reparaturen sind immer direkte physische Änderungen an die Datenbankstrukturen. Diese sind notwendig, um Inkonsistenzen in der Strukturen, z. B. Datenbankauswahlseite einen Datensatz der Tabelle oder eine Verbindung zwischen den zwei Seiten zu beheben. Sie sind anders als physische Datenbankänderungen wegen Abfragen durchführen Einfügungen, Aktualisierungen oder Löschungen auf Tabellen.
Diese Reparatur Operationen können nicht übersetzen in logische Operationen, die Sie auf Replikation Abonnenten angewendet haben können. Es gibt keine logischen Operationen als ausgedrückt werden können, mithilfe von Transact-SQL für das Äquivalent von der direkten strukturellen Veränderungen, die die Reparatur durchführt. Stellen Sie sich einen Reparaturvorgang ist gezwungen, eine Datenseite aus ein eindeutiger gruppierter Index (im Wesentlichen einige Datensätze löschen) zu entfernen. Das Abonnement wird nicht erneut initialisiert. Diese Datensätze würde noch auf die replizierte Kopie der Tabelle vorhanden sind.
Wenn eine nachfolgende Insert-Operation mit Cluster-Schlüsselwerten entspricht die Datensätze gelöscht, indem den Reparaturvorgang Datensätze eingefügt, würde der Verteilungs-Agent fehlschlagen, wenn Sie versuchen, die Einfügungen auf die replizierten Kopie anzuwenden. Der Reparaturvorgang würde nicht auf die Abonnementdatenbank angewendet wurden also ein doppelte Schlüsselverletzung Fehler auftreten wird, beim Versuch des Einsatzes auf replizierten Tabelle anwenden. Dies bedeutet, den Replikationsabonnements Reparaturvorgang jede Tabelle ändert, die Teil einer Replikationspublikation ist, nicht mehr gültig ist. Es muss erneut initialisiert werden.
Wenn der Reparaturvorgang die Replikation Metadata Tabellen auswirkt, ist die gesamte Replikationspublikation in einem inkonsistenten Zustand. Replikation muss vollständig entfernt und neu konfiguriert werden. Offensichtlich ist dies ein viel invasiver Prozess als nur Erneutes Initialisieren eines Abonnements. Die Quintessenz ist, dass, wenn überhaupt möglich, Sie zu vermeiden, dass eine Replikation-Publikationsdatenbank reparieren möchten.
.jpg)
Paul S. Randal ist der Geschäftsführer von SQLskills.com, Microsoft regional Director und einen SQL Server MVP. Er arbeitete an der SQL Server Storage Engine Team bei Microsoft von 1999 bis 2007. Er schrieb DBCC CHECKDB/Repair für SQL Server 2005 und war verantwortlich für das Kernspeichermodul während SQL Server 2008-Entwicklung. Randal ist Experte für Disaster Recovery, hohe Verfügbarkeit und Datenbankwartung und ein regelmäßiger Referent auf Konferenzen weltweit. Er bloggt auf SQLskills.com/blogs/paul und Sie finden ihn auf Twitter bei twitter.com/PaulRandal.