Generieren eines Statistikskripts zum Erstellen einer reinen Statistikdatenbank in SQL Server

  • Artikel

In diesem Artikel erfahren Sie, wie Sie mithilfe von Datenbankmetadaten ein Statistikskript zum Erstellen einer reinen Statistikdatenbank in SQL Server generieren.

Ursprüngliche Produktversion: SQL Server 2014, SQL Server 2012, SQL Server 2008

Ursprüngliche KB-Nummer: 914288

Einführung

DBCC CLONEDATABASE ist die bevorzugte Methode, um einen reinen Schemaklon einer Datenbank zu generieren, um Leistungsprobleme zu untersuchen. Verwenden Sie das Verfahren in diesem Artikel nur, wenn Sie nicht verwenden DBCC CLONEDATABASEkönnen.

Der Abfrageoptimierer in Microsoft SQL Server verwendet die folgenden Arten von Informationen, um einen optimalen Abfrageplan zu bestimmen:

  • Datenbankmetadaten
  • Hardwareumgebung
  • Datenbanksitzungsstatus

In der Regel müssen Sie alle diese Arten von Informationen simulieren, um das Verhalten des Abfrageoptimierers in einem Testsystem zu reproduzieren.

Der Microsoft-Kundendienst fordert Sie möglicherweise auf, ein Skript der Datenbankmetadaten zu generieren, um ein Abfrageoptimiererproblem zu untersuchen. In diesem Artikel werden die Schritte zum Generieren des Statistikskripts sowie die Verwendung der Informationen durch den Abfrageoptimierer beschrieben.

Hinweis

Die in diesen Daten gespeicherten Schlüssel können PII-Informationen enthalten. Wenn Ihre Tabelle z. B. eine Telefonnummernspalte mit einer Statistik enthält, befindet sich der hohe Schlüsselwert jedes Schritts im generierten Statistikskript.

Skripterstellung für die gesamte Datenbank

Wenn Sie eine reine Statistik-Klondatenbank generieren, ist es möglicherweise einfacher und zuverlässiger, ein Skript für die gesamte Datenbank zu erstellen, anstatt einzelne Objekte zu erstellen. Wenn Sie ein Skript für die gesamte Datenbank erstellen, erhalten Sie die folgenden Vorteile:

  • Sie vermeiden Probleme mit fehlenden abhängigen Objekten, die zum Reproduzieren des Problems erforderlich sind.
  • Sie benötigen weniger Schritte, um die erforderlichen Objekte auszuwählen.

Wenn Sie ein Skript für eine Datenbank generieren und die Metadaten für die Datenbank Tausende von Objekten enthalten, verbraucht der Skriptprozess erhebliche CPU-Ressourcen. Es wird empfohlen, das Skript außerhalb der Spitzenzeiten zu generieren, oder Sie können die zweite Option Script Individual Objects verwenden, um das Skript für einzelne Objekte zu generieren.

Führen Sie die folgenden Schritte aus, um ein Skript für jede Datenbank zu erstellen, auf die ihre Abfrage verweist:

  1. Öffnen Sie die SQL Server Management Studio.

  2. Erweitern Sie im Objekt-ExplorerDatenbanken, und suchen Sie dann die Datenbank, für die Sie ein Skript erstellen möchten.

  3. Klicken Sie mit der rechten Maustaste auf die Datenbank, zeigen Sie auf Aufgaben, und wählen Sie dann Skripts generieren aus.

  4. Vergewissern Sie sich im Skript-Assistenten, dass die richtige Datenbank ausgewählt ist. Wählen Sie die Option Skript für die gesamte Datenbank und alle Datenbankobjekte aus, und wählen Sie dann Weiter aus.

  5. Wählen Sie im Dialogfeld Skriptoptionen auswählen die Schaltfläche Erweitert aus, um die folgenden Einstellungen vom Standardwert in den Wert zu ändern, der in der folgenden Tabelle aufgeführt ist.

    Skriptoption Auszuwählende Wert
    Ansi-Auffüllung Wahr
    Skripterstellung bei Fehler fortsetzen Wahr
    Generieren eines Skripts für abhängige Objekte Wahr
    Systemeinschränkungsnamen einschließen Wahr
    Skriptsortierung Wahr
    Skript für Anmeldungen Wahr
    Skripterstellung für Berechtigungen auf Objektebene Wahr
    Skriptstatistik Skripterstellung für Statistiken und Histogramme
    Skriptindizes Wahr
    Skripttrigger Wahr

    Hinweis

    Beachten Sie, dass die Option Skriptanmeldungen und die Option Berechtigungen auf Skriptobjektebene möglicherweise nicht erforderlich sind, es sei denn, das Schema enthält Objekte, die anderen Anmeldungen als dbo gehören.

  6. Wählen Sie OK aus, um die Änderungen zu speichern, und schließen Sie die Seite Erweiterte Skriptoptionen .

  7. Wählen Sie In Datei speichern und dann die Option Einzelne Datei aus.

  8. Überprüfen Sie Ihre Auswahl, und wählen Sie Weiter aus.

  9. Wählen Sie Fertig stellen aus.

Skripterstellung für einzelne Objekte

Sie können nur ein Skript für die einzelnen Objekte erstellen, auf die von einer bestimmten Abfrage verwiesen wird, anstatt ein Skript für die vollständige Datenbank zu erstellen. Wenn jedoch nicht alle Datenbankobjekte mit der WITH SCHEMABINDING -Klausel erstellt wurden, sind die Abhängigkeitsinformationen in der sys.depends Systemtabelle möglicherweise nicht immer korrekt. Diese Ungenauigkeit kann eines der folgenden Probleme verursachen:

  • Der Skripterstellungsprozess erstellt kein Skript für ein abhängiges Objekt.

  • Der Skriptprozess kann Skripts für Objekte in der falschen Reihenfolge erstellen. Um das Skript erfolgreich auszuführen, müssen Sie das generierte Skript manuell bearbeiten.

Daher wird davon abgeraten, dass Sie ein Skript für einzelne Objekte erstellen, es sei denn, die Datenbank verfügt über viele Objekte, und die Skripterstellung würde andernfalls zu lange dauern. Wenn Sie skripts für einzelne Objekte verwenden müssen, führen Sie die folgenden Schritte aus:

  1. Erweitern Sie im SQL Server Management Studio Datenbanken, und suchen Sie dann die Datenbank, für die Sie ein Skript erstellen möchten.

  2. Klicken Sie mit der rechten Maustaste auf die Datenbank, zeigen Sie auf Skriptdatenbank als, zeigen Sie dann auf ERSTELLEN in, und wählen Sie dann Datei aus.

  3. Geben Sie einen Dateinamen ein, und wählen Sie dann Speichern aus.

    Für den Kerndatenbankcontainer wird ein Skript erstellt. Dieser Container umfasst Dateien, Dateigruppen, die Datenbank und Eigenschaften.

  4. Klicken Sie mit der rechten Maustaste auf die Datenbank, zeigen Sie auf Aufgaben, und wählen Sie dann Skripts generieren aus.

  5. Stellen Sie sicher, dass die richtige Datenbank ausgewählt ist, und wählen Sie dann Weiter aus.

  6. Wählen Sie im Dialogfeld Objekttypen auswählendie Option Bestimmte Datenbankobjekte auswählen aus, und wählen Sie alle Datenbankobjekttypen aus, auf die die problematische Abfrage verweist.

    Wenn die Abfrage beispielsweise nur auf Tabellen verweist, wählen Sie Tabellen aus. Wenn die Abfrage auf eine Ansicht verweist, wählen Sie Sichten und Tabellen aus. Wenn die problematische Abfrage eine benutzerdefinierte Funktion verwendet, wählen Sie Funktionen aus.

  7. Wenn Sie alle Objekttypen ausgewählt haben, auf die von der Abfrage verwiesen wird, wählen Sie Weiter aus.

  8. Wählen Sie im Dialogfeld Skriptoptionen festlegen die Schaltfläche Erweitert aus, und ändern Sie die folgenden Einstellungen vom Standardwert in den Wert, der in der folgenden Tabelle auf der Seite Erweiterte Skriptoptionen aufgeführt ist.

    Skriptoption Auszuwählende Wert
    Ansi-Auffüllung Wahr
    Skripterstellung bei Fehler fortsetzen Wahr
    Systemeinschränkungsnamen einschließen Wahr
    Generieren eines Skripts für abhängige Objekte Wahr
    Skriptsortierung Wahr
    Skript für Anmeldungen Wahr
    Skripterstellung für Berechtigungen auf Objektebene Wahr
    Skriptstatistik Skripterstellung für Statistiken und Histogramme
    Skript USE DATABASE Wahr
    Skriptindizes Wahr
    Skripttrigger Wahr

    Hinweis

    Beachten Sie, dass die Optionen Skriptanmeldungen und Berechtigungen auf Skriptobjektebene möglicherweise nicht erforderlich sind, es sei denn, das Schema enthält Objekte, die anderen Anmeldungen als dbo gehören.

  9. Wählen Sie OK aus, um die Seite Erweiterte Skriptoptionen zu speichern und zu schließen.

    Für jeden Datenbankobjekttyp, den Sie in Schritt 7 ausgewählt haben, wird ein Dialogfeld angezeigt.

  10. Wählen Sie in jedem Dialogfeld die spezifischen Tabellen, Sichten, Funktionen oder anderen Datenbankobjekte aus, und wählen Sie dann Weiter aus.

  11. Wählen Sie die Option Skript in Datei aus, und geben Sie dann denselben Dateinamen an, den Sie in Schritt 3 eingegeben haben.

  12. Wählen Sie Fertig stellen aus, um die Skripterstellung zu starten.

    Wenn die Skripterstellung abgeschlossen ist, senden Sie die Skriptdatei an den Microsoft-Support Engineer. Der Microsoft-Support Engineer kann auch die folgenden Informationen anfordern:

    • Hardwarekonfiguration, einschließlich der Anzahl der Prozessoren und des vorhandenen physischen Arbeitsspeichers.

    • SET-Optionen, die beim Ausführen der Abfrage aktiv waren.

    Beachten Sie, dass Sie diese Informationen möglicherweise bereits bereitgestellt haben, indem Sie einen SQLDiag-Bericht oder eine SQL Profiler-Ablaufverfolgung senden. Möglicherweise haben Sie auch eine andere Methode verwendet, um diese Informationen bereitzustellen.

Verwendung der Informationen

In den folgenden Tabellen wird erläutert, wie der Abfrageoptimierer diese Informationen verwendet, um einen Abfrageplan auszuwählen.

Metadaten

Option Erklärung
Einschränkungen Der Abfrageoptimierer verwendet häufig Einschränkungen, um Widersprüche zwischen der Abfrage und dem zugrunde liegenden Schema zu erkennen. Wenn die Abfrage beispielsweise die WHERE col = 5 -Klausel enthält und eine CHECK (col < 5) Einschränkung für die zugrunde liegende Tabelle vorhanden ist, weiß der Abfrageoptimierer, dass keine Zeilen übereinstimmen. Der Abfrageoptimierer führt ähnliche Arten von Ableitungen zur NULL-Zulässigkeit durch. Beispielsweise ist bekannt, dass die WHERE col IS NULL -Klausel true oder false ist, abhängig von der NULL-Zulässigkeit der Spalte und davon, ob die Spalte aus der äußeren Tabelle eines äußeren Joins stammt. Das Vorhandensein von FOREIGN KEY-Einschränkungen ist nützlich, um die Kardinalität und die entsprechende Joinreihenfolge zu bestimmen. Der Abfrageoptimierer kann Einschränkungsinformationen verwenden, um Joins zu beseitigen oder Prädikate zu vereinfachen. Diese Änderungen können die Anforderung für den Zugriff auf die Basistabellen entfernen.
Statistik Die Statistikinformationen enthalten dichte und ein Histogramm, das die Verteilung der führenden Spalte des Index- und Statistikschlüssels anzeigt. Abhängig von der Art des Prädikats kann der Abfrageoptimierer die Dichte, das Histogramm oder beides verwenden, um die Kardinalität eines Prädikats zu schätzen. Aktuelle Statistiken sind für genaue Kardinalitätsschätzungen erforderlich. Die Kardinalitätsschätzungen werden als Eingabe für die Schätzung der Kosten eines Operators verwendet. Daher müssen Sie über gute Kardinalitätsschätzungen verfügen, um optimale Abfragepläne zu erhalten.
Tabellengröße (Anzahl von Zeilen und Seiten) Der Abfrageoptimierer verwendet die Histogramme und die Dichte, um die Wahrscheinlichkeit zu berechnen, dass ein bestimmtes Prädikat true oder false ist. Die endgültige Kardinalitätsschätzung wird berechnet, indem die Wahrscheinlichkeit mit der Anzahl der Zeilen multipliziert wird, die der untergeordnete Operator zurückgibt. Die Anzahl der Seiten in der Tabelle oder dem Index ist ein Faktor bei der Schätzung der E/A-Kosten. Die Tabellengröße wird verwendet, um die Kosten einer Überprüfung zu berechnen, und es ist nützlich, wenn Sie die Anzahl der Seiten schätzen, auf die während einer Indexsuche zugegriffen wird.
Datenbankoptionen Mehrere Datenbankoptionen können sich auf die Optimierung auswirken. Die AUTO_CREATE_STATISTICS Optionen und AUTO_UPDATE_STATISTICS wirken sich darauf aus, ob der Abfrageoptimierer neue Statistiken erstellt oder statistiken aktualisiert, die veraltet sind. Die Parametrisierungsebene wirkt sich darauf aus, wie die Eingabeabfrage parametrisiert wird, bevor die Eingabeabfrage an den Abfrageoptimierer übergeben wird. Die Parametrisierung kann sich auf die Kardinalitätsschätzung auswirken und auch den Abgleich mit indizierten Sichten und anderen Optimierungstypen verhindern. Die DATE_CORRELATION_OPTIMIZATION Einstellung bewirkt, dass der Optimierer nach Korrelationen zwischen Spalten sucht. Diese Einstellung wirkt sich auf Kardinalität und Kostenschätzung aus.

Umgebung

Option Erklärung
Sitzungs-SET-Optionen Die ANSI_NULLS Einstellung wirkt sich auf die Auswertung des NULL = NULL Ausdrucks als true aus. Die Kardinalitätsschätzung für äußere Joins kann sich je nach aktueller Einstellung ändern. Darüber hinaus können sich auch mehrdeutige Ausdrücke ändern. Der Ausdruck wird z. B col = NULL . basierend auf der Einstellung unterschiedlich ausgewertet. Der col IS NULL Ausdruck wertet jedoch immer auf die gleiche Weise aus.
Hardwareressourcen Die Kosten für Sortier- und Hashoperatoren hängen von der relativen Arbeitsspeichermenge ab, die für SQL Server verfügbar ist. Wenn die Größe der Daten beispielsweise größer als der Cache ist, weiß der Abfrageoptimierer, dass die Daten immer auf den Datenträger gepoolt werden müssen. Wenn die Größe der Daten jedoch viel kleiner als der Cache ist, wird der Vorgang wahrscheinlich im Arbeitsspeicher ausgeführt. SQL Server berücksichtigt auch verschiedene Optimierungen, wenn der Server über mehr als einen Prozessor verfügt und die Parallelität nicht mithilfe eines MAXDOP Hinweises oder der Konfigurationsoption max. Grad an Parallelität deaktiviert wurde.

Siehe auch