Optimieren von Abfragen, die auf korrelierte datetime-Spalten zugreifen
Die SET-Datenbankoption DATE_CORRELATION_OPTIMIZATION führt zu einer Leistungssteigerung von Abfragen, mit denen eine Gleichheitsverknüpfung zwischen zwei Tabellen erstellt wird, deren date- oder datetime-Spalten voneinander abhängig sind und die im Abfrageprädikat eine Datumseinschränkung angeben.
Tabellen, deren date- oder datetime-Spaltenwerte korreliert sind und die von einer Aktivierung von DATE_CORRELATION_OPTIMIZATION profitieren können, sind in der Regel Bestandteil einer 1:n-Beziehung und werden hauptsächlich für Decision Support-, Berichts- oder Data Warehousing-Zwecke verwendet.
In der AdventureWorks-Beispieldatenbank sind z. B. die OrderDate-Spalte der Purchasing.PurchaseOrderHeader-Tabelle und die DueDate-Spalte der Purchasing.PurchaseOrderDetail-Tabelle korreliert. Die Datumswerte von PurchaseOrderDetail.DueDate folgen tendenziell kurz nach den Werten von PurchaseOrderHeader.OrderDate.
Wenn die DATE_CORRELATION_OPTIMIZATION-Datenbankoption auf ON festgelegt wird, verwaltet SQL Server Korrelationsstatistiken zwischen zwei beliebigen Tabellen in der Datenbank, die date- oder datetime-Spalten besitzen und durch eine einspaltige FOREIGN KEY-Einschränkung verknüpft sind. Standardmäßig wird diese Option auf OFF festgelegt.
SQL Server verwendet diese Korrelationsstatistiken zusammen mit der date-Einschränkung, die im Abfrageprädikat angegeben wurde, um zu folgern, dass der Abfrage weitere Einschränkungen hinzugefügt werden können, ohne das Resultset zu ändern. Der Abfrageoptimierer verwendet diese gefolgerten Bedingungen bei der Auswahl eines Abfrageplans. Ein schnellerer Abfrageplan kann das Ergebnis sein, da SQL Server durch die hinzugefügten Einschränkungen bei der Verarbeitung der Abfrage eine kleinere Datenmenge lesen muss. Die Leistung wird auch verbessert, wenn für beide Tabellen gruppierte Indizes definiert wurden und ihre date- oder datetime-Spalten, für die Korrelationsstatistiken verwaltet werden, der erste oder einzige Schlüssel des gruppierten Indexes sind.
Angenommen, Sie bereiten z. B. die AdventureWorks-Datenbank für die Verwaltung von Korrelationsinformationen für Purchasing.PurchaseOrderDetail und Purchasing.PurchaseOrderHeader vor, indem Sie das folgende Transact-SQL-Skript ausführen:
USE AdventureWorks;
GO
-- Create a unique index to take the place of the existing
-- primary key constraint
CREATE UNIQUE NONCLUSTERED INDEX
IX_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
ON Purchasing.PurchaseOrderDetail(PurchaseOrderID,PurchaseOrderDetailID);
GO
-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID;
GO
-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate);
GO
--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks
SET DATE_CORRELATION_OPTIMIZATION ON;
GO
Nehmen Sie nun an, dass die folgende Abfrage ausgeführt wird:
SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '20020101' AND '20020201';
Die Werte von PurchaseOrderDetail.DueDate, die von dieser Abfrage zurückgegeben werden, fallen in der Regel in einen bestimmten Zeitraum von Tagen (z. B. 14 Tage) des Bereichs der Werte von PurchaseOrderHeader.OrderDate. Da dies der Fall ist, kann SQL Server möglicherweise folgern, dass die vorherige Abfrage besser mithilfe einer Abfrage ausgedrückt werden kann, die mit dem folgenden Beispiel vergleichbar ist:
SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '1/1/02' AND '2/1/02'
AND d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14;
Die genaue Form der hinzugefügten Bedingung, die in der zweiten AND-Klausel angegeben wird, hängt von der ursprünglichen Abfrage sowie den Werten der Daten in Ihrer Datenbank ab. Nachdem eine implizite Bedingung hinzugefügt wurde, verwendet der Optimierer diese zum Erstellen eines Ausführungsplans. In diesem Beispiel ist ein gruppierter Index für PurchaseOrderDetail.DueDate vorhanden; dieser Index kann daher zum Abrufen der Zeilen verwendet werden, die den Bedingungen von d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14 genügen. Wenn Daten für mehrere Jahre in Purchasing.PurchaseOrderDetail enthalten sind, kann diese Abfrage im Vergleich zu der ursprünglichen Abfrage zu einer erheblichen Verkürzung (um ein Vielfaches) der Ausführungszeit führen.
Bevor ein Abfrageplan mit einer Bedingung, die aufgrund der Aktivierung von DATE_CORRELATION_OPTIMIZATION gefolgert wurde, ausgeführt wird, überprüft SQL Server, dass die Abfrage die richtige Antwort basierend auf dem aktuellen Inhalt der Datenbank generiert.
Anforderungen für das Verwenden der DATE_CORRELATION_OPTIMIZATION-Datenbankoption
Die folgenden Bedingungen müssen ausnahmslos erfüllt sein, damit zwei Tabellen von der Aktivierung der DATE_CORRELATION_OPTIMIZATION-Datenbankoption profitieren können:
Die SET-Optionen der Datenbank müssen auf die folgende Weise festgelegt werden. Die SET-Optionen ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL und QUOTED IDENTIFIER müssen auf ON festgelegt sein. Die SET-Option NUMERIC_ROUNDABORT muss auf OFF festgelegt sein.
Zwischen den Tabellen muss eine einspaltige Fremdschlüsselbeziehung bestehen.
Beide Tabellen müssen datetime-Spalten aufweisen, die als NOT NULL definiert sind.
Mindestens eine der datetime-Spalten muss die Schlüsselspalte eines gruppierten Indexes sein (wenn der Indexschlüssel zusammengesetzt ist, muss es sich um den ersten Schlüssel handeln), oder es muss sich um die Partitionierungsspalte handeln, wenn eine partitionierte Tabelle verwendet wird.
Beide Tabellen müssen sich im Besitz desselben Benutzers befinden.
Berücksichtigen Sie Folgendes, wenn Sie die DATE_CORRELATION_OPTIMIZATION-Datenbankoption auf ON festlegen:
SQL Server verwaltet Korrelationsinformationen in der Form von Statistiken. Diese Statistiken werden von SQL Server während INSERT-, UPDATE- und DELETE-Operationen für die betreffenden Tabellen aktualisiert; dieser Vorgang kann sich auf die Leistung dieser Operationen auswirken. Sie sollten DATE_CORRELATION_OPTIMIZATION nicht in aktualisierungsintensiven Datenbankumgebungen aktivieren.
Wenn eine der datetime-Spalten, für die Korrelationsstatistiken verwaltet werden, nicht der erste oder einzige Schlüssel eines gruppierten Indexes ist, sollten Sie das Erstellen eines gruppierten Indexes für diese in Erwägung ziehen. Auf diese Weise erzielen Sie im Allgemeinen eine bessere Leistung für die Abfragetypen, die von Korrelationsstatistiken abgedeckt werden. Wenn für die Primärschlüsselspalten bereits ein gruppierter Index vorhanden ist, können Sie eine Tabelle so ändern, dass der gruppierte Index und der Primärschlüssel verschiedene Spaltensätze verwenden.
Das Aktivieren von DATE_CORRELATION_OPTIMIZATION bringt unter den folgenden Umständen keine Vorteile mit sich:
Es sind keine Tabellenpaare vorhanden, die die zuvor aufgeführten Kriterien zum Verwalten von Korrelationsstatistiken erfüllen.
Es sind Tabellenpaare vorhanden, die die Kriterien zum Verwalten von Korrelationsstatistiken erfüllen, die Abfragen, die diese Tabellen verknüpfen, geben in ihren Prädikaten jedoch keine date-Einschränkung an.
So legen Sie die DATE_CORRELATION_OPTIMIZATION-Datenbankoption fest
Arbeiten mit Korrelationsstatistiken
Für alle in Frage kommenden Tabellenpaare werden Korrelationsstatistiken automatisch in Form indizierter Sichten erstellt, wenn Sie die DATE_CORRELATION_OPTIMIZATION-Datenbankoption auf ON festlegen. Wenn der SQL Server-Abfrageoptimierer in der Lage ist, die Korrelation zwischen Paaren von datetime-Spalten zu nutzen, verwendet er diese Korrelationsstatistiken in seinem Abfrageplan. Korrelationsstatistiken sind auch in der Logik von INSERT-, UPDATE- und DELETE-Anweisungen enthalten, wenn diese betroffen sind. Die Namen von Korrelationsstatistiken besitzen die folgende Form:
_MPStats_Sys_<constraint_object_id>_<GUID>_<FK_constraint_name>
<FK_constraint_name>ist der Name der foreign key-Einschränkung in der sys.objects-Katalogsicht, auf der die datetime-Übereinstimmung basiert. <constraint_object_id> ist eine 8-stellige hexadezimale Darstellung der objectid der foreign key-Einschränkung.
.gif "Hinweis") Hinweis |
|---|
Der FK_constraint_-Anteil des Namens der Korrelationsstatistik wird von SQL Server gekürzt, wenn der Name länger als die zulässige Länge für Bezeichner wäre. |
Wenn eine Abfrage mithilfe von SET SHOWPLAN XML ausgeführt wird, enthält jeder Filterknoten, der aus Korrelationsstatistiken abgeleitet ist, das folgende Attribut:
DateCorrelationOptimization="true"
Ein <Predicate>-Knoten, der von Korrelationsstatistiken beeinflusst wird, sieht z. B. folgendermaßen aus:
<Predicate DateCorrelationOptimization="true">
Dieses Attribut ist in jedem Filterknoten enthalten, der ausschließlich aus Korrelationsstatistiken oder durch Kombinieren eines durch Korrelationsstatistiken beeinflussten Prädikats mit einem anderen Prädikat generiert wird.
Wenn die DATE_CORRELATION_OPTIMIZATION-Datenbankoption auf ON festgelegt wurde, erstellt SQL Server in der Regel Korrelationsstatistiken für alle in Frage kommenden Paare von datetime-Spalten. SQL Server erstellt zusätzliche Korrelationsstatistiken, wenn Sie die folgenden Aufgaben ausführen:
Sie erstellen foreign key-Beschränkungen über CREATE TABLE oder ALTER TABLE, die die Anforderungen für die datetime-Korrelationsoptimierung erfüllen.
Sie erstellen einen gruppierten Index für eine datetime-Spalte, und diese Spalte kommt für Korrelationszuordnung mit der datetime-Spalte einer anderen Tabelle in Frage.
HinweisWenn gruppierte Indizes mithilfe der Option ONLINE = ON erstellt werden, werden keine Korrelationsstatistiken erstellt.Nachdem für die Indexerstellung ein Commit ausgeführt wurde, können jedoch als Ergebnis eines Ereignisses in einer anderen Transaktion, z. B. der Erstellung einer FOREIGN KEY-Einschränkung, vom Index abhängige Korrelationsstatistiken erstellt werden.
Sie ändern die NULL-Zulässigkeit oder den Datentyp einer Spalte, damit sie sich für die Korrelationszuordnung mit der datetime-Spalte einer anderen Tabelle eignet.
Sie sollten sich in Anwendungen nicht direkt auf Korrelationsstatistiken beziehen, weil SQL Server jederzeit die Entscheidung treffen kann, diese zu löschen. Sie können einzelne Korrelationsstatistiken ggf. löschen, wenn Sie feststellen, dass die Kosten, die für ihre Verwaltung erforderlich sind, die Leistung beeinträchtigen. Die Standardeinstellung für DROP-Berechtigungen für Korrelationsstatistiken wird auf die Mitglieder der festen Serverrolle sysadmin, die festen Datenbankrollen db_owner und db_ddladmin sowie den Besitzer des Tabellenpaars festgelegt, für das die Korrelationsstatistiken definiert werden. Diese Berechtigungen sind nicht übertragbar.
Korrelationsstatistiken werden unter folgenden Umständen gelöscht:
Wenn Sie die DATE_CORRELATION_OPTIMIZATION-Datenbankoption auf OFF festlegen, werden alle von SQL Server erstellten Korrelationsstatistiken gelöscht.
Korrelationsstatistiken, für deren Verwaltung erheblicher Speicherplatz erforderlich ist oder die als nicht vorteilhaft betrachtet werden, werden gelöscht.
Wenn Sie eine foreign key-Einschränkung mithilfe von DROP TABLE oder ALTER TABLE löschen, werden die Korrelationsstatistiken, die mit dieser Einschränkung verknüpft sind, ebenfalls gelöscht.
Wenn eine Operation bewirkt, dass sich die Tabellen, die für eine Korrelationszuordnung verwendet werden, nicht mehr im Besitz des gleichen Benutzers befinden, werden die entsprechenden Korrelationsstatistiken gelöscht.
Wenn Sie eine ALTER TABLE…SWITCH-Anweisung ausführen und für die Quell- oder die Zieltabelle Korrelationsstatistiken definiert sind, werden diese Korrelationsstatistiken gelöscht.
Wenn Sie einen gruppierten Index für eine datetime-Spalte erstellen, und die Korrelationsstatistiken werden anhand einer anderen datetime-Spalte der gleichen Tabelle generiert, werden die Korrelationsstatistiken gelöscht. SQL Server erstellt ggf. basierend auf dem neu erstellten gruppierten Index neue Korrelationsstatistiken.
Wenn Sie einen gruppierten Index löschen, dessen führender Indexschlüssel eine datetime-Spalte ist, werden alle zugehörigen Korrelationsstatistiken gelöscht, wenn eine andere datetime-Spalte für die gleiche Tabelle vorhanden ist, anhand derer die neuen Korrelationsstatistiken erstellt werden können.
Wenn Sie ALTER TABLE zum Ändern des Datentyps oder der NULL-Zulässigkeit einer Spalte verwenden, die für Korrelationsstatistiken verwendet wird, werden diese Statistiken gelöscht.
Korrelationsstatistiken werden als Teil der gleichen Transaktion erstellt oder gelöscht, die bewirkt hat, dass sie erstellt oder gelöscht werden. Diese Transaktion ist weder online noch asynchron.
Wenn Sie den Datenbankmodul-Optimierungsratgeber in einem einfachen, auf einem Server basierenden Optimierungsszenario zum direkten Optimieren des Produktionsservers verwenden, berücksichtigt dieser die Kosten und Vorteile von Korrelationsstatistiken. Wenn Sie den Datenbankmodul-Optimierungsratgeber jedoch in einem Szenario mit einem Testproduktionsserver verwenden, betrachtet er Korrelationsstatistiken nicht als interne Systemobjekte. Aus diesem Grund werden die Korrelationsstatistiken vom Datenbankmodul-Optimierungsratgeber während seiner Indexoptimierungsanalyse nicht für die Abfrageoptimierung verwendet. In einem Testproduktionsszenario können Sie die Empfehlungen des Datenbankmodul-Optimierungsratgebers zu den indizierten Sichten, die Korrelationsstatistiken enthalten, ggf. ignorieren. Der Datenbankmodul-Optimierungsratgeber kennt deren Kosten, aber nicht deren Vorteile. In beiden Szenarien empfiehlt der Datenbankmodul-Optimierungsratgeber möglicherweise nicht die Auswahl bestimmter Indizes, wie z. B. gruppierter Indizes für datetime-Spalten, die jedoch bei aktivierter DATE_CORRELATION_OPTIMIZATION-Option vorteilhaft sein könnte.
Abfragen von Metadaten zu Korrelationsstatistiken
Wenn Sie die Einstellung der DATE_CORRELATION_OPTIMIZATION-Datenbankoption anzeigen möchten, wählen Sie die is_date_correlation_on-Spalte der sys.databases-Katalogsicht aus.
Wählen Sie die is_date_correlation_view-Spalte der sys.views-Katalogsicht aus, um zu ermitteln, ob eine Sicht auf Korrelationsstatistiken basiert.