Aby poprawić wydajność kwerendy przy użyciu statystyk
optymalizator kwerendy używa statystyki do tworzenia planów kwerend, które poprawiają wydajność kwerendy.Większość kwerend optymalizator kwerendy generuje już niezbędne dane statystyczne dla planu kwerend o wysokiej jakości; w niektórych przypadkach trzeba utworzyć dodatkowe statystyki lub zmodyfikować projekt kwerendy, aby uzyskać najlepsze wyniki.
W tym temacie omówiono następujące pojęcia i wskazówki dotyczące używania kwerendy skutecznie statystyki optymalizacji:
Co to są statystyki optymalizacji kwerendy?
Przy użyciu opcji statystyki całej bazy danych
Określanie, kiedy tworzenie statystyk
Określanie, kiedy aktualizacji statystyk
Projektowanie kwerendy używające skutecznie statystyki
Aby uzyskać więcej informacji na temat planu kwerend i jak odnosi się ona do kwerendy wydajności, zobacz Analizowanie kwerendy.
Co to są statystyki optymalizacji kwerendy?
Statystyki dla optymalizacji kwerendy są obiekty, które zawierają informacje statystyczne dotyczące dystrybucji wartości w jednym lub więcej kolumn w tabela lub indeksowany widok.optymalizator kwerendy używa tych statystyk oszacowanie Kardynalność, lub liczbę wierszy w wyniku kwerendy.Te szacunków Kardynalność włączyć optymalizator kwerendy do tworzenia planu kwerend wysokiej jakości.Na przykład optymalizator kwerendy można użyć szacunki Kardynalność wybrać indeksu wyszukiwania zamiast bardziej operatora zasób-operator skanowania intensywnego indeksu, a czyniąc poprawić wydajność kwerendy.
Każdy obiekt statystyk jest tworzony na liście tabela jeden lub więcej kolumnas i zawiera histogram wyświetlanie dystrybucji wartości w pierwszym kolumna.Statystyki obiektów na wiele kolumn także przechowywać informacje statystyczne o korelacji wartości między kolumnami.Te statystyki korelacji lub gęstości, są obliczane na podstawie liczby wierszy różne wartości kolumna.Aby uzyskać więcej informacji o obiektach statystyk, zobacz DBCC SHOW_STATISTICS (Transact-SQL).
Filtrowane statystyki
Filtrowane statystyki można poprawić wydajność kwerendy dla kwerend, które z dobrze podzbiory danych wybierz.Filtrowane statystyki za pomocą predykat filtr wybierz podzbiór danych zawartych w statystykach.Dobrze filtrowane statystyki można poprawić plan wykonania kwerend w porównaniu z tabela pełne statystyki.Aby uzyskać więcej informacji dotyczących filtru predykatu, zobacz Tworzenie statystyk (Transact-SQL).Więcej informacji o Kiedy tworzyć statystyki przefiltrowane, zobacz Określania podczas tworzenia statystyk w tym temacie.Dla studium przypadek wpis blogu, zobacz Przy użyciu statystyk filtrowane z tabelami na partycje, witryna sieci Web SQLCAT.
Przy użyciu opcji statystyki całej bazy danych
Ważne jest zrozumienie, wykonaj następujące opcje statystyki całej bazy danych oraz aby sprawdzić, czy są zestaw odpowiednio:
AUTO_CREATE_STATISTICS
AUTO_UPDATE_STATISTICS
AUTO_UPDATE_STATISTICS_ASYNC
Za pomocą opcji AUTO_CREATE_STATISTICS
Podczas automatycznego tworzenia statystyk opcji, AUTO_CREATE_STATISTICS, jest włączona, optymalizator kwerendy tworzy statystyk dla poszczególnych kolumn w predykacie kwerendy w celu poprawienia danych szacunkowych Kardynalność dla planu kwerend.Te jedno -kolumna statystyki są tworzone na kolumnas, które nie zostały jeszcze histogram w istniejącego obiektu statystyki.
Następująca kwerenda służy do określenia, jeśli optymalizator kwerendy utworzył statystyki dla kolumna predykatu kwerendy.Wykonuje kwerendę widoki wykazu sys.stats i sys.stats_columns zwraca nazwę obiektu bazy danych, nazwę kolumna i nazwa statystyki dla wszystkich kolumn, które mają jednokolumnową statystyki.Kiedy optymalizator kwerendy tworzy statystyki na pojedynczej kolumny z opcją AUTO_CREATE_STATISTICS, nazwa statystyki rozpoczyna się od _WA.
USE AdventureWorks;
GO
SELECT OBJECT_NAME(s.object_id) AS object_name,
COL_NAME(sc.object_id, sc.column_id) AS column_name,
s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;
Opcja AUTO_CREATE_STATISTICS nie określa, czy statystyki tworzone dla indeksów.Ta opcja nie generuje również statystyki filtrowane.Dotyczy wyłącznie jedno -kolumna statystyki dla pełnego tabela.
Za pomocą opcji AUTO_UPDATE_STATISTICS
Podczas automatycznego aktualizacja statystyki włączona jest opcja AUTO_UPDATE_STATISTICS, optymalizator kwerendy określa kiedy statystyki mogą być nieaktualne i aktualizuje je, gdy są one używane przez kwerendę.Statystyki staje się poza-o-data po wstawić, maksymalniedata, usunąć lub scalić operacje zmiany rozkładu danych w tabela lub indeksowany widok.optymalizator kwerendy określa, kiedy statystyk może być mało-o-data przez liczenie modyfikacji danych od czasu ostatniego statystyki w górędata i porównanie liczby modyfikacje progu.Próg jest oparty na liczbę wierszy w tabela lub indeksowany widok.
optymalizator kwerendy szuka się-o-data Statystyka przed kompilowania kwerendy i przed wykonaniem buforowanego planu kwerend.Przed kompilowania kwerendy, optymalizator kwerendy używa kolumn, tabele i widoki indeksowane w predykacie kwerendy, aby określić, które statystyki może być poza-o-data.Przed wykonaniem buforowanego planu kwerend, Aparat baz danych weryfikuje, że plan kwerend odwołuje się-do-data Statystyka.
Opcja AUTO_UPDATE_STATISTICS odnosi się do obiektów statystyki dla indeksów kolumn pojedynczej kwerendy predykatów i statystyki tworzone z Tworzenie statystyk instrukcja.Opcja ta dotyczy również statystyki filtrowane.
Ustawienia na AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS do
Automatyczne tworzenie statystyk opcji, AUTO_CREATE_STATISTICS i opcję automatycznej aktualizacja statystyki AUTO_UPDATE_STATISTICS, są domyślnie i zaleca się używanie domyślnego dla większości baz danych użytkowników.Można wybrać następujące instrukcja do wyświetlania bieżącej wartości te opcje dla wszystkich baz danych użytkowników:
SELECT name AS "Name",
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO
Poniższy przykład ustawia on dla AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS AdventureWorks2008R2 bazy danych:
USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
SET AUTO_UPDATE_STATISTICS ON;
GO
Aby uzyskać więcej informacji dotyczących sposobu zestaw Zobacz Opcje te statystyki ZMIENIA opcje zestawu bazy danych (Transact-SQL).
Wyłączenie i ponowne włączenie AUTO_UPDATE_STATISTICS dla niektórych statystyk
Gdy AUTO_UPDATE_STATISTICS jest włączony, można zastąpić zachowanie aktualizacji statystyk całej bazy danych i zestaw statystyk automatycznych aktualizacji wyłączony dla pojedynczej tabela, indeks lub kolumna, jak wymagane przez aplikację.Gdy AUTO_UPDATE_STATISTICS jest włączony, można wyłączyć i ponownie włączyć Aktualizacje automatyczne statystyki dla tabela, indeks lub kolumna w następujący sposób:
Użyj sp_autostats systemowa procedura składowana.To wyłączyć lub włączyć ponownie aktualizacji statystyk dla tabela lub indeksu.
Określ opcję NORECOMPUTE z Aktualizacji statystyk instrukcja.Aby ponownie włączyć aktualizacje statystyki ponownie aktualizacji statystyk bez opcji NORECOMPUTE.
Określ opcję NORECOMPUTE z Tworzenie statystyk instrukcja.Aby ponownie włączyć aktualizacji statystyk, usuń statystyki z DROP statystyki , a następnie uruchomić tworzenia statystyk bez opcji NORECOMPUTE.
Określ opcję STATISTICS_NORECOMPUTE z CREATE INDEX instrukcja.Aby ponownie włączyć aktualizacji statystyk, można uruchomić Zmianę INDEKSU STATISTICS_NORECOMPUTE = OFF.
Gdy AUTO_UPDATE_STATISTICS jest wyłączone, nie zestaw automatyczne aktualizacje na pojedynczej tabela, indeks lub kolumna.Ponowne włączenie aktualizacji automatycznych statystyk przywraca zachowanie określone przez opcję AUTO_UPDATE_STATISTICS.Jeśli opcja AUTO_UPDATE_STATISTICS jest wyłączona, nie występuje w aktualizacji statystyki.
Kiedy używać aktualizacji statystyk synchroniczna lub asynchroniczna
Statystyki aktualizacje mogą być synchroniczne (domyślnie) lub asynchroniczna.Synchroniczne statystyki aktualizacje zawsze kompilowania kwerend i wykonać z aktualnych danych statystycznych; Gdy statystyki są poza-o-data, czeka optymalizator kwerendy dla updatad statystyki przed kompilowania i wykonaniem kwerendy.Asynchroniczne danych statystycznych w górędatas, kwerendy kompilacji z istniejących statystyk, nawet jeśli istniejących statystyk znajdują się-o-data; optymalizator kwerendy można wybrać plan kwerend warunkami panującymi, jeśli są statystyki poza-o-data podczas kompiluje kwerendy.Kwerendy, które skompilować po ukończeniu asynchronicznego aktualizacje będą korzystać z zaktualizowanych statystyk.
Opcja aktualizacji statystyk asynchronicznego całej bazy danych, AUTO_UPDATE_STATISTICS_ASYNC, określa, czy optymalizator kwerendy używa aktualizacji statystyk synchroniczna lub asynchroniczna.Domyślnie opcja aktualizacji statystyk asynchroniczne jest wyłączona i optymalizator kwerendy aktualizuje statystyki synchronicznie.Opcja AUTO_UPDATE_STATISTICS_ASYNC odnosi się do obiektów statystyki dla indeksów pojedynczej kolumny w predykaty kwerendy i statystyki tworzone z Tworzenie statystyk instrukcja.
Następujące polecenie służy do wyświetlania asynchronicznego opcja automatycznej aktualizacji dla wszystkich baz danych:
SELECT name AS "Name",
is_auto_update_stats_async_on AS "Asynchronous Update"
FROM sys.databases;
GO
Warto rozważyć synchroniczne statystyki dla następujących scenariuszy:
- Wykonywać operacje zmiany rozkładu danych, takich jak Obcinanie tabela lub wykonywanie aktualizacji zbiorczej duży procent wierszy.Jeśli nie zapasowejdata Statystyka po operaton, przy użyciu statystyk synchronicznego wykonywania zapewni statystyki są w górę-do-data przed wykonaniem kwerendy w zmienionych danych.
Należy rozważyć zastosowanie asynchronicznej statystyki do osiągnięcia bardziej przewidywalny czasy odpowiedzi kwerendy w następujących scenariuszach:
Aplikacja często wykonuje tę samą kwerendę, podobnych kwerend lub planów kwerend buforowaną podobne.Twoje czasy odpowiedzi kwerendy może być bardziej przewidywalny aktualizacji statystyk asynchronicznego niż aktualizacji statystyk synchroniczne, ponieważ optymalizator kwerendy można wykonać przychodzące kwerend bez oczekiwania na aktualnych danych statystycznych.Pozwala to uniknąć opóźniania niektórych kwerend i innych nie.Aby uzyskać więcej informacji na temat znajdowania podobnych kwerend Zobacz Znajdowanie i dostrajania podobnych kwerend za pomocą kwerendy i kwerendy Plan mieszania.
Aplikacja napotkała żądanie klient czas limity spowodowane przez jeden lub więcej kwerend oczekiwanie aktualizowane statystyki.W niektórych przypadkach oczekiwanie synchroniczne statystyk może spowodować, że aplikacje z agresywnym czas limity nie powiedzie się.
Określanie, kiedy tworzenie statystyk
optymalizator kwerendy utworzy już statystyki w następujący sposób:
optymalizator kwerendy tworzy statystyki dla indeksów na tabel lub widoków, podczas tworzenia indeksu.Te statystyki są tworzone na klucz kolumny indeksu.Jeżeli indeks jest indeksem filtrowane, optymalizator kwerendy tworzy filtrowane statystyki na tym samym podzbiór wierszy, określonego dla indeksu filtrowane.Więcej informacji o filtrowane indeksy, zobacz Filtrowane wskazówek indeksu i CREATE INDEX (Transact-SQL).
optymalizator kwerendy tworzy statystyki dla pojedynczej kolumny w kwerendzie predykaty przy AUTO_CREATE_STATISTICS.
Większość kwerend te dwie metody tworzenia statystyk zapewnienia planu kwerend wysokiej jakości; w kilku przypadkach planów kwerend można poprawić, tworząc dodatkowe statystyki z Tworzenie statystyk instrukcja.Te dodatkowe statystyki można przechwycić korelacji statystycznych, które optymalizator kwerendy nie uwzględniać podczas tworzenia statystyk dla indeksów lub pojedynczej kolumny.Aplikacja może mieć dodatkowe korelacji statystycznych w tabela danych, jeśli obliczone do obiektu statystyk może umożliwić optymalizator kwerendy w celu poprawy planów kwerend.Na przykład statystyki przefiltrowanego podzbiór wierszy danych lub wielokolumnowego statystyki predykatu kolumn w kwerendzie może zwiększyć planu kwerend.
Podczas tworzenia statystyk z instrukcja tworzenia statystyk, zaleca się przechowywanie opcja AUTO_CREATE_STATISTICS tak, aby optymalizator kwerendy w dalszym ciągu regularnie tworzyć jedno -kolumna statystyki dla predykatu kwerendy kolumnas.Więcej informacji o kwerendzie predykatów, zobacz Warunek wyszukiwania (Transact-SQL).
Należy rozważyć utworzenie statystyki za tworzenie statystyk instrukcja, gdy dotyczy następujących:
Aparat baz danych Dostrajania klasyfikatora sugeruje tworzenia statystyk.
Predykat kwerendy zawiera wiele kolumn skorelowanych, które nie są już w tym samym indeksem.
Kwerenda wybiera z podzbiór danych.
Kwerenda ma brakujące dane statystyczne.
Doradca dostrajania aparatu bazy danychSugeruje tworzenia statystyk
Aparat baz danych Dostrajania klasyfikator jest narzędzie , analizuje wydajność skutków obciążeń dla jednego lub kilku baz danych.Zalecenia dotyczące poprawy wydajności (takich jak sugerujące indeksy, aby utworzyć) oferuje i zasugerować tworzenie statystyk optymalizacji kwerendy przy użyciu tworzenia statystyk.Należy wykonać zalecenie.Więcej informacji o Aparat baz danych Dostrajania klasyfikatora, zobacz Dostrajanie fizycznego projektu bazy danych.
Kwerenda predykat zawiera wiele skorelowane kolumn
Predykat kwerendy zawiera wiele kolumn Kolumna krzyżowych związki i zależności, statystyki dotyczące wielu kolumn może poprawić planu kwerend.Statystyki dotyczące wielu kolumnas zawierają cross -kolumna statystyki korelacji, nazywane gęstości, które nie są dostępne w jedno -kolumna statystyki.Gęstości można poprawić Kardynalność szacunków, gdy wyniki kwerendy zależy od danych relacje między wiele kolumn.
Jeśli kolumny są już ten sam indeks, statystyki wielokolumnowego obiekt już istnieje i nie jest konieczne ręczne tworzenie.Jeśli kolumny nie są już w ten sam indeks, można utworzyć statystyki wielokolumnowego tworzenia indeksu w kolumnach lub przy użyciu instrukcja tworzenia statystyk.Wymaga więcej zasobów systemowych do utrzymania indeksu niż obiekt statystyki.Jeśli aplikacja wymaga indeksie wielokolumnowym, można economize na zasoby systemowe przez utworzenie obiektu statystyki bez tworzenia indeksu.
Podczas tworzenia statystyk wielokolumnowy, kolejność kolumn w definicji obiektu statystyki wpływa na skuteczność gęstości dokonywania szacunków kardynalność.Obiekt statystyki przechowuje gęstości dla każdego prefiksu kolumn klucz w definicji obiektu statystyki.Aby uzyskać więcej informacji o gęstości, zobacz DBCC SHOW_STATISTICS (Transact-SQL).
Aby utworzyć gęstości, które są przydatne do oszacowania Kardynalność, kolumny w predykacie kwerendy musi pasować prefiksów kolumn w definicji obiektu statystyki.Na przykład, następujące tworzy obiekt wielokolumnowego statystyki w kolumnach LastName, MiddleName, i FirstName.
USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.stats
WHERE name = 'LastFirst'
AND object_ID = OBJECT_ID ('Person.Person'))
DROP STATISTICS Person.Person.LastFirst;
GO
CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName);
GO
W tym przykładzie obiekt statystyki LastFirst ma gęstości dla prefiksów następujące kolumna: (LastName), (LastName, MiddleName), and (LastName, MiddleName, FirstName).Gęstość jest niedostępna dla (LastName, FirstName).Jeśli w kwerendzie użyto LastName i FirstName bez korzystania z MiddleName, gęstość jest niedostępne dla oszacowania kardynalność.
Kwerenda wybiera z podzbioru danych
Gdy optymalizator kwerendy tworzy statystyki dla pojedynczych kolumn i indeksów, tworzy statystyki dla wartości we wszystkich wierszach.Kwerendy Wybierz podzbiór wierszy, a to podzbiór wierszy ma dystrybucji unikatowe, filtrowane statystyki można poprawić planów kwerend.Filtrowane statystyki można utworzyć przy użyciu instrukcja tworzenia statystyk z klauzula WHERE, aby zdefiniować wyrażenie filtru predykatu.
Na przykład za pomocą AdventureWorks2008R2, każdy produkt w Production.Product tabela należy do jednej z czterech kategorii w Production.ProductCategory tabela: Rowery, składniki, odzież i akcesoria.Rozkład różnych danych dla wagi każdej kategorii ma: Bike zakresu wag od 13.77 do 30,0 zakres masy składnika od 2,12 1050.00 niektóre wartości NULL odzieży odważników są wagi wszystkich wartości NULL i akcesoria są również NULL.
Używając Bikes, na przykład, filtrowane dane statystyczne dotyczące wagi wszystkich rowerów zapewni bardziej dokładne statystyki optymalizator kwerendy i może zwiększyć jakość planu kwerend w porównaniu z tabela pełne statystyki lub nieistniejący statystyki na Weight kolumna.Kolumna wagi bike jest odpowiednie do pełnienia funkcji statystyki przefiltrowane, ale niekoniecznie odpowiednie do pełnienia funkcji indeks filtrowane, jeśli liczba wyszukiwań wagi jest stosunkowo niewielka.Uzyskanie wydajność dla wyszukiwania, które zawiera indeks filtrowane nie mogą przeważyć dodatkowe utrzymania i kosztów składowania dodawania filtrowane indeks do bazy danych.
Poniższa instrukcja tworzy BikeWeights Filtrowane statystyki wszystkie podkategorie Bikes.Filtrowane wyrażenie predykatu definiuje bikes, wyliczając wszystkie podkategorie rowerów z porównania Production.ProductSubcategoryID IN (1,2,3).Predykat nie można użyć nazwy kategorii Bikes, ponieważ jest on przechowywany w Production.ProductCategory tabela i wszystkich kolumn w filtrze wyrażenie musi być w tej samej tabela.
USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.stats
WHERE name = 'BikeWeights'
AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO
optymalizator kwerendy za pomocą BikeWeights Filtrowane statystyki poprawy plan kwerend dla następującej kwerendy wybierające wszystkie rowery, które ważyć więcej niż 25.
SELECT P.Weight AS Weight, S.Name AS BikeName
FROM Production.Product AS P
JOIN Production.ProductSubcategory AS S
ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25
ORDER BY P.Weight;
GO
Kwerenda ma brakujące dane statystyczne
Jeśli błąd lub inne zdarzenie uniemożliwiają tworzenie statystyk optymalizator kwerendy, optymalizator kwerendy tworzy plan kwerend bez użycia statystyki.optymalizator kwerendy oznacza statystyk jako brakujące i próbuje ponownie wygenerować statystyki następnego czas kwerenda jest wykonywana.
Brak statystyki są oznaczone jako ostrzeżeń (nazwa tabela czerwony), gdy plan wykonania kwerendy graficznie jest wyświetlany przy użyciu SQL Server Management Studio.Aby uzyskać więcej informacji, zobacz Wyświetlanie graficznego wykonanie planów (SQL Server Management Studio).Ponadto monitorowanie Missing Column Statistics klasa zdarzenia za pomocą SQL Server Profiler wskazuje brak statystyki.Aby uzyskać więcej informacji, zobacz Błędy i ostrzeżenia zdarzeń kategorii (aparat bazy danych).
Jeśli brakuje statystyki, wykonaj następujące czynności:
Sprawdź czy AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS na.
Zweryfikuj, że baza danych nie jest tylko do odczytu.Jeśli baza danych jest tylko do odczytu, optymalizator kwerendy nie może zapisać danych statystycznych.
Tworzenie statystyk brakujących przy użyciu instrukcja tworzenia statystyk.
Określanie, kiedy aktualizacji statystyk
optymalizator kwerendy określa, kiedy statystyk może być mało-o-data i w górędatas plan ich, kiedy są potrzebne dla kwerendy.W niektórych przypadkach można poprawić planu kwerend i dlatego poprawić wydajność kwerendy przez często aktualizowanie statystyki więcej niż wystąpić, jeśli AUTO_UPDATE_STATISTICS.Możesz aktualizacja statystyki z aktualizacja statystyki instrukcja lub procedura składowana sp_updatestats.
Aktualizowanie statystyki gwarantuje, że kwerendy kompilacji z góry-do-data statystyki.Jednakże aktualizowanie statystyki powoduje ponowną kompilację kwerend.Zaleca się, aby nie aktualizuje statystyki zbyt często ponieważ zależnościami wydajności między poprawy planów kwerend i czas ponownego kompilowania kwerend.Szczególnych skutków ubocznych zależy od aplikacji.
Podczas aktualizowania statystyk z aktualizacja statystyki lub sp_updatestats, zaleca się przechowywanie AUTO_UPDATE_STATISTICS zestaw on tak, aby optymalizator kwerendy w dalszym ciągu rutynowo aktualizacja statystyki.Aby uzyskać więcej informacji dotyczących aktualizacja statystyki na kolumna, indeks, tabela lub indeksowany widok, zobacz AKTUALIZUJ STATYSTYKĘ (Transact-SQL).Informacje dotyczące aktualizacja statystyki dla wszystkich tabel zdefiniowanych przez użytkownika i wewnętrznej bazy danych, zobacz procedura składowana sp_updatestats (języka Transact-SQL).Na przykład następujące polecenie wywołania sp_updatestats Aktualizacja wszystkich statystyk dla bazy danych.
EXEC sp_updatestats
Aby określić, kiedy ostatniej aktualizacji statystyk, użyj STATS_DATE funkcja.
Należy rozważyć aktualizowanie statystyki dla następujących warunków:
Czasów wykonania kwerendy są powolne.
Operacje występują w kolejności rosnącej lub malejącej kolumny klucz.
Po czynności konserwacji.
Czasów wykonania kwerendy są powolne
Jeżeli czasy odpowiedzi kwerendy są wolne lub nieprzewidywalne, zapewnić kwerend-do-data Statystyka przed wykonaniem dodatkowe kroki rozwiązywania problemów.Aby uzyskać więcej informacji na temat rozwiązywania problemów z wolno działającymi kwerendami, zobacz Lista kontrolna analizowanie wolno działającymi kwerendami.
Operacje występują w kolejności rosnącej lub malejącej kolumn klucza
Statystyki dotyczące rosnącej lub malejącej klucz kolumny, takie jak tożsamości lub rzeczywistym -czas czassygnatury kolumny, może wymagać częstsze aktualizacje statystyki niż wykonuje optymalizator kwerendy.Operacje dołączania nowych wartości do rosnącej lub malejącej kolumn.Liczba wierszy, które dodawane może być zbyt mały, aby wyzwolić aktualizacji statystyki.Jeśli statystyki nie są aktualne-do-data i kwerendy Wybierz niedawno dodane wiersze, bieżące statystyki nie będą miały Kardynalność szacunki dotyczące tych nowych wartości.Może to spowodować niedokładne Kardynalność szacunków i wydajność kwerendy powolne.
Na przykład kwerendy wybierające z najnowszych dat zamówienia sprzedaży mają niedokładne Kardynalność oszacowania Jeśli statystyki nie są aktualizowane uwzględnienie szacunki Kardynalność najnowszych dat zamówienia sprzedaży.
Po czynności konserwacji
Należy rozważyć aktualizowanie statystyki po wykonaniu procedury obsługi, które zmieniają dystrybucji danych, takich jak Obcinanie tabela lub wykonywanie zbiorczym wstawianiem duży procent wierszy.Można tego uniknąć przyszłych opóźnienia w przetwarzaniu kwerendę podczas oczekiwania kwerend statystyk automatycznych aktualizacji.
Operacje takie jak odbudowa defragmentacji lub reorganizacji indeksu nie należy zmieniać dystrybucji danych.Dlatego nie trzeba aktualizacja statystyki po wykonaniu operacji ALTER ODBUDOWAĆ indeks, ponownego INDEKSOWANIA DBCC, DBCC INDEXDEFRAG lub zmienić REORGANIZOWAĆ INDEKSU.optymalizator kwerendy aktualizuje statystyki podczas odbudowanie indeksu na tabela lub widoku ZMIENIA ODBUDOWAĆ indeks lub DBCC DBREINDEX jednak; Ta aktualizacja statystyk jest byproduct ponownego tworzenia indeksu.optymalizator kwerendy nie aktualizacja statystyki po operacji DBCC INDEXDEFRAG lub zmienić REORGANIZOWAĆ INDEKSU.
Projektowanie kwerendy Statystyka ta skutecznie
Niektóre implementacje kwerendy, takie jak zmienne lokalne i złożonych wyrażeń w predykacie kwerendy może prowadzić do planów kwerend warunkami panującymi.Aby tego uniknąć może pomóc w następujących wskazówki dotyczące projektu kwerendy dla skutecznego przy użyciu statystyk.Więcej informacji o kwerendzie predykatów, zobacz Warunek wyszukiwania (Transact-SQL).
Plany kwerend można zwiększyć stosując wskazówki dotyczące projektu kwerendy, które efektywnie korzystać do poprawy statystyk szacunków Kardynalność wyrażeń, zmienne i funkcje używane w kwerendzie predykatów.optymalizator kwerendy nie zna wartość wyrażenie, zmienna lub funkcja, on wyszukiwania na histogramie nie zna wartość, która i dlatego nie można pobrać oszacowana Kardynalność z histogramu.Zamiast tego optymalizator kwerendy opiera szacowania Kardynalność na Średnia liczba wierszy na różne wartości dla wszystkich wierszy próbki na histogramie.To prowadzi do oszacowania warunkami panującymi Kardynalność i mogą przeciążać wydajność kwerendy.
Poniższe wskazówki opisują sposób zapisu kwerendy w celu poprawy planów kwerend poprzez poprawę Kardynalność szacunków.
Poprawa szacunków Kardynalność wyrażeń
Poprawa szacunków Kardynalność w wyrażeniach, należy przestrzegać następujących zasad:
O ile to możliwe, uproszczenie wyrażenia stałe w nich.optymalizator kwerendy nie ocenić wszystkie funkcje i wyrażeń zawierających stałe przed do określania Kardynalność szacunków.Na przykład uproocić wyrażenie ABS (-100) to 100.
Wyrażenie używa wielu zmiennych, należy rozważyć utworzenie kolumna obliczana dla wyrażenia, a następnie utworzyć statystyki lub indeksu na kolumna obliczanej.Na przykład predykat kwerendy WHERE PRICE + Tax > 100 może być lepiej oszacować Kardynalność, tworząc kolumna obliczana dla wyrażenie Price + Tax.
Poprawa szacunków Kardynalność dla funkcji i zmiennych
Poprawa szacunków Kardynalność zmiennych i funkcji, należy przestrzegać następujących zasad:
Predykat kwerendy użyto zmiennej lokalnej, należy rozważyć poprawiania kwerendy, aby użyć parametru zamiast zmiennej lokalnej.Wartość zmiennej lokalnej nie jest znany, gdy optymalizator kwerendy tworzy plan wykonania kwerend.Gdy kwerenda używa parametru, optymalizator kwerendy używa szacowania Kardynalność pierwszą wartość rzeczywista parametr przekazany do procedura składowana.
Należy rozważyć użycie standardowej tabela lub tabela tymczasowa do przechowywania wyniki złożonych z wielu instrukcja funkcji oródwierszową.optymalizator kwerendy nie tworzyć statystyki dla multi-statement tabela-wycenione funkcji.Z tym podejściem optymalizator kwerendy można tworzyć statystyki w kolumnach tabela i używać ich do tworzenia lepszego planu kwerend.Więcej informacji o multi-statement tabela-ważnych funkcji, zobacz Typy funkcji.
Należy rozważyć użycie standardowej tabeli lub tabela tymczasowa jako zamiennik dla zmiennych Tabela.optymalizator kwerendy nie tworzyć statystyki dla zmiennych Tabela.Z tym podejściem optymalizator kwerendy można tworzyć statystyki w kolumnach tabela i używać ich do tworzenia lepszego planu kwerend.Przy ustalaniu, czy istnieją skutków ubocznych tabela tymczasowa lub zmiennej tabela; Tabela zmienne używane w przyczyny procedur przechowywanych mniej ponownych kompilacji procedura składowana niż tabele tymczasowe.W zależności od aplikacji, za pomocą tabela tymczasowa zamiast tabela zmiennej nie może zwiększyć wydajność.
Jeżeli procedura składowana zawiera kwerendę, która wykorzystuje parametr przekazany w, należy unikać zmieniania wartości parametru w ramach procedury przechowywanej, przed użyciem go w kwerendzie.Szacunki Kardynalność kwerendy są oparte na wartości parametru przekazany w i nie zaktualizowane wartości.Aby zapobiec zmianie wartość parametru, można napisać ponownie kwerendę, aby używać dwóch procedur przechowywanych.
Na przykład, następujące procedura składowana Sales.GetRecentSales zmienia wartość parametru @date po @date is NULL.
USE AdventureWorks2008R2; GO IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL DROP PROCEDURE Sales.GetRecentSales; GO CREATE PROCEDURE Sales.GetRecentSales (@date datetime) AS BEGIN IF @date is NULL SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader)) SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date END GOJeżeli pierwsze wywołanie procedura składowana Sales.GetRecentSales przekazuje wartość NULL @date parametru optymalizator kwerendy będzie kompilacji procedura składowana o szacowaniu Kardynalność @date = NULL , mimo że predykat kwerendy nie jest wywoływana z @date = NULL.Prognoza ta kardynalność może być znacznie różni się od liczby wierszy w wyniku kwerendy rzeczywiste.W wyniku optymalizator kwerendy może wybrać plan warunkami panującymi kwerendy.Aby temu zapobiec, można ponownie napisać procedura składowana do dwóch procedur w następujący sposób:
USE AdventureWorks2008R2; GO IF OBJECT_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL DROP PROCEDURE Sales.GetNullRecentSales; GO CREATE PROCEDURE Sales.GetNullRecentSales (@date datetime) AS BEGIN IF @date is NULL SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader)) EXEC Sales.GetNonNullRecentSales @date; END GO IF OBJECT_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL DROP PROCEDURE Sales.GetNonNullRecentSales; GO CREATE PROCEDURE Sales.GetNonNullRecentSales (@date datetime) AS BEGIN SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date END GO
Poprawa szacunków Kardynalność z kwerendy wskazówki
Poprawa szacunków Kardynalność zmiennych lokalnych, można wskazówki dotyczące optymalizacji dla lub optymalizacji dla NIEZNANYCH kwerendy ponownej kompilacji.Aby uzyskać więcej informacji, zobacz Wskazówki kwerendy (Transact-SQL).
W niektórych aplikacjach ponownej kompilacji kwerendy każdego czas wykonuje on może podjąć zbyt wiele czas.Wskazówka dotycząca optymalizacji dla kwerendy może pomóc nawet, jeśli nie używasz opcji ponownej kompilacji.Na przykład, można dodać opcję OPTYMALIZATOR dla procedura składowana Sales.GetRecentSales do określania określonej data.W następującym przykładzie dodano opcję OPTYMALIZUJ dla, aby Sales.GetRecentSales procedury.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
DROP PROCEDURE Sales.GetRecentSales;
GO
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
AS BEGIN
IF @date is NULL
SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND h.OrderDate > @date
OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))
END;
GO
Poprawa szacunków Kardynalność z prowadnicami Plan
Dla niektórych aplikacji projekt wytycznych nie może zastosować, ponieważ nie można zmienić kwerendę lub za pomocą kwerendy wskazówkę dotyczącą RECOMPILE może być przyczyną zbyt wiele ponownych kompilacji.Plan guides służy do określania innych wskazówek, takich jak PLAN SŁUŻY do sterowania zachowaniem kwerendy podczas zmian aplikacji z dostawcą aplikacji.Aby uzyskać więcej informacji na temat planów, zobacz Optymalizacja kwerend w wdrożonych aplikacji za pomocą prowadnic Plan.
Zobacz także