Útmutató a Memory-Optimized-táblák lekérdezésfeldolgozásához

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Felügyelt Példány

In-Memory OLTP memóriaoptimalizált táblákat és natívan lefordított tárolt eljárásokat vezet be az SQL Serveren. Ez a cikk áttekintést nyújt a memóriaoptimalizált táblák és a natívan lefordított tárolt eljárások lekérdezésfeldolgozásáról.

A dokumentum bemutatja a memóriaoptimalizált táblák lekérdezéseinek fordítását és végrehajtását, beleértve a következőket:

• A lemezalapú táblák lekérdezésfeldolgozási folyamata az SQL Serverben.

• Lekérdezésoptimalizálás; a memóriaoptimalizált táblák statisztikáinak szerepe, valamint a hibás lekérdezési tervek hibaelhárítására vonatkozó irányelvek.

• Az értelmezett Transact-SQL használata a memóriaoptimalizált táblák eléréséhez.

• A memóriaoptimalizált táblahozzáférés lekérdezésoptimalizálásával kapcsolatos szempontok.

• Natívan lefordított tárolt eljárás fordítása és feldolgozása.

• Az optimalizáló által a költségbecsléshez használt statisztikák.

• A hibás lekérdezési tervek kijavításának módjai.

Példa lekérdezés

Az alábbi példa a cikkben tárgyalt lekérdezésfeldolgozási fogalmak szemléltetésére szolgál.

Két táblát veszünk figyelembe, az Ügyfél és a Rendelés táblát. A következő Transact-SQL szkript a két tábla és a társított indexek definícióit tartalmazza a (hagyományos) lemezalapú formájukban:

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  

Az ebben a cikkben bemutatott lekérdezési tervek létrehozásához a két tábla a Northwind mintaadatbázisból származó mintaadatokkal lett feltöltve, amelyeket az SQL Server 2000 Northwind és pubs mintaadatbázisaiból tölthet le.

Fontolja meg a következő lekérdezést, amely a Customer és a Order táblákhoz csatlakozik, és visszaadja a rendelés azonosítóját és a kapcsolódó ügyféladatokat:

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

Az SQL Server Management Studio által megjelenített becsült végrehajtási terv a következő

Lemezalapú táblák összekapcsolásának lekérdezési terve.

A lekérdezési terv ismertetése:

• Az Ügyfél tábla sorai a fürtözött indexből lesznek lekérve, amely az elsődleges adatstruktúra, és a teljes táblaadatokkal rendelkezik.

• A Rendelés tábla adatai a CustomerID oszlop nem klaszterezett indexének segítségével kérhetők le. Ez az index tartalmazza az illesztéshez használt CustomerID oszlopot és az OrderID elsődleges kulcsoszlopot is, amelyet a rendszer visszaad a felhasználónak. A Rendelés táblából történő további oszlopok lekérdezése megkövetelné a keresést a Rendelés tábla fürtözött indexében.

• A logikai operátort a fizikai operátor Összeolvasztás kapcsolóáltal valósítják meg. A többi fizikai illesztéstípus a Beágyazott Hurkok és a Hash Illesztés. Az Merge Join operátor kihasználja, hogy mindkét index a CustomerID illesztési oszlop szerint van rendezve.

Vegyünk egy kis eltérést ezen a lekérdezésen, amely az Order tábla összes oszlopát adja vissza, nem csak az OrderID oszlopot:

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

A lekérdezés becsült terve a következő:

Lemezalapú táblák hash illesztésének lekérdezési terve.

Ebben a lekérdezésben az Order tábla sorait a klaszterezett index használatával kérik le. A Hash illesztés fizikai operátort mostantól a Belső illesztéshasználják. A Sorrend fürtözött indexe nincs a CustomerID alapján rendezve, ezért egy egyesítéses illesztés egy rendezési operátort igényelne, amely hatással lenne a teljesítményre. Figyelje meg a Kivonategyezés operátor relatív költségét (75%), összehasonlítva az előző példában szereplő Egyesítés operátor költségével (46%). Az optimalizáló az előző példában is figyelembe vette volna a Hash Match operátort, de arra a következtetésre jutott, hogy a Merge Join operátor jobb teljesítményt nyújtott.

SQL Server-lekérdezésfeldolgozás Disk-Based táblákhoz

Az alábbi diagram az SQL Server lekérdezésfeldolgozási folyamatát mutatja be az alkalmi lekérdezések esetében:

SQL Server lekérdezésfeldolgozási folyamat.

Ebben a forgatókönyvben:

1. A felhasználó lekérdezést ad ki.

2. Az elemző és az algebráló logikai operátorokkal rendelkező lekérdezésfát hoz létre a felhasználó által küldött Transact-SQL szöveg alapján.

3. Az optimalizáló létrehoz egy fizikai operátorokat tartalmazó optimalizált lekérdezési tervet (például beágyazott hurkok illesztését). Az optimalizálás után a terv a terv gyorsítótárában tárolódhat. Az a lépés kimarad, ha a tervgyorsítótár már tartalmaz egy tervet az adott lekérdezéshez.

4. A lekérdezés-végrehajtási motor feldolgozza a lekérdezésterv értelmezését.

5. Az egyes indexkeresési, indexvizsgálati és táblavizsgálati operátorok esetében a végrehajtási motor sorokat kér le a megfelelő index- és táblastruktúrákból az Access-metódusokból.

6. A hozzáférési módszerek lekérik a sorokat az indexekből és adottáblákból a pufferterületre, és szükség szerint betöltik a lemezen lévő lapokat a pufferterületbe.

Az első példa lekérdezés esetében a végrehajtási motor kéri le a fürtözött index sorait a vevőn, valamint a nem-fürtözött indexet a Megrendelésen a hozzáférési módszerekből. A hozzáférési módszerek bejárják az B-fa indexstruktúrákat a kért sorok lekéréséhez. Ebben az esetben a rendszer az összes sort lekéri, amikor a terv a teljes indexvizsgálatot kéri.

Jegyzet

A dokumentáció általában a B-fa kifejezést használja az indexekre hivatkozva. A sorkataszterekben az adatbázismotor egy B+ fát implementál. Ez nem vonatkozik az oszlopcentrikus indexekre vagy a memóriaoptimalizált táblák indexére. További információ: SQL Server és Azure SQL index architektúrája és tervezési útmutatója.

Transact-SQL Memory-Optimized-táblákhoz való hozzáférés értelmezése

Transact-SQL Az értelmezett Transact-SQL-nek alkalmi kötegeket és tárolt eljárásokat is nevezzük. Az értelmezés arra utal, hogy a lekérdezéstervet a lekérdezés végrehajtási motorja értelmezi a tervben szereplő operátorok mindegyikére. A végrehajtó motor beolvassa az operátort és annak paramétereit, és végrehajtja a műveletet.

Az értelmezett Transact-SQL memóriaoptimalizált és lemezalapú táblák elérésére is használható. Az alábbi ábra a memóriaoptimalizált táblákhoz való hozzáférés Transact-SQL értelmezett lekérdezésfeldolgozását szemlélteti:

Lekérdezésfeldolgozási folyamat az értelmezett Transact-SQL memóriaoptimalizált táblákhoz való hozzáféréshez.

Az ábrán látható módon a lekérdezésfeldolgozási folyamat többnyire változatlan marad:

• Az elemző és az algebráló hozza létre a lekérdezési fát.

• Az optimalizáló létrehozza a végrehajtási tervet.

• A lekérdezés-végrehajtási motor értelmezi a végrehajtási tervet.

A hagyományos lekérdezésfeldolgozási folyamat (2. ábra) fő különbsége, hogy a memóriaoptimalizált táblák sorai nem lesznek lekérve a pufferkészletből access metódusok használatával. Ehelyett a sorokat a rendszer a memóriában lévő adatstruktúrákból kéri le az In-Memory OLTP-motoron keresztül. Az adatstruktúrák eltérései miatt az optimalizáló bizonyos esetekben különböző csomagokat választ ki, ahogyan azt az alábbi példa is szemlélteti.

A következő Transact-SQL szkript a Rendelés és ügyfél táblák memóriaoptimalizált verzióit tartalmazza kivonatindexek használatával:

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  

Vegye figyelembe a memóriaoptimalizált táblákon végrehajtott lekérdezést:

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

A becsült terv a következő:

A memóriaoptimalizált táblák összekapcsolásának lekérdezési terve.

Figyelje meg az alábbi különbségeket a lemezalapú táblák ugyanazon lekérdezésének tervével (1. ábra):

• Ez a terv táblázatvizsgálatot tartalmaz a fürtözött indexvizsgálat helyett az Ügyfél táblánál.

  • A tábla definíciója nem tartalmaz fürtözött indexet.

  • A klaszterezett indexek nem támogatottak a memóriaoptimalizált táblákkal. Ehelyett minden memóriaoptimalizált táblának kell rendelkeznie legalább egy nem klaszterezett indexszel, és a memóriaoptimalizált táblák összes indexe hatékonyan hozzáférhet a tábla összes oszlopához anélkül, hogy az indexben kellene tárolnia őket, vagy klaszterezett indexre kellene hivatkoznia.

• Ez a terv Hash Match-t tartalmaz, nem pedig Merge Join-t. A Rendelés és az Ügyfél tábla indexei kivonatindexek, ezért nincsenek rendezve. Az egyesítési olyan rendezési operátorokat igényelne, amelyek csökkentik a teljesítményt.

Natívan lefordított tárolt eljárások

A natívan lefordított tárolt eljárások Transact-SQL gépi kódra lefordított tárolt eljárásokat, nem pedig a lekérdezés-végrehajtási motor által értelmezett eljárásokat. Az alábbi szkript létrehoz egy natívan lefordított tárolt eljárást, amely a példa lekérdezést futtatja (a Példa lekérdezés szakaszból).

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  

A natívan lefordított tárolt eljárások fordítása létrehozáskor történik, míg az értelmezett tárolt eljárások fordítása az első végrehajtási időpontban történik. (A fordítás egy része, különösen az elemzés és az algebrizáció létrehozásakor történik. Az értelmezett tárolt eljárások esetében azonban a lekérdezéstervek optimalizálása az első végrehajtáskor történik.) Az újrafordítási logika hasonló. A natívan lefordított tárolt eljárások újrafordítása az eljárás első végrehajtásakor történik, ha a kiszolgáló újraindul. Az értelmezett tárolt eljárásokat újra lefordítják, ha a terv már nem szerepel a tervtárban. Az alábbi táblázat a natívan lefordított és értelmezett tárolt eljárások fordítási és újrafordítási eseteit foglalja össze:

Kompilálás típusa	Natívan fordított	Értelmezve
Kezdeti kompiláció	Létrehozáskor.	Első végrehajtáskor.
Automatikus újrafordítás	Az adatbázis vagy a kiszolgáló újraindítása után az eljárás első végrehajtásakor.	A kiszolgáló újraindításakor. Vagy a gyorsítótárból való terv eltávolítása, amely általában séma- vagy statisztikai változásokon vagy memóriaterhelésen alapul.
Manuális újrafordítás	Használja a sp_recompile.	Használja a sp_recompile. Manuálisan kiürítheti a tervet a gyorsítótárból, például a DBCC FREEPROCCACHE-n keresztül. A tárolt eljárást úgy is létrehozhatja, hogy megadja a WITH RECOMPILE opciót, így az eljárás minden végrehajtáskor újrafordításra kerül.

Kompilálás és lekérdezés feldolgozás

Az alábbi ábra a natívan lefordított tárolt eljárások fordítási folyamatát mutatja be:

Tárolt eljárások natív kompilálása.

A folyamat leírása:

1. A felhasználó kiad egy CREATE PROCEDURE utasítást az SQL Servernek.

2. Az elemző és az algebráló létrehozza az eljárás feldolgozási folyamatát, valamint a tárolt eljárás Transact-SQL lekérdezéseinek lekérdezési fákat.

3. Az optimalizáló optimalizált lekérdezés-végrehajtási terveket hoz létre a tárolt eljárás összes lekérdezéséhez.

4. Az In-Memory OLTP-fordító a beágyazott optimalizált lekérdezési tervekkel veszi át a feldolgozási folyamatot, és létrehoz egy DLL-t, amely tartalmazza a tárolt eljárás végrehajtásához szükséges gépi kódot.

5. A létrehozott DLL betöltődik a memóriába.

Egy natívan lefordított tárolt eljárás meghívása a DLL-ben egy függvény meghívását jelenti.

Natívan lefordított tárolt eljárások végrehajtása.

Natívan lefordított tárolt eljárás meghívása a következőképpen írható le:

1. A felhasználó kiad egy EXECusp_myproc utasítást.

2. Az elemző kinyeri a nevet és a tárolt eljárás paramétereit.

   Ha az utasítás készült, például sp_prep_exechasználatával, az elemzőnek nem kell kinyernie az eljárás nevét és paramétereit a végrehajtáskor.

3. Az In-Memory OLTP-futtatókörnyezet megkeresi a tárolt eljárás DLL belépési pontjának helyét.

4. A RENDSZER végrehajtja a DLL-ben található gépkódot, és az eredmények visszakerülnek az ügyfélhez.

Paraméter szimatolás

Az értelmezett Transact-SQL tárolt eljárások az első végrehajtáskor lesznek kompilálva, szemben a natívan kompilált tárolt eljárásokkal, amelyeket a létrehozáskor kompilálunk. Ha az értelmezett tárolt eljárásokat a rendszer meghívásakor állítja össze, az optimalizáló a meghíváshoz megadott paraméterek értékeit használja a végrehajtási terv létrehozásakor. A paraméterek fordítás során történő használatát paraméterszniffingnek nevezzük.

A paraméterszenzisztálás nem használható natívan lefordított tárolt eljárások összeállításához. A tárolt eljárás összes paramétere ISMERETLEN értékűnek minősül. Az értelmezett tárolt eljárásokhoz hasonlóan a natívan lefordított tárolt eljárások is támogatják a OPTIMIZE FOR tippet. További információ: lekérdezési tippek (Transact-SQL).

Lekérdezés-végrehajtási terv lekérése natívan lefordított tárolt eljárásokhoz

A natívan lefordított tárolt eljárások lekérdezés-végrehajtási terve lekérhető Becsült végrehajtási terv a Management Studióban vagy a Transact-SQL SHOWPLAN_XML lehetőségével. Például:

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

A lekérdezésoptimalizáló által létrehozott végrehajtási terv egy olyan fából áll, amely lekérdezési operátorokat tartalmaz a csomópontokon és a fa levelein. A fa szerkezete határozza meg az operátorok közötti interakciót (a sorok egyik operátorból a másikba való áramlását). Az SQL Server Management Studio grafikus nézetében a folyamat jobbról balra halad. Az 1. ábrán látható lekérdezési terv például két indexvizsgálati operátort tartalmaz, amelyek sorokat szolgáltatnak az egyesítési illesztés operátorának. Az egyesítési illesztés operátor sorokat biztosít egy kiválasztott operátornak. A kiválasztási operátor végül visszaadja a sorokat az ügyfélnek.

Lekérdezési operátorok natívan lefordított tárolt eljárásokban

Az alábbi táblázat a natívan lefordított tárolt eljárásokban támogatott lekérdezési operátorokat foglalja össze:

Operátor	Minta lekérdezés	Jegyzetek
KIVÁLASZT	SELECT OrderID FROM dbo.[Order]
BEILLESZT	INSERT dbo.Customer VALUES ('abc', 'def')
FRISSÍT	UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
TÖRÖL	DELETE dbo.Customer WHERE CustomerID='abc'
Számítási skalár	SELECT OrderID+1 FROM dbo.[Order]	Ez az operátor belső függvényekhez és típuskonverziókhoz egyaránt használható. Nem minden függvény és típusátalakítás támogatott natívan lefordított tárolt eljárásokban.
Beágyazott ciklusok összekapcsolása	SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c	A Nested Loops az egyetlen olyan illesztési operátor, amely natívan fordított tárolt eljárásokban támogatott. Az olyan tervek, amelyek csatlakozást tartalmaznak, a Beágyazott Hurkok operátort használják, még akkor is, ha ugyanazon lekérdezés értelmezett Transact-SQL terve hash vagy egyesítési csatlakozást tartalmaz.
Fajta	SELECT ContactName FROM dbo.Customer ORDER BY ContactName
Felső	SELECT TOP 10 ContactName FROM dbo.Customer
Elsőbbségi rendezés	SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName	A TOP kifejezés (a visszaadandó sorok száma) nem haladhatja meg a 8000 sort. Kevesebb, ha a lekérdezésben is vannak illesztési és összesítési operátorok. Az illesztések és az összesítések általában csökkentik a rendezendő sorok számát az alaptáblák sorszámával összehasonlítva.
Stream összesítése	SELECT count(CustomerID) FROM dbo.Customer	Vegye figyelembe, hogy a Hash Match operátor nem támogatott az összesítéshez. Ezért a natívan fordított tárolt eljárások összes összesítése a Stream Aggregate operátort használja, még akkor is, ha az értelmezett lekérdezéshez tartozó terv a Transact-SQL-ban a Hash Match operátort használja.

Oszlopstatisztikák és illesztések

Az SQL Server az indexkulcs oszlopaiban lévő értékek statisztikáit tartja fenn, hogy megbecsülje bizonyos műveletek költségét, például az indexvizsgálatot és az indexkereséseket. (Az SQL Server akkor is létrehoz statisztikákat az indexen kívüli kulcsoszlopokon, ha explicit módon hozza létre őket, vagy ha a lekérdezésoptimalizáló egy predikátummal rendelkező lekérdezésre válaszul hozza létre őket.) A költségbecslés fő metrikája az egyetlen operátor által feldolgozott sorok száma. Vegye figyelembe, hogy a lemezalapú táblák esetében az adott operátor által elért lapok száma jelentős a költségbecslésben. Mivel azonban az oldalszám nem fontos a memóriaoptimalizált táblák esetében (ez mindig nulla), ez a vitafórum a sorok számára összpontosít. A becslés a tervben szereplő indexkeresési és vizsgálati operátorokkal kezdődik, majd kiterjesztve a többi operátorra is, például az illesztő operátorra. Az illesztési operátor által feldolgozandó sorok becsült száma az alapul szolgáló index- és keresési operátorok becslésén alapul. Az értelmezett Transact-SQL memóriaoptimalizált táblákhoz való hozzáférés esetén megfigyelheti a tényleges végrehajtási tervet, hogy megtekintse a tervben szereplő operátorok becsült és tényleges sorszáma közötti különbséget.

Az 1. ábrán látható példa

• Az ügyfélen végzett fürtözött indexvizsgálat becsült értéke 91; tényleges 91.
• A CustomerID nem klaszteres index vizsgálatának becsült értéke: 830; tényleges: 830.
• A Merge Join operátor becsült értéke 815, a tényleges valódi értéke 830.

Az indexvizsgálatok becslései pontosak. Az SQL Server fenntartja a lemezalapú táblák sorszámát. A teljes táblázat- és indexvizsgálatok becslései mindig pontosak. Az illesztés becslése is meglehetősen pontos.

Ha ezek a becslések változnak, a különböző terv alternatívák költségére vonatkozó szempontok is megváltoznak. Ha például az illesztés egyik oldala 1 vagy csak néhány sor becsült sorszámával rendelkezik, a beágyazott hurkok összekapcsolása kevésbé költséges. Fontolja meg a következő lekérdezést:

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

A Customer táblából minden sor kivételével törlődik az összes sor, és a következő lekérdezési terv jön létre:

A lekérdezési tervvel kapcsolatban:

• A Hash illesztés helyébe egy beágyazott hurkok fizikai illesztő operátor került.
• A IX_CustomerID teljes indexvizsgálatát indexkeresésre cseréltük. Ez 5 sort eredményezett a teljes indexvizsgálathoz szükséges 830 sor helyett.

Lásd még:

Memory-Optimized táblák