En guide till frågebearbetning för Memory-Optimized-tabeller

Artikel
2025-03-05

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance

In-Memory OLTP introducerar minnesoptimerade tabeller och inbyggda kompilerade lagrade procedurer i SQL Server. Den här artikeln ger en översikt över frågebearbetning för både minnesoptimerade tabeller och inbyggda kompilerade lagrade procedurer.

Dokumentet förklarar hur frågor på minnesoptimerade tabeller kompileras och körs, inklusive:

Pipelinen för frågebearbetning i SQL Server för diskbaserade tabeller.
Frågeoptimering; statistikrollen för minnesoptimerade tabeller samt riktlinjer för felsökning av felaktiga frågeplaner.
Användning av tolkade Transact-SQL för att komma åt minnesoptimerade tabeller.
Överväganden om frågeoptimering för minnesoptimerad tabellåtkomst.
Inbyggd kompilerad kompilering och bearbetning av lagrade procedurer.
Statistik som används för kostnadsuppskattning av optimeraren.
Sätt att åtgärda felaktiga frågeplaner.

Exempelfråga

Följande exempel används för att illustrera de frågebearbetningsbegrepp som beskrivs i den här artikeln.

Vi överväger två tabeller, Kund och Order. Följande Transact-SQL-skript innehåller definitionerna för dessa två tabeller och associerade index, i deras (traditionella) diskbaserade 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

För att skapa frågeplaner som visas i den här artikeln fylldes de två tabellerna med exempeldata från Northwind-exempeldatabasen, som du kan ladda ned från Northwind och pubs Sample Databases for SQL Server 2000.

Tänk på följande fråga, som ansluter tabellerna Kund och Order och returnerar ID:t för ordern och den associerade kundinformationen:

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

Den uppskattade exekveringsplanen som visas av SQL Server Management Studio visas på följande sätt.

Frågeplan för anslutning av diskbaserade tabeller.

Om den här frågeplanen:

Raderna från Kundtabellen hämtas från det klustrade indexet, som är den primära datastrukturen och har hela tabelldata.
Data från tabellen Order hämtas med hjälp av det icke-klustrade indexet i kolumnen CustomerID. Det här indexet innehåller både kolumnen CustomerID, som används för kopplingen, och den primära nyckelkolumnen OrderID, som returneras till användaren. Att återge ytterligare kolumner från tabellen Order skulle kräva uppslag i det klustrade indexet för tabellen Order.
Den logiska operatören Inre sammanslagning implementeras av den fysiska operatören Fusjonssammanfogning. De andra fysiska kopplingstyperna är kapslade loopar och Hash Join. Operatorn Merge Join drar nytta av att båda indexen sorteras i kopplingskolumnen CustomerID.

Överväg en liten variant av den här frågan, som returnerar alla kolumner från tabellen Order, inte bara OrderID-kolumnen:

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

Den uppskattade planen för den här frågan är:

Frågeplan för en hashkoppling av diskbaserade tabeller.

I den här databasfrågeställningen hämtas rader från tabellen Order med hjälp av det klustrade indexet. Den fysiska operatorn Hash Match används nu för Inre koppling. Det klustrade indexet på Order sorteras inte efter CustomerID, så en Slå samman koppling skulle kräva en sorteringsoperator, vilket skulle påverka prestanda. Observera den relativa kostnaden för operatorn Hash Match (75%) jämfört med kostnaden för operatorn Merge Join i föregående exempel (46%). Optimeraren skulle ha beaktat operatorn Hash Match även i föregående exempel, men drog slutsatsen att operatorn Merge Join gav bättre prestanda.

SQL Server-frågebearbetning för Disk-Based-tabeller

Följande diagram beskriver frågebearbetningsflödet i SQL Server för ad hoc-frågor:

SQL Server-frågebearbetningspipeline.

I det här scenariot:

Användaren utfärdar en fråga.
Parsern och algebrizern skapar ett frågeträd med logiska operatorer baserat på den Transact-SQL text som skickats av användaren.
Optimeraren skapar en optimerad frågeplan som innehåller fysiska operatorer (till exempel kapslade loopar). Efter optimeringen kan planen lagras i plancachen. Det här steget kringgås om plancachen redan innehåller en plan för den här frågan.
Frågekörningsmotorn kör en tolkning av frågeplanen.
För varje indexsökning, indexgenomsökning och tabellgenomsökningsoperator begär körningsmotorn rader från respektive index- och tabellstrukturer från åtkomstmetoder.
Åtkomstmetoder hämtar raderna från index- och datasidorna i buffertpoolen och läser in sidor från disken till buffertpoolen efter behov.

För den första exempelfrågan begär körningsmotorn rader i det klustrade indexet på Customer och det icke-klustrade indexet på Order från Access Methods. Åtkomstmetoder passerar B-trädindexstrukturerna för att hämta de begärda raderna. I det här fallet hämtas alla rader när planen kräver fullständiga indexgenomsökningar.

Not

I dokumentationen används termen B-träd vanligtvis som referens till index. I radlagringsindex implementerar databasmotorn ett B+-träd. Detta gäller inte för kolumnlagringsindex eller index i minnesoptimerade tabeller. Mer information finns i arkitekturen och designguiden för SQL Server och Azure SQL-index.

Tolkad Transact-SQL-åtkomst till Memory-Optimized-tabeller

Transact-SQL ad hoc-batchar och lagrade procedurer kallas även tolkade Transact-SQL. Tolkad hänvisar till det faktum att frågeplanen bearbetas av frågemotorn för varje operator i planen. Körningsmotorn läser operatorn och dess parametrar och utför operationen.

Tolkade Transact-SQL kan användas för att komma åt både minnesoptimerade och diskbaserade tabeller. Följande bild illustrerar frågebearbetning för tolkad Transact-SQL åtkomst till minnesoptimerade tabeller:

Pipeline för frågebearbetning för tolkad tsql.
Frågebearbetningspipeline för tolkad Transact-SQL åtkomst till minnesoptimerade tabeller.

Som bilden visar förblir frågebearbetningspipelinen mestadels oförändrad:

Parsern och algebrizern konstruerar frågeträdet.
Optimeraren skapar utförandeplanen.
Frågekörningsmotorn tolkar exekveringsplanen.

Den största skillnaden med den traditionella frågebearbetningspipelinen (bild 2) är att rader för minnesoptimerade tabeller inte hämtas från buffertpoolen med hjälp av åtkomstmetoder. I stället hämtas rader från minnesinterna datastrukturer via In-Memory OLTP-motorn. Skillnader i datastrukturer gör att optimeraren i vissa fall väljer olika planer, vilket illustreras i följande exempel.

Följande Transact-SQL skript innehåller minnesoptimerade versioner av tabellerna Order och Customer med hash-index:

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

Överväg samma fråga som körs på minnesoptimerade tabeller:

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

Den beräknade planen är följande:

Frågeplan för anslutning av minnesoptimerade tabeller.
Frågeplan för koppling av minnesoptimerade tabeller.

Observera följande skillnader med planen för samma fråga i diskbaserade tabeller (bild 1):

Den här planen innehåller en tabellsökning snarare än en klustrad indexsökning för tabellen Kund.
- Definitionen av tabellen innehåller inte ett grupperat index.
- Klustrade index stöds inte med minnesoptimerade tabeller. I stället måste varje minnesoptimerad tabell ha minst ett icke-grupperat index och alla index på minnesoptimerade tabeller kan effektivt komma åt alla kolumner i tabellen utan att behöva lagra dem i indexet eller referera till ett grupperat index.
Den här planen innehåller en Hash Match i stället för en Merge Join. Indexen i både tabellen Order och Customer är hash-index och sorteras därför inte. En Merge Join skulle kräva sorteringsoperatorer som försämrar prestandan.

Inbyggt kompilerade lagrade procedurer

Nativt kompilerade lagrade procedurer är Transact-SQL lagrade procedurer som kompileras till maskinkod, snarare än att tolkas av frågemotorn. Följande skript skapar en inbyggt kompilerad lagrad procedur som kör exempelfrågan (från avsnittet Exempelfråga).

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

Nativt kompilerade lagrade procedurer kompileras vid skapandet, medan tolkade lagrade procedurer kompileras vid första körningen. (En del av kompileringen, särskilt parsning och algebrisering, sker vid skapande. För tolkade lagrade procedurer sker dock optimeringen av frågeplanerna vid första körningen.) Omkompileringslogik är liknande. Internt kompilerade lagrade procedurer kompileras om vid första körningen av proceduren om servern startas om. Tolkade lagrade procedurer omkompileras om planen inte längre finns i plancachen. I följande tabell sammanfattas kompilerings- och omkompileringsfall för både internt kompilerade och tolkade lagrade procedurer:

Kompileringstyp	Internt kompilerad	Tolkad
Inledande kompilering	Vid tidpunkten för skapande.	Vid första körningen.
Automatisk omkompilering	Vid första körningen av proceduren efter en databas eller serveromstart.	Vid omstart av servern. Eller utdrivning från plancachen, vanligtvis baserad på ändringar i scheman eller statistik, eller minnestryck.
Manuell omkompilering	Använd sp_recompile.	Använd sp_recompile. Du kan ta bort planen manuellt från cachen, till exempel via DBCC FREEPROCCACHE. Du kan också skapa den lagrade proceduren WITH RECOMPILE, och den kommer att kompileras om vid varje körning.

Kompilering och frågebearbetning

Följande diagram illustrerar kompileringsprocessen för internt kompilerade lagrade procedurer:

Intern kompilering av lagrade procedurer.

Processen beskrivs som,

Användaren utfärdar en CREATE PROCEDURE-instruktion till SQL Server.
Parsern och algebrizern skapar bearbetningsflödet för proceduren samt frågeträd för Transact-SQL-frågor i den lagrade proceduren.
Optimeraren skapar optimerade frågekörningsplaner för alla frågor i den lagrade proceduren.
In-Memory OLTP-kompilatorn tar bearbetningsflödet med inbäddade optimerade frågeplaner och genererar en DLL som innehåller datorkoden för att köra den lagrade proceduren.
Den genererade DLL:en läses in i minnet.

Anrop av en inbyggt kompilerad lagrad procedur översätts till att anropa en funktion i DLL:en.

Körning av internt kompilerade lagrade procedurer.

Anrop av en inbyggt kompilerad lagrad procedur beskrivs på följande sätt:

Användaren utfärdar en EXEC-usp_myproc-instruktion.
Parsern extraherar parametrarna för namn och lagrad procedur.

Om påståendet har förberetts, till exempel genom att använda sp_prep_exec, behöver parsern inte extrahera procedurnamnet och parametrarna vid körning.
In-Memory OLTP-körning letar upp DLL-startpunkten för den lagrade proceduren.
Datorkoden i DLL:en körs och resultatet av returneras till klienten.

Parametern sniffar

Tolkade Transact-SQL lagrade procedurer kompileras vid första körningen, till skillnad från internt kompilerade lagrade procedurer som kompileras vid skapandetillfället. När tolkade lagrade procedurer kompileras vid anrop används värdena för de parametrar som anges för det här anropet av optimeraren för att generera körningsplanen. Den här användningen av parametrar under kompileringen kallas parametersniffning.

Parametersniffning används inte för kompilering av internt kompilerade lagrade procedurer. Alla parametrar i den lagrade proceduren anses ha OKÄNDA värden. Precis som tolkade lagrade procedurer stöder inbyggda lagrade procedurer även OPTIMERA FÖR ledtråd. Mer information finns i Frågetips (Transact-SQL).

Hämta en frågekörningsplan för internt kompilerade lagrade procedurer

Frågekörningsplanen för en nativt kompilerad lagrad procedur kan hämtas med Uppskattad körningsplan i Management Studio eller med hjälp av alternativet SHOWPLAN_XML i Transact-SQL. Till exempel:

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

Körningsplanen som genereras av frågeoptimeraren består av ett träd med frågeoperatörer på trädes noder och blad. Trädets struktur avgör interaktionen (flödet av rader från en operator till en annan) mellan operatorerna. I den grafiska vyn i SQL Server Management Studio är flödet från höger till vänster. Till exempel innehåller frågeplanen i bild 1 två indexskanningsoperatorer som tillhandahåller rader till en sammanfogningsoperator. Sammanslagningskopplingsoperatorn tillhandahåller rader till en select-operator. Väljoperatorn returnerar slutligen raderna till klienten.

Frågeoperatorer i internt kompilerade lagrade procedurer

I följande tabell sammanfattas de frågeoperatorer som stöds i inbyggda kompilerade lagrade procedurer:

Operatör	Exempelfråga	Anteckningar
VÄLJ	`SELECT OrderID FROM dbo.[Order]`
INFOGA	`INSERT dbo.Customer VALUES ('abc', 'def')`
UPPDATERA	`UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'`
TA BORT	`DELETE dbo.Customer WHERE CustomerID='abc'`
Beräkningsskalar	`SELECT OrderID+1 FROM dbo.[Order]`	Den här operatorn används både för inbyggda funktioner och typkonverteringar. Alla funktioner och typkonverteringar stöds inte i internt kompilerade lagrade procedurer.
Kapslade loopar - sammanfogning	`SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c`	Kapslade loopar är den enda sammanfogningsoperator som stöds i nativt kompilerade lagrade procedurer. Alla planer som innehåller sammanslagningar använder operatorn kapslade loopar, även om planen för samma fråga som körs som interpretad Transact-SQL innehåller en hash- eller sammanslagningsoperation.
Sortera	`SELECT ContactName FROM dbo.Customer ORDER BY ContactName`
Topp	`SELECT TOP 10 ContactName FROM dbo.Customer`
Toppsortering	`SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName`	Uttrycket TOP (antalet rader som ska returneras) får inte överstiga 8 000 rader. Färre om det också finns kopplings- och aggregeringsoperatorer i sökfrågan. Kopplingar och sammansättning minskar vanligtvis antalet rader som ska sorteras, jämfört med antalet rader i bastabellerna.
Datainströmning och aggregering	`SELECT count(CustomerID) FROM dbo.Customer`	Observera att Hash Match-operatorn inte stöds för aggregering. Därför använder alla aggregeringar i internt kompilerade lagrade procedurer Stream Aggregate-operatorn, även om planen för samma fråga i tolkad Transact-SQL använder Hash Match-operatorn.

Kolumnstatistik och kopplingar

SQL Server har statistik över värden i indexnyckelkolumner för att beräkna kostnaden för vissa åtgärder, till exempel indexgenomsökning och indexsökningar. ( SQL Server skapar också statistik över icke-indexnyckelkolumner om du uttryckligen skapar dem eller om frågeoptimeraren skapar dem som svar på en fråga med ett predikat.) Huvudmåttet i kostnadsuppskattning är antalet rader som bearbetas av en enda operator. Observera att för diskbaserade tabeller är antalet sidor som används av en viss operator betydande i kostnadsuppskattningen. Men eftersom sidantalet inte är viktigt för minnesoptimerade tabeller (det är alltid noll) fokuserar den här diskussionen på radantal. Uppskattningen börjar med indexsöknings- och genomsökningsoperatorerna i planen och utökas därefter till att omfatta de övriga operatorerna, såsom kopplingsoperatorn. Det uppskattade antalet rader som ska bearbetas av en kopplingsoperator baseras på uppskattningen för de underliggande index-, sök- och genomsökningsoperatorerna. För tolkad åtkomst till Transact-SQL minnesoptimerade tabeller kan du observera den faktiska utförandeplanen för att se skillnaden mellan det uppskattade och faktiska radantalet för operatorerna inom planen.

För exemplet i bild 1,

Den klustrade indexgenomsökningen på kunden har uppskattat 91; faktiska 91.
Den icke-klustrade indexsökningen på CustomerID har uppskattat 830; faktiska 830.
Merge Join-operatorn har uppskattningen 815; det faktiska värdet är 830.

Uppskattningarna för indexgenomsökningarna är korrekta. SQL Server underhåller radantalet för diskbaserade tabeller. Uppskattningar för fullständiga tabell- och indexgenomsökningar är alltid korrekta. Uppskattningen för kopplingen är också ganska exakt.

Om dessa uppskattningar ändras ändras även kostnadsövervägandena för olika planalternativ. Om en av sidorna av kopplingen till exempel har ett uppskattat radantal på 1 eller bara några rader är det billigare att använda kapslade loop-kopplingar. Överväg följande fråga:

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

När du har raderat alla rader utom en i tabellen Customer genereras följande frågeplan:

Kolumnstatistik och kopplingar.

Angående den här frågeplanen:

Hash-matchningen har ersatts med en fysisk kopplingsoperator för kapslade loopar.
Den fullständiga indexsökningen på IX_CustomerID har ersatts med en indexsökning. Detta resulterade i genomsökning av 5 rader i stället för de 830 rader som krävs för den fullständiga indexgenomsökningen.

Se även

Memory-Optimized tabeller

Dela via