Lekcja 41. Statystyki

Wykonując zadania w poprzednich lekcjach można zastanawiać się skąd SQL Serwer wie czy zapytanie jest odpowiednio selektywne aby użyć indeksu do wyszukania wierszy. SQL Serwer gromadzi wiedzę w postaci statystyk rozkładu kluczowych wartości w specjalnym systemie stron statystycznych. Optymalizator zapytań używa tych statystyk do oszacowania liczności czy też liczby wierszy w zbiorze wynikowym.

Automatyczne tworzenie statystyk

Domyślnie SQL Serwer tworzy statystyki automatycznie. Tworzy je dla każdego indeksu i dla pojedynczych kolumn używanych jako argument wyszukiwania w zapytaniach. Istnieją trzy opcje w bazie danych które mają wpływ na tworzenie statystyk:

AUTO_CREATE_STATISTICS - kiedy ta opcja jest ustawiona na ON to SQL serwer tworzy statystyki automatycznie. Jest to opcja domyślna i w większości przypadków nie powinna być zmieniana.
AUTO_UPDATE_STATISTICS - kiedy ta opcja jest ustawiona na ON to SQL serwer automatycznie aktualizuje statystyki kiedy w tabelach i indeksach powstają odpowiednie zmiany. Dzięki tej opcji SQL Serwer aktualizuje również nieaktualne statystyki w trakcie optymalizacji zapytania. SQL Serwer sprawdza nieaktualne statystyki przed skompilowaniem zapytania i przed wykonaniem zapytania zapisanego w pamięci podręcznej. Generalnie ta opcja powinna być włączona.
AUTO_UPDATE_STATISTICS_ASYNC - Ta opcja określa, czy SQL Serwer używa synchronicznych lub asynchronicznych aktualizacji statystyk podczas optymalizacji zapytań. Jeżeli statystyki są aktualizowane asynchronicznie to SQL Serwer nie może użyć ich do optymalizacji zapytania które spowodowało aktualizację; jednakże SQL Serwer nie czeka na aktualizację statystyk w czasie fazy optymalizacji. Tę opcję powinno włączać się w przypadku gdy zapytania czekają na synchroniczne aktualizacje statystyk za często i przez co powodują problemy wydajnościowe.

Każda statystyka jest obiektem i jest przechowywana w statystycznym binarnym wielkim obiekcie i jest utworzona na jednej lub więcej kolumn. Statystyki zawierają histogram z rozkładem wartości w pierwszej kolumnie. Obiekty statystyk na wielu kolumnach dodatkowo zawierają informację o statystycznej korelacji wartości między kolumnami. Te statystyki korelacji są również nazywane gęstościami. Pochodzą one z wielu różnych kombinacji wartości kolumn indeksu kompozytowego. Istnieje limit liczby kroków w histogramie. Statystyka może posiadać maksymalnie 200 kroków. Obiekt statystyki zawiera również nagłówek z metadanymi o statystyce oraz wektor gęstości do pomiaru korelacji między kolumnami. SQL Serwer oblicza szacowaną liczbę wierszy, które są zwracane przez zapytanie lub oszacowaną liczność z jakichkolwiek danych w statystykach obiektu. Informacje o statystykach można otrzymać odpytując widoki systemowe sys.stats i sys.stats_columns. Możesz uzyskać szczegółowe informacje na temat danych statystycznych używając komendy DBCC SHOW_STATISTICS. Statystyki mogą zarządzane ręcznie z pomocą komend CREATE STATISTIC, DROP STATISTIC i UPDATE STATISTIC. Można również użyć procedury systemowej sys.sp_updatestats do manualnej aktualizacji statystyk dla wszystkich tabel w bazie danych. Poniższy przykład używa kursora który wykonuje pętlę na widoku sys.stats z automatycznie utworzonymi statystykami na kolumnach które nie są używane jako klucz indeksu dla tabeli Sales.Orders. Zapytanie dynamicznie tworzy komendę do usuwania statystyk i je usuwa:

DECLARE @statistics_name AS NVARCHAR(128)
,@ds AS NVARCHAR(1000);
DECLARE acs_cursor CURSOR
FOR
SELECT NAME AS statistics_name
FROM sys.stats
WHERE object_id = OBJECT_ID(N'Sales.Orders', N'U')
AND auto_created = 1;
OPEN acs_cursor;
FETCH NEXT
FROM acs_cursor
INTO @statistics_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ds = N'DROP STATISTICS Sales.Orders.' + @statistics_name + ';';
EXEC (@ds);
FETCH NEXT
FROM acs_cursor
INTO @statistics_name;
END;
CLOSE acs_cursor;
DEALLOCATE acs_cursor;

Po wykonaniu zapytania powinny pozostać jedynie statystyki dla indeksów. pokazuje to poniższe zapytanie:

SELECT OBJECT_NAME(object_id) AS table_name
,NAME AS statistics_name
,auto_created
FROM sys.stats
WHERE object_id = OBJECT_ID(N'Sales.Orders', N'U');

Wynik:

table_name statistics_name auto_created ---------- --------------------- ----------- Orders PK_Orders 0 Orders idx_nc_custid 0 Orders idx_nc_empid 0 Orders idx_nc_shipperid 0 Orders idx_nc_orderdate 0 Orders idx_nc_shippeddate 0 Orders idx_nc_shippostalcode 0

Kolumna auto_created przyjmuje wartość 1 dla statystyk które SQL Serwer wygenerował automatycznie dla pojedynczych kolumn używanych jako argumenty wyszukiwania w czasie wykonywania zapytań. przed sprawdzeniem statystyki dla indeksu idx_nc_empid wykonajmy jego przebudowę aby upewnić się, że statystyki zostaną zaktualizowane:

ALTER INDEX idx_nc_empid ON Sales.Orders REBUILD;

Poniższe zapytanie pokazuje histogram dla statystyk indeksu idx_nc_empid:

DBCC SHOW_STATISTICS(N'Sales.Orders',N'idx_nc_empid') WITH HISTOGRAM;

Wynik:

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ------------ ------------- ------------- -------------------- -------------- 1 0 123 0 1 2 0 96 0 1 3 0 127 0 1 4 0 156 0 1 5 0 42 0 1 6 0 67 0 1 7 0 72 0 1 8 0 104 0 1 9 0 43 0 1 DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Istnieje tylko 9 stopni histogramu ponieważ kolumna empid posiada tylko 9 unikalnych wartości. Jeżeli wykonamy komendę DBCC SHOW_STATISTICS bez opcji WITH HISTOGRAM to otrzymamy wszystkie informacje o statystykach w tym również nagłówek i wektor gęstości. Z nagłówka można pobrać użyteczne informacje jak np kiedy statystyka była ostatnio aktualizowana:

DBCC SHOW_STATISTICS(N'Sales.Orders',N'idx_nc_empid') WITH STAT_HEADER;

Wynik:

Name Updated Rows Rows Sampled Steps Density Average key length ------------ ------------------- ---- ------------ ----- ------- ------------------ idx_nc_empid Mar 24 2012 1:57PM 830 830 9 0 8

Informację o ostatniej aktualizacji statystyk można pobrać również używając funkcji STATS_DATE(). Jak zostało wspomniane SQL Serwer tworzy automatycznie statystyki dla kolumn wyszukiwanych w czasie wykonywania zapytania. Aby to przetestować zacznijmy od dodania nieklastrowanego indeksu do tabeli Sales.Orders z kolumnami custid i shipcity:

CREATE NONCLUSTERED INDEX idx_nc_custid_shipcity ON Sales.Orders(custid, shipcity);

Poniższe zapytanie pobierze trzy wiersze dla klienta z identyfikatorem 42:

SELECT orderid, custid, shipcity
FROM Sales.Orders
WHERE custid = 42;

Sprawdźmy czy istnieją automatycznie utworzone statystyki:

SELECT OBJECT_NAME(object_id) AS table_name,
name AS statistics_name
FROM sys.stats
WHERE object_id = OBJECT_ID(N'Sales.Orders', N'U')
AND auto_created = 1;

Zapytanie zwraca zero wierszy, oznacza to, że nie ma automatycznie utworzonych statystyk. SQL Serwer miał wystarczające informacje w indeksie idx_nc_custid_shipcity. Pobierzmy te same trzy wiersze lecz użyjmy kolumn shipcity do filtracji wierszy:

SELECT orderid, custid, shipcity
FROM Sales.Orders
WHERE shipcity = N'Vancouver';

Poniższe zapytanie sprawdza czy istnieją statystyki utworzone automatycznie i dodatkowo dodaje informacje o kolumnach dla których statystyki zostały utworzone:

SELECT OBJECT_NAME(s.object_id) AS table_name
,S.NAME AS statistics_name
,C.NAME AS column_name
FROM sys.stats AS S
INNER JOIN sys.stats_columns AS SC ON S.stats_id = SC.stats_id
INNER JOIN sys.columns AS C ON S.object_id = C.object_id
AND SC.column_id = C.column_id
WHERE S.object_id = OBJECT_ID(N'Sales.Orders', N'U')
AND auto_created = 1;

Wynik:

table_name statistics_name column_name ---------- ------------------------- ----------- Orders _WA_Sys_0000000B_20C1E124 shipcity

Jak widać SQL Serwer stworzył statystykę dla kolumny shipcity. Nazwy automatycznie utworzonych statystyk zaczynają się od _WA_Sys_. Usuńmy dodany indeks aby wyczyścić zmiany:

DROP INDEX idx_nc_custid_shipcity ON Sales.Orders;

Manualne zarządzanie statystykami

Istnieje kilka powodów dla tworzenia statystyk manualnie. Pierwszym przykładem jest sytuacja kiedy predykat w zapytaniu zawiera wiele kolumn które posiadają powiązania między kolumnami, statystyki na wielu kolumnach mogą poprawić plan zapytania. Statystyki wielokolumnowe zawierają gęstości między kolumnami które nie są dostępne dla statystyk jednokolumnowych. jeżeli kolumny należą do tego samego indeksu to wielokolumnowa statystyka już istnieje i nie powinno się tworzyć dodatkowej. Podobnie do indeksów filtrowanych, można stworzyć statystyki filtrowane. Automatycznie tworzone statystyki są zawsze tworzone dla wszystkich wierszy tabeli. Jeżeli zapytanie często pobiera podzbiór wierszy które posiadają uniklny rozkład to statystyka filtrowana może poprawić plan zapytania. Czasem możemy otrzymać ostrzeżenie w planie wykonania o braku wyszczególnionej statystyki. Taką statystykę możemy stworzyć manualnie. Jednakże przed jej utworzeniem należy sprawdzić czy opcje AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS są włączone i czy baza nie jest w stanie tylko do odczytu. Jeżeli baza jest tylko do odczytu to optymalizator zapytań nie może zapisać statystyk. Manualną aktualizację statystyk należy rozważać gdy:

Czas wykonania zapytania jest długi i wiesz, że zapytanie jest napisane poprawnie i wspierane przez odpowiednie indeksy. Przed użyciem wskazówek zapytań (ang. query hints), zaktualizuj statystyki. SQL Serwer nie rozważa użycia indeksu z przestarzałymi statystykami.
Podczas operacji wstawiania występującej na rosnących lub malejących kolumnach kluczowych. Statystyki nie są aktualizowane dla każdego wiersza, dlatego liczba wstawionych wierszy może być za mała aby uruchomić aktualizację statystyk. jeżeli zapytania pobierają dane z nowo dodanych wierszy to aktualne statystyki mogą mieć złą oszacowaną liczność dla tych wartości. Dodatkowo wstawianie masowe (ang. bulk insert) i czyszczenie tabeli (ang. truncate) może mieć duży wpływ na rozkład danych. zapytania wykonywane po tych operacjach mogą posiadać nieoptymalne plany wykonania ponieważ statystyki nie zostały jeszcze zaktualizowane.
Po aktualizacji wersji SQL Serwera.

Ćwiczenia

Wyłączenie automatycznego tworzenia statystyk

Otwórz nowe okno SSMS i połącz się do instancji serwera. Zmień kontekst na bazę danych TSQL2012:
```
USE TSQL2012
GO
```

Użyj poniższego skryptu do usunięcia wszystkich automatycznie stworzonych statystyk w tabeli Sales.Orders:

DECLARE @statistics_name AS NVARCHAR(128)
	,@ds AS NVARCHAR(1000);
DECLARE acs_cursor CURSOR
FOR
SELECT NAME AS statistics_name
FROM sys.stats
WHERE object_id = OBJECT_ID(N'Sales.Orders', N'U')
	AND auto_created = 1;
OPEN acs_cursor;
FETCH NEXT
FROM acs_cursor
INTO @statistics_name;
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @ds = N'DROP STATISTICS Sales.Orders.' + @statistics_name + ';';
	EXEC (@ds);
	FETCH NEXT
	FROM acs_cursor
	INTO @statistics_name;
END;
CLOSE acs_cursor;
DEALLOCATE acs_cursor;

Wyłącz automatyczne tworzenie statystyk dla bazy danych TSQL2012:

ALTER DATABASE TSQL2012
SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT;

Efekt wyłączenia automatycznego tworzenia statystyk.

Dodaj kompozytowy nieklastrowany indeks do tabeli Sales.Orders używając kolumn custid i shipcity:
```
CREATE NONCLUSTERED INDEX idx_nc_custid_shipcity
ON Sales.Orders(custid, shipcity);
```
Włącz pokazywanie aktualnego planu wykonania. Użyj zapytania pobierającego zamówienia dla miasta Vancouver:
```
SELECT orderid, custid, shipcity
FROM Sales.Orders
WHERE shipcity = N'Vancouver';
```
Przeanalizuj plan wykonania. Powinieneś otrzymać skanowanie indeksu klastrowanego z ostrzeżeniem na jego operatorze. Klikając F4 można sprawdzić tekst ostrzeżenia w oknie właściwości.

Sprawdź czy jakiekolwiek automatycznie utworzone statystyki istnieją w tabeli Sales.Orders:

SELECT OBJECT_NAME(object_id) AS table_name,
name AS statistics_name
FROM sys.stats
WHERE object_id = OBJECT_ID(N'Sales.Orders', N'U')
AND auto_created = 1;

Zapytanie nie zwróciło żadnego wiersza. Stwórzmy brakującą statystykę manualnie. Dodatkowo wyczyścimy plan wykonania z pamięci podręcznej:
```
CREATE STATISTICS st_shipcity ON Sales.Orders(shipcity);
DBCC FREEPROCCACHE;
```

Użyj zapytania pobierającego zamówienia dla miasta Vancouver jeszcze raz:

SELECT orderid, custid, shipcity
FROM Sales.Orders
WHERE shipcity = N'Vancouver';

Sprawdź plan wykonania. tym razem nie powinno być żadnych ostrzeżeń.

wyczyść zmiany poprzez włączenie automatycznego tworzenia statystyk, aktualizację statystyk i usunięcie utworzonej statystyki:

ALTER DATABASE TSQL2012
SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT;
EXEC sys.sp_updatestats;
DROP STATISTICS Sales.Orders.st_shipcity;
DROP INDEX idx_nc_custid_shipcity ON Sales.Orders;

Podsumowanie

SQL Serwer używa statystyk do określania liczności zapytań.
SQL Serwer zarządza statystykami automatycznie. Można również zarządzać nimi manualnie.