Przewodnik dotyczący przetwarzania zapytań dla tabel Memory-Optimized

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In-Memory OLTP wprowadza tabele zoptymalizowane pod kątem pamięci i natywnie skompilowane procedury składowane w programie SQL Server. Ten artykuł zawiera omówienie przetwarzania zapytań dla tabel zoptymalizowanych pod kątem pamięci i natywnie skompilowanych procedur składowanych.

W dokumencie wyjaśniono, w jaki sposób zapytania dotyczące tabel zoptymalizowanych pod kątem pamięci są kompilowane i wykonywane, w tym:

• Potok przetwarzania zapytań w programie SQL Server dla tabel opartych na dyskach.

• Optymalizacja zapytań; rola statystyk dotyczących tabel zoptymalizowanych pod kątem pamięci, a także wskazówki dotyczące rozwiązywania problemów z nieprawidłowymi planami zapytań.

• Użycie zinterpretowanego Transact-SQL do uzyskania dostępu do tabel zoptymalizowanych pod kątem pamięci.

• Zagadnienia dotyczące optymalizacji zapytań pod kątem dostępu do tabeli zoptymalizowanej pod kątem pamięci.

• Kompilacja i przetwarzanie natywnie skompilowanej procedury składowanej.

• Statystyki używane do szacowania kosztów przez optymalizator.

• Sposoby korygowania nieprawidłowych planów zapytań.

Przykładowe zapytanie

Poniższy przykład zostanie użyty do zilustrowania pojęć związanych z przetwarzaniem zapytań omówionych w tym artykule.

Rozważamy dwie tabele: Customer (Klient) i Order (Zamówienie). Poniższy skrypt Transact-SQL zawiera definicje tych dwóch tabel i skojarzonych indeksów w postaci opartej na dysku (tradycyjnej):

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO  

Aby utworzyć plany zapytań pokazane w tym artykule, dwie tabele zostały wypełnione przykładowymi danymi z przykładowej bazy danych Northwind, którą można pobrać z witryny Northwind i pubs Sample Databases for SQL Server 2000.

Rozważ następujące zapytanie, które łączy tabele Customer (Klient) i Order (Zamówienie) i zwraca identyfikator zamówienia oraz skojarzone informacje o kliencie:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Szacowany plan wykonania wyświetlany przez program SQL Server Management Studio jest następujący

Plan zapytania dla sprzężenia tabel opartych na dyskach.

Informacje o tym planie zapytania:

• Wiersze z tabeli Customer są pobierane z indeksu klastrowanego, który jest podstawową strukturą danych i zawiera pełne dane tabeli.

• Dane z tabeli Order są pobierane przy użyciu indeksu nieklastrowanego w kolumnie CustomerID. Ten indeks zawiera zarówno kolumnę CustomerID, używaną do sprzężenia, jak i kolumnę OrderID klucza podstawowego, która jest zwracana użytkownikowi. Zwracanie dodatkowych kolumn z tabeli "Order" wymaga wyszukiwań w indeksie klastrowanym tej tabeli.

• Operator logiki Inner Join jest implementowany przez operator fizyczny Merge Join. Inne typy sprzężeń fizycznych to zagnieżdżone pętle oraz połączenie haszowe. Operator Merge Join korzysta z faktu, że oba indeksy są sortowane w kolumnie sprzężenia CustomerID.

Rozważ niewielkie różnice w tym zapytaniu, które zwraca wszystkie kolumny z tabeli Order, a nie tylko kolumnę OrderID:

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Szacowany plan dla tego zapytania to:

Plan zapytania dla sprzężenia skrótu tabel opartych na dyskach.

W tym zapytaniu wiersze z tabeli Order są pobierane przy użyciu indeksu klastrowanego. Operator fizyczny dopasowanie skrótu jest teraz używany dla sprzężenia wewnętrznego. Indeks klastrowany w kolumnie Order (Kolejność) nie jest sortowany według identyfikatora CustomerID, dlatego scalanie wymaga operatora sortowania, co wpłynie na wydajność. Zwróć uwagę na względny koszt operatora Hash Match (75%) w porównaniu z kosztem operatora Merge Join w poprzednim przykładzie (46%). Optymalizator rozważyłby również operator Hash Match w poprzednim przykładzie, ale doszedł do wniosku, że operator Merge Join zapewniał lepszą wydajność.

Przetwarzanie zapytań programu SQL Server dla tabel Disk-Based

Na poniższym diagramie przedstawiono przepływ przetwarzania zapytań w programie SQL Server dla zapytań ad hoc:

Potok przetwarzania zapytań programu SQL Server.

W tym scenariuszu:

1. Użytkownik wystawia zapytanie.

2. Analizator i algebrizer skonstruują drzewo zapytań z operatorami logicznymi na podstawie tekstu Transact-SQL przesłanego przez użytkownika.

3. Optymalizator tworzy zoptymalizowany plan zapytania zawierający operatory fizyczne (na przykład łączenie zagnieżdżonych pętli). Po optymalizacji plan może być przechowywany w pamięci podręcznej planu. Ten krok zostanie pominięty, jeśli pamięć podręczna planu już zawiera plan dla tego zapytania.

4. Aparat wykonywania zapytań przetwarza interpretację planu zapytania.

5. Dla każdego operatora wyszukiwania indeksu, skanowania indeksu oraz skanowania tabeli, silnik wykonawczy żąda wierszy z odpowiednich struktur indeksów i tabel z Metod dostępu.

6. Metody dostępu pobierają wiersze ze stron indeksu i danych w puli buforów oraz ładują strony z dysku do tej puli w razie potrzeby.

W przypadku pierwszego przykładowego zapytania silnik wykonawczy żąda wierszy w indeksie klastrowanym na kliencie Customer i indeksie nieklastrowanym na tabeli Order z Metod Dostępu. Metody dostępu przechodzą przez struktury indeksów drzewa B w celu pobrania żądanych wierszy. W takim przypadku wszystkie wiersze są pobierane, ponieważ plan wywołuje pełne skanowanie indeksu.

Notatka

W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach magazynu wierszy silnik baz danych implementuje drzewo B+. Nie dotyczy to indeksów magazynu kolumn ani indeksów w tabelach zoptymalizowanych pod kątem pamięci. Aby uzyskać więcej informacji, zobacz przewodnik dotyczący architektury i projektowania indeksów dla SQL Server i Azure SQL.

Interpretowany dostęp Transact-SQL do tabel Memory-Optimized

Transact-SQL ad hoc partie i procedury składowane są również określane jako interpretowane Transact-SQL. Termin "interpretowane" odnosi się do tego, że plan zapytania jest interpretowany przez aparat wykonywania zapytań dla każdego operatora. Mechanizm wykonawczy odczytuje operatora oraz jego parametry i wykonuje operację.

Interpretowane Transact-SQL mogą być używane do dostępu do tabel zoptymalizowanych dla pamięci oraz tych opartych na dyskach. Na poniższej ilustracji przedstawiono przetwarzanie zapytań dla interpretowanego dostępu Transact-SQL do tabel zoptymalizowanych pod kątem pamięci:

Potok przetwarzania zapytań na potrzeby interpretowanego Transact-SQL dostępu do tabel zoptymalizowanych pod kątem pamięci.

Jak pokazano na rysunku, potok przetwarzania zapytań pozostaje w większości niezmieniony:

• Analizator i algebrizer skonstruują drzewo zapytań.

• Optymalizator tworzy plan wykonania.

• Aparat wykonywania zapytań interpretuje plan wykonywania.

Główną różnicą w tradycyjnym potoku przetwarzania zapytań (rysunek 2) jest to, że wiersze dla tabel zoptymalizowanych pod kątem pamięci nie są pobierane z puli przy użyciu metod dostępu. Zamiast tego wiersze są pobierane ze struktur danych w pamięci za pomocą silnika OLTP In-Memory. Różnice w strukturach danych powodują, że optymalizator wybiera różne plany w niektórych przypadkach, jak pokazano w poniższym przykładzie.

Poniższy skrypt Transact-SQL zawiera zoptymalizowane pod kątem pamięci wersje tabel Order (Zamówienie) i Customer (Klient) przy użyciu indeksów skrótów:

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO  

Rozważ wykonanie tego samego zapytania w tabelach zoptymalizowanych pod kątem pamięci:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Szacowany plan jest następujący:

Plan zapytania dla sprzężenia tabel zoptymalizowanych pod kątem pamięci.

Zwróć uwagę na następujące różnice w planie dla tego samego zapytania w tabelach opartych na dyskach (rysunek 1):

• Ten plan zawiera skanowanie tabeli, a nie skanowanie indeksu klastrowanego dla tabeli Klient:

  • Definicja tabeli nie zawiera indeksu klastrowanego.

  • Indeksy klastrowane nie są obsługiwane w przypadku tabel zoptymalizowanych pod kątem pamięci. Zamiast tego każda tabela zoptymalizowana pod kątem pamięci musi mieć co najmniej jeden indeks nieklastrowany, a wszystkie indeksy w tabelach zoptymalizowanych pod kątem pamięci mogą efektywnie uzyskiwać dostęp do wszystkich kolumn w tabeli bez konieczności przechowywania ich w indeksie lub odwoływania się do indeksu klastrowanego.

• Ten plan zawiera Hash Match zamiast Merge Join. Indeksy w tabeli Order (Zamówienie) i Customer (Klient) są indeksami skrótów i w związku z tym nie są uporządkowane. Złączenie scalające wymaga operatorów sortowania, które zmniejszyłyby wydajność.

Natywnie skompilowane procedury składowane

Natywnie skompilowane procedury składowane są Transact-SQL procedury składowane kompilowane w kodzie maszynowym, a nie interpretowane przez aparat wykonywania zapytań. Poniższy skrypt tworzy natywnie skompilowaną procedurę składowaną, która uruchamia przykładowe zapytanie (z sekcji Przykładowe zapytanie).

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END  

Natywnie skompilowane procedury składowane są kompilowane w czasie tworzenia, natomiast interpretowane procedury składowane są kompilowane podczas pierwszego wykonywania. (Część kompilacji, szczególnie analizowanie i algebrizacja, odbywa się w momencie utworzenia. Jednak w przypadku zinterpretowanych procedur składowanych optymalizacja planów zapytań odbywa się w pierwszym wykonaniu). Logika ponownej kompilacji jest podobna. Natywnie skompilowane procedury składowane są ponownie kompilowane podczas pierwszego wykonywania procedury, jeśli serwer zostanie uruchomiony ponownie. Interpretowane procedury składowane są kompilowane ponownie, jeśli plan nie znajduje się już w pamięci podręcznej planu. W poniższej tabeli przedstawiono podsumowanie przypadków kompilacji i ponownej kompilacji dla natywnie skompilowanych i zinterpretowanych procedur składowanych:

Typ kompilacji	Natywnie skompilowane	Zinterpretowane
Początkowa kompilacja	Podczas tworzenia.	Pierwsze wykonanie.
Automatyczna ponowna kompilacja	Po pierwszym wykonaniu procedury po ponownym uruchomieniu bazy danych lub serwera.	Przy ponownym uruchomieniu serwera. Lub usunięcie z pamięci podręcznej planu, zwykle na podstawie zmian schematu, statystyk lub presji pamięciowej.
Ręczna ponowna kompilacja	Użyj sp_recompile.	Użyj sp_recompile. Możesz ręcznie wykluczyć plan z pamięci podręcznej, na przykład za pomocą funkcji DBCC FREEPROCCACHE. Można również utworzyć procedurę składowaną ZA POMOCĄ FUNKCJI RECOMPILE, a procedura składowana zostanie ponownie skompilowana przy każdym wykonaniu.

Kompilacja i przetwarzanie zapytań

Na poniższym diagramie przedstawiono proces kompilacji dla natywnie skompilowanych procedur składowanych:

Natywna kompilacja funkcji składowanych.

Proces jest opisany jako,

1. Użytkownik wydaje instrukcję CREATE PROCEDURE do programu SQL Server.

2. Analizator i algebrizer tworzą przepływ przetwarzania dla procedury, a także drzewa zapytań dla zapytań Transact-SQL w procedurze przechowywanej.

3. Optymalizator tworzy zoptymalizowane plany wykonywania zapytań dla wszystkich zapytań w procedurze składowanej.

4. Kompilator OLTP In-Memory pobiera przepływ przetwarzania z osadzonymi zoptymalizowanymi planami zapytań i generuje bibliotekę DLL zawierającą kod maszynowy do wykonywania procedury składowanej.

5. Wygenerowana biblioteka DLL jest ładowana do pamięci.

Wywołanie natywnie skompilowanej procedury składowanej przekłada się na wywołanie funkcji w bibliotece DLL.

Wykonywanie natywnie skompilowanych procedur składowanych.

Wywołanie natywnie skompilowanej procedury składowanej jest opisane w następujący sposób:

1. Użytkownik wydaje instrukcję EXECusp_myproc.

2. Analizator wyodrębnia nazwę i parametry procedury składowanej.

   Jeśli instrukcja została przygotowana, na przykład przy użyciu sp_prep_exec, analizator nie musi wyodrębniać nazwy i parametrów procedury w czasie wykonywania.

3. Środowisko uruchomieniowe OLTP In-Memory lokalizuje punkt wejścia biblioteki DLL dla procedury składowanej.

4. Kod maszyny w dll jest wykonywany, a wyniki są zwracane do klienta.

Sniffowanie parametrów

Interpretowane Transact-SQL procedury składowane są kompilowane podczas pierwszego wykonywania, w przeciwieństwie do natywnie skompilowanych procedur składowanych, które są kompilowane w czasie tworzenia. Kiedy interpretowane procedury składowane są kompilowane w momencie wywołania, wartości parametrów dostarczonych podczas tego wywołania są wykorzystywane przez optymalizator do wygenerowania planu wykonania. To wykorzystanie parametrów podczas kompilacji jest nazywane sniffowaniem parametrów.

Wąchanie parametrów nie jest używane do kompilowania natywnie skompilowanych procedur składowanych. Wszystkie parametry procedury składowanej są uznawane jako wartości nieznane. Podobnie jak w przypadku interpretowanych procedur składowanych, natywnie skompilowane procedury składowane obsługują również podpowiedź OPTIMIZE FOR. Aby uzyskać więcej informacji, zobacz wskazówki dotyczące zapytań (Transact-SQL).

Pobieranie planu wykonywania zapytania dla natywnie skompilowanych procedur składowanych

Plan wykonywania zapytania dla natywnie skompilowanej procedury składowanej można pobrać przy użyciu szacowanego planu wykonania w programie Management Studio lub przy użyciu opcji SHOWPLAN_XML w języku Transact-SQL. Na przykład:

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO  

Plan wykonywania wygenerowany przez optymalizator zapytań składa się z drzewa z operatorami zapytań w węzłach i liśćmi drzewa. Struktura drzewa określa interakcję (przepływ wierszy z jednego operatora na inny) między operatorami. W graficznym widoku programu SQL Server Management Studio przepływ jest od prawej do lewej. Na przykład plan zapytania na rysunku 1 zawiera dwa operatory skanowania indeksu, które dostarczają wiersze do operatora złączenia scalającego. Operator złączenia scalającego dostarcza wiersze do operatora wybierającego. Operator select zwraca na koniec wiersze do klienta.

Operatory zapytań w natywnie skompilowanych procedurach składowanych

Poniższa tabela zawiera podsumowanie operatorów zapytań obsługiwanych wewnątrz natywnie skompilowanych procedur składowanych:

Operator	Przykładowe zapytanie	Notatki
WYBRAĆ	SELECT OrderID FROM dbo.[Order]
WSTAWIAĆ	INSERT dbo.Customer VALUES ('abc', 'def')
AKTUALIZACJA	UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
USUNĄĆ	DELETE dbo.Customer WHERE CustomerID='abc'
Obliczanie skalarne	SELECT OrderID+1 FROM dbo.[Order]	Ten operator jest używany zarówno do funkcji wewnętrznych, jak i konwersji typów. Nie wszystkie funkcje i konwersje typów są obsługiwane wewnątrz natywnie skompilowanych procedur składowanych.
Sprzężenia zagnieżdżonych pętli	SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c	Operator łączenia Zagnieżdżone Pętle jest jedynym wspieranym operatorem w natywnie kompilowanych procedurach składowanych. Wszystkie plany zawierające sprzężenia będą używać operatora zagnieżdżonych pętli, nawet jeśli plan dla tego samego zapytania wykonanego jako zinterpretowany Transact-SQL zawiera połączenie haszujące lub połączenie scalające.
Sortuj	SELECT ContactName FROM dbo.Customer ORDER BY ContactName
Do góry	SELECT TOP 10 ContactName FROM dbo.Customer
Sortowanie od góry	SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName	Wyrażenie TOP (liczba zwracanych wierszy) nie może przekraczać 8000 wierszy. Mniej, jeśli w zapytaniu znajdują się również operatory sprzężenia i agregacji. Sprzężenia i agregacja zwykle zmniejszają liczbę wierszy do sortowania w porównaniu z liczbą wierszy tabel podstawowych.
Agregat Strumieniowy	SELECT count(CustomerID) FROM dbo.Customer	Należy pamiętać, że operator dopasowania hashu nie jest obsługiwany w przypadku agregacji. W związku z tym wszystkie agregacje w natywnie skompilowanych procedurach składowanych używają operatora Strumieniowej Agregacji, nawet jeśli plan dla tego samego zapytania w interpretowanym Transact-SQL używa operatora Dopasowania skrótu.

Statystyki kolumn i łączenia

SQL Server utrzymuje statystyki dotyczące wartości w kolumnach kluczy indeksu, aby ułatwić oszacowanie kosztów niektórych operacji, takich jak skanowanie indeksu i wyszukiwania indeksu. ( Program SQL Server tworzy również statystyki dotyczące kolumn kluczy innych niż indeks, jeśli jawnie je utworzysz lub jeśli optymalizator zapytań tworzy je w odpowiedzi na zapytanie z predykatem). Główną metryką szacowania kosztów jest liczba wierszy przetwarzanych przez pojedynczego operatora. Należy pamiętać, że w przypadku tabel opartych na dyskach liczba stron, do których uzyskuje dostęp określony operator, jest znacząca w szacowaniu kosztów. Jednak ponieważ liczba stron nie jest ważna w przypadku tabel zoptymalizowanych pod kątem pamięci (zawsze jest zero), ta dyskusja koncentruje się na liczbie wierszy. Szacowanie rozpoczyna się od operatorów wyszukiwania indeksu i skanowania w planie, a następnie jest rozszerzany w celu uwzględnienia innych operatorów, takich jak operator sprzężenia. Szacowana liczba wierszy do przetworzenia przez operatora sprzężenia jest oparta na szacowaniu dla bazowych operatorów: indeksu, wyszukiwania i skanowania. W przypadku interpretowanego dostępu Transact-SQL do tabel zoptymalizowanych pod kątem pamięci można obserwować rzeczywisty plan wykonania, aby dostrzec różnicę między szacowaną a rzeczywistą liczbą wierszy dla operatorów w planie.

Na przykład na rysunku 1,

• Skanowanie indeksu klastrowanego na tabeli Customer zostało oszacowane na 91; rzeczywiste 91.
• Skanowanie indeksu nieklastrowanego dla identyfikatora CustomerID szacowano na 830; rzeczywiste 830.
• Operator scalania sprzężenia miał szacunkową wartość 815; rzeczywista wartość to 830.

Oszacowania skanowania indeksów są dokładne. Program SQL Server utrzymuje liczbę wierszy dla tabel opartych na dyskach. Oszacowania dla pełnych skanów tabel i indeksów są zawsze dokładne. Oszacowanie połączenia jest również dość dokładne.

Jeśli te szacunki się zmienią, zagadnienia dotyczące kosztów dla różnych alternatyw planu również się zmienią. Jeśli na przykład jedna ze stron sprzężenia ma szacowaną liczbę wierszy 1 lub kilka wierszy, użycie sprzężeń zagnieżdżonych jest tańsze. Rozważ następujące zapytanie:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Po usunięciu wszystkich wierszy z tabeli Customer poza jednym, zostanie wygenerowany następujący plan zapytania:

Jeśli chodzi o ten plan zapytania:

• Dopasowanie skrótu zostało zastąpione operatorem sprzężenia fizycznego zagnieżdżonych pętli.
• Pełne skanowanie indeksu w IX_CustomerID zostało zastąpione wyszukiwaniem indeksu. Spowodowało to skanowanie 5 wierszy zamiast 830 wierszy wymaganych do pełnego skanowania indeksu.

Zobacz też

Memory-Optimized tabele