Optymalizacja kwerend że dostępu skorelowane kolumny Data/Godzina

DATE_CORRELATION_OPTIMIZATION bazy danych zestawu opcji poprawia wydajność kwerend, które wykonują łączyć równoważne między dwie tabele, których date lub datetime skorelowanych kolumn i które określić ograniczenie data w predykacie kwerendy.

Tabele, których date lub datetime kolumna skorelowanych wartości i 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, zgłoszenie lub danych celów.

Na przykład w AdventureWorks2008R2 Przykładowa baza danych, OrderDate kolumna Purchasing.PurchaseOrderHeader tabela i DueDate kolumna Purchasing.PurchaseOrderDetail tabela są skorelowane.Wartości data PurchaseOrderDetail.DueDate mają tendencję do wykonaj wkrótce po tych PurchaseOrderHeader.OrderDate.

Gdy opcja DATE_CORRELATION_OPTIMIZATION bazy danych jest zestaw on, SQL Server utrzymuje statystyki korelacji między dwiema tabelami, w bazie danych, które mają date lub datetime kolumna i są połączone przez ograniczenie klucza OBCEGO jednej kolumna.Domyślnie opcja ta jest zestaw do OFF.

SQL Server za pomocą tych statystyk korelacji wraz z ograniczeń data określone w predykacie kwerendy wywnioskować, że dodatkowe ograniczenia mogą być dodawane do kwerendy bez zmiany zestaw wyników.optymalizator kwerendy używa tych warunków inferred wybiera planu kwerend.Szybsze planu kwerend może spowodować, ponieważ pozwoli dodano ograniczenia SQL Server odczytać mniej danych podczas przetwarzania kwerendy.Również zwiększona wydajność obie tabele mają klastrowanego zdefiniowane na nich, indeksy i ich date lub datetime kolumny, dla których statystyki korelacji są utrzymywane są najpierw lub tylko klucz indeks klastrowany.

Załóżmy na przykład, możesz przygotować AdventureWorks2008R2 bazy danych do przechowywania informacji o korelacji dla Purchasing.PurchaseOrderDetail i Purchasing.PurchaseOrderHeader , uruchamiając następujące Transact-SQL skryptu:

USE AdventureWorks2008R2;
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 AdventureWorks2008R2
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

Załóżmy teraz, uruchom następującą kwerendę:

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
INNER JOIN Purchasing.PurchaseOrderDetail AS d
    ON h.PurchaseOrderID = d.PurchaseOrderID
WHERE h.OrderDate BETWEEN '20060101' AND '20060201';

Wartości PurchaseOrderDetail.DueDate zwrócony przez to kwerendy mogą kształtować się zazwyczaj w ciągu dni, takie jak wartości w ciągu 14 dni PurchaseOrderHeader.OrderDate.Z tego powodu SQL Server może być w stanie rozpoznać, że poprzedniej kwerendy można lepiej wyrażoną za pomocą kwerendy porównywalne do tego:

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
    Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '1/1/06' AND '2/1/06'
AND d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14;

Dokładnej formie dodano warunek, określony w klauzula i drugi, zależy od oryginalnego kwerendy i wartości danych w bazie danych.Po dodaniu warunku dorozumianych, optymalizator używa go do konstruowania plan wykonania.W tym przykładzie na jest indeks klastrowany PurchaseOrderDetail.DueDate Aby można było używać tego indeksu, aby pobrać wiersze, które spełniają d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14.Jeśli istnieje kilka lat nadaje danych w Purchasing.PurchaseOrderDetail, to kwerenda może spowodować znaczny spadek (several-fold) w realizacji czas w porównaniu do pierwotnej kwerendy.

Przed wykonaniem planu kwerend z warunku wywnioskowane ze względu na włączenie DATE_CORRELATION_OPTIMIZATION, SQL Server weryfikuje, że kwerenda da poprawną odpowiedź na podstawie bieżącej zawartości bazy danych.

Wymagania dotyczące używania opcji bazy danych DATE_CORRELATION_OPTIMIZATION

Dla dwóch tabel, z włączeniem opcji DATE_CORRELATION_OPTIMIZATION bazy danych muszą być spełnione następujące warunki:

• Bazy danych zestaw opcje muszą być zestaw w następujący sposób.ANSI_NULLS, spowodowałyby, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL i identyfikator NOTOWANE należy USTAWIĆ na ON.NUMERIC_ROUNDABORT należy USTAWIĆ na OFF.

• Musi istnieć jedno -kolumna relacji klucz obcy między tabelami.

• Tabele muszą mieć datetime kolumny są definiowane NULL.

• Co najmniej jeden z datetime kolumna muszą być kolumna klucz indeks klastrowany (w przypadku złożony indeks klucz musi być pierwszym klucz), lub musi być kolumna partycjonowania, jeśli jest on tabela podzielonym na partycje.

• Obie tabele muszą być własnością tego samego użytkownika.

Należy uwzględnić następujące po możesz 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 WSTAWIANIA, aktualizacji i usuwania operacji na zastosowanie tabel, które mogą wpłynąć na wydajność tych operacji.Nie należy włączać DATE_CORRELATION_OPTIMIZATION w środowiskach aktualizacji dużej bazy danych.

• Jeśli jeden z datetime kolumny, dla których korelacji statystyki są utrzymywane jest nie pierwszego lub klucz klastrowanego indeksu, należy rozważyć utworzenie indeks klastrowany w typie.Spowoduje to zazwyczaj prowadzi do lepszej wydajności typów kwerend objęte statystykami korelacji.Jeżeli indeks klastrowany jest już na klucz podstawowy kolumna, można zmodyfikować tabela tak, aby indeks klastrowany i klucz podstawowy korzystanie z zestawów innej kolumna.

• Włączanie DATE_CORRELATION_OPTIMIZATION nie zapewnia żadnych korzyści w następujących sytuacjach:

  • Nie istnieją żadne pary tabel, które spełniają kryteria uprzednio podanych prowadzenie statystyki korelacji.

  • Istnieją pary tabel, które spełniają kryteria prowadzenie statystyki korelacji, ale kwerendy, łączyć tych tabel nie określono ograniczenie data w ich predykatów.

Aby zestaw DATE_CORRELATION_OPTIMIZATION opcję bazy danych

• ALTER DATABASE (Transact-SQL)

Praca z statystyki korelacji

Dla wszystkich kwalifikujących się par pasujące tabel korelacji statystyki są tworzone automatycznie w formularzu Widoki indeksowane podczas możesz zestaw na opcję bazy danych DATE_CORRELATION_OPTIMIZATION.Gdy SQL Server optymalizator kwerendy jest w stanie wykorzystać korelacji między parami datetime kolumny, używa tych statystyk korelacji w jej planu kwerend.Statystyki korelacji znajdują się również w logice INSERT, UPDATE i Usuń sprawozdań, których one dotyczą.Formę nazwy korelacji statystyki:

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.

Ostrzeżenie

SQL Serverskraca FK_constraint_część nazwy statystyki korelacji, jeśli nazwa przekroczyłaby limit długości identyfikator.

Podczas wykonywania kwerendy za pomocą zestawu XML SHOWPLAN, dowolny węzeł filtru pochodzącej z korelacji statystyki zawiera następujący atrybut:

DateCorrelationOptimization="true"

Na przykład <predykatu> pod wpływem statystyki korelacji węzła wygląda następująco:

<Predicate DateCorrelationOptimization="true">

Ten atrybut jest dołączona z dowolnego węzła filtr wygenerowane całkowicie z korelacji statystyki lub łącząc predykat 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 klucza OBCEGO poprzez tworzenie tabeli lub ALTER TABLE, który spełnia wymagania dla datetime korelacji optymalizacji.

• Tworzenie indeks klastrowany w datetime kolumna i że kwalifikują się do dopasowywania korelacji z datetime kolumna z innej tabela.

  Ostrzeżenie

  Statystyki korelacji nie są tworzone podczas tworzenia indeksów klastrowanych za pomocą ONLINE = opcji.Jednak po poświęca budowanie indeksu statystyk korelacji zależą od indeksu mogą być emitowane w wyniku zdarzenie w innej transakcji, takich jak tworzenie ograniczenia na klucz OBCY.

• Zmień typ danych lub opcje dopuszczania wartości null kolumn dokonanie kwalifikujących się do dopasowywania korelacji z datetime kolumna z innej tabela.

Nie należy odwołanie do statystyk korelacji bezpośrednio w aplikacji, ponieważ SQL Server może zadecydować o upuścić je w dowolnym czas.Użytkownik może zdecydować upuścić statystyki poszczególnych korelacji, jeśli koszty utrzymania ich wpływa na wydajność.Domyślnie uprawnienia UPUSZCZANIA statystyki korelacji zestaw członków sysadmin stałej roli serwera db_owner i db_ddladmin stałe role bazy danych i właściciela pary tabel, na których są zdefiniowane statystyki korelacji.Uprawnienia te nie są zbywalne.

Statystyki korelacji są usunięte w następujących sytuacjach:

• Kiedy można zestaw opcję bazy danych DATE_CORRELATION_OPTIMIZATION OFF żadnej statystyki korelacji utworzone przez SQL Server są opuszczane.

• Statystyki korelacji nadmiernego magazynu do utrzymania, które wymagają lub który nie powinien być korzystne są opuszczane.

• Kiedy strącisz ograniczenie klucza OBCEGO za pomocą DROP TABLE lub ALTER TABLE są opuszczane żadnej 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, odpowiednie statystyki korelacji są opuszczane.

• Podczas wykonywania ALTER substancjiPRZEŁĄCZ instrukcja i albo źródło tabela miejsce docelowe lub 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.

• Podczas upuszczania indeks klastrowany, której klucz wiodących indeksu jest datetime kolumna, dowolne skojarzone korelacji statystyki są odrzucane, jeżeli inny datetime kolumna w tej samej tabela, w którym można tworzyć nowe statystyki korelacji.

• Gdy użytkownik wykonać ALTER TABLE, aby zmienić typ danych lub opcje dopuszczania wartości null kolumna uczestniczących w statystyce korelacji, te statystyki są odrzucane.

Korelacji statystyki są tworzone lub porzucone jako część tej samej transakcji, które spowodowało ich tworzenia lub porzucone.Jest to transakcja online ani asynchronicznego.

Podczas korzystania z Doradca dostrajania aparatu bazy danych w prosty, jeden na serwerze dostrajania scenariusz do dostrojenia serwera produkcyjnego bezpośrednio, uzna kosztów i korzyści korelacji statystyk.Jednak podczas korzystania z Doradca dostrajania aparatu bazy danych w scenariuszu serwer do badania produkcji nie uważa korelacji statystyki do wewnętrznych obiektów systemu.Dlatego korelacji statystyki nie są używane w optymalizacji kwerendy przez Doradca dostrajania aparatu bazy danych podczas jej indeks dostrajania analizy.W scenariuszu produkcji test może być ignorowanie wszelkich zaleceń, Doradca dostrajania aparatu bazy danych sprawia, że o Widoki indeksowane, przechowujące statystyki korelacji, ponieważ wie, ich kosztów, ale nie ich świadczenia.W obu scenariuszach Doradca dostrajania aparatu bazy danych nie może zalecić zaznaczenia niektóre indeksy, takie jak stosowanie indeksów klastrowanych w datetime kolumny, które mogłyby być korzystne, gdy włączone jest DATE_CORRELATION_OPTIMIZATION.

Badanie metadane dotyczące statystyk korelacji

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ć, czy widok jest oparty na statystyki korelacji, wybierz is_date_correlation_view kolumna sys.views wykazu widoku.

Zobacz także

Koncepcje

Ustawianie opcji

Inne zasoby

Za pomocąDoradca dostrajania aparatu bazy danych