Statystyka

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL database w usłudze Microsoft Fabric

Optymalizator zapytań używa statystyk do tworzenia planów zapytań, które zwiększają wydajność zapytań. W przypadku większości zapytań optymalizator zapytań generuje już niezbędne statystyki dla planu zapytania wysokiej jakości; w niektórych przypadkach należy utworzyć dodatkowe statystyki lub zmodyfikować projekt zapytania, aby uzyskać najlepsze wyniki. W tym artykule omówiono pojęcia dotyczące statystyk i przedstawiono wskazówki dotyczące efektywnego używania statystyk optymalizacji zapytań.

Składniki i pojęcia

Statystyka

Statystyki optymalizacji zapytań to binarne duże obiekty (BLOB), które zawierają informacje statystyczne dotyczące rozkładu wartości w co najmniej jednej kolumnie tabeli lub widoku indeksowanego. Optymalizator zapytań używa tych statystyk do oszacowania kardynalności lub liczby wierszy w wyniku zapytania. Te szacunki kardynalności umożliwiają optymalizatorowi zapytań utworzenie planu zapytań wysokiej jakości. Na przykład w zależności od predykatów optymalizator zapytań może użyć oszacowań kardynalności, aby wybrać operator wyszukiwania indeksu zamiast operatora skanowania indeksu intensywniejszego dla zasobów, jeśli to poprawi wydajność zapytań.

Każdy obiekt statystyk jest tworzony na liście co najmniej jednej kolumny tabeli i zawiera histogram przedstawiający rozkład wartości w pierwszej kolumnie. Obiekty statystyk w wielu kolumnach przechowują również informacje statystyczne dotyczące korelacji wartości między kolumnami. Te statystyki korelacji lub gęstości pochodzą z liczby odrębnych wierszy wartości kolumn.

Histogram

Histogram mierzy częstotliwość występowania dla każdej odrębnej wartości w zestawie danych. Optymalizator zapytań oblicza histogram wartości kolumn w pierwszej kolumnie kluczowej obiektu statystyk, wybierając wartości kolumn przez próbkując wiersze statystycznie lub wykonując pełne skanowanie wszystkich wierszy w tabeli lub widoku. Jeśli histogram jest tworzony na podstawie próbkowanego zestawu wierszy, przechowywane sumy dla liczby wierszy i liczby unikatowych wartości są szacowane i nie muszą być liczbami całkowitymi.

Note

Histogramy w programie SQL Server są tworzone tylko dla jednej kolumny — pierwszej kolumny w zestawie kluczowych kolumn obiektu statystyk.

Aby utworzyć histogram, Optymalizator zapytań sortuje wartości kolumn, oblicza liczbę wartości, które pasują do każdej odrębnej wartości kolumny, a następnie agreguje wartości kolumn w maksymalnie 200 ciągłych kroków histogramu. Każdy krok histogramu obejmuje zakres wartości kolumn, po którym następuje wartość graniczna kolumny. Zakres zawiera wszystkie możliwe wartości kolumn między wartościami granic, z wyłączeniem samych wartości granic. Najniższa z posortowanych wartości kolumn to górna wartość granicy pierwszego kroku histogramu.

Bardziej szczegółowo program SQL Server tworzy histogram na podstawie posortowanego zestawu wartości kolumn w trzech krokach:

• Inicjowanie histogramu: W pierwszym kroku jest przetwarzana sekwencja wartości rozpoczynających się od początku posortowanego zestawu, a do 200 wartości range_high_key, equal_rows, range_rows i distinct_range_rows są zbierane (range_rows i distinct_range_rows są zawsze zerowe w tym kroku). Pierwszy krok kończy się po wyczerpaniu wszystkich danych wejściowych lub znalezieniu 200 wartości.
• Skanowanie za pomocą scalania zasobników: każda dodatkowa wartość z kolumny wiodącej klucza statystyk jest przetwarzana w drugim kroku w kolejności sortowania; każda kolejna wartość jest dodawana do ostatniego zakresu lub tworzony jest nowy zakres na końcu (jest to możliwe, ponieważ wartości wejściowe są sortowane). Jeśli zostanie utworzony nowy zakres, jedna para istniejących, sąsiednich zakresów zostaje połączona w jeden zakres. Ta para zakresów jest wybierana w celu zminimalizowania utraty informacji. Ta metoda używa algorytmu maksymalnej różnicy , aby zminimalizować liczbę kroków w histogramie, jednocześnie maksymalizując różnicę między wartościami granic. Liczba kroków po zwinięciu zakresów pozostaje na wartości 200 przez cały ten etap.
• Konsolidacja histogramu: w trzecim kroku można zwinąć więcej zakresów, jeśli znaczna ilość informacji nie zostanie utracona. Liczba kroków histogramu może być mniejsza niż liczba unikatowych wartości, nawet w przypadku kolumn z mniej niż 200 punktami granic. W związku z tym nawet jeśli kolumna ma więcej niż 200 unikatowych wartości, histogram może zawierać mniej niż 200 kroków. W przypadku kolumny składającej się tylko z unikatowych wartości skonsolidowany histogram ma co najmniej trzy kroki.

Note

Jeśli histogram został utworzony przy użyciu próbki, a nie pełnego skanowania, wartości equal_rows, range_rows i distinct_range_rows i average_range_rows są szacowane, dlatego nie muszą być całkowite.

Na poniższym diagramie przedstawiono histogram z sześcioma krokami. Obszar po lewej stronie pierwszej górnej wartości granicy jest pierwszym krokiem.

Dla każdego kroku histogramu w poprzednim przykładzie:

• Linia pogrubiona reprezentuje górną wartość granicy (range_high_key) i liczbę wystąpień (equal_rows)

• Obszar po lewej stronie od range_high_key reprezentuje zakres wartości kolumn i średnią liczbę wystąpień każdej z tych wartości (average_range_rows). Dla pierwszego etapu histogramu, `average_range_rows` jest zawsze 0.

• Linie kropkowane reprezentują próbkowane wartości używane do szacowania całkowitej liczby odrębnych wartości w zakresie (distinct_range_rows) i łącznej liczby wartości w zakresie (range_rows). Optymalizator zapytań używa range_rows i distinct_range_rows do obliczania average_range_rows i nie przechowuje przykładowych wartości.

Wektor gęstości

Gęstość to informacje o liczbie duplikatów w danej kolumnie lub kombinacji kolumn i są obliczane jako 1/(liczba unikatowych wartości). Optymalizator zapytań używa gęstości w celu zwiększenia oszacowań kardynalności zapytań, które zwracają wiele kolumn z tej samej tabeli lub widoku indeksowanego. W miarę spadku gęstości wybór wartości zwiększa się. Na przykład w tabeli reprezentującej samochody wiele samochodów ma tego samego producenta, ale każdy samochód ma unikatowy numer identyfikacyjny pojazdu (VIN). Indeks dotyczący VIN jest bardziej selektywny niż indeks dotyczący producenta, ponieważ VIN ma niższą gęstość niż producent.

Note

Częstotliwość to informacje o wystąpieniu każdej odrębnej wartości w pierwszej kolumnie klucza obiektu statystyki i są obliczane jako row count * density. Maksymalna częstotliwość wynosząca 1 może być znaleziona w kolumnach z unikalnymi wartościami.

Wektor gęstości zawiera jedną gęstość dla każdego prefiksu kolumn w obiekcie statystyk. Jeśli na przykład obiekt statystyki zawiera kolumny kluczy CustomerId, ItemId i Price, gęstość jest obliczana dla każdego z następujących prefiksów kolumny.

Prefiks kolumny	Gęstość obliczona na
(CustomerId)	Wiersze z pasującymi wartościami dla CustomerId
(CustomerId, ItemId)	Wiersze z pasującymi wartościami dla CustomerId i ItemId
(CustomerId, ItemId, Price)	Wiersze z pasującymi wartościami dla CustomerId, ItemIdi Price

Przefiltrowane statystyki

Przefiltrowane statystyki mogą zwiększyć wydajność zapytań dla zapytań wybranych z dobrze zdefiniowanych podzestawów danych. Przefiltrowane statystyki używają predykatu filtru, aby wybrać podzestaw danych uwzględnionych w statystykach. Dobrze zaprojektowane przefiltrowane statystyki mogą poprawić plan wykonywania zapytań w porównaniu ze statystykami pełnej tabeli. Aby uzyskać więcej informacji na temat predykatu filtru, zobacz CREATE STATISTICS (TWORZENIE STATYSTYK). Aby uzyskać więcej informacji o tym, kiedy utworzyć przefiltrowane statystyki, zobacz sekcję Kiedy utworzyć statystykę w tym artykule.

Opcje statystyk

Istnieją opcje wpływające na czas i sposób tworzenia i aktualizowania statystyk. Te opcje można konfigurować tylko na poziomie bazy danych.

opcja AUTO_CREATE_STATISTICS

Gdy opcja automatycznego tworzenia statystyk, AUTO_CREATE_STATISTICS jest włączona, Optymalizator zapytań tworzy statystyki dotyczące poszczególnych kolumn w predykacie zapytania, w razie potrzeby, w celu zwiększenia szacunków kardynalności planu zapytania. Te statystyki z jedną kolumną są tworzone na kolumnach, które nie mają jeszcze histogramu w istniejącym obiekcie statystyk. Opcja AUTO_CREATE_STATISTICS nie określa, czy statystyki są tworzone dla indeksów. Ta opcja nie generuje również przefiltrowanych statystyk. Dotyczy to wyłącznie statystyk z jedną kolumną dla pełnej tabeli.

Gdy optymalizator zapytań tworzy statystyki w wyniku użycia AUTO_CREATE_STATISTICS opcji, nazwa statystyk zaczyna się od _WA. Poniższe zapytanie umożliwia określenie, czy optymalizator zapytań utworzył statystyki dla kolumny predykatu zapytania.

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
    INNER 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_UPDATE_STATISTICS

Gdy opcja statystyk automatycznej aktualizacji, AUTO_UPDATE_STATISTICS jest włączona, Optymalizator zapytań określa, kiedy statystyki mogą być nieaktualne, a następnie aktualizuje je, gdy są używane przez zapytanie. Ta akcja jest również nazywana ponowną kompilacją statystyk. Statystyki stają się nieaktualne, gdy operacje wstawiania, aktualizowania, usuwania lub scalania modyfikują rozkład danych w tabeli lub w indeksowanym widoku. Optymalizator zapytań określa, kiedy statystyki mogą być nieaktualne, zliczając liczbę modyfikacji wierszy od ostatniej aktualizacji statystyk i porównując liczbę modyfikacji wierszy do progu. Próg jest oparty na kardynalności tabeli, którą można zdefiniować jako liczbę wierszy w tabeli lub widoku indeksowanym.

Oznaczanie statystyk jako nieaktualnych na podstawie modyfikacji wierszy występuje nawet wtedy, gdy opcja jest wyłączona AUTO_UPDATE_STATISTICS . AUTO_UPDATE_STATISTICS Jeśli opcja jest wyłączona, statystyki nie są aktualizowane, nawet jeśli są one oznaczone jako nieaktualne. Plany nadal używają nieaktualnych obiektów statystyk. Ustawienie AUTO_UPDATE_STATISTICS opcji WYŁ. może spowodować nieoptymalne plany zapytań i obniżoną wydajność zapytań. AUTO_UPDATE STATISTICS Zalecane jest ustawienie opcji WŁĄCZONE.

• Do wersji SQL Server 2014 (12.x) włącznie, silnik bazy danych używa progu ponownej kompilacji opartego na liczbie wierszy w tabeli lub widoku indeksowanego w momencie oceny statystyk. Próg różni się od tego, czy tabela jest tymczasowa, czy stała.

  Typ tabeli	Kardynalność tabeli (n)	Próg ponownej kompilacji (liczba modyfikacji)
  Temporary	n< 6	6
  Temporary	6 <= n<= 500	500
  Permanent	n<= 500	500
  Tymczasowe lub trwałe	n> 500	500 + (0,20 * n)

  Jeśli na przykład tabela zawiera 20 tysięcy wierszy, obliczenie to 500 + (0.2 * 20,000) = 4,500 i statystyki są aktualizowane co 4500 modyfikacji.

• Począwszy od programu SQL Server 2016 (13.x) i poziomu zgodności bazy danych 130, aparat bazy danych używa również malejącego, dynamicznego progu ponownego skompilowania statystyk, który dostosowuje się zgodnie z kardynalnością tabeli w momencie oceny statystyk. Dzięki tej zmianie statystyki dotyczące dużych tabel są aktualizowane częściej. Jeśli jednak baza danych ma poziom zgodności poniżej 130, obowiązują progi programu SQL Server 2014 (12.x).

  Typ tabeli	Kardynalność tabeli (n)	Próg ponownej kompilacji (liczba modyfikacji)
  Temporary	n < 6	6
  Temporary	6 <= n <= 500	500
  Permanent	n <= 500	500
  Tymczasowe lub trwałe	n > 500	MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

  Jeśli na przykład tabela zawiera 2 miliony wierszy, obliczenie jest wartością minimalną pomiędzy 500 + (0.20 * 2,000,000) = 400,500 a SQRT(1,000 * 2,000,000) = 44,721. Oznacza to, że statystyki są aktualizowane co 44 721 modyfikacji.

Important

Od SQL Server 2008 R2 (10.50.x) do SQL Server 2014 (12.x), lub w SQL Server 2016 (13.x) i nowszych wersjach, z poziomem zgodności bazy danych 120 i niższymi wersjami, włącz flagę śledzenia 2371, aby umożliwić programowi SQL Server użycie malejącego, dynamicznego progu aktualizacji statystyk.

Chociaż jest to zalecane we wszystkich scenariuszach, włączenie flagi śledzenia 2371 jest opcjonalne. Można jednak użyć poniższych wskazówek dotyczących włączania flagi śledzenia 2371 w środowisku przed programem SQL Server 2016 (13.x):

• Jeśli korzystasz z systemu SAP, włącz ten ślad. Aby uzyskać więcej informacji, zobacz ten blog na temat flagi śledzenia 2371.
• Jeśli musisz polegać na wykonywaniu nocnych zadań w celu aktualizacji statystyk, ponieważ bieżąca automatyczna aktualizacja nie jest wywoływana wystarczająco często, rozważ uruchomienie flagi śledzenia 2371, aby dostosować próg do kardynalności tabeli.

Optymalizator zapytań sprawdza nieaktualne statystyki przed skompilowaniem zapytania i przed wykonaniem buforowanego planu zapytania. Przed skompilowanie zapytania optymalizator zapytań używa kolumn, tabel i indeksowanych widoków w predykacie zapytania, aby określić, które statystyki mogą być nieaktualne. Przed wykonaniem buforowanego planu zapytania silnik bazy danych sprawdza, czy plan zapytania odnosi się do statystyk up-to-date.

Opcja AUTO_UPDATE_STATISTICS dotyczy obiektów statystyk utworzonych dla indeksów, pojedynczych kolumn w predykatach zapytań i statystyk utworzonych za pomocą instrukcji CREATE STATISTICS . Ta opcja dotyczy również przefiltrowanych statystyk.

Możesz użyć sys.dm_db_stats_properties , aby dokładnie śledzić liczbę wierszy zmienionych w tabeli i zdecydować, czy chcesz ręcznie zaktualizować statystyki.

AUTO_UPDATE_STATISTICS zawsze jest wyłączona dla tabel zoptymalizowanych pod kątem pamięci.

AUTO_UPDATE_STATISTICS_ASYNC

Opcja aktualizacji statystyk asynchronicznych AUTO_UPDATE_STATISTICS_ASYNC określa, czy optymalizator zapytań używa aktualizacji statystyk synchronicznych lub asynchronicznych. Domyślnie opcja aktualizacji statystyk asynchronicznych jest wyłączona, a optymalizator zapytań aktualizuje statystyki synchronicznie. Opcja AUTO_UPDATE_STATISTICS_ASYNC dotyczy obiektów statystyk utworzonych dla indeksów, pojedynczych kolumn w predykatach zapytań i statystyk utworzonych za pomocą instrukcji CREATE STATISTICS .

Note

Aby ustawić opcję aktualizacji statystyk asynchronicznych w programie SQL Server Management Studio, na stronie Opcje okna Właściwości bazy danych, należy ustawić opcje Automatyczna aktualizacja statystyk oraz Asynchroniczna automatyczna aktualizacja statystyk na Prawda.

Aktualizacje statystyk mogą być synchroniczne (domyślne) lub asynchroniczne.

• W przypadku synchronizacji aktualizacji statystyk, zapytania zawsze są kompilowane i wykonywane przy użyciu statystyk z datą up-to. Gdy statystyki są nieaktualne, optymalizator zapytań czeka na zaktualizowane statystyki przed skompilowaniem i wykonaniem zapytania.

• Dzięki aktualizacjom statystyk asynchronicznych zapytania kompilują się z istniejącymi statystykami, nawet jeśli istniejące statystyki są nieaktualne. Optymalizator zapytań może wybrać nieoptymalny plan zapytania, jeśli statystyki są nieaktualne podczas kompilowania zapytania. Statystyki są zwykle aktualizowane wkrótce później. Zapytania kompilowane po aktualizacji statystyk mogą korzystać ze zaktualizowanych statystyk.

Rozważ użycie statystyk synchronicznych podczas wykonywania operacji, które zmieniają rozkład danych, takich jak obcięcie tabeli lub przeprowadzenie zbiorczej aktualizacji dużego procentu wierszy. Jeśli nie zaktualizujesz ręcznie statystyk po zakończeniu operacji, użycie statystyk synchronicznych zapewni, że statystyki są up-to-date przed wykonaniem zapytań na zmienionych danych.

Rozważ użycie statystyk asynchronicznych w celu osiągnięcia bardziej przewidywalnych czasów odpowiedzi zapytań w następujących scenariuszach:

• Aplikacja często wykonuje to samo zapytanie, podobne zapytania lub podobne buforowane plany zapytań. Czasy odpowiedzi zapytania mogą być bardziej przewidywalne dzięki aktualizacjom statystyk asynchronicznych niż w przypadku aktualizacji statystyk synchronicznych, ponieważ optymalizator zapytań może wykonywać zapytania przychodzące bez oczekiwania na up-to-date statistics. Pozwala to uniknąć opóźnienia niektórych zapytań, a nie innych.

• Aplikacja doświadczyła przerwania obsługi żądania klienta spowodowanego przez co najmniej jedno zapytanie oczekujące na zaktualizowane statystyki. W niektórych przypadkach oczekiwanie na synchroniczne statystyki może spowodować niepowodzenie aplikacji z agresywnymi przekroczeniami limitu czasu.

Note

Statystyki dotyczące lokalnych tabel tymczasowych są zawsze aktualizowane synchronicznie niezależnie od AUTO_UPDATE_STATISTICS_ASYNC opcji. Statystyki dotyczące globalnych tabel tymczasowych są aktualizowane synchronicznie lub asynchronicznie zgodnie z zestawem opcji AUTO_UPDATE_STATISTICS_ASYNC dla bazy danych użytkowników.

Aktualizowanie statystyk asynchronicznych jest realizowane za pomocą żądania w tle. Gdy żądanie jest gotowe do zapisania zaktualizowanych statystyk w bazie danych, próbuje uzyskać blokadę modyfikacji schematu w obiekcie metadanych statystyk. Jeśli inna sesja już przechowuje blokadę na tym samym obiekcie, aktualizacja statystyk asynchronicznych zostanie zablokowana do momentu uzyskania blokady modyfikacji schematu. Podobnie, sesje, które muszą uzyskać blokadę stabilności schematu (Sch-S) na obiekcie metadanych statystyk, aby skompilować zapytanie, mogą być zablokowane przez asynchroniczną sesję aktualizacji statystyk w tle, która już posiada lub czeka na uzyskanie blokady modyfikacji schematu. W związku z tym w przypadku obciążeń z bardzo częstymi kompilacjami zapytań i częstymi aktualizacjami statystyk użycie statystyk asynchronicznych może zwiększyć prawdopodobieństwo problemów ze współbieżnością z powodu blokowania blokady.

W usłudze Azure SQL Database, usłudze Azure SQL Managed Instance i począwszy od programu SQL Server 2022 (16.x) można uniknąć potencjalnych problemów ze współbieżnością przy użyciu aktualizacji statystyk asynchronicznych, jeśli włączysz ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY konfigurację w zakresie bazy danych. Po włączeniu tej konfiguracji żądanie w tle oczekuje na uzyskanie blokady modyfikacji schematu (Sch-M) i utrwala zaktualizowane statystyki w oddzielnej kolejce o niskim priorytecie, co umożliwia innym żądaniom kontynuowanie kompilowania zapytań z istniejącymi statystykami. Gdy żadna inna sesja nie posiada blokady na obiekcie metadanych statystyk, żądanie w tle uzyskuje blokadę modyfikacji schematu i aktualizuje statystyki. W mało prawdopodobnym przypadku, gdy żądanie w tle nie może uzyskać blokady w ciągu kilku minut, aktualizacja statystyk asynchronicznych zostanie przerwana, a statystyki nie zostaną zaktualizowane do momentu wyzwolenia kolejnej automatycznej aktualizacji statystyk lub do momentu ręcznego zaktualizowania statystyk.

Note

Opcja konfiguracji bazy danych ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY jest dostępna w usługach Azure SQL Database, Azure SQL Managed Instance oraz w SQL Server, od wersji SQL Server 2022 (16.x).

opcja AUTO_DROP

Dotyczy: Azure SQL Database, Azure SQL Managed Instance i począwszy od programu SQL Server 2022 (16.x)

W programie SQL Server przed programem SQL Server 2022 (16.x), jeśli statystyki są tworzone ręcznie przez narzędzie użytkownika lub innej firmy w bazie danych użytkownika, te obiekty statystyczne mogą blokować lub zakłócać zmiany schematu, które mogą być pożądane.

Począwszy od programu SQL Server 2022 (16.x), opcja automatycznego upuszczania jest domyślnie włączona dla wszystkich nowych i migrowanych baz danych. Właściwość AUTO_DROP umożliwia tworzenie obiektów statystyk w trybie, tak aby kolejna zmiana schematu nie została zablokowana przez obiekt statystyk, ale zamiast tego statystyki są usuwane w razie potrzeby. W ten sposób ręcznie utworzone statystyki z włączonym automatycznym usuwaniem zachowują się jak statystyki utworzone automatycznie.

W usługach Azure SQL Database, Azure SQL Managed Instance i SQL Server 2022 (16.x) i nowszych wersjach automatycznie utworzone statystyki zawsze zachowują się tak, jakby ustawiono AUTO_DROP .

Note

Próba ustawienia lub wyłączenia właściwości automatycznego usunięcia w statystykach utworzonych automatycznie może zgłaszać błędy. Automatycznie tworzone statystyki zawsze używają automatycznego upuszczania. Niektóre kopie zapasowe, po przywróceniu, mogą mieć tę właściwość niepoprawnie ustawioną do czasu następnego zaktualizowania obiektu statystyk (ręcznie lub automatycznie). Jednak automatycznie utworzone statystyki zawsze zachowują się jak statystyki automatycznego porzucania. Podczas przywracania bazy danych do SQL Server 2022 (16.x) z poprzedniej wersji zaleca się wykonanie sp_updatestats na bazie danych, aby ustawić odpowiednie metadane dla funkcji automatycznego upuszczania statystyk.

Aby na przykład ręcznie utworzyć obiekt statystyk w dbo.DatabaseLog tabeli:

CREATE STATISTICS [mystats]
    ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser])
    WITH AUTO_DROP = ON;

Na przykład aby zaktualizować ustawienie automatycznego upuszczania obiektu statystyk w dbo.DatabaseLog tabeli:

UPDATE STATISTICS [dbo].[DatabaseLog] ([mystats])
    WITH AUTO_DROP = ON;

Aby ocenić ustawienie automatycznego upuszczania dla istniejących statystyk, użyj kolumny auto_drop w pliku sys.stats:

SELECT object_id,
       [name],
       auto_drop
FROM sys.stats;

Aby uzyskać więcej informacji, zobacz AUTO_DROP.

INCREMENTAL

Dotyczy: SQL Server 2014 (12.x) i nowszych wersjach.

Gdy opcja INCREMENTAL w CREATE STATISTICS jest WŁĄCZONA, tworzone statystyki to statystyki per partycja. Po wyłączeniu drzewo statystyk jest porzucane, a program SQL Server ponownie skompiluje statystyki. Wartość domyślna to WYŁĄCZONE. To ustawienie zastępuje właściwość INCREMENTAL na poziomie bazy danych. Aby uzyskać więcej informacji na temat tworzenia statystyk przyrostowych, zobacz CREATE STATISTICS (TWORZENIE STATYSTYK). Aby uzyskać więcej informacji na temat automatycznego tworzenia statystyk na partycje, zobacz Właściwości bazy danych (strona opcji) i OPCJE ALTER DATABASE SET.

Po dodaniu nowych partycji do dużej tabeli należy zaktualizować statystyki w celu uwzględnienia nowych partycji. Jednak czas wymagany do skanowania całej tabeli (FULLSCAN lub SAMPLE opcji) może być dość długi. Ponadto skanowanie całej tabeli nie jest konieczne, ponieważ mogą być potrzebne tylko statystyki dotyczące nowych partycji. Opcja przyrostowa tworzy i przechowuje statystyki na podstawie partycji, a po zaktualizowaniu odświeża tylko statystyki dla tych partycji, które wymagają nowych statystyk

Jeśli statystyki poszczególnych partycji nie są obsługiwane, opcja zostanie zignorowana i zostanie wygenerowane ostrzeżenie. Statystyki przyrostowe nie są obsługiwane w przypadku następujących typów statystyk:

• Statystyki utworzone za pomocą indeksów, które nie są dostosowane do partycji z tabelą podstawową.
• Statystyki utworzone w przypadku pomocniczych baz danych z możliwością odczytu zawsze włączone.
• Statystyki utworzone w bazach danych tylko do odczytu.
• Statystyki utworzone na podstawie filtrowanych indeksów.
• Statystyki utworzone w widokach.
• Statystyki utworzone w tabelach wewnętrznych.
• Statystyki utworzone za pomocą indeksów przestrzennych lub indeksów XML.

Kiedy utworzyć statystyki

Optymalizator zapytań już tworzy statystyki w następujący sposób:

1. Optymalizator zapytań tworzy statystyki indeksów w tabelach lub widokach podczas tworzenia indeksu. Te statystyki są tworzone na kluczowych kolumnach indeksu. Jeśli indeks jest indeksem filtrowanym, optymalizator zapytań tworzy przefiltrowane statystyki dla tego samego podzestawu wierszy określonych dla filtrowanego indeksu. Aby uzyskać więcej informacji na temat filtrowanych indeksów, zobacz Tworzenie filtrowanych indeksów i CREATE INDEX.

   Note

   W programie SQL Server 2014 (12.x) i nowszych wersjach statystyki nie są tworzone przez skanowanie wszystkich wierszy w tabeli podczas tworzenia lub odbudowy indeksu partycjonowanego. Zamiast tego optymalizator zapytań używa domyślnego algorytmu próbkowania do generowania statystyk. Po uaktualnieniu bazy danych z indeksami podzielonymi na partycje można zauważyć różnicę w danych histogramu dla tych indeksów. Ta zmiana zachowania może nie mieć wpływu na wydajność zapytań. Aby uzyskać statystyki dotyczące partycjonowanych indeksów, przeskanując wszystkie wiersze w tabeli, użyj CREATE STATISTICS lub UPDATE STATISTICS z klauzulą FULLSCAN.

2. Optymalizator zapytań tworzy statystyki dla pojedynczych kolumn w predykatach zapytań, gdy AUTO_CREATE_STATISTICS jest włączona.

W przypadku większości zapytań te dwie metody tworzenia statystyk zapewniają wysokiej jakości plan zapytań; w kilku przypadkach można ulepszyć plany zapytań, tworząc dodatkowe statystyki za pomocą instrukcji CREATE STATISTICS . Te dodatkowe statystyki mogą przechwytywać korelacje statystyczne, których optymalizator zapytań nie uwzględnia podczas tworzenia statystyk dla indeksów lub pojedynczych kolumn. Aplikacja może mieć dodatkowe korelacje statystyczne w danych tabeli, które w przypadku obliczenia w obiekcie statystyk mogą umożliwić optymalizatorowi zapytań ulepszanie planów zapytań. Na przykład przefiltrowane statystyki dotyczące podzbioru wierszy danych lub wielokolumnowych statystyk dotyczących kolumn predykatu zapytania mogą poprawić plan zapytania.

Podczas tworzenia statystyk za pomocą instrukcji CREATE STATISTICS zalecamy zachowanie opcji AUTO_CREATE_STATISTICS WŁĄCZONE, aby optymalizator zapytań nadal rutynowo tworzyć statystyki z jedną kolumną dla kolumn predykatu zapytania. Aby uzyskać więcej informacji na temat predykatów zapytań, zobacz Warunek wyszukiwania.

Rozważ utworzenie statystyk za pomocą instrukcji CREATE STATISTICS, jeśli ma zastosowanie dowolny z następujących elementów:

• Doradca dostrajania aparatu bazy danych sugeruje tworzenie statystyk.
• Predykat zapytania zawiera wiele skorelowanych kolumn, które nie są jeszcze kluczami w tym samym indeksie.
• Zapytanie wybiera z podzestawu danych.
• Zapytanie ma brakujące statystyki.

Note

Aby uzyskać informacje specyficzne dla tabeli i statystyk związanych z OLTP In-Memory, zobacz Statystyki dla tabel Memory-Optimized.

Predykat zapytania zawiera wiele skorelowanych kolumn

Gdy predykat zapytania zawiera wiele kolumn, które mają relacje i zależności między kolumnami, statystyki dotyczące wielu kolumn mogą poprawić plan zapytania. Statystyki dotyczące wielu kolumn zawierają statystyki korelacji między kolumnami, nazywane gęstościami, które nie są dostępne w statystykach z jedną kolumną. Gęstości mogą poprawić szacowanie kardynalności, gdy wyniki zapytania zależą od relacji danych między wieloma kolumnami.

Jeśli kolumny znajdują się już w tym samym indeksie, obiekt statystyk wielokolumnowych już istnieje i nie jest konieczne ręczne utworzenie go. Jeśli kolumny nie są jeszcze w tym samym indeksie, możesz utworzyć statystyki wielokolumnowe, tworząc indeks w kolumnach lub przy użyciu instrukcji CREATE STATISTICS . Wymaga więcej zasobów systemowych do utrzymania indeksu niż obiektu statystyk. Jeśli aplikacja nie wymaga indeksu wielokolumnowego, możesz ekonomizować zasoby systemowe, tworząc obiekt statystyk bez tworzenia indeksu.

Podczas tworzenia statystyk wielokolumnowych kolejność kolumn w definicji obiektu statystyk wpływa na skuteczność gęstości w celu oszacowania kardynalności. Obiekt statystyk przechowuje gęstości dla każdego prefiksu kolumn kluczy w definicji obiektu statystyk. Aby uzyskać więcej informacji na temat gęstości, zobacz sekcję Gęstość na tej stronie.

Aby utworzyć gęstości, które są przydatne w przypadku oszacowań kardynalności, kolumny w predykacie zapytania muszą być zgodne z jednym z prefiksów kolumn w definicji obiektu statystyk. Na przykład poniższy przykład tworzy obiekt statystyk wielokolumnowych w kolumnach LastName, MiddleNamei FirstName.

USE AdventureWorks2022;
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 LastFirst statystyk ma gęstości dla następujących prefiksów kolumn: (LastName), (LastName, MiddleName)i (LastName, MiddleName, FirstName). Gęstość nie jest dostępna dla elementu (LastName, FirstName). Jeśli zapytanie używa LastName i FirstName bez użycia MiddleName, brak dostępności gęstości dla oszacowania kardynalności.

Zapytanie wybiera z podzestawu danych

Gdy optymalizator zapytań tworzy statystyki dla pojedynczych kolumn i indeksów, tworzy statystyki dla wartości we wszystkich wierszach. Gdy zapytania wybierają z podzbioru wierszy, a podzbiór wierszy ma unikatową dystrybucję danych, przefiltrowane statystyki mogą poprawić plany zapytań. Przefiltrowane statystyki można utworzyć przy użyciu instrukcji CREATE STATISTICS z klauzulą WHERE w celu zdefiniowania wyrażenia predykatu filtru.

Na przykład przy użyciu aplikacji AdventureWorks2025 każdy produkt w Production.Product tabeli należy do jednej z czterech kategorii w Production.ProductCategory tabeli: Bikes, , ComponentsClothingi Accessories. Każda z kategorii ma inny rozkład danych dla wagi: wagi rowerów wahają się od 13,77 do 30,0, wagi komponentów wahają się od 2,12 do 1050,00 z niektórymi NULL wartościami, wagi odzieży to NULL, a wagi akcesoriów są NULL.

Korzystając z Bikes jako przykład, przefiltrowane statystyki dotyczące wszystkich wag rowerów dostarczają bardziej dokładnych danych dla Optymalizatora zapytań i mogą poprawić jakość planu zapytania w porównaniu z pełnotabelarycznymi statystykami lub brakiem statystyk w kolumnie Waga. Kolumna dotycząca wagi roweru jest dobrym kandydatem do przefiltrowanych statystyk, ale niekoniecznie dobrym kandydatem do indeksu filtrowanego, jeśli liczba wyszukiwań wagi jest stosunkowo mała. Wzrost wydajności wyszukiwania filtrowanego indeksu może nie przeważać nad dodatkowymi kosztami konserwacji i magazynowania w celu dodania filtrowanego indeksu do bazy danych.

Poniższa instrukcja tworzy BikeWeights przefiltrowane statystyki dla wszystkich podkategorii dla elementu Bikes. Filtrowane wyrażenie predykatu definiuje rowery, wyliczając wszystkie podkategorie rowerów z porównaniem Production.ProductSubcategoryID IN (1,2,3). Predykat nie może użyć Bikes nazwy kategorii, ponieważ jest on przechowywany w Production.ProductCategory tabeli, a wszystkie kolumny w wyrażeniu filtru muszą znajdować się w tej samej tabeli.

USE AdventureWorks2022;
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 zapytań może użyć BikeWeights filtrowanych statystyk, aby ulepszyć plan zapytania dla następującego zapytania, które wybiera wszystkie rowery, które ważyją więcej niż 25.

SELECT P.Weight AS Weight,
       S.Name AS BikeName
FROM Production.Product AS P
     INNER 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

Zapytanie identyfikuje brakujące statystyki

Jeśli błąd lub inne zdarzenie uniemożliwia optymalizatorowi zapytań tworzenie statystyk, optymalizator zapytań tworzy plan zapytania bez używania statystyk. Optymalizator zapytań oznacza statystyki jako brakujące i próbuje ponownie wygenerować statystyki przy następnym wykonaniu zapytania.

Brakujące statystyki są wskazywane jako ostrzeżenia (nazwa tabeli w czerwonym tekście), gdy plan wykonywania zapytania jest wyświetlany graficznie przy użyciu programu SQL Server Management Studio. Ponadto monitorowanie klasy zdarzeń Brak statystyk kolumny przy użyciu programu SQL Server Profiler wskazuje, kiedy brakuje statystyk. Aby uzyskać więcej informacji, zobacz Kategoria zdarzeń Błędy i ostrzeżenia (Silnik bazy danych).

Jeśli brakuje statystyk, wykonaj następujące kroki:

• Sprawdź, czy AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS są włączone.
• Sprawdź, czy baza danych nie jest tylko do odczytu. Jeśli baza danych jest tylko do odczytu, nie można zapisać nowego obiektu statystyk.
• Utwórz brakujące statystyki przy użyciu instrukcji CREATE STATISTICS .

Gdy brakuje lub są nieaktualne statystyki dotyczące bazy danych lub migawki tylko do odczytu, aparat bazy danych tworzy i utrzymuje tymczasowe statystyki w programie tempdb. Gdy aparat bazy danych tworzy tymczasowe statystyki, nazwa statystyk jest dołączana z sufiksem _readonly_database_statistic , aby odróżnić tymczasowe statystyki od stałych statystyk. Sufiks _readonly_database_statistic jest zarezerwowany dla statystyk generowanych przez program SQL Server. Skrypty dla statystyk tymczasowych można utworzyć i odtworzyć w bazie danych odczytu i zapisu. Po utworzeniu skryptu program Management Studio zmienia sufiks nazwy statystyk z _readonly_database_statistic na _readonly_database_statistic_scripted.

Tylko program SQL Server może tworzyć i aktualizować tymczasowe statystyki. Można jednak usunąć tymczasowe statystyki i monitorować właściwości statystyk przy użyciu tych samych narzędzi, które są używane do stałych statystyk:

• Usuń tymczasowe statystyki przy użyciu instrukcji DROP STATISTICS .
• Monitoruj statystyki przy użyciu widoków katalogowych sys.stats i sys.stats_columns. sys.stats Widok wykazu systemu zawiera kolumnęis_temporary, aby wskazać, które statystyki są trwałe i które są tymczasowe.

Ponieważ tymczasowe statystyki są przechowywane w tempdbprogramie , ponowne uruchomienie usługi SQL Server powoduje, że wszystkie tymczasowe statystyki znikną.

Kiedy zaktualizować statystyki

Optymalizator zapytań określa, kiedy statystyki mogą być nieaktualne, a następnie aktualizuje je, gdy są one potrzebne do planu zapytania. W niektórych przypadkach można poprawić plan zapytania, a tym samym zwiększyć jego wydajność, aktualizując statystyki częściej niż to się dzieje, gdy opcja AUTO_UPDATE_STATISTICS jest włączona. Statystyki można zaktualizować za pomocą instrukcji UPDATE STATISTICS lub procedury składowanej sp_updatestats.

Aktualizowanie statystyk zapewnia kompilowanie zapytań przy użyciu statystyk opartych na dacie up-to. Aktualizowanie statystyk za pośrednictwem dowolnego procesu może spowodować automatyczne ponowne skompilowanie planów zapytań. Nie zalecamy zbyt częstego ręcznego aktualizowania statystyk, ponieważ występuje kompromis między ulepszaniem planów zapytań a czasem ponownego kompilowania zapytań. Konkretne kompromisy zależą od aplikacji.

Podczas aktualizowania statystyk za pomocą UPDATE STATISTICS lub sp_updatestats, zalecamy ustawienie AUTO_UPDATE_STATISTICS na ON, aby optymalizator zapytań rutynowo aktualizował statystyki.

• Aby uzyskać więcej informacji na temat aktualizowania statystyk dotyczących kolumny, indeksu, tabeli lub widoku indeksowanego, zobacz UPDATE STATISTICS (STATYSTYKI AKTUALIZACJI).

• Aby uzyskać informacje o sposobie aktualizowania statystyk dla wszystkich tabel zdefiniowanych przez użytkownika i wewnętrznych w bazie danych, zobacz procedurę składowaną sp_updatestats.

• Aby uzyskać więcej informacji na temat progów aktualizacji automatycznych statystyk, zobacz AUTO_UPDATE_STATISTICS Opcja.

Gdy AUTO_UPDATE_STATISTICS jest ustawiony na OFF, ponowna kompilacja planu może się wciąż odbywać z różnych innych powodów, ale nie następuje automatycznie z powodu nieaktualności statystyk. Gdy AUTO_UPDATE_STATISTICS jest ustawiona wartość OFF, statystyki są aktualizowane tylko za pośrednictwem innych ręcznie zaplanowanych procesów, takich jak plany konserwacji. Ustawienie AUTO_UPDATE_STATISTICS opcji WYŁ. może zatem spowodować nieoptymalne plany zapytań i obniżoną wydajność zapytań.

Wykrywanie nieaktualnych statystyk

Aby określić, kiedy statystyki zostały ostatnio zaktualizowane, użyj funkcji sys.dm_db_stats_properties lub STATS_DATE .

Rozważ zaktualizowanie statystyk dla następujących warunków:

• Czasy wykonywania zapytań są powolne.
• Operacje wstawiania są wykonywane w kolumnach kluczy rosnących lub malejących.
• Po operacjach konserwacji.

Przykłady ręcznego aktualizowania statystyk można znaleźć w temacie UPDATE STATISTICS (STATYSTYKI AKTUALIZACJI).

Czasy wykonywania zapytań są powolne

Jeśli czasy odpowiedzi zapytania są powolne lub nieprzewidywalne, upewnij się, że zapytania mają up-to-date statistics przed wykonaniem dodatkowych kroków rozwiązywania problemów.

Operacje wstawiania są wykonywane w kolumnach kluczy rosnących lub malejących

Statystyki dotyczące rosnących lub malejących kolumn kluczy, takich jak kolumny IDENTITY lub znaczniki czasu w czasie rzeczywistym, mogą wymagać częstszych aktualizacji statystyk niż to zazwyczaj odbywa się przez Optymalizator zapytań. Operacje wstawiania dołączają nowe wartości do kolumn rosnących lub malejących. Liczba dodanych wierszy może być zbyt mała, aby wyzwolić aktualizację statystyk. Jeśli statystyki nie są up-to-date i zapytania wybierane z ostatnio dodanych wierszy, bieżące statystyki nie mają oszacowań kardynalności dla tych nowych wartości. Może to spowodować niedokładne szacunki kardynalności i niską wydajność zapytań.

Na przykład zapytanie wybierające najnowsze daty zamówień sprzedaży ma niedokładne oszacowania kardynalności, jeśli statystyki nie są aktualizowane w celu uwzględnienia oszacowań kardynalności dla najnowszych dat zamówień sprzedaży.

Po operacjach konserwacji

Rozważ zaktualizowanie statystyk po wykonaniu procedur konserwacji, które zmieniają rozkład danych, takich jak obcięcie tabeli lub wykonywanie zbiorczego wstawiania dużej części wierszy. Może to uniknąć przyszłych opóźnień w przetwarzaniu zapytań, podczas gdy zapytania oczekują na aktualizacje automatycznych statystyk.

Operacje, takie jak ponowne kompilowanie, defragmentowanie lub reorganizacja indeksu, nie zmieniają rozkładu danych. W związku z tym nie trzeba aktualizować statystyk po wykonaniu operacji ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG lub ALTER INDEX REORGANIZE . Optymalizator zapytań aktualizuje statystyki podczas odbudowywania indeksu w tabeli lub widoku przy użyciu ALTER INDEX REBUILD lub DBCC DBREINDEX, jednak ta aktualizacja statystyk jest produktem ubocznym ponownego utworzenia indeksu. Optymalizator zapytań nie aktualizuje statystyk po operacjach DBCC INDEXDEFRAG ani ALTER INDEX REORGANIZE.

Tip

Począwszy od programu SQL Server 2016 (13.x) SP1 CU4, użyj PERSIST_SAMPLE_PERCENT opcji CREATE STATISTICS lub UPDATE STATISTICS, aby ustawić i zachować określony procent próbkowania dla kolejnych aktualizacji statystyk, które nie określają jawnie wartości procentowej próbkowania.

Automatyczne zarządzanie indeksami i statystykami

Użyj inteligentnych rozwiązań, takich jak defragmentacja indeksu adaptacyjnego , aby automatycznie zarządzać defragmentacją indeksu i aktualizacjami statystyk dla co najmniej jednej bazy danych. Ta procedura automatycznie wybiera, czy należy ponownie skompilować lub zreorganizować indeks zgodnie z poziomem fragmentacji, między innymi parametrami, oraz zaktualizować statystyki z progiem liniowym.

Zapytania skutecznie korzystające ze statystyk

Niektóre implementacje zapytań, takie jak zmienne lokalne i złożone wyrażenia w predykacie zapytania, mogą prowadzić do nieoptymalnych planów zapytań. Przestrzeganie wytycznych dotyczących projektowania zapytań dotyczących efektywnego używania statystyk może pomóc uniknąć tego. Aby uzyskać więcej informacji na temat predykatów zapytań, zobacz Warunek wyszukiwania.

Plany zapytań można ulepszyć, stosując wytyczne dotyczące projektowania zapytań, które skutecznie wykorzystują statystyki w celu poprawy szacowania kardynalności dla wyrażeń, zmiennych i funkcji używanych w predykatach zapytań. Gdy optymalizator zapytań nie zna wartości wyrażenia, zmiennej lub funkcji, nie wie, która wartość ma być wyszukiwana w histogramie i dlatego nie może pobrać najlepszego oszacowania kardynalności z histogramu. Zamiast tego optymalizator zapytań opiera oszacowanie kardynalności na średniej liczbie wierszy na wartość odrębną dla wszystkich próbkowanych wierszy w histogramie. Prowadzi to do nieoptymalnych szacunków kardynalności i może zaszkodzić wydajności zapytań. Aby uzyskać więcej informacji na temat histogramów, zobacz sekcję histogramu na tej stronie lub sys.dm_db_stats_histogram.

W poniższych wytycznych opisano sposób pisania zapytań w celu poprawienia planów zapytań poprzez ulepszone oszacowanie kardynalności.

Zwiększanie oszacowań kardynalności dla wyrażeń

Aby poprawić szacowanie kardynalności dla wyrażeń, postępuj zgodnie z następującymi wytycznymi:

• Jeśli to możliwe, uprość wyrażenia, które zawierają stałe. Optymalizator zapytań nie ocenia wszystkich funkcji i wyrażeń zawierających stałe przed określeniem oszacowań kardynalności. Na przykład uprość wyrażenie ABS(-100) na 100.
• Jeśli wyrażenie używa wielu zmiennych, rozważ utworzenie obliczonej kolumny dla wyrażenia, a następnie utworzenie statystyk lub indeksu w obliczonej kolumnie. Na przykład predykat WHERE PRICE + Tax > 100 zapytania może mieć lepsze oszacowanie kardynalności, jeśli utworzysz obliczoną kolumnę dla wyrażenia Price + Tax.

Zwiększanie oszacowań kardynalności zmiennych i funkcji

Aby poprawić szacowanie kardynalności zmiennych i funkcji, postępuj zgodnie z następującymi wytycznymi:

• Jeśli predykat zapytania używa zmiennej lokalnej, rozważ ponowne zapisywanie zapytania w celu użycia parametru zamiast zmiennej lokalnej. Wartość zmiennej lokalnej nie jest znana, gdy optymalizator zapytań tworzy plan wykonywania zapytania. Gdy zapytanie używa parametru, optymalizator zapytań używa oszacowania kardynalności dla pierwszej rzeczywistej wartości parametru przekazanej do procedury składowanej.

• Rozważ użycie standardowej tabeli lub tabeli tymczasowej do przechowywania wyników funkcji tabeli z wieloma instrukcjami (mstvf). Optymalizator zapytań nie tworzy statystyk dla funkcji zwracających tabelę z wieloma instrukcjami. Dzięki temu optymalizator zapytań może tworzyć statystyki kolumn tabeli i używać ich do tworzenia lepszego planu zapytań.

• Rozważ użycie standardowej tabeli lub tabeli tymczasowej jako zastąpienia zmiennych tabeli. Optymalizator zapytań nie tworzy statystyk dla zmiennych tabeli. Dzięki temu optymalizator zapytań może tworzyć statystyki kolumn tabeli i używać ich do tworzenia lepszego planu zapytań. Istnieją kompromisy w określaniu, czy używać tabeli tymczasowej, czy zmiennej tabeli; Zmienne tabeli używane w procedurach składowanych powodują mniejszą liczbę ponownych kompilacji procedury składowanej niż tabele tymczasowe. W zależności od aplikacji użycie tabeli tymczasowej zamiast zmiennej tabeli może nie poprawić wydajności.

• Jeśli procedura składowana zawiera zapytanie, które używa przekazanego parametru, należy unikać zmiany wartości parametru w procedurze składowanej przed użyciem go w zapytaniu. Szacunki kardynalności zapytania są oparte na przekazanej wartości parametru, a nie zaktualizowanej wartości. Aby uniknąć zmiany wartości parametru, możesz ponownie napisać zapytanie, aby użyć dwóch procedur składowanych.

  Na przykład poniższa procedura Sales.GetRecentSales składowana zmienia wartość parametru @date , gdy @date ma wartość NULL.

  USE AdventureWorks2022;
  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 AS h, Sales.SalesOrderDetail AS d
      WHERE h.SalesOrderID = d.SalesOrderID
          AND h.OrderDate > @date;
  END
  GO
  

  Jeśli pierwsze wywołanie procedury składowanej Sales.GetRecentSales przejdzie NULL dla parametru @date, optymalizator zapytań kompiluje procedurę składowaną z oszacowaniem kardynalności dla @date = NULL, mimo że predykat zapytania nie jest wywoływany z @date = NULL. To oszacowanie kardynalności może być znacznie inne niż liczba wierszy w rzeczywistym wyniku zapytania. W związku z tym optymalizator zapytań może wybrać nieoptymalny plan zapytania. Aby uniknąć tego, można przekształcić procedurę składowaną na dwie procedury w następujący sposób:

  USE AdventureWorks2022;
  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));
      EXECUTE 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 AS h, Sales.SalesOrderDetail AS d
      WHERE h.SalesOrderID = d.SalesOrderID
          AND h.OrderDate > @date;
  END
  GO
  

Ulepszanie oszacowań kardynalności za pomocą wskazówek dotyczących zapytań

Aby poprawić szacowanie kardynalności zmiennych lokalnych, możesz użyć wskazówek zapytania OPTIMIZE FOR <value> lub OPTIMIZE FOR UNKNOWN wraz z RECOMPILE. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące zapytań.

W przypadku niektórych aplikacji ponowne skompilowanie zapytania za każdym razem, gdy jest wykonywane, może zająć zbyt dużo czasu. Wskazówka OPTIMIZE FOR dotycząca zapytania może pomóc nawet wtedy, gdy nie używasz RECOMPILE tej opcji. Można na przykład dodać OPTIMIZE FOR opcję do procedury Sales.GetRecentSales składowanej, aby określić określoną datę. Poniższy przykład dodaje OPTIMIZE FOR opcję do Sales.GetRecentSales procedury.

USE AdventureWorks2022;
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 AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date
    OPTION (OPTIMIZE FOR (@date = '2004-05-01 00:00:00.000'));
END
GO

Ulepszanie oszacowań kardynalności za pomocą przewodników dotyczących planu

W przypadku niektórych aplikacji wytyczne dotyczące projektowania zapytań mogą nie być stosowane, ponieważ nie można zmienić zapytania lub RECOMPILE wskazówka zapytania może spowodować zbyt wiele ponownych kompilacji. Przewodniki planu umożliwiają określenie innych podpowiedzi, takich jak USE PLAN, w celu kontrolowania zachowania zapytania podczas analizowania zmian aplikacji we współpracy z dostawcą aplikacji. Aby uzyskać więcej informacji na temat przewodników dotyczących planu, zobacz Przewodniki dotyczące planu.

W usłudze Azure SQL Database rozważ użycie podpowiedzi Magazynu zapytań, aby wymusić plany zamiast przewodników planowania. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące Query Store.

Treści powiązane

• Statystyki dotyczące tabel Memory-Optimized
• UTWÓRZ STATYSTYKI (Transact-SQL)
• UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• opcje ALTER DATABASE SET (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• STWÓRZ INDEKS (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• Tworzenie filtrowanych indeksów
• STATS_DATE (Transact-SQL)
• sys.dm_db_stats_properties (Transact-SQL)
• sys.dm_db_stats_histogram (Transact-SQL)
• sys.stats
• sys.stats_columns (Transact-SQL)
• Defragmentacja Indeksu Adaptacyjnego