Zapytania sparametryzowane

SQL Server automatycznie parametryzuje zapytania ad hoc. Jednakże SQL Serwer jest bardzo zachowawczy w ponownym używaniu planu zapytania. Nie chce aby używany był zły plan. SQL Serwer decyduje się na użycie planu tylko wtedy gdy jest pewien, że plan zapisany w pamięci jest poprawny dla danego zapytania. Zmiany w typie danych parametru, w opcjach SET, w kontekście bezpieczeństwa i więcej mogą produkować nowy plan kiedy można spodziewać się ponownego użycia planu zapisanego. Informacje o zapisanych planach wykonania i liczbie użyć danego planu można odczytać z funkcji dynamicznej sys.dm_exec_query_stats. Można również pobrać dokładny plan zapytania z funkcji dynamicznej sys.dm_exec_sql_text. Aby wyczyścić pamięć podręczną z zapisanymi planami wykonania można użyć komendy DBCC FREEPROCCACHE. Komenda DBCC FREEPROCCACHE jest bardzo użyteczna w środowiskach testowych lecz należy być ostrożnym w środowisku produkcyjnym. Jeżeli użyjemy tej komendy w środowisku produkcyjnym to SQL Serwer musi skompilować wszystkie następne zapytania, procedury, funkcje i wyzwalacze. Może to mieć negatywny wpływ na wydajność środowiska. Poniższe trzy zapytania pobierają po jednym zamówieniu z tabeli Sales.Orders. Wszystkie używają kolumny orderid jako parametr predykatu w klauzuli WHERE. Tabela posiada założony klucz główny na tej kolumnie. Dlatego SQL Serwer wie, że każde z zapytań zwraca jeden wiersz. Jednakże pierwsze dwa zapytania używają typu danych liczbowego całkowitego dla parametru a trecie zapytanie typu zmiennoprzecinkowego.

-- Parameter INT
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10248;
-- Parameter INT
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10249;
-- Parameter DECIMAL
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10250.0;

Można sprawdzić plany zapisane w pamięci podręcznej i liczbę ich wykonań używając poniższego zapytania nazywanego dalej w tej lekcji jako "sprawdzenie ponownego użycia planu":

SELECT qs.execution_count AS cnt,
qt.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE N'%Orders%'
AND qt.text NOT LIKE N'%qs.execution_count%'
ORDER BY qs.execution_count;

Wynik:

cnt text --- ---- 1 (@1 numeric(6,1))SELECT [orderid],[custid],[empid],[orderdate] FROM [Sales].[Orders] WHERE [orderid]=@1 2 (@1 smallint)SELECT [orderid],[custid],[empid],[orderdate] FROM [Sales].[Orders] WHERE [orderid]=@1

Jak widać SQL Serwer sparametryzował zapytania. Plan dla parametru liczbowego całkowitego został użyty ponownie. Dla parametru zmiennoprzecinkowego  został wygenerowany nowy plan. Aby zobrazować zachowawcze zachowanie SQL Serwera w ponownym użyciu planu wykonania przedstawione są poniższe trzy zapytania używające kolumny custid w klauzuli WHERE. Pierwsze zapytanie zwraca jeden wiersz, drugie zwraca dwa wiersze a trzecie 31 wierszy. SQL Serwer nie potrafi oszacować selektywności kolumny custid tabeli Sales.Orders.

-- One row
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = 13;
GO
-- Two rows
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = 33;
GO
-- 31 rows
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = 71;
GO

Wykonanie tych zapytań z uprzednim wyczyszczeniem pamięci podręcznej daje następujący wynik użycia ponownego planów zapytań:

cnt text --- ---- 1 SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 33; 1 SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 13; 1 SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 71;

Na ponowne wykorzystanie planów wykonania ma również wpływ również zmiana opcji SET. Poniższe zapytania są powtórzone z pierwszego przykładu w którym plan został ponownie wykorzystany, jednakże między nimi wykonywana jest zmiana opcji SET:

-- Query that is parameterized
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10248;
-- Changing a SET option
SET CONCAT_NULL_YIELDS_NULL OFF;
-- Query that could use the same plan
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10249;
-- Restoring the SET option
SET CONCAT_NULL_YIELDS_NULL ON;

Jeżeli przed wykonaniem zapytań wyczyścimy pamięć podręczną to wynik wykorzystania planów wykonania będzie następujący:

cnt text --- ---- 1 (@1 smallint)SELECT [orderid],[custid],[empid],[orderdate] FROM [Sales].[Orders] WHERE [orderid]=@1 1 (@1 smallint)SELECT [orderid],[custid],[empid],[orderdate] FROM [Sales].[Orders] WHERE [orderid]=@1

Pomimo tego, że SQL Serwer sparametryzował zapytania to nie użył ponownie planu wykonania. Można pomyśleć, że SQL Serwer jest za bardzo zachowawczy w ponownym wykorzystywaniu planów zapytań. Jednakże sytuacja w rzeczywistości nie jest taka zła. Większość zapytań pochodzi zwykle z aplikacji które generują zwykle zapytania w taki sam sposób. Dodatkowo można pomóc SQL Serwerowi używając procedury systemowej sys.sp_executesql do wykonywania sparametryzowanego dynamicznego kodu SQL. Użycie dynamicznych sparametryzowanych zapytań można uważać za lepszą praktykę niż wykonywanie zapytań ad hoc. Poniższy przykład używa sparametryzowanego zapytania wykonywanego dwukrotnie z różnymi parametrami. W pierwszym wykonaniu parametr jest typu liczbowego całkowitego a w drugim typu zmiennoprzecinkowego:

DECLARE @v INT;
DECLARE @s NVARCHAR(500);
DECLARE @p NVARCHAR(500);
-- Build the SQL string
SET @s = N'
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid';
SET @p = N'@orderid INT';
-- Parameter integer
SET @v = 10248;
EXECUTE sys.sp_executesql @s, @p, @orderid = @v;
-- Parameter decimal
SET @v = 10249.0;
EXECUTE sp_executesql @s, @p, @orderid = @v;

Wynik wykorzystania planów wykonania:

cnt text --- ---- 2 (@orderid INT) SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderid = @orderid

Pomimo tego, że użyty został parametr zmiennoprzecinkowy to SQL Serwer wiedział, że parametr jest liczbą całkowitą ponieważ tak został zadeklarowany w procedurze sys.sp_executesql. Oczywiście wartość parametru musi być niejawnie konwertowana na typ całkowity. Użycie dynamicznego kodu SQL nie jest dobrą praktyką. Aby ponownie wykorzystywać plan wykonania powinno się używać obiektów programistycznych jak procedury składowane. Poniższe zapytanie tworzy procedurę która opakowuje zapytanie pobierające pojedyncze zamówienie:

CREATE PROCEDURE Sales.GetOrder
(@orderid INT)
AS
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid;

Wykonajmy procedurę dwukrotnie używając parametrów o typie całkowitym i zmiennoprzecinkowym:

EXEC Sales.GetOrder @orderid = 10248;
EXEC Sales.GetOrder @orderid = 10249.0;

Wynik wykorzystania planów wykonania:

cnt text --- ---- 2 CREATE PROCEDURE Sales.GetOrder (@orderid INT) AS SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderid = @orderid;

Plan został ponownie wykorzystany. Procedury składowane egekwują ponowne wykorzystanie planu wykonania. Jednakże czasami chcemy aby wykonanie procedury składowanej nie używało planu zapisanego w pamięci. procedura może zwracać różną ilość wierszy w zależności od wartości parametru. Dla pewnych wartości parametru zapytanie w środku procedury może być bardzo selektywne a dla innych wartości nie. W takich wypadkach chcemy mieć różne plany wykonania dla różnych wywołań. Można wymusić aby procedury składowane były ponownie kompilowane za pomocą opcji WITH RECOMPILE. Dodatkowo można wymusić ponowną kompilację tylko krytycznych wyrażeń procedury. Aby wyczyścić bazę danych wykonaj kod:

DROP PROCEDURE Sales.GetOrder;

Przetwarzanie wsadowe

SQL Serwer używa 3 podstawowych algorytmów złączeń. Złączenia hash join mogą być optymalizowane poprzez podział na więcej mniejszych złączeń które są wykonywane w wielu wątkach oraz wczesną eliminację wierszy z większej tabeli z użyciem filtrów bitmap. Jest to używane głównie w scenariuszach hurtowni danych gdzie używa się dużych tabel lecz jednocześnie konkuruje ze sobą o dostęp mała ilość użytkowników. Takie złączenia są czasami nazywane złączeniami gwiazdowymi (ang. star join). Nazwa pochodzi od typowego schematu tabel w hurtowniach danych gdzie w środku znajduje sie tabela centralna z wieloma złączeniami to tabel ją otaczających. Poniższy kod tworzy cztery tabele w bardzo prostym schemacie gwiazdy i wypełnia je dużą ilością danych.

-- Data distribution settings for DW
DECLARE
@dim1rows AS INT = 100,
@dim2rows AS INT = 50,
@dim3rows AS INT = 200;
-- First dimension
CREATE TABLE dbo.Dim1
(
key1 INT NOT NULL CONSTRAINT PK_Dim1 PRIMARY KEY,
attr1 INT NOT NULL,
filler BINARY(100) NOT NULL DEFAULT (0x)
);
-- Second dimension
CREATE TABLE dbo.Dim2
(
key2 INT NOT NULL CONSTRAINT PK_Dim2 PRIMARY KEY,
attr1 INT NOT NULL,
filler BINARY(100) NOT NULL DEFAULT (0x)
);
-- Third dimension
CREATE TABLE dbo.Dim3
(
key3 INT NOT NULL CONSTRAINT PK_Dim3 PRIMARY KEY,
attr1 INT NOT NULL,
filler BINARY(100) NOT NULL DEFAULT (0x)
);
-- Fact table
CREATE TABLE dbo.Fact
(
key1 INT NOT NULL CONSTRAINT FK_Fact_Dim1 FOREIGN KEY REFERENCES dbo.Dim1,
key2 INT NOT NULL CONSTRAINT FK_Fact_Dim2 FOREIGN KEY REFERENCES dbo.Dim2,
key3 INT NOT NULL CONSTRAINT FK_Fact_Dim3 FOREIGN KEY REFERENCES dbo.Dim3,
measure1 INT NOT NULL,
measure2 INT NOT NULL,
measure3 INT NOT NULL,
filler BINARY(100) NOT NULL DEFAULT (0x),
CONSTRAINT PK_Fact PRIMARY KEY(key1, key2, key3)
);

-- Populating the first dimension
INSERT INTO dbo.Dim1(key1, attr1)
SELECT n, ABS(CHECKSUM(NEWID())) % 20 + 1
FROM dbo.GetNums(1, @dim1rows);
-- Populating the second dimension
INSERT INTO dbo.Dim2(key2, attr1)
SELECT n, ABS(CHECKSUM(NEWID())) % 10 + 1
FROM dbo.GetNums(1, @dim2rows);
-- Populating the third dimension
INSERT INTO dbo.Dim3(key3, attr1)
SELECT n, ABS(CHECKSUM(NEWID())) % 40 + 1
FROM dbo.GetNums(1, @dim3rows);
-- Populating the fact table
INSERT INTO dbo.Fact WITH (TABLOCK)
(key1, key2, key3, measure1, measure2, measure3)
SELECT N1.n, N2.n, N3.n,
ABS(CHECKSUM(NEWID())) % 1000000 + 1,
ABS(CHECKSUM(NEWID())) % 1000000 + 1,
ABS(CHECKSUM(NEWID())) % 1000000 + 1
FROM dbo.GetNums(1, @dim1rows) AS N1
CROSS JOIN dbo.GetNums(1, @dim2rows) AS N2
CROSS JOIN dbo.GetNums(1, @dim3rows) AS N3;

Schemat tabel: Poniższe zapytanie łączy wszystkie cztery tabele i agreguje dane.

-- Measuring IO and time
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Query demonstrating star join
SELECT D1.attr1 AS x, D2.attr1 AS y, D3.attr1 AS z,
COUNT(*) AS cnt, SUM(F.measure1) AS total
FROM dbo.Fact AS F
INNER JOIN dbo.Dim1 AS D1
ON F.key1 = D1.key1
INNER JOIN dbo.Dim2 AS D2
ON F.key2 = D2.key2
INNER JOIN dbo.Dim3 AS D3
ON F.key3 = D3.key3
WHERE D1.attr1 <= 10
AND D2.attr1 <= 15
AND D3.attr1 <= 10
GROUP BY D1.attr1, D2.attr1, D3.attr1;

Zapytanie zostało uruchomione na komputerze z procesorem z ośmioma rdzeniami . SQL Serwer użył 8 logicznych procesorów. Poniżej pokazany jest częściowy plan wykonania tego zapytania. Zauważ, że zapytanie zostało wykonane w wielu wątkach (operator Parallelism), użyty został hash join (operator Hash Match) i bitmap filter został użyty przed wykonaniem złączenia (operator Bitmap). Zauważ we właściwościach operatora złączenia właściwości Actual Execution Mode i Estimated Execution Mode których wartość to Row. Skrócone wyniki STATISTICS IO i STATISTICS TIME:

Table 'Dim2'. Scan count 1, logical reads 2, … Table 'Dim3'. Scan count 1, logical reads 5, … Table 'Dim1'. Scan count 1, logical reads 4, … Table 'Worktable'. Scan count 0, logical reads 0, … Table 'Fact'. Scan count 47, logical reads 8152, … Table 'Worktable'. Scan count 0, logical reads 0, … SQL Server Execution Times: CPU time = 671 ms, elapsed time = 255 ms.

W tabeli dbo.Fact jest ogromna ilość odczytów logicznych. Ponieważ zapytanie zostało wykonane w wielu wątkach to obciążenie procesora jest wysokie i może być wąskim gardłem takiego scenariusza. Jeżeli tabela posiadała by kompresję to SQL Serwer musiałby wykonać dekompresję. Na tabeli można również założyć indeks columnstore. W takim wypadku SQL Serwer musiałby odtworzyć wiersze wyjściowe. Dlatego CPU może być wąskim gardłem w scenariuszach hurtowni danych. SQL Serwer 2012 przynosi rozwiązania problemu obciążenia procesora. Wprowadza operatory które przetwarzają partie wierszy jednocześnie a nie wiersz po wierszu. W ten sposób, procesor musi radzić sobie z metadanymi wierszy tylko raz dla każdej partii. Przetwarzanie wsadowe może być wykorzystywane nie tylko z indeksami columnstore lecz ma z nimi najlepsze wyniki. W niektórych wypadkach SQL Serwer potrafi wykonać operacje wsadowe bezpośrednio na skompresowanych danych. SQL Serwer potrafi używać operatorów wsadowych i wierszowych jednocześnie oraz przełączać się między nimi. Poniższe operatory wspierają użycie przetwarzania wsadowego:

• Filter
• Project
• Scan
• Local hash aggregation
• Hash inner join
• Bath hash table build

Aby przetestować działanie operatorów wsadowych, poniższy kod buduje indeks columnstore na tabeli dbo.Fact:

CREATE COLUMNSTORE INDEX idx_cs_fact
ON dbo.Fact(key1, key2, key3, measure1, measure2, measure3);

Wykonajmy ponownie zapytanie:

-- Measuring IO and time
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Query demonstrating star join
SELECT D1.attr1 AS x, D2.attr1 AS y, D3.attr1 AS z,
COUNT(*) AS cnt, SUM(F.measure1) AS total
FROM dbo.Fact AS F
INNER JOIN dbo.Dim1 AS D1
ON F.key1 = D1.key1
INNER JOIN dbo.Dim2 AS D2
ON F.key2 = D2.key2
INNER JOIN dbo.Dim3 AS D3
ON F.key3 = D3.key3
WHERE D1.attr1 <= 10
AND D2.attr1 <= 15
AND D3.attr1 <= 10
GROUP BY D1.attr1, D2.attr1, D3.attr1;

Poniższy obraz pokazuje część planu wykonania. Zapytanie używa operatora Columnstore Index Scan. Dodatkowo we właściwościach operatora Hash Match można zobaczyć, że używane jest przetwarzanie wsadowe: Skrócone wyniki STATISTICS IO i STATISTICS TIME:

Table 'Dim3'. Scan count 1, logical reads 5, … Table 'Dim2'. Scan count 1, logical reads 2, … Table 'Dim1'. Scan count 1, logical reads 4, … Table 'Fact'. Scan count 8, logical reads 993,… Table 'Worktable'. Scan count 0, logical reads 0, … Table 'Worktable'. Scan count 0, logical reads 0, … SQL Server Execution Times: CPU time = 63 ms, elapsed time = 186 ms.

Jak widać liczba odczytów logicznych z tabeli dbo.Fact jest znacznie mniejsza niż w poprzednim wykonaniu. Wykorzystanie CPU jest również dużo mniejsze, w przybliżeniu 10 razy. Poniższy kod czyści bazę danych:

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
DROP TABLE dbo.Fact;
DROP TABLE dbo.Dim1;
DROP TABLE dbo.Dim2;
DROP TABLE dbo.Dim3;

Ćwiczenia

I. Praca z zapytaniami dla których SQL Serwer nie używa ponownie planu wykonania.

1. Otwórz nowe okno SSMS i ustaw kontekst bazy danych na bazę danych TSQL2012.

   USE TSQL2012
   GO
   

2. Wykonaj poniższe zapytanie i sprawdź plan wykonania.

   SELECT orderid, custid, empid, orderdate
   FROM Sales.Orders
   WHERE custid = 13;
   

   Plan wykonania powinien wykorzystywać operatory Index Seek iKey Lookup.
3. Wykonaj to samo zapytanie z inną wartością custidw klauzuli WHERE.

   SELECT orderid, custid, empid, orderdate
   FROM Sales.Orders
   WHERE custid = 71;
   

   Plan wykonania powinien wykorzystywać operator Clustered Index Scan.

II. Rekompilacja procedur składowanych

1. Utwórz sparametryzowaną procedurę z zapytaniem z poprzedniego ćwiczenia.

   CREATE PROCEDURE Sales.GetCustomerOrders
   (@custid INT)
   AS
   SELECT orderid, custid, empid, orderdate
   FROM Sales.Orders
   WHERE custid = @custid;
   

2. Wyczyść pamięć podręczną.

   DBCC FREEPROCCACHE
   

3. Wykonaj procedurę dwukrotnie dla wartości parametrów 13 i 71.

   EXEC Sales.GetCustomerOrders @custid = 13;
   EXEC Sales.GetCustomerOrders @custid = 71;
   

   Plan wykonania powinien być identyczny dla obu przypadków. Pierwsze wywołnie procedury spowodowało utworzenie tego planu, każde następne wykonanie używa tego planu.
4. Zmodyfikuj procedurę tak aby zawsze była rekompilowana.

   ALTER PROCEDURE Sales.GetCustomerOrders
   (@custid INT)
   WITH RECOMPILE
   AS
   SELECT orderid, custid, empid, orderdate
   FROM Sales.Orders
   WHERE custid = @custid;
   

5. Wykonaj procedurę dwukrotnie dla wartości parametrów 13 i 71.

   EXEC Sales.GetCustomerOrders @custid = 13;
   EXEC Sales.GetCustomerOrders @custid = 71;
   

   Tym razem każde wykonanie powinno posiadać swój plan wykonania.
6. Wyczyść bazę danych

   DROP PROCEDURE Sales.GetCustomerOrders;
   

Podsumowanie

1. SQL Serwer parametryzuje zapytania w celu ponownego użycia planu wykonania.
2. Zapytania można parametryzować ręcznie.
3. Przetwarzanie wsadowe może poprawić wydajność zapytań, w szczególności użycie procesora.
4. Przetwarzanie wsadowe dobrze współgra z indeksami columnstore.