Optimalizált terv kényszerítése a Lekérdezéstár használatával

A következőkre vonatkozik: SQL Server 2022 (16.x) Azure SQL DatabaseSQL-adatbázis a Microsoft Fabricben

A lekérdezésoptimalizálás a "megfelelő" lekérdezés-végrehajtási terv létrehozásának többfázisú folyamata. Bizonyos esetekben a lekérdezésoptimalizálás részét képező lekérdezés-fordítás a teljes lekérdezés-végrehajtási idő nagy százalékát jelentheti, és jelentős rendszererőforrásokat használhat fel. Az optimalizált terv kényszerítése a funkciók intelligens lekérdezésfeldolgozó családjának része. Az optimalizált terv kényszerítése csökkenti az ismétlődő kényszerített lekérdezések kompilációs többletterhelését, és megköveteli a Query Store engedélyezését és írás-olvasási módban történő használatát. A lekérdezés-végrehajtási terv létrehozása után a rendszer az egyes fordítási lépéseket optimalizálási újrajátszási szkriptként tárolja újra. Az optimalizálási visszajátszási szkriptek a tömörített showplan XML részeként vannak tárolva Lekérdezéstár, rejtett OptimizationReplay attribútumban.

Az optimalizált terv kényszeríti a megvalósítást

Amikor egy lekérdezés először végighalad a fordítási folyamaton, az optimalizálási idő becslésén alapuló küszöbérték (a lekérdezésoptimalizáló bemeneti fája alapján) határozza meg, hogy létrejön-e egy optimalizálási visszajátszási szkript.

A fordítás befejezése után több futtatókörnyezeti metrika is elérhetővé válik annak felméréséhez, hogy az előző becslés helyes volt-e. Ha az adatbázismotor megerősíti, hogy túllépte a küszöbértéket, az optimalizálási visszajátszási szkript jogosult az adatmegőrzésre. Ezek a futtatókörnyezeti metrikák közé tartozik a elért objektumok száma, az illesztések száma, az optimalizálás során végrehajtott optimalizálási feladatok száma és a tényleges optimalizálási idő.

Az optimalizálási visszajátszási szkriptek használatának lehetséges előnye az optimalizálási visszajátszási szkript tárolásának többletterhelésével is összehasonlítva. Az optimalizálási visszajátszási szkript visszajátszásához szükséges relatív idő becslését összehasonlítjuk a normál optimalizálási folyamat végrehajtásával töltött idővel. Ez a becslés az optimalizálási visszajátszási szkriptben tárolt optimalizálási feladatok számán és a normál fordítás során végrehajtott optimalizálási feladatok számán alapul. Ha az optimalizálási visszajátszási szkript ismétlése jelentős előnyt mutat a fordítási idő csökkentésében, az optimalizálási visszajátszási szkript megmarad.

Considerations

Ha az optimalizált terv kényszerítési funkciója engedélyezve van, az optimalizált terv kényszerítési jogosultsági feltételei a következők:

1. Csak a teljes optimalizáláson áteső lekérdezési tervek jogosultak, amelyek a tulajdonság jelenlétével StatementOptmLevel="FULL" ellenőrizhetők.

2. A RECOMPILE tippet vagy elosztott lekérdezéseket tartalmazó utasítások nem használhatóak.

Ha azonban a Lekérdezéstár önállóan rögzít egy lekérdezési tervet, amely az optimalizált terv kényszerítésével lett kiszelektált, létrejön az optimalizálási visszajátszási szkript a lekérdezés második újrafordításához, az alapértelmezett újrafordítási események feltételei szerint. Tudjon meg többet az újrafordításról a végrehajtási tervek újrafordításával kapcsolatos részben.

Még ha létre is jött egy optimalizálási visszajátszási szkript, előfordulhat, hogy nem marad meg a Lekérdezéstárban, ha a Lekérdezéstár által konfigurált rögzítési szabályzatok feltételei nem teljesülnek, nevezetesen az utasítás végrehajtásának száma, valamint az összesített fordítási és végrehajtási idők. Ebben az esetben az érvénytelen optimalizálási visszajátszási szkript aszinkron módon törlődik a memóriából.

Optimalizált terv kényszerítése engedélyezése és letiltása

Engedélyezheti vagy letilthatja az optimalizált terv kényszerítését egy adatbázishoz. Ha az optimalizált terv kényszerítése be van kapcsolva egy adatbázishoz, az egyes lekérdezések esetén letilthatja a DISABLE_OPTIMIZED_PLAN_FORCING lekérdezési javaslat használatával. A lekérdezéstárban kényszerített lekérdezéstervek esetében letilthatja az optimalizált tervek kényszerítését is.

Optimalizált tervre való kényszerítés engedélyezése vagy letiltása adatbázishoz

Az optimalizált terv kényszerítése alapértelmezés szerint engedélyezve van az SQL Server 2022-ben (16.x) és újabb verziókban létrehozott új adatbázisokhoz. A lekérdezéstárat minden olyan adatbázishoz engedélyezni kell, ahol optimalizált terv kényszerítését használják. A meglévő adatbázisokkal vagy az SQL Server alacsonyabb verziójából visszaállított adatbázisokkal rendelkező frissített példányoknál alapértelmezés szerint az optimalizált terv kényszerítése engedélyezve van.

Ha az adatbázis szintjén szeretné engedélyezni az optimalizált terv kényszerítését, használja az ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON adatbázis-hatókörű konfigurációt. Ha még nincs engedélyezve, engedélyeznie kell a Lekérdezéstárat. Keresse meg a példakódot az A példában, vagy tudjon meg többet a Lekérdezéstár monitorozási teljesítményéről a Lekérdezéstár használatával.

Ha az adatbázis szintjén szeretné letiltani az optimalizált terv kényszerítését, használja az ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF adatbázis-hatókörű konfigurációt.

Optimalizált terv kényszerítésének leállítása lekérdezési utasítással

Ha az optimalizált terv kényszerítési funkciója engedélyezve van egy adatbázisban, a DISABLE_OPTIMIZED_PLAN_FORCING használatával letilthatja az egyes lekérdezésekre való optimalizált terv-kényszerítést.

Az E példában talál egy példát a lekérdezési tipp alkalmazására.

Terv kényszerítése a Lekérdezéstár használatával, de az optimalizált terv kényszerítésének letiltása

A sp_query_store_force_plan eljárás tartalmaz egy paramétert disable_optimized_plan_forcing . A paraméter használatához a tárolt eljárás egy további paramétert igényel sp_query_store_force_plan . A további paraméter neve @replica_group_id. Alapértelmezés szerint az elsődleges @replica_group_id érték egy (1) akkor is, ha nincsenek konfigurált másodlagos replikák.

A sp_query_store_force_plan talál egy példát a megfelelő paramétereknek a tárolt eljárásra való alkalmazására.

A sys.query_store_plan katalógusnézet olyan oszlopokat tartalmaz, amelyek jelzik, hogy a terv rendelkezik-e optimalizálási visszajátszási szkripttel, és új állapotot ad hozzá a meglévő sikertelenség oka oszlophoz, amely a társított optimalizálási visszajátszási szkriptre jellemző. További információ a sys.query_store_plan.

Examples

A cikkben szereplő kódminták a AdventureWorks2025 vagy AdventureWorksDW2025 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

A. Lekérdezéstár engedélyezése és optimalizált terv kényszerítése az adatbázisra.

Az alábbi kód engedélyezi a lekérdezéstárat egy adatbázisban, majd engedélyezi az optimalizált terv kényszerítését az adatbázisban. További információ a lekérdezéstár ALTER DATABASE SET-beállításokban való engedélyezésének lehetőségeiről.

A kód futtatása előtt csatlakozzon a megfelelő felhasználói adatbázishoz.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. Válassza ki az összes olyan lekérdezést, amely rendelkezik optimalizálási visszajátszási szkripttel

Az alábbi példakód kiválasztja az összes olyan query_ids, amely rendelkezik optimalizálási visszajátszási szkripttel a Lekérdezéstárban. A példakód futtatása előtt csatlakozzon a megfelelő felhasználói adatbázishoz.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. Kényszerítsen egy tervet és tiltsa le az optimalizált terv kényszerítését a Lekérdezéstárban

Az alábbi kód kényszeríti a tervet a Lekérdezéstárban, de letiltja az optimalizált terv kényszerítését. A következő kód futtatása előtt cserélje le a @query_id és @plan_id karakterláncokat a példánynak megfelelő kombinációra. A sp_query_store_force_plan tárolt eljárás arra számít, hogy a @replica_group_id paraméter harmadik paraméterértékként lesz átadva, amikor letiltja az optimalizált terv kényszerítését a Lekérdezéstárban. Ezzel letiltható az optimalizált tervkényszerítés egy adott kényszerített tervre vonatkozóan egy konkrét replikán. Az @replica_group_id = 1 érték használatával letiltják a funkciót az elsődleges replikán.

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

További információ a sp_query_store_force_plan.

D. Válassza ki az összes olyan lekérdezést, amelyben a Lekérdezéstár letiltja az optimalizált terv kényszerítését

Az alábbi példa lekérdezi az összes olyan tervet, amelyet a Lekérdezéstárban kényszerítettek, ahol is_optimized_plan_forcing_disabled be van állítva 1. A kód futtatása előtt csatlakozzon a megfelelő felhasználói adatbázishoz.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. Lekérdezés optimalizált tervkényszerítésének letiltása

Az alábbi példa letiltja az optimalizált terv kényszerítését egy lekérdezéshez a DISABLE_OPTIMIZED_PLAN_FORCINGlekérdezési tipp használatával.

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

Kapcsolódó tartalom

• sys.query_store_plan (Transact-SQL)
• ALTER DATABASE HATÓKÖRŰ KONFIGURÁCIÓ (Transact-SQL)
• sp_query_store_force_plan (Transact-SQL)
• intelligens lekérdezésfeldolgozás SQL-adatbázisokban