Using Statistics to Improve Query Performance
optymalizator kwerendy używa statystyki do tworzenia planów kwerend, które poprawiają wydajność kwerendy.W przypadku większości kwerend optymalizator kwerendy generuje już konieczne statystyki dotyczące planu kwerendy wysokiej jakości, w kilku przypadkach należy utworzyć dodatkowe statystyki lub zmodyfikowania projektu kwerendy, aby uzyskać najlepsze wyniki.
W tym temacie omówiono następujące pojęcia i wskazówki dotyczące używania skutecznie kwerendy statystyki optymalizacji:
Co to są statystyki optymalizacji kwerendy?
Korzystając z opcji Statystyka obowiązujących w całej bazy danych
Określanie, kiedy można utworzyć statystyki
Określanie, kiedy aktualizacja statystyki
Projektowanie kwerendy, które efektywnie korzystać statystyki
Aby uzyskać więcej informacji na temat tego planu kwerend i w jaki sposób odnosi się ona do kwerendy wydajności Zobacz Analyzing a Query.
Co to są statystyki optymalizacji kwerendy?
Statystyki dotyczące optymalizacji kwerendy są obiekty, które zawierają informacje statystyczne dotyczące występowania wartości w jednej lub kilku kolumn w tabela lub widok indeksowany.optymalizator kwerendy używa tych statystyk w celu oszacowania Relacjalub liczbę wierszy, w wyniku kwerendy.Te liczebność oszacowania włączyć optymalizator kwerendy do tworzenia planu kwerendy wysokiej jakości.Na przykład optymalizator kwerendy może używać Kardynalność oszacowań, aby wybrać indeksu wyszukiwania operator zamiast operatora bardziej obciąża indeksu skanowania i w ten sposób zwiększyć wydajność kwerendy.
Każdy obiekt statystyki jest tworzony na liście jednej lub więcej tabel kolumna s i zawiera histogramu, wyświetlanie rozkład wartości w pierwszym kolumna.Obiekty danych statystycznych na wiele kolumn również przechowywać informacje statystyczne o korelację wartości w obu kolumnach.Te statystyki korelacji lub gęstość zapisu, są obliczane na podstawie liczby wierszy różne wartości kolumna.Aby uzyskać więcej informacji na temat statystyki obiektów Zobacz DBCC SHOW_STATISTICS (Transact-SQL).
Statystyka filtrowane
Określona metoda musi być statyczna metoda klasy.Filtrowanych danych statystycznych Użyj predykat filtru, aby wybrać podzbiór danych, która nie znajduje się w statystykach.Dobrze filtrowanych danych statystycznych może poprawić plan wykonania kwerend w porównaniu z całości tabela statystyk.Aby uzyskać więcej informacji na temat predykat filtru zobacz CREATE STATISTICS (Transact-SQL). Aby uzyskać więcej informacji o tym, kiedy umożliwia tworzenie statystyk filtrowane zobacz Określanie, kiedy można utworzyć statystyki sekcję w tym temacie.
Korzystając z opcji Statystyka obowiązujących w całej bazy danych
Ważne jest zrozumienie, jakie należy wykonać następujące opcje statystyki dla całej bazy danych i sprawdzić ustawienia 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, jak to konieczne, poprawić Kardynalność szacowania dla planu kwerend.Te statystyki jednokolumnową są tworzone na kolumna, które nie zostały jeszcze histogramu 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ę sys.stats widoki wykazu i sys.stats_columns do zwracania nazwy obiektu bazy danych, nazwę kolumna oraz nazwa statystyki dla wszystkich kolumn, które mają jedną kolumną statystyki.Kiedy optymalizator kwerendy tworzy statystyk na pojedynczej kolumny z użyciem opcji AUTO_CREATE_STATISTICS, statystyki rozpoczyna się nazwa _WA.
Opcja AUTO_CREATE_STATISTICS nie określa, czy statystyki uzyskać utworzony dla indeksów.Ta opcja nie generuje również filtrowanych danych statystycznych.Dotyczy to ściśle jednokolumnową statystyki dla pełnej tabela.
Za pomocą opcji AUTO_UPDATE_STATISTICS
Podczas automatycznego aktualizacja statystyki opcja AUTO_UPDATE_STATISTICS, jest włączona, optymalizator kwerendy decyduje o tym, kiedy statystyki mogą być nieaktualne i następnie aktualizuje, gdy są one używane przez kwerendę.Statystyki stają się poza - o-data po wstawić, maksymalnie data, usuwanie lub scalić operacje zmiany rozkładu danych tabela lub widok indeksowany.optymalizator kwerendy określa, kiedy statystyki może być poza - z-data zliczając liczbę modyfikacji danych od czasu ostatniego statystyki data i porównanie liczby zmian progu.Próg zależy od liczby wierszy w tabela lub widok indeksowany.
Kwerendy Optymalizator kontroli dla poza - o-data statystyki przed kompilowania kwerendy i przed wykonaniem buforowanego planu kwerend.Przed kompilowania kwerendy, optymalizator kwerendy korzysta z kolumn tabel, i widoki indeksowane w predykacie kwerendy, aby określić, które statystyki może być mało - z-data.Przed wykonaniem buforowanego planu kwerend, Database Engine sprawdza, czy plan kwerend odwołuje - do-data statystyki.
Opcja AUTO_UPDATE_STATISTICS odnosi się do obiektów statystyki dla indeksów, jedno kolumn predykaty kwerendy i statystyki utworzone za pomocą TWORZENIE STATYSTYK instrukcja.Opcja ta dotyczy również filtrowanych danych statystycznych.
Ustawianie AUTO_CREATE_STATISTICS i opcje AUTO_UPDATE_STATISTICS na
Automatyczne tworzenie statystyk, opcja AUTO_CREATE_STATISTICS, a opcja automatycznej aktualizacja statystyki, AUTO_UPDATE_STATISTICS, są domyślnie i firma Microsoft zaleca, aby większość użytkowników baz danych przy użyciu domyślnego.Służy do wyświetlania bieżącej wartości tych opcji dla wszystkich baz danych użytkowników umożliwia następującą instrukcję WYBIERAJĄCĄ:
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
W poniższym przykładzie nadano AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS on dla bazy danych AdventureWorks:
USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks
SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks
SET AUTO_UPDATE_STATISTICS ON;
GO
Aby uzyskać więcej informacji na temat sposobu zestaw Zobacz Opcje te statystyki ALTER DATABASE SET Options (Transact-SQL).
Wyłączanie i Re-enabling AUTO_UPDATE_STATISTICS niektóre statystyki
Jeśli AUTO_UPDATE_STATISTICS jest włączona, można zmienić zachowanie aktualizacji statystyk dla całej bazy danych i poczet aktualizacji automatycznych statystyk dla pojedynczej tabela, indeks lub kolumna, jak jest to wymagane przez daną aplikację.Jeśli AUTO_UPDATE_STATISTICS jest włączona, można wyłączyć i ponownie włączyć Aktualizacje automatyczne statystyki dla tabela, indeks lub kolumna w następujący sposób:
Użycie sp_autostats systemu procedura przechowywana.Można to wyłączyć lub ponownie włączyć aktualizacji statystyk dla tabela lub indeksu.
Określ opcję NORECOMPUTE z AKTUALIZACJA STATYSTYK instrukcja.Aby ponownie włączyć aktualizacja statystyki, należy ponownie uruchomić aktualizacja statystyki bez opcji NORECOMPUTE.
Określ opcję NORECOMPUTE w instrukcji CREATE STATISTICS.Aby ponownie włączyć aktualizacje statystyk, usuń statystyki za pomocą instrukcji DROP STATISTICS, a następnie wykonaj instrukcję CREATE STATISTICS bez opcji NORECOMPUTE.
Określ opcję STATISTICS_NORECOMPUTE z TWORZENIE INDEKSU instrukcja.Aby ponownie włączyć aktualizacji statystyk, można uruchomić ZMIANA INDEKSU z STATISTICS_NORECOMPUTE = OFF.
Jeśli AUTO_UPDATE_STATISTICS jest wyłączona, nie zestaw automatyczne aktualizacje na dla pojedynczej tabela, kolumn lub indeksu.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 będzie wykonywana aktualizacji statystyki.
Kiedy należy używać aktualizacji statystyki synchroniczny lub asynchroniczny
Statystyki aktualizacje mogą być synchroniczne (ustawienie domyślne) lub asynchroniczna.W przypadku aktualizacji statystyk synchroniczne zawsze kompilowania kwerendy i wykonać aktualnych danych statystycznych; gdy statystyki są nieaktualne, optymalizator kwerendy czeka na zaktualizowaną statystykę przed kompilowania i wykonaniem kwerendy.Z updatas statystyki asynchronicznych kwerend skompilować istniejące dane statystyczne nawet wtedy, gdy istniejące statystyk znajdują się - o-data; optymalizator kwerendy można wybrać plan kwerend suboptimal, jeśli statystyk znajdują się - o-data kiedy kompiluje kwerendy.Kwerendy skompilować po aktualizacji asynchroniczny został zakończony, będzie korzystać z zaktualizowanych statystyk.
Opcje aktualizacji statystyk asynchronicznego dla 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, a 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 utworzone za pomocą TWORZENIE STATYSTYK instrukcja.
Za pomocą następującego polecenia do wyświetlenia opcji asynchronicznego automatycznej aktualizacji w przypadku wszystkich baz danych:
SELECT name AS "Name",
is_auto_update_stats_async_on AS "Asynchronous Update"
FROM sys.databases;
GO
Należy rozważyć zastosowanie synchroniczne statystyki dla następujących scenariuszy:
- Można wykonywać operacje zmiany rozkładu danych, takich jak Obcinanie tabela lub wykonywanie aktualizacji zbiorczej z duży procent wierszy.Jeśli użytkownik nie wykona się data statystyki po zakończeniu operaton, przy użyciu statystyk synchroniczne zapewni statystyk uruchomionych - 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ę, podobne kwerendy lub plany podobne kwerendy buforowana.Twoje czasy odpowiedzi kwerendy może być bardziej przewidywalny z aktualizacjami asynchronicznego statystyki niż w przypadku aktualizacji statystyk synchronicznych, ponieważ optymalizator kwerendy można wykonywać kwerendy przychodzące bez oczekiwania na aktualnych danych statystycznych.Pozwala to uniknąć opóźnienie niektórych kwerend, a innych nie.Aby uzyskać więcej informacji na temat podobnych kwerendy wyszukiwania zobacz Finding and Tuning Similar Queries by Using Query and Query Plan Hashes.
Aplikacja napotkał spowodowany przez co najmniej jedną kwerendę trwa oczekiwanie na zaktualizowanych statystyk ostro czas żądania klient.W niektórych przypadkach oczekiwania dla statystyk synchroniczne może spowodować, że aplikacje z agresywny ostro czas kończy się niepowodzeniem.
Określanie, kiedy można utworzyć statystyki
optymalizator kwerendy utworzy już statystyk w następujący sposób:
optymalizator kwerendy tworzy statystyki dla indeksów w tabelach lub widokach, podczas tworzenia indeksu.Te statystyki są tworzone w kolumnach kluczy indeksu.Jeżeli indeks jest filtrowany indeksu, optymalizator kwerendy tworzy filtrowanych danych statystycznych na tej samej podzbiór wierszy określonego dla indeksu filtrowane.Aby uzyskać więcej informacji na temat indeksów filtrowane zobacz Wskazówki dotyczące projektowania indeks filtrowane i CREATE INDEX (języka Transact-SQL).
optymalizator kwerendy tworzy statystyki dla pojedynczej kolumny w predykaty kwerendy przy włączonej AUTO_CREATE_STATISTICS.
W przypadku większości kwerend te dwie metody tworzenia statystyk pewność, plan wysokiej jakości kwerendy; w kilku przypadkach można poprawić, tworząc dodatkowe statystyki z planów kwerend TWORZENIE STATYSTYK instrukcja.Te dodatkowe statystyki może przechwytywać statystycznych korelacji, że optymalizator kwerendy nie konta na podczas tworzenia statystyk dla indeksów lub pojedynczej kolumny.Aplikacja może zawierać dodatkowe korelacji statystyczne danych tabela, które, jeśli obliczenia w obiekcie statystyk można włączyć optymalizator kwerendy w celu poprawienia planów kwerend.Na przykład filtrowane dane statystyczne dotyczące podzbiór wierszy, dane lub wielokolumnowego statystyki na podstawie kwerendy predykatu kolumn może zwiększyć planu kwerend.
Podczas tworzenia statystyk w instrukcja CREATE STATISTICS, zaleca się przechowywanie opcji AUTO_CREATE_STATISTICS taki sposób, że optymalizator kwerendy w dalszym ciągu regularnie tworzyć jedno-kolumna kolumna s predykat statystyki dla kwerendy.Aby uzyskać więcej informacji na temat predykaty kwerendy Zobacz Search Condition (Transact-SQL).
Należy wziąć pod uwagę tworzenia statystyk w instrukcja CREATE STATISTICS, gdy stosuje się jedną z następujących czynności:
The Database Engine Tuning Advisor suggests creating statistics.
Predykat kwerendy zawiera wiele skorelowanej kolumn, które nie znajdują się już w tym samym indeksem.
Kwerendy wybiera się z podzbiór danych.
Kwerenda ma brakujące dane statystyczne.
Aparat bazy danych, dostosowywanie klasyfikatora sugeruje tworzenia statystyk
The Database Engine Tuning Advisor is a a narzędzie that analyzes the performance effects of workloads on one or more databases. Zawiera zalecenia dotyczące zwiększania wydajności (na przykład sugerowanie indeksy, aby utworzyć) i może sugerować przy użyciu CREATE STATISTICS umożliwia tworzenie statystyk optymalizacji kwerendy.Należy postępować zgodnie z tym zaleceniem.Aby uzyskać więcej informacji na temat Database Engine Dostrajanie klasyfikatora, zobacz Tuning the Physical Database Design.
Kwerendy predykatu zawiera wiele powiązane kolumny
Predykat kwerendy zawiera wiele kolumn, których kolumna wzajemne związki i zależności, statystyki dla wielu kolumn może poprawić planu kwerend.Statystyki korelacji między kolumnami, o nazwie zawierają statystyki dla wielu kolumn gęstość zapisu, które nie są dostępne w statystyce jedną kolumną.Gęstości może zwiększyć Kardynalność oszacowań, gdy wyniki kwerendy zależy od danych relacje między wiele kolumn.
Jeśli kolumny są już w ten sam indeks, statystyki wielokolumnowego obiekt już istnieje i nie jest konieczne ręcznie utworzyć.Jeżeli kolumny nie są już w ten sam indeks, można utworzyć statystyki wielokolumnowego przy tworzeniu indeksu w kolumnach lub za pomocą instrukcja CREATE STATISTICS.Wymaga więcej zasobów systemowych, aby zachować indeks niż obiekt statystyki.Jeśli aplikacja nie wymaga wielokolumnowego indeksu, można można economize na zasoby systemowe przez utworzenie obiektu statystyki bez tworzenia indeksu.
Podczas tworzenia statystyk w oparciu o wiele kolumn, kolejność kolumn w definicji obiektu statystyki wpływa na skuteczność gęstości dokonywania Kardynalność oszacowań.Obiekt statystyki przechowuje gęstości dla każdego prefiksu kolumn klucz w definicji obiektu statystyki.Aby uzyskać więcej informacji na temat gęstości zobacz DBCC SHOW_STATISTICS (Transact-SQL).
Do tworzenia gęstości, które są przydatne do oszacowania relacja, musi być zgodna kolumn w predykacie kwerendy jedną prefiksów dla kolumn w definicji obiektu statystyki.Na przykład następujące tworzy obiekt wielokolumnowego statystyk w kolumnach LastName, MiddleName, a FirstName.
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.stats
WHERE name = 'LastFirst'
AND object_ID = OBJECT_ID ('Person.Contact'))
DROP STATISTICS Person.Contact.LastFirst;
GO
CREATE STATISTICS LastFirst ON Person.Contact (LastName, MiddleName, FirstName);
GO
W tym przykładzie obiekt statystyk LastFirst ma gęstości dla prefiksów następujące kolumna: (LastName), (LastName, MiddleName), a ()LastName, MiddleName, FirstName). Gęstość jest niedostępna)LastName, FirstName). Jeśli w kwerendzie użyto LastName i FirstName bez użycia MiddleName, gęstość jest niedostępna dla Kardynalność oszacowań.
Kwerendy wybiera z podzbiór danych
Gdy optymalizator kwerendy tworzy statystyki dla pojedynczej kolumny i indeksów, tworzy statystyki dla wartości wszystkich wierszy.Kwerendy Wybierz podzbiór wierszy, a to podzbiór wierszy ma dystrybucji unikatowe, filtrowane statystyk można poprawić planów kwerend.Statystyki filtrowanego można utworzyć za pomocą instrukcja CREATE STATISTICS z klauzula WHERE, aby zdefiniować wyrażenie predykatu filtru.
Na przykład przy użyciu AdventureWorks, każdego produktu w tabela Production.Product należy do jednej z czterech kategorii w tabela Production.ProductCategory: Rowery, składniki, odzież i akcesoria. Każdej z kategorii ma dystrybucji danych wagi: zakres wag roweru z 13.77 30.0 zakres wag składnika z 2.12 1050.00 niektóre wartości NULL wag ubrania są wagi wszystkich wartości NULL i akcesoriów są również mieć wartości NULL.
Za pomocą Bikes (Rowery), na przykład, filtrowane dane statystyczne dotyczące wszystkich wag roweru zapewnia dokładniejsze dane statystyczne do optymalizator kwerendy i może zwiększyć jakość planu kwerendy, w porównaniu z tabela pełne statystyki lub nieistniejącej statystyki na podstawie kolumna waga.kolumna wagi rowerów jest odpowiednie do pełnienia funkcji filtrowanych danych statystycznych, ale niekoniecznie odpowiednie do pełnienia funkcji filtrowane indeksu, jeśli jest stosunkowo niewielka liczba wyszukiwań wagi.Wydajność uzyskać dla wyszukiwania, które zawiera filtrowane indeksu nie może być przewyższają dodatkowe konserwacji i kosztu magazynowania do dodawania indeksu filtrowanych w bazie danych.
Poniższa instrukcja tworzy BikeWeights filtrowane Statystyka wszystkie podkategorie na rowery. Filtrowane wyrażenie predykatu definiuje rowery przez wyliczanie wszystkich podkategorii rowerów z porównania Production.ProductSubcategoryID IN (1,2,3). Predykat nie można użyć nazwy kategorii Bikes (Rowery), ponieważ jest on przechowywany w tabela Production.ProductCategory, a wszystkie kolumny w wyrażeniu filtru musi być w tej samej tabela.
optymalizator kwerendy, można użyć BikeWeights filtrowane statystyk w celu poprawienia plan kwerend dla następującej kwerendy, która powoduje zaznaczenie wszystkich rowerów, które zważ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 jest brak statystyki
Jeśli błąd lub inne zdarzenie uniemożliwia optymalizator kwerendy tworzenia statystyk, optymalizator kwerendy tworzy plan kwerend bez użycia statystyki.optymalizator kwerendy oznacza statystyki, jak brak i podejmuje próbę ponownego generowania statystyk następnego czas kwerenda jest wykonywana.
Brak statystyki są oznaczane 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 zobaczDisplaying Graphical Execution Plans (SQL Server Management Studio).Ponadto, monitorowanie Brak kolumna Statistics klasa zdarzenia przy użyciu SQL Server Profiler Wskazuje, kiedy nie ma danych statystycznych. Aby uzyskać więcej informacji zobaczBłędy i Kategoria zdarzenie ostrzeżeń (aparat bazy danych).
Jeśli brakuje statystyk, należy wykonać następujące czynności:
Sprawdź, czy AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS.
Sprawdź, czy baza danych nie jest tylko do odczytu.Jeśli baza danych jest tylko do odczytu, optymalizator kwerendy nie może zapisać danych statystycznych.
Za pomocą instrukcja CREATE STATISTICS, aby utworzyć brakujące dane statystyczne.
Określanie, kiedy aktualizacja statystyki
optymalizator kwerendy określa, kiedy statystyki może być poza - z-data i następnie updatas je, gdy są one wymagane w przypadku kwerendy.W niektórych przypadkach można poprawić planu kwerend i w związku z tym zwiększyć wydajność kwerendy przez aktualizowanie statystyk więcej często nie występują wtedy, gdy AUTO_UPDATE_STATISTICS znajduje się.Statystyki można zaktualizować za pomocą instrukcja aktualizacja statystyki lub sp_updatestats procedura przechowywana.
Aktualizowanie statystyk gwarantuje, że kwerendy będą kompilowane za pomocą aktualnych statystyk.Jednak aktualizowanie statystyk powoduje konieczność ponownego kompilowania kwerend.Zalecane jest, aby nie aktualizować zbyt często statystyk ze względu na kompromis dotyczący wydajności między ulepszaniem planów wykonania, a czasem potrzebnym na ponowne skompilowanie kwerend.Szczegóły kompromisów dotyczących wydajności są zależne od aplikacji.
W przypadku aktualizowania statystyk za pomocą instrukcji UPDATE STATISTICS lub procedury składowanej sp_updatestats zalecane jest zachowanie dla parametru AUTO_UPDATE_STATISTICS wartości ON, która powoduje, że optymalizator kwerend będzie rutynowo aktualizował statystyki.Aby uzyskać więcej informacji dotyczących sposobu aktualizowania statystyk w kolumnie, indeksie, tabeli lub indeksowanym widoku, zobacz temat UPDATE STATISTICS (Transact-SQL).Aby uzyskać informacje dotyczące sposobu aktualizowania statystyk dla wszystkich zdefiniowanych przez użytkownika i wewnętrznych tabel w bazie danych, zobacz opis procedury składowanej sp_updatestats (języka Transact-SQL).Na przykład poniższe polecenie wywołuje procedurę składowaną sp_updatestats w celu zaktualizowania wszystkich statystyk dla bazy danych.
EXEC sp_updatestats
Aby ustalić, kiedy statystyki były aktualizowane po raz ostatni, użyj funkcji STATS_DATE.
Należy wziąć pod uwagę aktualizacji statystyki dla następujących warunków:
Czas wykonania kwerendy są powolne.
Operacje wstawiania występują w kolejności rosnącej lub malejącej kolumny klucz.
Po operacji konserwacji.
Godziny wykonanie kwerendy są powolne
Jeżeli czas odpowiedzi kwerendy są wolne lub nieprzewidywalne, upewnij się, że kwerendy mają - do-data statystyki 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 Checklist for Analyzing Slow-Running Queries.
Operacje wstawiania występuje na rosnąco lub malejąco kolumny klucz
Statystyki dotyczące rosnący lub malejący kolumnach kluczy, taki jak IDENTITY lub kolumny sygnatury czasowej w czasie rzeczywistym, może wymagać częstsze aktualizacje statystyki nie wykonuje optymalizator kwerendy.Operacje wstawiania nowych wartości należy dołączyć do sortowania rosnącego lub malejącego kolumn.Liczba wierszy dodawany może być zbyt mały, aby wyzwolić aktualizacji statystyki.Statystyki nie są aktualne i kwerendy Wybierz najbardziej ostatnio dodanych wierszy, bieżące statystyki nie będzie zawierał Kardynalność szacowania dla tych nowych wartości.Może to spowodować niedokładne Kardynalność oszacowania i wydajność kwerendy powolne.
Na przykład kwerendy wybierające z ostatniej daty zamówienia sprzedaży mają oszacowań niedokładne Kardynalność Jeśli statystyki zostaną zaktualizowane tak, aby uwzględnić szacowania Kardynalność najnowszych dat zamówienia sprzedaży.
Po operacji konserwacji
Należy rozważyć możliwość aktualizowania statystyk po wykonaniu procedury obsługi, który zmienia rozmieszczenie danych, takich jak Obcinanie tabela lub wykonywanie wstawiania zbiorczego z duży procent wierszy.Uniknąć przyszłych opóźnień podczas przetwarzania kwerendy kwerendy oczekiwać statystyk automatycznych aktualizacji.
Operacje takie jak przebudowy, defragmentowanie lub zmiana indeksu nie ulegają zmianie rozmieszczenia danych.Dlatego też nie ma potrzeby aktualizacja statystyki po wykonaniu operacji ALTER ODBUDOWAĆ indeks, DBCC INDEKSOWANIA, INDEXDEFRAG DBCC lub ALTER REORGANIZOWAĆ indeks.optymalizator kwerendy aktualizuje statystyki po odbudowanie indeksu na tabela ani widoku zawierającego ALTER ODBUDOWAĆ indeks lub DBCC DBREINDEX, jednak; jest to aktualizacja statystyki byproduct o ponowne tworzenie indeksu.optymalizator kwerendy nie aktualizuje statystyki po operacji INDEXDEFRAG DBCC lub ALTER REORGANIZOWAĆ indeks.
Projektowanie kwerendy skutecznie to statystyki użycia
Niektóre implementacje kwerendy, takie jak zmiennych lokalnych i złożone wyrażenia w predykacie kwerendy może prowadzić do planów kwerendy suboptimal.Aby tego uniknąć może pomóc w następujących zasad projektowania kwerendy o efektywnym używaniem statystyki.Aby uzyskać więcej informacji na temat predykaty kwerendy Zobacz Search Condition (Transact-SQL).
Plany kwerend można zwiększyć, stosując wskazówki projektowania kwerendy, które efektywnie korzystać statystyk ułatwiających liczebność oszacowania wyrażeń, zmiennych i funkcje używane w kwerendzie predykatów.Gdy optymalizator kwerendy nie zna wartość wyrażenie, zmienną lub funkcja, nie będzie wiadomo, która wartość do wyszukiwania w histogramu i w związku z tym nie można pobrać Najlepsze oszacowanie relacja z histogramu.Zamiast tego optymalizator kwerendy określa szacowania Kardynalność na średnią liczbę wierszy na różne wartości dla wszystkich wierszy próbkowany histogramu.Ten prowadzi do oszacowania suboptimal liczebność i mogą wówczas wydajność kwerendy.
Poniższe wskazówki zawierają opis zapisu kwerendy w celu poprawienia planów kwerend udoskonalając Kardynalność oszacowań.
Zwiększanie wartości szacunkowych Kardynalność w wyrażeniach
Aby poprawić Kardynalność szacowania dla wyrażenia, należy postępować zgodnie z poniższymi wskazówkami:
O ile to możliwe, uproszczenie wyrażenia zawierające stałe w nich.optymalizator kwerendy nie zwraca wszystkich funkcji i wyrażeń zawierających stałe przed do określania Kardynalność oszacowań.Na przykład uprościć 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 w kolumnie obliczanej.Na przykład predykat kwerendy WHERE PRICE + Tax > 100 Po utworzeniu kolumna obliczana w wyrażeniu mogą mieć lepsze oszacowanie Kardynalność Price + Tax.
Zwiększanie Kardynalność oszacowań dla funkcji i zmiennych
Aby poprawić szacowania Kardynalność dla zmiennych i funkcji, należy postępować zgodnie z poniższymi wskazówkami:
Jeżeli predykat kwerendy użyto zmiennej lokalnej, należy rozważyć możliwość poprawiania kwerendy, aby użyć parametru zamiast zmiennej lokalnej.Wartość zmienna lokalna nie jest znany, gdy optymalizator kwerendy tworzy plan wykonania kwerend.Gdy kwerenda używa parametru, optymalizator kwerendy używa szacowania jest pierwszą wartość rzeczywista parametru jest przekazywany do procedura przechowywana.
Należy wziąć pod uwagę przy użyciu standardowej tabeli lub tabela tymczasowa do przechowywania wyniki multi-instrukcja wycenione tabelę funkcji.optymalizator kwerendy nie powoduje utworzenia statystyki multi-instrukcja wycenione tabela funkcji.Z tej metody optymalizator kwerendy można utworzyć statystyk w kolumnach tabela i używać ich do tworzenia lepszego planu kwerend.Aby uzyskać więcej informacji na temat funkcji multi-instrukcja wycenione tabela zobacz Types of Functions.
Należy wziąć pod uwagę przy użyciu standardowej tabeli lub tymczasowej tabeli jako zamiennik dla zmiennych Tabela.optymalizator kwerendy nie powoduje utworzenia statystyki dla zmiennych Tabela.Z tej metody optymalizator kwerendy można utworzyć statystyk w kolumnach tabela i używać ich do tworzenia lepszego planu kwerend.W określaniu, czy należy użyć tabela tymczasowa lub zmiennej tabeli nie ma tradeoffs; zmienne Tabela używane w procedurach przechowywanych powodują mniej ponownych kompilacji procedura przechowywana niż tabele tymczasowe.Zależnie od aplikacji przy użyciu tabela tymczasowa zamiast zmiennej tabeli nie może zwiększyć wydajność.
Jeśli procedura przechowywana zawiera kwerendę, która wykorzystuje parametr przekazany w, należy unikać zmieniania wartości parametrów w procedurze przechowywanej przed użyciem go w kwerendzie.Oszacowania Kardynalność kwerendy opierają się na wartość 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 przechowywana Sales.GetRecentSales Zmienia wartość parametru @date Kiedy @date is NULL.
USE AdventureWorks; 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 przechowywana Sales.GetRecentSales przekazuje wartość NULL @date parametr, optymalizator kwerendy będzie kompilacji procedura przechowywana o szacowaniu Kardynalność @date = NULL Mimo że predykat kwerendy nie jest wywoływana z @date = NULL. To oszacowanie relacja może być znacznie różni się od liczby wierszy w wyniku kwerendy rzeczywiste.W rezultacie optymalizator kwerendy może wybrać plan suboptimal kwerendy.Aby tego uniknąć, można napisać ponownie procedura przechowywana w dwie procedury w następujący sposób:
USE AdventureWorks; 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
Zwiększanie Kardynalność szacowań z podpowiedzi kwerendy
W celu poprawy Kardynalność szacowania dla zmiennych lokalnych, można użyć wskazówki dotyczące optymalizacji dla lub OPTYMALIZOWANIE dla nieznany kwerendy z RECOMPILE.Aby uzyskać więcej informacji zobaczQuery Hints (Transact-SQL).
W niektórych aplikacjach ponownej kompilacji przy każdym jego wykonuje kwerendę może zająć zbyt dużo czas.Do OPTYMALIZOWANIA do kwerendy wskazówkę dotyczącą kwerendy może pomóc, nawet jeśli nie korzystasz z opcji RECOMPILE.Na przykład można dodać jedną z opcji optymalizacji dla do przechowywanej procedury Sales.GetRecentSales, aby określić data dezaktywacji.W następującym przykładzie dodano opcji OPTYMALIZUJ dla procedury Sales.GetRecentSales.
USE AdventureWorks;
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
Zwiększanie Kardynalność oszacowań z prowadnicami plan
Niektóre aplikacje projekt wskazówki nie mogą być stosowane, ponieważ nie można zmienić kwerendę lub użycie wskazówki dotyczącej kwerendy RECOMPILE może być powodować, że zbyt wiele rekompilacji.Plan prowadnic umożliwia określić inne wskazówki, takie jak USE PLAN w celu sterowania zachowaniem kwerendy podczas badania zmian aplikacji z dostawcą aplikacji.Aby uzyskać więcej informacji na temat prowadnic planu zobacz Optimizing Queries in Deployed Applications by Using Plan Guides.
Historia zmian
Microsoft Learning |
|---|
Rewrote cały temat zawiera aktualne informacje o efektywnym używaniem statystyki. |
See Also