sys.dm_db_tuning_recommendations (Transact-SQL)

A következőkre vonatkozik: Az SQL Server 2017 (14.x) és újabb verziói az Azure SQL DatabaseAzure SQL Managed InstanceSQL Database-adatbázist a Microsoft Fabricben

Részletes információkat ad vissza az automatikus finomhangolási javaslatokról. További információ: Automatikus finomhangolás

További információ: Monitorozás és teljesítményhangolás az Azure SQL Database-ben és a felügyelt Azure SQL-példányban.

Az Azure SQL Database-ben a dinamikus felügyeleti nézetek nem tehetnek közzé olyan információkat, amelyek hatással lennének az adatbázis-elszigetelésre, vagy nem tehetnek közzé információkat más olyan adatbázisokról, amelyekhez a felhasználó hozzáfér. Az információk felfedésének elkerülése érdekében a rendszer kiszűr minden olyan sort, amely nem a csatlakoztatott bérlőhöz tartozó adatokat tartalmaz.

Oszlop neve	Adattípus	Leírás
név	nvarchar(4000)	A javaslat egyedi neve.
type	nvarchar(4000)	A javaslatot tartalmazó automatikus hangolási lehetőség neve, például: FORCE_LAST_GOOD_PLAN
ok	nvarchar(4000)	A javaslat indoka.
valid_since	datetime2	A javaslat első létrehozásakor.
last_refresh	datetime2	A javaslat legutóbbi létrehozásakor.
state	nvarchar(4000)	A javaslat állapotát leíró JSON-dokumentum. A következő mezők érhetők el: - currentValue - a javaslat aktuális állapota. - reason - állandó, amely leírja, hogy a javaslat miért van a jelenlegi állapotban.
is_executable_action	bit	1 = A javaslat Transact-SQL szkripttel végrehajtható az adatbázison. 0 = A javaslat nem hajtható végre az adatbázison (például: csak információk vagy a javaslat visszaállítása)
is_revertable_action	bit	1 = Az adatbázismotor automatikusan monitorozza és visszaállítja a javaslatot. 0 = A javaslat nem figyelhető automatikusan, és nem állítható vissza. A legtöbb végrehajtható művelet visszaállítható lesz.
execute_action_start_time	datetime2	A javaslat alkalmazásának dátuma.
execute_action_duration	time	A végrehajtási művelet időtartama.
execute_action_initiated_by	nvarchar(4000)	User = Felhasználó manuálisan kényszerített terv a javaslatban. System = A rendszer automatikusan alkalmazza a javaslatot.
execute_action_initiated_time	datetime2	A javaslat alkalmazásának dátuma.
revert_action_start_time	datetime2	A javaslat visszaállításának dátuma.
revert_action_duration	time	A visszaállítási művelet időtartama.
revert_action_initiated_by	nvarchar(4000)	User = Felhasználó által manuálisan ki nem kényszerített javasolt csomag. System = A rendszer automatikusan visszaállította a javaslatot.
revert_action_initiated_time	datetime2	A javaslat visszaállításának dátuma.
pontszám	int	A javaslat becsült értéke/hatása a 0–100-ra (minél nagyobb, annál jobb)
Részletek	nvarchar(max)	JSON-dokumentum, amely további részleteket tartalmaz a javaslatról. A következő mezők érhetők el: planForceDetails - queryId - a regressziós lekérdezés query_id. - regressedPlanId - a regressziós terv plan_id. - regressedPlanExecutionCount - A regresszió észlelése előtt regressziós tervvel rendelkező lekérdezés végrehajtásának száma. - regressedPlanAbortedCount – A regressziós terv végrehajtása során észlelt hibák száma. - regressedPlanCpuTimeAverage - A regresszió észlelése előtt a regressziós lekérdezés által felhasznált átlagos processzoridő (mikro másodpercben). - regressedPlanCpuTimeStddev – A regresszió észlelése előtt a regressziós lekérdezés által felhasznált processzoridő szórása. - recommendedPlanId - plan_id a tervről, amelyet kényszeríteni kell. - recommendedPlanExecutionCount– A regresszió észlelése előtt kényszerített tervvel rendelkező lekérdezés végrehajtásának száma. - recommendedPlanAbortedCount - A kényszeríteni kívánt terv végrehajtása során észlelt hibák száma. - recommendedPlanCpuTimeAverage - A kényszerített tervvel végrehajtott lekérdezés által felhasznált átlagos cpu-idő (mikro másodpercben) (a regresszió észlelése előtt kiszámítva). - recommendedPlanCpuTimeStddev A regresszió észlelése előtt a regressziós lekérdezés által felhasznált processzoridő szórása. implementationDetails - method - A regresszió javításához használandó módszer. Az érték mindig TSql. - script - Transact-SQL szkriptet, amelyet az ajánlott terv kényszerítéséhez kell végrehajtani.

Megjegyzések

Az adatbázismotor által sys.dm_db_tuning_recommendations visszaadott információk akkor frissülnek, ha az adatbázismotor azonosítja a lekérdezési teljesítmény regresszióját, és nem marad meg. A javaslatok csak az adatbázismotor újraindításáig maradnak meg. A sqlserver_start_time oszlopával keresse meg az adatbázismotor utolsó indítási idejét. Az adatbázis-rendszergazdáknak rendszeres időközönként biztonsági másolatot kell készíteniük a hangolási javaslatról, ha a kiszolgáló újrahasznosítása után meg szeretnék tartani.

Az currentValue oszlop mezője a state következő értékekkel rendelkezhet:

Státusz	Description
Active	A javaslat aktív, és még nincs alkalmazva. A felhasználó átveheti a javaslatszkriptet, és manuálisan hajthatja végre.
Verifying	A javaslatot az adatbázismotor alkalmazza, és a belső ellenőrzési folyamat összehasonlítja a kényszerített terv teljesítményét a regressziós tervvel.
Success	A javaslat alkalmazása sikeresen megtörtént.
Reverted	A javaslat visszaáll, mert nincs jelentős teljesítménynövekedés.
Expired	A javaslat lejárt, és már nem alkalmazható.

Az oszlopban található state JSON-dokumentum azt az okot tartalmazza, amely leírja, hogy miért van a javaslat az aktuális állapotban. Az ok mezőben a következő értékek lehetnek:

Reason	Description
SchemaChanged	A javaslat lejárt, mert egy hivatkozott tábla sémája módosult. Új javaslat jön létre, ha új lekérdezési terv regresszióját észleli az új sémában.
StatisticsChanged	A javaslat egy hivatkozott tábla statisztikáinak változása miatt lejárt. Új javaslat jön létre, ha új statisztikai adatok alapján új lekérdezésterv-regressziót észlel.
ForcingFailed	Az ajánlott terv nem kényszeríthető lekérdezésre. Keresse meg a last_force_failure_reasonsys.query_store_plan nézetben a hiba okát.
AutomaticTuningOptionDisabled	FORCE_LAST_GOOD_PLAN beállítást a felhasználó letiltotta az ellenőrzési folyamat során. Engedélyezze a FORCE_LAST_GOOD_PLAN beállítást az ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) utasítással, vagy kényszerítse manuálisan a tervet az details oszlopban lévő szkripttel.
UnsupportedStatementType	A terv nem kényszeríthető a lekérdezésre. A nem támogatott lekérdezések például a kurzorok és INSERT BULK az utasítás.
LastGoodPlanForced	A javaslat alkalmazása sikeresen megtörtént.
AutomaticTuningOptionNotEnabled	Az adatbázismotor potenciális teljesítményregressziót észlelt, de a FORCE_LAST_GOOD_PLAN beállítás nincs engedélyezve – lásd: ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Alkalmazza manuálisan a javaslatot, vagy engedélyezze a FORCE_LAST_GOOD_PLAN beállítást.
VerificationAborted	Az ellenőrzési folyamat megszakadt az újraindítás vagy a Lekérdezéstár törlése miatt.
VerificationForcedQueryRecompile	A lekérdezés újrafordításra kerül, mert nincs jelentős teljesítménybeli javulás.
PlanForcedByUser	A felhasználó manuálisan kényszerítette a tervet sp_query_store_force_plan (Transact-SQL) eljárással. Az adatbázismotor nem alkalmazza a javaslatot, ha a felhasználó kifejezetten úgy döntött, hogy kényszerít egy tervet.
PlanUnforcedByUser	A felhasználó manuálisan, sp_query_store_unforce_plan (Transact-SQL) eljárással oldotta fel a tervet. Mivel a felhasználó kifejezetten visszaállította az ajánlott tervet, az adatbázismotor továbbra is az aktuális tervet használja, és új javaslatot hoz létre, ha a jövőben valamilyen tervregresszió történik.
UserForcedDifferentPlan	A felhasználó manuálisan kényszerítette a különböző csomagokat sp_query_store_force_plan (Transact-SQL) eljárással. Az adatbázismotor nem alkalmazza a javaslatot, ha a felhasználó kifejezetten úgy döntött, hogy kényszerít egy tervet.
TempTableChanged	A tervben használt ideiglenes tábla módosul.

Az oszlop statisztikái nem jelenítik meg a details futásidejű terv statisztikáit (például az aktuális CPU-időt). A javaslat részletei a regresszió észlelésekor jelennek meg, és ismertetik, hogy az adatbázismotor miért azonosította a teljesítményregressziót. A regressedPlanId használatával recommendedPlanId és lekérdezéseivel pontos futásidejű tervstatisztikákat kereshet.

Példák finomhangolási javaslatok információinak használatára

1. példa

Az alábbi mintakód lekéri a létrehozott Transact-SQL szkriptet, amely egy jó tervet kényszerít ki egy adott lekérdezéshez:

SELECT name,
    reason,
    score,
    JSON_VALUE(details, '$.implementationDetails.script') AS script,
    details.*
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(details, '$.planForceDetails') WITH (
        [query_id] INT '$.queryId',
        regressed_plan_id INT '$.regressedPlanId',
        last_good_plan_id INT '$.recommendedPlanId'
        ) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';

2. példa

Az alábbiakban a létrehozott Transact-SQL szkriptet kapjuk meg, amely egy jó tervet kényszerít ki az adott lekérdezéshez, és további információkat a becsült nyereségről:

SELECT reason,
    score,
    script = JSON_VALUE(details, '$.implementationDetails.script'),
    planForceDetails.*,
    estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount) * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000,
    error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(Details, '$.planForceDetails') WITH (
        [query_id] INT '$.queryId',
        regressedPlanId INT '$.regressedPlanId',
        recommendedPlanId INT '$.recommendedPlanId',
        regressedPlanErrorCount INT,
        recommendedPlanErrorCount INT,
        regressedPlanExecutionCount INT,
        regressedPlanCpuTimeAverage FLOAT,
        recommendedPlanExecutionCount INT,
        recommendedPlanCpuTimeAverage FLOAT
        ) AS planForceDetails;

3. példa

Az alábbiakban lekéri a létrehozott Transact-SQL szkriptet, amely egy jó tervet kényszerít ki minden adott lekérdezéshez, valamint további információkat, amelyek tartalmazzák a lekérdezés szövegét és a Lekérdezéstárban tárolt lekérdezésterveket:

WITH cte_db_tuning_recommendations
AS (
    SELECT reason,
        score,
        query_id,
        regressedPlanId,
        recommendedPlanId,
        current_state = JSON_VALUE(STATE, '$.currentValue'),
        current_state_reason = JSON_VALUE(STATE, '$.reason'),
        script = JSON_VALUE(details, '$.implementationDetails.script'),
        estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount) *
                         (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000,
        error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO')
    FROM sys.dm_db_tuning_recommendations
    CROSS APPLY OPENJSON(Details, '$.planForceDetails') WITH (
            [query_id] INT '$.queryId',
            regressedPlanId INT '$.regressedPlanId',
            recommendedPlanId INT '$.recommendedPlanId',
            regressedPlanErrorCount INT,
            recommendedPlanErrorCount INT,
            regressedPlanExecutionCount INT,
            regressedPlanCpuTimeAverage FLOAT,
            recommendedPlanExecutionCount INT,
            recommendedPlanCpuTimeAverage FLOAT
            )
    )
SELECT qsq.query_id,
    qsqt.query_sql_text,
    dtr.*,
    CAST(rp.query_plan AS XML) AS RegressedPlan,
    CAST(sp.query_plan AS XML) AS SuggestedPlan
FROM cte_db_tuning_recommendations AS dtr
INNER JOIN sys.query_store_plan AS rp
    ON rp.query_id = dtr.query_id
        AND rp.plan_id = dtr.regressedPlanId
INNER JOIN sys.query_store_plan AS sp
    ON sp.query_id = dtr.query_id
        AND sp.plan_id = dtr.recommendedPlanId
INNER JOIN sys.query_store_query AS qsq
    ON qsq.query_id = rp.query_id
INNER JOIN sys.query_store_query_text AS qsqt
    ON qsqt.query_text_id = qsq.query_text_id;

Az értékek ajánlási nézetben való lekérdezéséhez használható JSON-függvényekről további információt az adatbázismotor JSON-támogatása című témakörben talál.

Permissions

Engedélyre van szükség VIEW SERVER STATE az SQL Serverben.

VIEW DATABASE STATE Az Azure SQL Database-ben az adatbázis engedélyére van szükség.

Engedélyek az SQL Server 2022-hez és újabb verziókhoz

Engedélyre van szükség VIEW SERVER PERFORMANCE STATE a kiszolgálón.

Következő lépések

• Automatikus hangolás
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• JSON-támogatás
• sys.dm_os_sys_info (Transact-SQL)