Gry predykat zapytania zawiera wiele kolumn, które są połączone relacjami i zależnościami, statystyki dla wielu kolumn mogą poprawić plan zapytania. Statystyki dla wielu kolumn zawierają statystyki korelacji między kolumnami, nazywane gęstościami, które nie są dostępne w statystykach jednokolumnowych. Gęstości mogą poprawić jakość szacowań kardynalności w sytuacji, gdy wyniki zapytania zależą od relacji między danymi w wielu kolumnach.

Jeśli kolumny są już uwzględnione w jednym indeksie, obiekt wielokolumnowych statystyk już istnieje i nie trzeba tworzyć go ręcznie. Jeśli kolumny nie są jeszcze uwzględnione w jednym indeksie, można utworzyć statystyki wielokolumnowe, tworząc indeks dla tych kolumn lub używając instrukcji CREATE STATISTICS. Obsługa indeksu wymaga większej ilości zasobów systemowych niż obsługa obiektu statystyk. Jeśli aplikacja nie wymaga indeksu wielokolumnowego, można zmniejszyć zużycie zasobów systemu, tworząc obiekt statystyk bez tworzenia indeksu.

Podczas tworzenia statystyk wielokolumnowych kolejność kolumn w definicji obiektu statystyk ma wpływ na efektywność gęstości w zakresie tworzenia szacowań kardynalności. W obiekcie statystyk są przechowywane gęstości dla każdego prefiksu kolumn klucza w definicji obiektu statystyk. Aby uzyskać więcej informacji dotyczących gęstości, zobacz temat DBCC SHOW_STATISTICS (języka Transact-SQL).

Aby można było tworzyć gęstości, które będą użyteczne podczas tworzenia szacowań kardynalności, kolumny w predykacie zapytania muszą pasować do jednego z prefiksów kolumn w definicji obiektu statystyk. Na przykład poniższy kod powoduje utworzenie obiektu wielokolumnowych statystyk dla kolumn LastName, MiddleName i FirstName.

USE AdventureWorks2012;
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 statystyk LastFirst 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 prefiksu (LastName, FirstName). Jeśli w zapytaniu są używane kolumny LastName i FirstName, ale nie jest używana kolumna MiddleName, gęstość nie jest dostępna na potrzeby tworzenia szacowań kardynalności.

Gdy optymalizator zapytań tworzy statystyki dla pojedynczych kolumn i indeksów, tworzy statystyki dla wartości znajdujących się we wszystkich wierszach. Gdy zapytanie wybiera dane z podzestawu wierszy, a podzestaw wierszy ma unikatowy rozkład danych, filtrowane statystyki mogą ulepszyć plany zapytań. Filtrowane statystyki można tworzyć przy użyciu instrukcji CREATE STATISTICS z klauzulą WHERE definiującą wyrażenie predykatu filtru.

Na przykład w bazie danych AdventureWorks2012 każdy produkt w tabeli Production.Product należy do jednej z czterech kategorii wymienionych w tabeli Production.ProductCategory: Bikes, Components, Clothing i Accessories. Każda z kategorii ma różny rozkład danych w zależności od wagi: waga rowerów (kategoria Bikes) należy do zakresu od 13,77 do 30,0, waga części (kategoria Components) należy do zakresu od 2,12 do 1050,00 (niektóre mają wagę równą NULL), waga ubrań (kategoria Clothing) zawsze jest równa NULL, a waga akcesoriów (kategoria Accessories) także zawsze jest równa NULL.

Gdy jako przykład będzie używana kategoria Bikes, filtrowane statystyki dla wszystkich wag rowerów będą dla optymalizatora zapytań dokładniejsze niż statystyki dla całej tabeli lub nieistniejące statystyki dla kolumny Weight i umożliwią mu zwiększenie jakości planu zapytania. Kolumna wag rowerów dobrze nadaje się do tworzenie filtrowanych statystyk, ale niekoniecznie nadaje się do utworzenia filtrowanego indeksu, jeśli liczba odnośników do wagi jest relatywnie mała. Poprawa wydajności związana z odnośnikami oferowanymi przez filtrowany indeks nie musi być na tyle duża, aby równoważyła koszty dodatkowej obsługi i przechowywania związane z dodaniem do bazy danych filtrowanego indeksu.

Następująca instrukcja tworzy filtrowaną statystykę BikeWeights dla wszystkich podkategorii kategorii Bikes. Wyrażenie filtrowanego predykatu definiuje rowery, wyliczając wszystkie podkategorie rowerów z porównaniem Production.ProductSubcategoryID IN (1,2,3). W predykacie nie można użyć nazwy kategorii Bikes, ponieważ jest ona przechowywana w tabeli Production.ProductCategory, a wszystkie kolumny w wyrażeniu filtru muszą znajdować się w jednej tabeli.

USE AdventureWorks2012;
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ć filtrowanej statystyki BikeWeights w celu ulepszenia planu zapytania dla następującego zapytania, które wybiera wszystkie rowery o wadze większej 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

Jeśli błąd lub inne zdarzenie uniemożliwi optymalizatorowi zapytań utworzenie statystyk, optymalizator zapytań utworzy plan zapytania bez użycia tych statystyk. Optymalizator zapytań oznaczy te statystyki jako brakujące i podejmie próbę ich ponownego utworzenia podczas następnego wykonywania danego zapytania.

Gdy plan wykonania zapytania jest wyświetlany graficznie w programie SQL Server Management Studio, brakujące statystyki są wskazywane w formie ostrzeżeń (nazwa tabeli napisana czerwoną czcionką). Aby uzyskać więcej informacji, zobacz temat Graphically Displaying the Execution Plan Using SQL Server Management Studio. Ponadto monitorowanie klasy zdarzeń Missing Column Statistics (Brakujące statystyki kolumn) przy użyciu programu SQL Server Profiler umożliwia określenie, kiedy brakuje statystyk. Aby uzyskać więcej informacji, zobacz temat Błędy i ostrzeżenia zdarzenia kategorii (aparat bazy danych).

W przypadku braku statystyk wykonaj następujące kroki:

• Sprawdź, czy opcje AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS mają wartość ON (włączona).

• Sprawdź, czy baza danych nie jest tylko do odczytu. Jeśli baza danych jest tylko do odczytu, optymalizator zapytań nie może zapisywać statystyk.

• Utwórz brakujące statystyki, używając instrukcji CREATE STATISTICS.

Gdy dla bazy danych tylko do odczytu albo migawki tylko do odczytu brakuje statystyk lub są one nieaktualne, program Aparat baz danych tworzy i obsługuje tymczasowe statystyki w bazie danych tempdb. Gdy program Aparat baz danych tworzy tymczasowe statystyki, do nazw statystyk jest dołączany sufiks _readonly_database_statistic w celu odróżnienia ich od trwałych statystyk. Sufiks _readonly_database_statistic jest zastrzeżony dla statystyk generowanych przez program SQL Server. Skrypty obsługujące tymczasowe statystyki można tworzyć i odtwarzać w bazie danych do odczytu i zapisu. Gdy są używane skrypty, program Management Studio zmienia sufiks nazw statystyk z _readonly_database_statistic na _readonly_database_statistic_scripted.

Tworzyć i aktualizować tymczasowe statystyki może tylko program SQL Server. Można jednak usuwać tymczasowe statystyki i monitorować właściwości statystyk, używając tych samych narzędzi co w przypadku trwałych statystyk:

• Usuń tymczasowe statystyki, używając instrukcji UPUŚĆ statystyki (języka Transact-SQL).

• Monitoruj statystyki przy użyciu widoków wykazu sys.stats i sys.stats_columns. Widok sys_stats zawiera kolumnę is_temporary wskazującą, które statystyki są trwałe, a które tymczasowe.

Tymczasowe statystyki są przechowywane w bazie danych tempdb, więc ponowne uruchomienie usługi SQL Server powoduje utratę wszystkich tymczasowych statystyk.

Powrót do początku

Jeśli czas odpowiedzi na zapytanie jest długi lub nieprzewidywalny, przed wykonaniem dodatkowych kroków rozwiązywania problemów należy sprawdzić, czy w zapytaniach są używane aktualne statystyki. Aby uzyskać więcej informacji dotyczących rozwiązywania problemów z wolno działającymi zapytaniami, zobacz temat Checklist for Analyzing Slow-Running Queries.

Statystyki dla rosnących lub malejących kolumn klucza, takich jak kolumna IDENTITY lub kolumna sygnatur czasowych czasu rzeczywistego, mogą wymagać częstszego aktualizowania niż wykonywane przez optymalizatora zapytań. Operacje wstawiania powodują dołączanie nowych wartości do kolumn rosnących lub malejących. Liczba dodawanych wierszy może być zbyt mała, aby wyzwolić aktualizację statystyk. Jeśli statystyki są nieaktualne, a zapytanie wybiera dane z ostatnio dodanych wierszy, bieżące statystyki nie będą zawierać szacowań kardynalności dla tych nowych wartości. Może to spowodować używanie niedokładnych szacowań kardynalności i niską wydajność zapytań.

Na przykład zapytanie wybierające dane z najnowszych dat zamówień sprzedaży będzie mieć niedokładne szacowania kardynalności, jeśli statystyki nie zostaną zaktualizowane w celu uwzględnienia szacowań kardynalności dla najnowszych dat zamówień sprzedaży.

Zaktualizowanie statystyk warto rozważyć po wykonaniu procedur konserwacji zmieniających rozkład danych, takich jak obcinanie tabeli czy zbiorcze wstawianie dużego odsetka wierszy. Pomoże to uniknąć opóźnienia przetwarzania zapytań wykonywanych w przyszłości spowodowanego tym, że zapytania będą oczekiwać na automatyczne zaktualizowanie statystyk.

Operacje, takie jak odbudowywanie, defragmentowanie lub reorganizowanie indeksu nie powodują zmiany rozkładu danych. Dlatego nie trzeba aktualizować statystyk po wykonaniu operacji ALTER INDEX REBUILD, DBCC REINDEX, DBCC INDEXDEFRAG lub ALTER INDEX REORGANIZE. Jednak optymalizator zapytań aktualizuje statystyki po odbudowaniu indeksu tabeli lub widoku za pomocą instrukcji ALTER INDEX REBUILD lub DBCC DBREINDEX; ta aktualizacja statystyk jest skutkiem ubocznym operacji ponownego utworzenia indeksu. Optymalizator zapytań nie aktualizuje statystyk po wykonaniu operacji DBCC INDEXDEFRAG lub ALTER INDEX REORGANIZE.

Powrót do początku

Aby ulepszyć szacowania kardynalności dla wyrażeń, należy skorzystać z następujących wytycznych:

• Gdy tylko jest to możliwe, należy upraszczać wyrażenia, używając w nich stałych. Optymalizator zapytań nie oblicza wszystkich funkcji i wyrażeń zawierających stałe przed ustaleniem szacowań kardynalności. Na przykład można uprościć wyrażenie ABS(-100) to 100.

• Jeśli w wyrażeniu jest używanych wiele zmiennych, warto rozważyć utworzenie kolumny obliczanej dla wyrażenia, a następnie utworzenie statystyk albo indeksu dla tej kolumny obliczanej. Na przykład predykat zapytania WHERE PRICE + Tax > 100 może mieć lepsze szacowanie kardynalności, jeśli zostanie utworzona kolumna obliczana dla wyrażenia Price + Tax.

Aby ulepszyć szacowania kardynalności dla zmiennych i funkcji, należy skorzystać z następujących wytycznych:

• Jeśli w predykacie zapytania jest używana zmienna lokalna, warto rozważyć ponowne napisanie zapytania, tak aby zamiast zmiennej lokalnej był używany parametr. Wartość zmiennej lokalnej jest nieznana, gdy optymalizator zapytań tworzy plan wykonania zapytania. Gdy w zapytaniu jest używany parametr, optymalizator zapytań używa szacowania kardynalności dla pierwszej rzeczywistej wartości parametru przekazanej do procedury składowanej.

• Warto rozważyć użycie standardowej lub tymczasowej tabeli do przechowywania wyników wieloinstrukcyjnych funkcji zwracających tabele. Optymalizator zapytań nie tworzy statystyk dla wieloinstrukcyjnych funkcji zwracających tabele. Dzięki temu podejściu optymalizator zapytań może tworzyć statystyki dla kolumn tabeli i używać ich do tworzenia lepszego planu zapytania. Aby uzyskać więcej informacji dotyczących wieloinstrukcyjnych funkcji zwracających tabele, zobacz temat Types of Functions.

• Warto rozważyć użycie standardowej lub tymczasowej tabeli jako zamiennika zmiennych tabeli. Optymalizator zapytań nie tworzy statystyk dla zmiennych tabeli. Dzięki temu podejściu optymalizator zapytań może tworzyć statystyki dla kolumn tabeli i używać ich do tworzenia lepszego planu zapytania. Decyzja, czy należy użyć tymczasowej tabeli, czy zmiennej tabeli, jest wynikiem kompromisu. Zmienne tabeli używane w procedurach składowanych powodują mniejszą liczbę ponownych kompilacji procedury składowanej niż tymczasowe tabele. W zależności od aplikacji, użycie tymczasowej tabeli zamiast zmiennej tabeli może nie poprawić wydajności.

• Jeśli procedura składowana zawiera zapytanie, w którym jest używany przekazany parametr, należy unikać zmieniania wartości parametru w procedurze składowanej przed użyciem go w zapytaniu. Szacowania kardynalności dla zapytania są oparte na przekazanej wartości parametru, a nie na zaktualizowanej wartości. Aby uniknąć zmiany wartości parametru, można ponownie napisać zapytanie, tak aby były w nim używane dwie procedury składowane.

  Na przykład procedura składowana Sales.GetRecentSales zmienia wartość parametru @date, gdy jest spełniony warunek @date is NULL.

  USE AdventureWorks2012;
  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
  GO

  Jeśli pierwsze wywołanie procedury składowanej Sales.GetRecentSales przekaże dla parametru @date wartość NULL, optymalizator zapytań skompiluje procedurę składowaną z użyciem szacowania kardynalności dla wartości @date = NULL, nawet jeśli predykat zapytania nie jest wywoływany z wartością @date = NULL. Szacowanie kardynalności może być znacząco inne niż liczba wierszy w rzeczywistych wynikach zapytania. W wyniku tego optymalizator zapytań może wybrać nieoptymalny plan zapytania. Aby uniknąć tego problemu, można ponownie napisać procedurę składowaną, dzieląc ją na dwie procedury, tak jak pokazano poniżej:

  USE AdventureWorks2012;
  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

Aby ulepszyć szacowania kardynalności dla zmiennych lokalnych, można użyć wskazówki zapytania OPTIMIZE FOR lub OPTIMIZE FOR UNKNOWN z opcją RECOMPILE. Aby uzyskać więcej informacji, zobacz temat Wskazówki kwerendy (języka Transact-SQL).

W przypadku niektórych aplikacji ponowne kompilowanie zapytania w czasie wykonywania może zajmować zbyt dużo czasu. Użycie wskazówki OPTIMIZE FOR może być pomocne, nawet jeśli nie jest używana opcja RECOMPILE. Na przykład można dodać opcję OPTIMIZE FOR do procedury składowanej Sales.GetRecentSales w celu określenia konkretnej daty. W poniższym przykładzie dodano opcję OPTIMIZE FOR do procedury Sales.GetRecentSales.

USE AdventureWorks2012;
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

W przypadku niektórych aplikacji wytyczne dotyczące projektowania zapytań mogą nie mieć zastosowania, ponieważ nie będzie można zmienić zapytania albo użycie wskazówki zapytania RECOMPILE będzie powodować zbyt dużą liczbę operacji ponownego kompilowania. Za pomocą przewodników planu można określić inne wskazówki, takie jak USE PLAN, w celu kontrolowania zachowania zapytania podczas analizowania zmian w aplikacji wraz z jej dostawcą. Aby uzyskać więcej informacji dotyczących przewodników planu, zobacz temat Plan Guides.

Powrót do początku