Průvodce zpracováním dotazů pro tabulky Memory-Optimized

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

In-Memory OLTP zavádí tabulky optimalizované pro paměť a nativně kompilované uložené procedury na SQL Serveru. Tento článek poskytuje přehled zpracování dotazů pro tabulky optimalizované pro paměť i nativně zkompilované uložené procedury.

Dokument vysvětluje, jak se kompilují a spouští dotazy na tabulky optimalizované pro paměť, včetně následujících:

• Kanál zpracování dotazů v SQL Serveru pro tabulky založené na disku.

• Optimalizace dotazů; role statistiky pro tabulky optimalizované pro paměť a také pokyny pro řešení potíží s nesprávnými plány dotazů.

• Použití Transact-SQL pro interpretaci přístupu k paměťově optimalizovaným tabulkám.

• Úvahy o optimalizaci dotazů pro přístup k tabulkám optimalizovaným pro paměť.

• Nativně zkompilované uložené procedury a jejich zpracování.

• Statistiky používané pro odhad nákladů optimalizátorem

• Způsoby, jak opravit chybné plány dotazů

Příklad dotazu

Následující příklad se použije k ilustraci konceptů zpracování dotazů, které jsou popsány v tomto článku.

Zvažujeme dvě tabulky, Customer (Zákazník) a Order (Objednávka). Následující skript Transact-SQL obsahuje definice těchto dvou tabulek a přidružených indexů v jejich (tradiční) diskové formě:

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  

Pro vytvoření plánů dotazů uvedených v tomto článku byly dvě tabulky naplněny ukázkovými daty z ukázkové databáze Northwind, kterou si můžete stáhnout z Northwind a pubs Sample Databases for SQL Server 2000.

Představte si následující dotaz, který spojí tabulky Customer (Zákazník) a Order (Objednávka) a vrátí ID objednávky a přidružené informace o zákaznících:

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

Odhadovaný plán provádění zobrazený nástrojem SQL Server Management Studio je následující:

Plán dotazů pro připojení tabulek založených na disku

O tomto plánu dotazů:

• Řádky z tabulky Customer se načítají z clusterovaného indexu, což je primární datová struktura a obsahuje úplná data tabulky.

• Data z tabulky objednávek se načítají pomocí neklastrovaného indexu ve sloupci CustomerID. Tento index obsahuje jak sloupec CustomerID, který se používá pro spojení, tak sloupec OrderID, který je primárním klíčem a vrací se uživateli. Vrácení dalších sloupců z tabulky Order by vyžadovalo vyhledávání v clusterovém indexu pro tabulku Objednávky.

• Logický operátor vnitřní spojení je implementován fyzickým operátorem Merge Join. Další typy fyzických spojení jsou vnořené smyčky a hash join. Operátor Merge Join využívá toho, že oba indexy jsou seřazeny podle spojovacího sloupce CustomerID.

Zvažte mírnou variantu tohoto dotazu, který vrací všechny sloupce z tabulky Objednávky, nejen sloupec OrderID:

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

Odhadovaný plán pro tento dotaz:

Plán dotazu pro spojení hodnot hash tabulek založených na disku

V tomto dotazu se řádky z tabulky Order načítají pomocí clusterovaného indexu. Hash Match fyzický operátor se nyní používá pro Inner Join. Clusterovaný index objednávky není seřazený podle ID zákazníka, takže sloučení spojení by vyžadoval operátor řazení, který by ovlivnil výkon. Všimněte si relativních nákladů operátoru Hash Match (75%) v porovnání s náklady operátoru Merge Join v předchozím příkladu (46%). Optimalizátor by považoval operátor hash match také v předchozím příkladu, ale dospěl k závěru, že operátor Merge Join dal lepší výkon.

Zpracování dotazů SQL Serveru pro tabulky Disk-Based

Následující diagram popisuje tok zpracování dotazů v SQL Serveru pro ad hoc dotazy:

Kanál zpracování dotazů SQL Serveru

V tomto scénáři:

1. Uživatel vydá dotaz.

2. Analyzátor a algebrizer vytvoří strom dotazu s logickými operátory na základě Transact-SQL textu odeslaného uživatelem.

3. Optimalizátor vytvoří optimalizovaný plán dotazu obsahující fyzické operátory (například spojení vnořených smyček). Po optimalizaci může být plán uložen v mezipaměti plánu. Tento krok se vynechá, pokud mezipaměť plánu již obsahuje plán pro tento dotaz.

4. Modul spouštění dotazů zpracovává interpretaci plánu dotazu.

5. Pro každý přístup k indexu, prohledávání indexu a prohledávání tabulky prováděcí modul požaduje řádky z příslušných indexových a tabulkových struktur z Access Methods.

6. Access Methods načte řádky z indexu a datových stránek ve fondu vyrovnávací paměti a podle potřeby načte stránky z disku do fondu vyrovnávací paměti.

V prvním ukázkovém dotazu modul spouštění požaduje řádky v clusterovém indexu pro zákazníka a neclusterovaný index objednávky z metod přístupu. AccessOvé metody procházejí strukturami indexu B-tree a načítají požadované řádky. V tomto případě se všechny řádky načtou, protože plán vyžaduje úplné prohledávání indexů.

Poznámka

Dokumentace používá termín B-tree obecně v odkazu na indexy. V indexech rowstore databázový stroj implementuje strom B+. To neplatí pro indexy columnstore ani indexy v tabulkách optimalizovaných pro paměť. Další informace najdete v SQL Serveru a architektuře indexu Azure SQL a průvodci návrhem.

Interpretovaný přístup Transact-SQL k tabulkám Memory-Optimized

Transact-SQL ad hoc dávky a uložené procedury se také označují jako interpretované Transact-SQL. Interpretováno odkazuje na skutečnost, že plán dotazu je interpretován prováděcím modulem dotazu pro každý operátor v plánu dotazu. Prováděcí modul přečte operátor a jeho parametry a provede operaci.

Interpretované Transact-SQL lze použít pro přístup k tabulkám optimalizovaným pro paměť i na disku. Následující obrázek znázorňuje zpracování dotazů pro interpretovaný Transact-SQL přístup k pamětí optimalizovaným tabulkám:

Kanál zpracování dotazů pro interpretovaný Transact-SQL přístup k paměťově optimalizovaným tabulkám.

Jak je znázorněno na obrázku, kanál zpracování dotazů zůstává převážně nezměněný:

• Analyzátor a algebrizer vytvoří strom dotazu.

• Optimalizátor vytvoří plán provádění.

• Prováděcí modul dotazů interpretuje plán provádění.

Hlavní rozdíl oproti tradičnímu kanálu zpracování dotazů (obrázek 2) spočívá v tom, že pomocí metod Access Methods se řádky tabulek optimalizovaných pro paměť nečtou z fondu vyrovnávací paměti. Místo toho se řádky načítají z datových struktur v paměti prostřednictvím modulu In-Memory OLTP. Rozdíly v datových strukturách způsobují, že optimalizátor v některých případech vybírá různé plány, jak je znázorněno v následujícím příkladu.

Následující skript Transact-SQL obsahuje verze tabulek Objednávek a Zákazníků optimalizované pro paměť pomocí indexů hash:

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  

Zvažte stejný dotaz spuštěný v tabulkách optimalizovaných pro paměť:

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

Odhadovaný plán je následující:

Plán dotazů pro spojení tabulek optimalizovaných pro paměť

Podívejte se na následující rozdíly s plánem stejného dotazu na tabulky založené na disku (obrázek 1):

• Tento plán obsahuje prohledávání tabulek místo vyhledávání clusterovaného indexu pro tabulku Zákazník:

  • Definice tabulky neobsahuje clusterovaný index.

  • Clusterované indexy nejsou podporovány v tabulkách optimalizovaných pro paměť. Místo toho musí mít každá tabulka optimalizovaná pro paměť alespoň jeden neclusterovaný index a všechny indexy v tabulkách optimalizovaných pro paměť můžou efektivně přistupovat ke všem sloupcům v tabulce, aniž by je musely ukládat do indexu nebo odkazovat na clusterovaný index.

• Tento plán obsahuje porovnání hodnot hash místo sloučení. Indexy v tabulce Order i Customer jsou indexy hash, a proto nejsou seřazené. Merge Join by vyžadoval operátory řazení, které by snížily výkon.

Nativně zkompilované uložené procedury

Nativně kompilované uložené procedury jsou Transact-SQL uložené procedury kompilované do strojového kódu, nikoli interpretované prováděcím modulem dotazů. Následující skript vytvoří nativně zkompilovanou uloženou proceduru, která spustí ukázkový dotaz (z části Příklad dotazu).

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  

Nativně kompilované uložené procedury se kompilují při vytváření, zatímco interpretované uložené procedury se kompilují při prvním spuštění. (Část kompilace, zejména analýza a algebrizace, probíhá při vytváření. Pro interpretované uložené procedury se však optimalizace plánů dotazů provádí při prvním spuštění.) Logika rekompilace je podobná. Nativně zkompilované uložené procedury se při prvním spuštění procedury znovu kompilují, pokud je server restartován. Interpretované uložené procedury se znovu kompilují, pokud už plán není v mezipaměti plánu. Následující tabulka shrnuje případy kompilace a rekompilace pro nativně zkompilované i interpretované uložené procedury:

Typ kompilace	Nativní kompilace	Interpretovaný
Počáteční kompilace	Při vytváření.	Při prvním spuštění.
Automatická rekompilace	Při prvním spuštění procedury po restartování databáze nebo serveru.	Při restartu serveru. Nebo vyřazení z mezipaměti plánu, obvykle na základě změn schématu nebo statistik nebo zatížení paměti.
Ruční rekompilace	Použijte sp_recompile.	Použijte sp_recompile. Plán můžete ručně vyřadit z mezipaměti, například prostřednictvím DBCC FREEPROCCACHE. Můžete také vytvořit uloženou proceduru WITH RECOMPILE a uložená procedura bude rekompilována při každém spuštění.

Kompilace a zpracování dotazů

Následující diagram znázorňuje proces kompilace pro nativně zkompilované uložené procedury:

Nativní kompilace uložených procedur.

Proces je popsán takto:

1. Uživatel vydá příkaz CREATE PROCEDURE sql Serveru.

2. Analyzátor a algebrizer vytvářejí tok zpracování pro proceduru a také stromy dotazů pro dotazy Transact-SQL v uložené proceduře.

3. Optimalizátor vytvoří optimalizované plány provádění dotazů pro všechny dotazy v uložené proceduře.

4. Kompilátor OLTP In-Memory vezme tok zpracování s vloženými optimalizovanými plány dotazů a vygeneruje knihovnu DLL, která obsahuje kód počítače pro spuštění uložené procedury.

5. Vygenerovaná knihovna DLL se načte do paměti.

Vyvolání nativně zkompilované uložené procedury se překládá na volání funkce v knihovně DLL.

Spouštění nativně zkompilovaných uložených procedur.

Volání nativně zkompilované uložené procedury je popsáno takto:

1. Uživatel vydá příkaz EXECusp_myproc.

2. Analyzátor extrahuje název a parametry uložené procedury.

   Pokud byl příkaz připravený, například pomocí sp_prep_exec, analyzátor nemusí extrahovat název procedury a parametry v době provádění.

3. Modul runtime In-Memory OLTP vyhledá vstupní bod knihovny DLL pro uloženou proceduru.

4. Kód počítače v knihovně DLL se spustí a výsledky se vrátí klientovi.

při šifrování parametrů

Interpretované uložené procedury Transact-SQL se kompilují při prvním spuštění, na rozdíl od nativně kompilovaných uložených procedur, které se kompilují již při vytváření. Při kompilaci interpretovaných uložených procedur při vyvolání jsou hodnoty parametrů zadaných pro toto vyvolání použity optimalizátorem při generování prováděcího plánu. Toto použití parametrů během kompilace se nazývá zašifrování parametrů.

Při kompilaci nativně zkompilovaných uložených procedur se nepoužívá zašifrování parametrů. Všechny parametry uložené procedury jsou považovány za neznámé hodnoty. Podobně jako interpretované uložené procedury podporují nativně zkompilované uložené procedury také příznak OPTIMIZE FOR. Další informace najdete v tématu Nápovědy k dotazům (Transact-SQL).

Načtení plánu spouštění dotazů pro nativně zkompilované uložené procedury

Plán spouštění dotazů pro nativně zkompilovanou uloženou proceduru lze načíst pomocí odhadovaného plánu provádění v sadě Management Studio nebo pomocí možnosti SHOWPLAN_XML v jazyce Transact-SQL. Například:

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

Plán provádění vygenerovaný optimalizátorem dotazů se skládá ze stromu, na jehož uzlech a listech se nacházejí operátory dotazu. Struktura stromu určuje interakci (tok řádků z jednoho operátoru do druhého) mezi operátory. V grafickém zobrazení aplikace SQL Server Management Studio je tok zprava doleva. Například plán dotazu na obrázku 1 obsahuje dva operátory prohledávání indexu, které dodávají řádky operátoru sloučení spojů. Operátor spojení pomocí sloučení poskytuje řádky operátoru výběru. Operátor select nakonec vrátí řádky klientovi.

Operátory dotazů v nativně zkompilovaných uložených procedurách

Následující tabulka shrnuje operátory dotazů podporované uvnitř nativně zkompilovaných uložených procedur:

Operátor	Ukázkový dotaz	Poznámky
VYBRAT	SELECT OrderID FROM dbo.[Order]
VLOŽIT	INSERT dbo.Customer VALUES ('abc', 'def')
AKTUALIZACE	UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
VYMAZAT	DELETE dbo.Customer WHERE CustomerID='abc'
Skalární výpočet	SELECT OrderID+1 FROM dbo.[Order]	Tento operátor se používá pro vnitřní funkce i převody typů. Ne všechny funkce a převody typů jsou podporovány uvnitř nativně kompilovaných uložených procedur.
Spojení vnořených smyček	SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c	Vnořené smyčky jsou jediným operátorem spojení podporovaným v nativně zkompilovaných uložených procedurách. Všechny plány obsahující spojení budou používat operátor vnořených smyček, i když plán pro stejný dotaz, spuštěný jako interpretovaný Transact-SQL, obsahuje hash spojení nebo slučovací spojení.
Třídit	SELECT ContactName FROM dbo.Customer ORDER BY ContactName
Vrchol	SELECT TOP 10 ContactName FROM dbo.Customer
Řazení nahoře	SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName	Výraz TOP (počet vrácených řádků) nesmí překročit 8 000 řádků. Méně, pokud jsou v dotazu také operátory spojení a agregace. Spojení a agregace obvykle snižují počet řádků, které se mají seřadit, ve srovnání s počtem řádků základních tabulek.
Agregace streamu	SELECT count(CustomerID) FROM dbo.Customer	Všimněte si, že u agregace není podporován operátor Hash Match. Proto všechna agregace v nativně zkompilovaných uložených procedurách používají operátor Agregace streamu, i když plán pro stejný dotaz v interpretovaném Transact-SQL používá operátor Hash Match.

Statistika sloupců a spojení

SQL Server udržuje statistiky o hodnotách ve sloupcích s klíči indexu, aby pomohl odhadnout náklady na určité operace, jako je prohledávání indexů a hledání indexů. ( SQL Server také vytvoří statistiky pro sloupce bez indexu klíče, pokud je explicitně vytvoříte nebo pokud je optimalizátor dotazů vytvoří v reakci na dotaz s predikátem.) Hlavní metrika odhadu nákladů je počet řádků zpracovaných jedním operátorem. Všimněte si, že u tabulek založených na disku je počet stránek, ke kterým přistupuje konkrétní operátor, významný v odhadu nákladů. Vzhledem k tomu, že počet stránek není pro tabulky optimalizované pro paměť důležitý (vždy je nula), tato diskuze se zaměřuje na počet řádků. Odhad začíná u operátorů hledání a skenování indexů v plánu a poté je rozšířen tak, aby zahrnoval ostatní operátory, jako je operátor spojení. Odhadovaný počet řádků, které má operátor spojování zpracovat, je založen na odhadu pro vyhledávací, prohledávací a indexové operátory. Pro interpretovaný přístup přes Transact-SQL k tabulkám optimalizovaným pro paměť můžete sledovat skutečný plán provádění, abyste viděli rozdíl mezi odhadovanými a skutečnými počty řádků pro operátory v plánu.

Příklad na obrázku 1:

• Průzkum clusterovaného indexu u tabulky Zákazníci odhadl: 91; skutečné: 91.
• Prohledávání neklastrovaného indexu u ID zákazníka bylo odhadnuto na 830; skutečnost 830.
• Operátor Merge Join odhadoval 815; skutečná hodnota 830.

Odhady pro skenování indexu jsou přesné. SQL Server udržuje počet řádků pro tabulky založené na disku. Odhady pro úplné prohledávání tabulek a indexů jsou vždy přesné. Odhad propojení je také poměrně přesný.

Pokud se tyto odhady změní, změní se také aspekty nákladů pro různé alternativy plánu. Pokud má například jedna ze stran spojení odhadovaný počet řádků 1 nebo jen několik řádků, je použití vnořených smyček levnější. Představte si následující dotaz:

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

Po odstranění všech řádků kromě řádků v tabulce Customer se vygeneruje následující plán dotazu:

Ohledně tohoto plánu dotazů:

• Operátor Hash Match byl nahrazen fyzickým operátorem spojování vnořených smyček.
• Úplná kontrola indexu na IX_CustomerID byla nahrazena vyhledáváním v indexu. Výsledkem je prohledávání 5 řádků místo 830 řádků požadovaných pro úplnou kontrolu indexu.

Viz také

Memory-Optimized Tabulky