Een handleiding voor het uitvoeren van query's voor Memory-Optimized tabellen

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In-Memory OLTP introduceert tabellen die zijn geoptimaliseerd voor geheugen en systeemeigen gecompileerde opgeslagen procedures in SQL Server. Dit artikel bevat een overzicht van queryverwerking voor tabellen die zijn geoptimaliseerd voor geheugen en systeemeigen opgeslagen procedures.

In het document wordt uitgelegd hoe query's op tabellen die zijn geoptimaliseerd voor geheugen, worden gecompileerd en uitgevoerd, waaronder:

• De pijplijn voor het verwerken van query's in SQL Server voor tabellen op basis van schijven.

• Queryoptimalisatie; de rol van statistieken voor tabellen die zijn geoptimaliseerd voor geheugen en richtlijnen voor het oplossen van problemen met slechte queryplannen.

• Het gebruik van geïnterpreteerde Transact-SQL voor toegang tot tabellen die zijn geoptimaliseerd voor geheugen.

• Overwegingen over queryoptimalisatie voor geheugen-geoptimaliseerde tabeltoegang.

• Systeemeigen gecompileerde opgeslagen procedure compilatie en verwerking.

• Statistieken die worden gebruikt voor kostenramingen door de optimizer.

• Methoden om slechte queryplannen op te lossen.

Voorbeeldquery

Het volgende voorbeeld wordt gebruikt om de concepten voor queryverwerking te illustreren die in dit artikel worden besproken.

We beschouwen twee tabellen, Klant en Order. Het volgende Transact-SQL script bevat de definities voor deze twee tabellen en bijbehorende indexen, in hun (traditionele) schijfvorm:

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  

Voor het maken van de queryplannen die in dit artikel worden weergegeven, zijn de twee tabellen gevuld met voorbeeldgegevens uit de Northwind-voorbeelddatabase, die u kunt downloaden van Northwind- en pubsvoorbeelddatabases voor SQL Server 2000.

Houd rekening met de volgende query, die de tabellen Klant en Order samenvoegt en de id van de order en de bijbehorende klantgegevens retourneert:

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

Het geschatte uitvoeringsplan zoals weergegeven door SQL Server Management Studio is als volgt

Queryplan voor het samenvoegen van tabellen op basis van schijven.

Over dit queryplan:

• De rijen uit de tabel Klant worden opgehaald uit de geclusterde index. Dit is de primaire gegevensstructuur en bevat de volledige tabelgegevens.

• Gegevens uit de tabel Order worden opgehaald met behulp van de niet-geclusterde index in de kolom CustomerID. Deze index bevat zowel de kolom CustomerID, die wordt gebruikt voor de join en de primaire-sleutelkolom OrderID, die wordt geretourneerd aan de gebruiker. Voor het retourneren van extra kolommen uit de tabel Order zijn zoekacties in de geclusterde index voor de tabel Order vereist.

• De logische operator Inner Join wordt geïmplementeerd door de fysieke operator Merge Join. De andere fysieke join-types zijn Nested Loops en Hash Join. De samenvoegoperator maakt gebruik van het feit dat beide indexen zijn gesorteerd op de klant-ID kolom van de join.

Overweeg een kleine variatie op deze query, die alle kolommen uit de tabel Order retourneert, niet alleen order-id-kolom:

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

Het geschatte plan voor deze query is:

Queryplan voor een hash-join van schijftabellen.

In deze query worden rijen uit de tabel Order opgehaald met behulp van de geclusterde index. De Hash Match fysieke operator wordt nu gebruikt voor de Inner Join-. De geclusterde index op Order is niet gesorteerd op CustomerID, waardoor een Merge Join een sorteeroperator vereist, iets dat de prestaties zou beïnvloeden. Let op de relatieve kosten van de Hash Match operator (75%) vergeleken met de kosten van de Merge Join operator in het vorige voorbeeld (46%). De optimizer zou in het vorige voorbeeld ook de operator Hash Match hebben overwogen, maar geconcludeerd dat de operator Merge Join betere prestaties leverde.

SQL Server-queryverwerking voor Disk-Based tabellen

In het volgende diagram ziet u een overzicht van de queryverwerkingsstroom in SQL Server voor ad-hocquery's:

Sql Server-pijplijn voor queryverwerking.

In dit scenario:

1. De gebruiker geeft een query uit.

2. De parser en algebrizer maken een querystructuur met logische operatoren op basis van de Transact-SQL tekst die door de gebruiker is verzonden.

3. De optimizer maakt een geoptimaliseerd queryplan met een fysieke operator (bijvoorbeeld geneste lussen-koppeling). Na optimalisatie kan het plan worden opgeslagen in de plancache. Deze stap wordt overgeslagen als de plancache al een plan voor deze query bevat.

4. De query-uitvoeringsengine verwerkt een interpretatie van het queryplan.

5. Voor elk indexzoeken, indexscan en tabelscanoperator vraagt de uitvoeringsengine rijen op van de respectieve index- en tabelstructuren van Access Methods.

6. Access Methods haalt de rijen op uit de index- en gegevenspagina's in de buffergroep en laadt indien nodig pagina's van schijf naar de buffergroep.

Voor de eerste voorbeeldquery vraagt de uitvoeringsengine rijen op in de geclusterde index op Klant en de niet-geclusterde index op Order vanuit Access Methods. Access Methods doorkruist de B-structuurindexstructuren om de aangevraagde rijen op te halen. In dit geval worden alle rijen opgehaald omdat het plan vraagt om volledige index scans.

Notitie

Documentatie maakt gebruik van de term B-tree in het algemeen in verwijzing naar indexen. In rijstore-indexen implementeert de Database-engine een B+-boom. Dit geldt niet voor columnstore-indexen of indexen voor tabellen die zijn geoptimaliseerd voor geheugen. Zie de SQL Server- en Azure SQL-indexarchitectuur en ontwerphandleidingvoor meer informatie.

Geïnterpreteerde Transact-SQL toegang tot Memory-Optimized tabellen

Transact-SQL ad-hocbatches en opgeslagen procedures worden ook wel geïnterpreteerd als Transact-SQL. Geïnterpreteerd verwijst naar het feit dat het queryplan wordt geïnterpreteerd door de queryuitvoeringsengine voor elke operator in het queryplan. De uitvoeringsengine leest de operator en de bijbehorende parameters en voert de bewerking uit.

Geïnterpreteerde Transact-SQL kan worden gebruikt voor toegang tot zowel geheugen-geoptimaliseerde als schijftabellen. In de volgende afbeelding ziet u queryverwerking voor geïnterpreteerde Transact-SQL toegang tot tabellen die zijn geoptimaliseerd voor geheugen:

nl-NL: Pijplijn voor queryverwerking voor Transact-SQL-geïnterpreteerde toegang tot geheugen-geoptimaliseerde tabellen.

Zoals u in de afbeelding kunt zien, blijft de pijplijn voor queryverwerking grotendeels ongewijzigd:

• De parser en algebrizer maken de querystructuur.

• De optimizer maakt het uitvoeringsplan.

• De engine voor het uitvoeren van query's interpreteert het uitvoeringsplan.

Het belangrijkste verschil met de traditionele pijplijn voor queryverwerking (afbeelding 2) is dat rijen voor tabellen die zijn geoptimaliseerd voor geheugen, niet worden opgehaald uit de buffergroep met behulp van Access-methoden. In plaats daarvan worden rijen opgehaald uit de in-memory gegevensstructuren via de In-Memory OLTP-engine. Verschillen in gegevensstructuren zorgen ervoor dat de optimizer in sommige gevallen verschillende plannen kiest, zoals wordt geïllustreerd in het volgende voorbeeld.

Het volgende Transact-SQL script bevat voor geheugen geoptimaliseerde versies van de tabellen Order en Klant, met behulp van hash-indexen:

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  

Houd rekening met dezelfde query die wordt uitgevoerd op tabellen die zijn geoptimaliseerd voor geheugen:

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

Het geschatte plan is als volgt:

Queryplan voor het samenvoegen van tabellen die zijn geoptimaliseerd voor geheugen.

Bekijk de volgende verschillen met het plan voor dezelfde query op schijftabellen (afbeelding 1):

• Dit plan bevat een tabelscan in plaats van een geclusterde indexscan voor de tabel Klant:

  • De definitie van de tabel bevat geen geclusterde index.

  • Geclusterde indexen worden niet ondersteund met tabellen die zijn geoptimaliseerd voor geheugen. In plaats daarvan moet elke tabel die is geoptimaliseerd voor geheugen ten minste één niet-geclusterde index hebben en alle indexen in tabellen die zijn geoptimaliseerd voor geheugen, efficiënt toegang hebben tot alle kolommen in de tabel zonder ze in de index op te slaan of naar een geclusterde index te verwijzen.

• Dit plan bevat een Hash-vergelijking in plaats van een Merge Join. De indexen voor zowel de tabel Order als De tabel Klant zijn hash-indexen en worden dus niet geordend. Voor een Merge Join- zijn sorteeroperators vereist die de prestaties zouden verminderen.

Systeemeigen gecompileerde opgeslagen procedures

Natively gecompileerde opgeslagen procedures zijn Transact-SQL procedures die zijn gecompileerd tot machinecode, in plaats van geïnterpreteerd door de query-uitvoeringsengine. Met het volgende script maakt u een systeemeigen gecompileerde opgeslagen procedure waarmee de voorbeeldquery wordt uitgevoerd (uit de sectie Voorbeeldquery).

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  

Systeemeigen gecompileerde opgeslagen procedures worden gecompileerd tijdens het maken, terwijl geïnterpreteerde opgeslagen procedures worden gecompileerd tijdens de eerste uitvoering. (Een deel van de compilatie, met name parseren en algebrization, vindt plaats bij het maken. Voor geïnterpreteerde opgeslagen procedures vindt optimalisatie van de queryplannen echter plaats bij de eerste uitvoering.) De hercompilatielogica is vergelijkbaar. Systeemeigen gecompileerde opgeslagen procedures worden opnieuw gecompileerd bij de eerste uitvoering van de procedure als de server opnieuw wordt opgestart. Geïnterpreteerde opgeslagen procedures worden opnieuw gecompileerd als het plan zich niet meer in de plancache bevindt. De volgende tabel bevat een overzicht van compilatie- en hercompilatiecases voor zowel systeemeigen gecompileerde als geïnterpreteerde opgeslagen procedures:

Compilatietype	Direct gecompileerd	Geïnterpreteerd
Eerste compilatie	Bij aanmaaktijdstip.	Bij de eerste uitvoering.
Automatische hercompilatie	Bij de eerste uitvoering van de procedure nadat een database of server opnieuw is opgestart.	Bij het opnieuw opstarten van de server. Ofwel verwijdering uit de plancache, meestal op basis van wijzigingen in schema of statistieken, of geheugendruk.
Handmatige hercompilatie	Gebruik sp_recompile.	Gebruik sp_recompile. U kunt het plan handmatig uit de cache verwijderen, bijvoorbeeld via DBCC FREEPROCCACHE. U kunt ook de opgeslagen procedure MET RECOMPILE maken en de opgeslagen procedure wordt bij elke uitvoering opnieuw gecompileerd.

Compilatie en queryverwerking

In het volgende diagram ziet u het compilatieproces voor systeemeigen gecompileerde opgeslagen procedures:

Systeemeigen compilatie van opgeslagen procedures.

Het proces wordt beschreven als:

1. De gebruiker voert een CREATE PROCEDURE statement uit naar SQL Server.

2. De parser en algebrizer creëren de verwerkingsstroom voor de procedure en de boomstructuren voor de Transact-SQL-query's in de opgeslagen procedure.

3. De optimizer maakt geoptimaliseerde queryuitvoeringsplannen voor alle query's in de opgeslagen procedure.

4. De In-Memory OLTP-compiler neemt de verwerkingsstroom met de ingesloten geoptimaliseerde queryplannen en genereert een DLL die de machinecode bevat voor het uitvoeren van de opgeslagen procedure.

5. De gegenereerde DLL wordt in het geheugen geladen.

Het aanroepen van een systeemeigen gecompileerde opgeslagen procedure wordt omgezet in het aanroepen van een functie in het DLL-bestand.

Uitvoering van systeemeigen gecompileerde opgeslagen procedures.

Aanroep van een systeemeigen gecompileerde opgeslagen procedure wordt als volgt beschreven:

1. De gebruiker voert een EXECusp_myproc instructie uit.

2. De parser extraheert de naam en opgeslagen procedureparameters.

   Als de instructie is voorbereid, bijvoorbeeld met behulp van sp_prep_exec, hoeft de parser niet de naam van de procedure en parameters op te halen tijdens de uitvoering.

3. De In-Memory OLTP-runtime zoekt het DLL-invoerpunt voor de opgeslagen procedure.

4. De computercode in het DLL-bestand wordt uitgevoerd en de resultaten worden geretourneerd naar de client.

Parameter sniffing

Geïnterpreteerde Transact-SQL stored procedures worden bij de eerste uitvoering gecompileerd, in tegenstelling tot systeemeigen gecompileerde stored procedures, die bij het aanmaken worden gecompileerd. Wanneer geïnterpreteerde opgeslagen procedures worden gecompileerd bij aanroep, worden de waarden van de parameters die voor deze aanroep worden opgegeven door de optimizer gebruikt bij het genereren van het uitvoeringsplan. Dit gebruik van parameters tijdens de compilatie wordt parameter-sniffing genoemd.

Parameter-sniffing wordt niet gebruikt voor het compileren van systeemeigen gecompileerde opgeslagen procedures. Alle parameters voor de opgeslagen procedure worden beschouwd als onbekende waarden. Net als geïnterpreteerde opgeslagen procedures ondersteunen inheemse gecompileerde opgeslagen procedures ook de OPTIMIZE FOR hint. Zie queryhints (Transact-SQL)voor meer informatie.

Een queryuitvoeringsplan ophalen voor systeemeigen gecompileerde opgeslagen procedures

Het queryuitvoeringsplan voor een systeemeigen opgeslagen procedure kan worden opgehaald met behulp van Geschat uitvoeringsplan in Management Studio of met behulp van de optie SHOWPLAN_XML in Transact-SQL. Bijvoorbeeld:

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

Het uitvoeringsplan dat door de queryoptimalisatie wordt gegenereerd, bestaat uit een structuur met queryoperators op de knooppunten en bladeren van de structuur. De structuur van de boom bepaalt de interactie (de stroom van rijen van de ene operator naar de andere) tussen de operators. In de grafische weergave van SQL Server Management Studio is de stroom van rechts naar links. Het queryplan in afbeelding 1 bevat bijvoorbeeld twee indexscanoperators, die rijen levert aan een samenvoegoperator. De samenvoegoperator levert rijen aan een selectieoperator. De select-operator retourneert tot slot de rijen naar de client.

Queryoperators in systeemeigen gecompileerde opgeslagen procedures

De volgende tabel bevat een overzicht van de queryoperators die worden ondersteund in systeemeigen gecompileerde opgeslagen procedures:

Bediener	Voorbeeldquery	Notities
SELECTEREN	SELECT OrderID FROM dbo.[Order]
INVOEGEN	INSERT dbo.Customer VALUES ('abc', 'def')
UPDATE	UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
VERWIJDEREN	DELETE dbo.Customer WHERE CustomerID='abc'
Scalar berekenen	SELECT OrderID+1 FROM dbo.[Order]	Deze operator wordt zowel gebruikt voor intrinsieke functies als typeconversies. Niet alle functies en typeconversies worden ondersteund in systeemeigen gecompileerde opgeslagen procedures.
Geneste lussen samenvoegen	SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c	Geneste lussen is de enige join-operator die wordt ondersteund in systeemeigen gecompileerde opgeslagen procedures. Alle plannen die joins bevatten, maken gebruik van de Nested Loops-operator, zelfs als het plan voor dezelfde query uitgevoerd als Transact-SQL een hash- of samenvoegjoin bevat.
Sorteren	SELECT ContactName FROM dbo.Customer ORDER BY ContactName
Boven	SELECT TOP 10 ContactName FROM dbo.Customer
Topsorteerd	SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName	De expressie TOP (het aantal rijen dat moet worden geretourneerd) mag niet groter zijn dan 8000 rijen. Minder wanneer er ook join- en aggregatieoperators in de query aanwezig zijn. Joins en aggregatie verminderen doorgaans het aantal rijen dat moet worden gesorteerd, vergeleken met het aantal rijen van de basistabellen.
Stroomaggregatie	SELECT count(CustomerID) FROM dbo.Customer	Houd er rekening mee dat de Hash Match-operator niet wordt ondersteund voor aggregatie. Daarom maakt alle aggregatie in opgeslagen procedures die inheems zijn gecompileerd gebruik van de Stream Aggregate-operator, zelfs als het plan voor dezelfde query in geïnterpreteerde Transact-SQL gebruikmaakt van de Hash Match-operator.

Kolomstatistieken en joins

SQL Server onderhoudt statistieken over waarden in indexsleutelkolommen om de kosten van bepaalde bewerkingen te schatten, zoals indexscans en indexzoekopdrachten. ( SQL Server maakt ook statistieken voor niet-indexsleutelkolommen als u deze expliciet maakt of als de queryoptimalisatie deze maakt als reactie op een query met een predicaat.) De belangrijkste metrische waarde in kostenraming is het aantal rijen dat door één operator wordt verwerkt. Houd er rekening mee dat voor tabellen op basis van schijven het aantal pagina's dat door een bepaalde operator wordt geopend, aanzienlijk is in de kostenraming. Omdat het aantal pagina's echter niet belangrijk is voor tabellen die zijn geoptimaliseerd voor geheugen (het is altijd nul), richt deze discussie zich op het aantal rijen. De schatting begint met de indexzoek- en scanoperators in het plan en wordt vervolgens uitgebreid met de andere operators, zoals de join-operator. Het geschatte aantal rijen dat door een joinoperator moet worden verwerkt, is gebaseerd op de schatting van de onderliggende index, zoek- en scanoperators. Voor geïnterpreteerde Transact-SQL toegang tot tabellen die zijn geoptimaliseerd voor geheugen, kunt u het werkelijke uitvoeringsplan bekijken om het verschil te zien tussen het geschatte en werkelijke aantal rijen voor de operators in het plan.

Voor het voorbeeld in afbeelding 1,

• De geclusterde indexscan op Klant schatte 91; werkelijk 91.
• De niet-geclusterd indexscan op CustomerID heeft een schatting van 830; de werkelijke waarde is 830.
• De operator Join samenvoegen heeft geschat op 815; werkelijk 830.

De schattingen voor de indexscans zijn nauwkeurig. SQL Server onderhoudt het aantal rijen voor tabellen op basis van schijven. Schattingen voor volledige tabel- en indexscans zijn altijd nauwkeurig. De schatting voor de koppeling is ook redelijk nauwkeurig.

Als deze schattingen veranderen, worden ook de kostenoverwegingen voor verschillende abonnementsalternatieven gewijzigd. Als een van de zijden van de join bijvoorbeeld een geschatte rijaantal van 1 of slechts een paar rijen heeft, is het gebruik van een geneste lussen-joins goedkoper. Houd rekening met de volgende query:

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

Na het verwijderen van alle rijen, maar één in de Customer tabel, wordt het volgende queryplan gegenereerd:

Met betrekking tot dit queryplan:

• De Hash-overeenkomst is vervangen door een fysieke joinoperator geneste lussen.
• De volledige indexscan op IX_CustomerID is vervangen door een indexzoekfunctie. Dit heeft geresulteerd in het scannen van 5 rijen in plaats van de 830 rijen die nodig zijn voor de volledige indexscan.

Zie ook

Memory-Optimized tabellen