Optimizing Queries That Access Correlated datetime Columns
Bazy danych DATE_CORRELATION_OPTIMIZATION zestaw opcja zwiększa wydajność kwerendy, która wykonuje łączyć równoważne między dwie tabele, których Data or Data/Godzina kolumn są zależne, a które określą ograniczenie daty w predykacie kwerendy.
Tabele, których Data or Data/Godzina wartości kolumna są powiązane, a które mogą korzystać z włączeniem DATE_CORRELATION_OPTIMIZATION, zazwyczaj są częścią relacji jeden do wielu i są używane głównie do obsługi wspomaganie decyzji, raportowania lub danych, magazynowanie celów.
Na przykład w AdventureWorks Przykładowa baza danych, DataZamówienia. kolumnaPurchasing.PurchaseOrderHeader Tabela a DueDate kolumnaPurchasing.PurchaseOrderDetail tabeli są zależne.Wartości data PurchaseOrderDetail.DueDate zwykle należy wykonać wkrótce po tych PurchaseOrderHeader.OrderDate.
Gdy opcja DATE_CORRELATION_OPTIMIZATION bazy danych jest zestaw on, SQL Server obsługuje statystykę korelacji między dowolnym dwie tabele w bazie danych, zawierające Data or Data/Godzina kolumn i są połączone przez ograniczenia klucz obcy jednej kolumna.Domyślnie opcja ta ma wartość OFF.
SQL Server za pomocą tych statystyk korelacji wraz z ograniczeniem data określonej w predykacie kwerendy wywnioskować dodatkowe ograniczenia mogą być dodawane do kwerendy bez wprowadzania zmian zestaw wyników.optymalizator kwerendy używa tych warunków inferred wybiera planu kwerend.Szybsze planu kwerend może spowodować, ponieważ pozwoli dodatkowych ograniczeń SQL Server podczas przetwarzania kwerendy, należy przeczytać mniej danych. Również zwiększona wydajność, gdy obie tabele zostały zgromadzone indeksów zdefiniowane na nich, a ich Data or Data/Godzina kolumny, dla których korelacji statystyki są zachowywane są najpierw lub tylko klucz indeks klastrowany.
Na przykład, załóżmy, że konieczne jest przygotowanie AdventureWorks bazy danych do przechowywania informacji o korelacji dla Purchasing.PurchaseOrderDetail and Purchasing.PurchaseOrderHeader uruchamiając następujący skrypt języka Transact-SQL:
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
Załóżmy teraz, uruchom następującą kwerendę:
SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '20020101' AND '20020201';
Wartości PurchaseOrderDetail.DueDate zwrócone przez tę kwerendę zazwyczaj mogą wchodzić w pewnym okresie dni, takie jak wartości w ciągu 14 dniPurchaseOrderHeader.OrderDate.Z tego powodu SQL Server może być w stanie wywnioskować, że poprzedniej kwerendy mogą być lepiej wyrażone za pomocą porównywalne z tej kwerendy:
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;
Dokładną formę dodano warunek, określony w drugim klauzula AND, zależy od oryginalnego kwerendy i wartości danych w bazie danych.Po dodaniu warunku dorozumianych, optymalizator używa jej do utworzenia planu wykonania.W tym przykładzie istnieje indeks klastrowany na PurchaseOrderDetail.DueDate , tak aby można było używać tego indeksu, aby pobrać wiersze, które spełniają d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14. Jeśli istnieje kilka lat nadaje się na danych w Purchasing.PurchaseOrderDetail, tej kwerendy może spowodować znaczny spadek (several-fold) w czasie wykonania w porównaniu z oryginalnej kwerendy.
Przed wykonaniem planu kwerendy z warunku, który jest wywnioskować z powodu DATE_CORRELATION_OPTIMIZATION, włączanie SQL Server weryfikuje, że kwerendy będą być poprawną odpowiedź na podstawie bieżącej zawartości bazy danych.
Wymagania dotyczące korzystania z opcji DATE_CORRELATION_OPTIMIZATION bazy danych
W przypadku dwóch tabel w celu skorzystania z włączeniem opcji DATE_CORRELATION_OPTIMIZATION bazy danych muszą być spełnione wszystkie następujące warunki:
Opcje zestaw bazy danych należy ustawić w następujący sposób.ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS ARITHABORT, CONCAT_NULL_YIELDS_NULL i umieszczony w CUDZYSŁOWIE IDENTIFIER musi być zestaw on.Musi być zestaw NUMERIC_ROUNDABORT na OFF.
Musi istnieć relacja klucz obcy jednokolumnową między tabelami.
Tabele muszą oba mają Data/Godzina kolumn, które są zdefiniowane NOT NULL.
Co najmniej jeden z Data/Godzina kolumny muszą znajdować się w kolumnie klucz indeks klastrowany (w przypadku złożonych klucz indeksu musi być pierwszym klucz), lub musi być kolumna partycjonowania, jeżeli jest podzielone na partycje tabela.
Obie tabele muszą być własnością tego samego użytkownika.
Należy wziąć pod uwagę następujące kwestie, gdy użytkownik zestaw DATE_CORRELATION_OPTIMIZATION opcję bazy danych na:
SQL Server przechowuje informacje o korelacji w formularzu Statystyki.Te statystyki są aktualizowane przez SQL Server Podczas operacji INSERT, UPDATE i DELETE na stosowne tabele, które może mieć wpływ na wydajność tych operacji. Nie należy włączać DATE_CORRELATION_OPTIMIZATION w środowiskach aktualizacji dużej bazy danych.
Jeśli jeden z Data/Godzina kolumn, dla których korelacji statystyki są zachowywane jest nie pierwszej lub tylko klucz indeks klastrowany należy rozważyć tworzenie indeks klastrowany.W ten sposób zwykle prowadzi do lepszą wydajność w typach kwerend objętych statystyki korelacji.Jeżeli indeks klastrowany jest już istnieje w kolumnach klucz podstawowy, można zmodyfikować tabela tak, aby indeksu klastrowanego i klucz podstawowy należy używać zestawów innej kolumna.
Włączanie DATE_CORRELATION_OPTIMIZATION nie zapewnia żadnych korzyści w następujących sytuacjach:
Brak nie pary tabel, które spełniają kryteria podane wcześniej do przechowywania statystyki korelacji.
Brak pary tabel, które spełniają kryteria do przechowywania statystyki korelacji, ale kwerendy, które połączyć te tabele tak, aby nie określono ograniczenie data w ich predykatów.
Aby ustawić opcję DATE_CORRELATION_OPTIMIZATION bazy danych
Praca z Statystyka korelacji
Dla wszystkich uprawnionych pary pasujących tabel, korelacji statystyki są tworzone automatycznie w formularzu Widoki indeksowane, gdy użytkownik zestaw DATE_CORRELATION_OPTIMIZATION opcję bazy danych na.Gdy SQL Server optymalizator kwerendy jest w stanie wykorzystać korelacji między parami Data/Godzina kolumn, używa tych statystyk korelacji w jego planu kwerend.Statystyki korelacji znajdują się również w logice INSERT, UPDATE, a DELETE instrukcje, których ma wpływu.Nazwy korelacji statystyk mieć następującą postać:
_MPStats_Sys_<constraint_object_id>_<GUID>_<FK_constraint_name>
<FK_constraint_name>is the name of the foreign key constraint in the sys.objects catalog view on which the datetime match is based.<constraint_object_id> is an 8-digit hexadecimal representation of the objectid of the foreign key constraint.
Uwaga
SQL Server skraca FK_constraint_fragment nazwy statystyki korelacji, jeśli nazwa mogłaby przekroczyć limit długości identyfikator.
Podczas wykonywania kwerendy za pomocą zestaw plan wykonania XML, dowolny węzeł filtr pochodzącej z korelacji statystyki zawiera następujący atrybut:
DateCorrelationOptimization="true"
Na przykład <Predykat> węzeł pod wpływem statystyki korelacji wygląda następująco:
<Predicate DateCorrelationOptimization="true">
Ten atrybut jest dołączane z dowolnego węzła filtr całkowicie z korelacji statystyki lub łącząc predykatu pod wpływem statystyki korelacji z niektórych innych predykatu.
Generally, when the DATE_CORRELATION_OPTIMIZATION database option is set to ON, SQL Server creates correlation statistics for all eligible pairs of datetime columns.SQL Server creates additional correlation statistics when you perform the following:
Tworzenie ograniczenia na klucz obcy CREATE tabela lub ALTER tabela, które spełniają wymagania dotyczące Data/Godzina korelacji optymalizacji.
Tworzenie indeks klastrowany w Data/Godzina kolumna i że kwalifikuje się do korelacji zgodne z Data/Godzina kolumnie innej tabela.
Uwaga
Statystyki korelacji nie są tworzone podczas tworzenia indeksów klastrowanych przy użyciu ONLINE = ON opcji.Jednak po zaangażowana w tworzenie indeksu statystyki korelacji, które zależą od indeksu może być wbudowane w wyniku zdarzenie w innej transakcji, takich jak tworzenie ograniczenia na klucz obcy.
W przypadku zmiany typu danych lub opcje dopuszczania wartości null kolumna, że kwalifikuje korelacji zgodne z Data/Godzina kolumnie innej tabela.
Nie należy odwoływać się do statystyk korelacji bezpośrednio w aplikacjach, ponieważ program SQL Server może w dowolnej chwili je porzucić.Można podjąć decyzję o porzuceniu poszczególnych statystyk korelacji, jeśli okaże się, że zachowanie ich ma negatywny wpływ na wydajność.Wartość domyślna uprawnień DROP dotyczących statystyk korelacji jest ustawiania dla członków stałej roli serwera sysadmin, stałych ról bazy danych db_owner i db_ddladmin oraz właściciela pary tabel, dla których zostały zdefiniowane statystyki korelacji.Tych uprawnień nie można przenosić.
Statystyki korelacji są usunięte w następujących sytuacjach:
Podczas ustawiania opcji DATE_CORRELATION_OPTIMIZATION bazy danych do wyłączona, wszelkie statystyki korelacji utworzone przez SQL Server są odrzucane.
Statystyki korelacji nadmierne pamięci masowej do zachowania, które wymagają lub nie oczekuje się korzystne są odrzucane.
Po upuszczeniu ograniczenia klucz obcy przy użyciu DROP tabela lub ALTER tabela, są usuwane wszystkie statystyki korelacji skojarzonych z tym ograniczeniem.
Podczas operacji powoduje, że tabele, które uczestniczą w korelacji pasujące do już być własnością tego samego użytkownika, odpowiadające im statystyki korelacji są odrzucane.
Podczas wykonywania instrukcja ALTER tabela…SWITCH i tabela źródłowej lub miejsce docelowe tabela ma zdefiniowane na nim statystyki korelacji, tych statystyk korelacji są odrzucane.
When you create a clustered index on a datetime column and correlation statistics are built on a different datetime column of the same table, the correlation statistics are dropped.SQL Server may create new correlation statistics based on the newly created clustered index, if eligible.
Po upuszczeniu indeks klastrowany, której klucz wiodących indeksu jest Data/Godzina kolumna, udostępnienia skojarzonych z nim korelacji statystyki są odrzucane, jeśli inny Data/Godzina kolumna znajduje się na tej samej tabela, w którym można tworzyć nowe statystyki korelacji.
Podczas wykonywania instrukcji ALTER tabela, aby zmienić typ danych lub opcje dopuszczania wartości null uczestniczących w statystyce korelacji kolumna te statystyki są odrzucane.
Korelacja statystyki są tworzone lub usunięte w ramach tej samej transakcji, które spowodowało ich ma zostać utworzony lub usunięty.Ta transakcja jest online ani asynchronicznego.
Po użyciu Doradca dostrajania aparat bazy danych w prosty, jedno na serwerze dostrajanie scenariusz dostroić serwer produkcyjny bezpośrednio, uzna kosztów i zysków statystyk korelacji.Jednak po użyciu Doradca dostrajania aparat bazy danych scenariuszu produkcji badania serwera go nie bierze pod uwagę korelacji statystyki do wewnętrznych obiektów systemu.Dlatego korelacji statystyki nie są używane w optymalizacji kwerendy przez Doradca dostrajania aparat bazy danych podczas jej indeks dostrajanie analizy.W scenariuszu produkcji testu, możesz zignorować zaleceń, Doradca dostrajania aparat bazy danych sprawia, że temat widoków indeksowanych, przechowujące statystyki korelacji, ponieważ jego wie, że ich kosztów, ale nie ich świadczenia.W obu przypadkach klasyfikatora strojenia aparat bazy danych może zalecić nie zaznaczenie niektórych indeksy, takie jak stosowanie indeksów klastrowanych w Data/Godzina kolumn, które mogą być korzystne, gdy DATE_CORRELATION_OPTIMIZATION jest włączona.
Podczas badania metadane statystyki Korelacja — informacje
To view the setting of the DATE_CORRELATION_OPTIMIZATION database option, select the is_date_correlation_on column of the sys.databasescatalog view.
Aby określić, jeśli widok jest oparty na statystyki korelacji, wybierz is_date_correlation_view kolumnasys.views Służy do wyświetlania katalogu.