Opcje sesji SET

SQL Server przechowuje dane na stronach. Strona jest jednostką fizyczną na dysku w bazie danych SQL Server. Wielkość strony jest pomiędzy 8,192B do 8KB. Strony należą tylko do jednego obiekt bazy danych np pojedynczej tabeli lub indeksu. Strony są pogrupowane w logiczne grupy po 8 stron zwane zakresami (ang extents). Zakresy mogą być mieszane, strony zakresu mogą należeć do wielu obiektów  lub być jednolite kiedy wszystkie strony należą do jednego obiektu. Aby zapytanie było wydajne zależy nam na jak najmniejszym odczycie i zapisie danych na dysku czyli chcemy aby SQL Server musiał odczytać jak najmniej stron. Aby dostać informacje o liczbie wierszy w tabeli których  używa zapytanie należy włączyć statystyki I/O poprzez komendę sesji T-SQL:

SET STATISTICS IO ON

Jest to komenda sesji ponieważ zostaje ona niezmieniona aż do zakończenia sesji, rozłączenia z serwerem lub wyłączenia jej. Poniższe zapytanie sprawdza liczbę stron tabel Sales.Customers i Sales.Orders w bazie TSQL2012:

DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SELECT * FROM Sales.Customers;
SELECT * FROM Sales.Orders;

Wynik z zakładki Messages:

DBCC execution completed. If DBCC printed error messages, contact your system administrator. (91 row(s) affected) Table 'Customers'. Scan count 1, logical reads 5, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (830 row(s) affected) Table 'Orders'. Scan count 1, logical reads 21, physical reads 1, read-ahead reads 20, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Komenda DBCC DROPCLEANBUFFERS czyści dane z pamięci podręcznej. SQL Server zapisuje do pamięci dane tak samo jak zapytania i plany wykonania. Aby pobrać wiarygodne statystyki trzeba wyczyścić pamięć podręczną. Wartości zwrócone przez zapytanie oznaczają:

  • Scan count - liczba wykonanych skanów indeksu lub tabeli.
  • Logical reads - Liczba stron czytana z pamięci podręcznej danych. Kiedy odczytujemy dane całej tabeli jak w powyższych zappytaniach to liczba ta daje szacunkową wielkość tabeli.
  • Physical reads - liczba stron odczytanych z dysku. Ta liczba jest mniejsza niż aktualna liczba stron ponieważ wiele stron jest zapisanych w pamięci podręcznej.
  • Lob logical reads - liczba stron wielkich obiektów (LOB) odczytanych z pamięci podręcznej danych. LOB to kolumny typu VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTILE, IMAGE, XML lub duże typy CLR zawierające typy geometryczne i geograficzne GEOMETRY i GEOGRAPHY.
  • Lob physical reads - liczba stron dużych obiektów odczytanych z dysku.
  • Lob read-ahead reads - liczba stron dużych obiektów które SQL Serwer czyta dalej.

Pierwszym punktem analizy jest Logical reads. Daje on pierwszą orientacyjną skuteczność zapytania. Jednakże nie należy korzystać z tej wiedzy bez dodatkowej wiedzy. Rozważmy dwa zapytania:

SELECT C.custid
,C.companyname
,O.orderid
,O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O ON C.custid = O.custid

SELECT C.custid
,C.companyname
,O.orderid
,O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O ON C.custid = O.custid
WHERE O.custid < 5

Statystyki:

(830 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (30 row(s) affected) Table 'Customers'. Scan count 0, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Statystyki IO dla pierwszego zapytania pokazały tylko 2 logiczne odczyty dla tabeli Sales.Customers, mimo to pokazały 60 logicznych odczytów dla drugiego zapytania. Drugie zapytanie filtruje wiersze klientów z identyfikatorem mniejszym niż 5 więc czy nie powinno odczytywać mniej IO? SQL Server zlicza każde dotknięcie tabeli, nawet jeżeli jest już ona w pamięci podręcznej. Kiedy strony są w pamięci podręcznej to pobranie ich kosztuje mało. Wyjaśnieniem tej sytuacji jest to, że SQL Server użył algorytmu złączenia NESTED LOOPS w drugim zapytaniu a tabela Sales.Customers była wewnętrzną tabela tego złączenia i jej strony były odczytywane z pamięci podręcznej. W tym przykładzie widać, że statystyki IO nie są wystarczającą informacją w czasie badania wydajności zapytania. Kolejną komendą SET użyteczną w czasie analizy wydajności jest komenda :

SET STATISTICS TIME ON

Wykonajmy pierwszy przykład z włączoną opcją SET STATISTICS TIME:

SET STATISTICS TIME ON;
DBCC DROPCLEANBUFFERS;

SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid;

Przykładowy wynik:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. (830 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 49 ms.

Wynik statystyk czasu zwraca czas wykonania CPU i całkowity czas potrzebny dla operacji oraz czasy parsowania i kompilacji zapytania. Porównajmy wyniki z drugim zapytaniem:

DBCC DROPCLEANBUFFERS;
SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.custid < 5;

Wynik statystyk:

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (30 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2 ms.

Teraz widac, że drugie zapytanie jest szybsze. Istnieje jeszcze więcej narzędzi do śledzenia jak SQL Server wykonuje zapytania.

Execution Plans

Analizując plan wykonania można otrzymać najbardziej wyczerpujące informacje o tym jak zapytanie jest wykonywane. SQL Serwer udostępnia plany szacunkowe (ang estimated) i rzeczywiste (ang actual). jeżeli analizujesz szacunkowy plan to zapytanie nie jest wykonywane. Zwykle plan szacunkowy i rzeczywisty są takie same, jednakże istnieją sytuacje gdzie plan szacunkowy nie może dać kompletnych informacji takich jak plan rzeczywisty. Przykładowo gdy tworzymy zapytanie z użyciem tabeli tymczasowej stworzonej w tej samej porcji kodu T-SQL to SQL Serwer nie może zoptymalizować dostępu do tej tabeli ponieważ nie jest ona jeszcze utworzona. Podobnie jest z kodem dynamicznym ponieważ SQL Serwer nie wie jakie zapytanie ma zoptymalizować do czasu aż zostanie ono wykonane. Jednakże dla dużych zapytań które są wykonywane przez długi czas szacunkowy plan wykonania może być bardzo użyteczny ponieważ prawdopodobnie odczyt milionów rekordów po to aby uzyskać plan wykonania byłby niepożądany. Plan wykonania zarówno szacunkowy jak i aktualny można pobrać w trzech formach: jako tekst, jako XML lub graficznie. SQL Serwer oryginalnie zwraca plan wykonania w formie XML. Prezentacja tekstowa jest przedawniona i będzie usunięta w przyszłych wersjach SQL Serwera. Pobieranie planu wykonania można włączyć za pomocą komendy SET sesji T-SQL:

  • SET SHOWPLAN_TEXT i SET SHOWPLAN_ALL dla planów szacunkowych.
  • SET STATISTICS PROFILE dla planów aktualnych.

Włączanie planu w formie XML odbywa się za pomocą komend:

  • SET SHOWPLAN_XML dla planów szacunkowych.
  • SET STATISTICS XML dla planów aktualnych.

Generowanie planów graficznych w SSMS można włączyć za pomocą:

  • Wciśnięcie prawego przycisku myszy w oknie Query Editor i wybranie opcji Display Estimated Execution Plan lub Include Actual Execution Plan.
  • Użycie skrótów klawiszowych Ctrl+L dla Display Estimated Execution Plan lub CTRL+M dla Include Actual Execution Plan.
  • Wybór opcji z menu Query.
  • Naciśnięcie guzików na pasku narzędzi.

Oto przykład zapytania oraz prezentacji graficznej jego planu wykonania:

SELECT C.custid, MIN(C.companyname) AS companyname,
COUNT(*) AS numorders
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.custid < 5
GROUP BY C.custid
HAVING COUNT(*) > 6;

ep Na graficznej prezentacji planu wykonania możemy zobaczyć fizyczne operatory użyte podczas wykonania. Plan wykonania czytamy od prawej do lewej, od góry do dołu. SQL Serwer startuje wykonywanie zapytania od przeszukania indeksu klastrowego na tabeli Sales.Customers po czym wykonuje przeszukanie indeksu nieklastrowego na tabeli Sales.Orders. Później SQL Serwer łączy wyniki poprzednich operacji złączeniem Nested Loops itd. Na planie wykonania można również odczytać relatywny koszt każdego operatora jako procent całkowitego kosztu zapytania. Strzałki pokazują przepływ danych między operatorami fizycznymi. Grubość strzałek odnosi się do relatywnej ilości wierszy przekazywanych między operatorami. najeżdżając kursorem myszki na strzałkę lub operator możemy podejrzeć więcej detali operacji. Poniższe zdjęcie pokazuje detale operacji Nested Loops. nl W widoku detali operacji możemy zobaczyć użyte operatory fizyczne i logiczne, szacunkową liczbę wierszy oraz aktualną liczbę wierszy. Dzięki temu możemy szybko zauważyć błędy w oszacowaniu liczności. Możemy również zobaczyć szacowany koszt operatorów i szacowany koszt kompletnego poddrzewa do tego punktu. Koszt operatora jest używany do wyliczenia procentowego udziału w całej operacji. szczegóły każdego operatora możemy również w oknie właściwości poprzez naciśnięcie guzika F4. Plan wykonania można zapisać aby móc przeanalizować go później. Plany są zapisywane w ich domyślnym formacie XML w plikach z rozszerzeniem sqlplan. Wszystkie możliwe operatory planu wykonania można znaleźć na stronie http://msdn.microsoft.com/en-us/library/ms175913%28v%3DSQL.105%29.aspx.

Ćwiczenia

I. Utworzenie tabeli z danymi i indeksem nieklastrowym.

  1. Wykonaj poniższe zapytanie w bazie danych TSQL2012 tworzące tabelę i wypełniające ją danymi: 
    SELECT N1.n * 100000 + O.orderid AS norderid,
O.*
INTO dbo.NewOrders
FROM Sales.Orders AS O
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),
(17),(18),(19),(20),(21),(22),(23),
(24),(25),(26),(27),(28),(29),(30)) AS N1(n);
  2. Wykonaj poniższe zapytanie tworzące indeks nieklastrowy: 
    CREATE NONCLUSTERED INDEX idx_nc_orderid
ON dbo.NewOrders(orderid);

II. Analiza zapytań.

  1. Włącz statystyki IO i czasowe w nowym oknie zapytania: 
    SET STATISTICS IO ON;
SET STATISTICS TIME ON;
  2. Wykonaj poniższe zapytanie: 
    SELECT norderid
FROM dbo.NewOrders
WHERE norderid = 110248
ORDER BY norderid;
  3. Przeanalizuj czasy wykonania i kompilacji oraz liczbę odczytów logicznych. Zapamiętaj statystyki i wyłącz ich wyświetlanie zapytaniem: 
    SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
  4. Wykonaj zapytanie z włączony aktualnym planem wykonania: 
    SELECT norderid
FROM dbo.NewOrders
WHERE norderid = 110248
ORDER BY norderid;
  5. Podejrzyj plan zapytania i utwórz brakujący indeks: 
    CREATE NONCLUSTERED INDEX idx_nc_norderid
ON dbo.NewOrders(norderid);
  6. Wykonaj zapytanie jeszcze raz z włączonymi statystykami i porównaj je: 
    SELECT norderid
FROM dbo.NewOrders
WHERE norderid = 110248
ORDER BY norderid;

Podsumowanie

  1. Do analizy zapytań możemy użyć szeregu opcji sesji SET.
  2. Aby podejrzeć detale planu wykonania możemy użyć graficznego planu wykonania.
  3. Plan wykonania może być szacunkowy lub aktualny.