Teljesítmény finomhangolása a Lekérdezéstárral

A következőkre vonatkozik: Sql Server 2016 (13.x) és újabb verziók Azure SQL DatabaseFelügyelt Azure SQL-példányAzure Synapse Analytics (csak dedikált SQL-készlet)SQL-adatbázis a Microsoft Fabricben

Az SQL Server Lekérdezéstár funkcióval az SQL Server Management Studio vizuális felületén és T-SQL-lekérdezéseken keresztül felderítheti és hangolhatja a számítási feladatban lévő lekérdezéseket. Ez a cikk bemutatja, hogyan alkalmazhat hasznos információkat az adatbázis lekérdezési teljesítményének javítása érdekében, beleértve a lekérdezések használati statisztikák alapján történő azonosítását és a végrehajtási tervek kényszerítését. A Lekérdezéstár tippek funkcióval azonosíthatja a lekérdezéseket, és alkalmazáskód módosítása nélkül alakíthatja a lekérdezésterveket.

• További információ az adatok gyűjtéséről: Hogyan gyűjti a Lekérdezéstár az adatokat.
• A lekérdezéstár konfigurálásáról és felügyeletéről további információt A lekérdezéstárhasználatával végzett teljesítményfigyelés című témakörben talál.
• A lekérdezéstár Azure SQL Database-ben való működtetéséről további információt A lekérdezéstár üzemeltetése az Azure SQL Database-bencímű témakörben talál.

Teljesítmény finomhangolására szolgáló minta lekérdezések

A Lekérdezéstár a lekérdezésvégrehajtások során megőrzi a fordítási és futásidejű metrikák előzményeit, így kérdéseket tehet fel a számítási feladatával kapcsolatban.

A következő minta lekérdezések hasznosak lehetnek a teljesítménykonfigurációban és a lekérdezési teljesítmény vizsgálatában:

Az adatbázisban végrehajtott utolsó lekérdezések

Az utolsó n az adatbázison az elmúlt egy órán belül végrehajtott lekérdezések:

SELECT TOP 10 qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.last_execution_time DESC;

Végrehajtások száma

Az egyes lekérdezések végrehajtásának száma az elmúlt egy órában:

SELECT q.query_id,
    qt.query_text_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY q.query_id,
    qt.query_text_id,
    qt.query_sql_text
ORDER BY total_execution_count DESC;

Leghosszabb átlagos végrehajtási idő

Az elmúlt egy órában a legmagasabb átlagos időtartamú lekérdezések száma:

SELECT TOP 10 ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) /
        NULLIF(SUM(rs.count_executions), 0), 2) avg_duration,
    SUM(rs.count_executions) AS total_execution_count,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    GETUTCDATE() AS CurrentUTCTime,
    MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id
ORDER BY avg_duration DESC;

Legmagasabb átlagos fizikai bemenet/kimenet (I/O) olvasások

Azoknak a lekérdezéseknek a száma, amelyek az elmúlt 24 órában a legnagyobb átlagos fizikai I/O-olvasást érték el, a megfelelő átlagos sorszámmal és végrehajtási számmal:

SELECT TOP 10 rs.avg_physical_io_reads,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.runtime_stats_id,
    rsi.start_time,
    rsi.end_time,
    rs.avg_rowcount,
    rs.count_executions
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;

Több végrehajtási tervvel rendelkező lekérdezések

Az egynél több tervvel rendelkező lekérdezések különösen érdekesek, mivel a tervválasztás változása miatt teljesítménycsökkenésre hajlamosak lehetnek.

Az alábbi lekérdezés az elmúlt egy órában a legtöbb tervvel rendelkező lekérdezéseket azonosítja.

SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    COUNT(*) AS QueryPlanCount,
    STRING_AGG(p.plan_id, ',') plan_ids,
    qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY OBJECT_NAME(object_id),
    q.query_id,
    qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY QueryPlanCount DESC;

Az alábbi lekérdezés az elmúlt egy órában azonosítja ezeket a lekérdezéseket, valamint az összes tervet:

WITH Query_MultPlans
AS (
    SELECT COUNT(*) AS QueryPlanCount,
        q.query_id
    FROM sys.query_store_query_text AS qt
    INNER JOIN sys.query_store_query AS q
        ON qt.query_text_id = q.query_text_id
    INNER JOIN sys.query_store_plan AS p
        ON p.query_id = q.query_id
    GROUP BY q.query_id
    HAVING COUNT(DISTINCT plan_id) > 1
)
SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    query_sql_text,
    p.plan_id,
    p.query_plan AS plan_xml,
    p.last_compile_start_time,
    p.last_execution_time
FROM Query_MultPlans AS qm
INNER JOIN sys.query_store_query AS q
    ON qm.query_id = q.query_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY q.query_id,
    p.plan_id;

Legmagasabb várakozási időtartamok

Ez a lekérdezés a 10 legnagyobb várakozási időtartamú lekérdezést adja vissza az elmúlt órában:

SELECT TOP 10 qt.query_text_id,
    q.query_id,
    p.plan_id,
    sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
INNER JOIN sys.query_store_plan p
    ON ws.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_text_id,
    q.query_id,
    p.plan_id
ORDER BY sum_total_wait_ms DESC;

Note

Az Azure Synapse Analyticsben az ebben a szakaszban szereplő Lekérdezéstár minta lekérdezései támogatottak a várakozási statisztikák kivételével, amelyek nem érhetők el az Azure Synapse Analytics lekérdezéstár dMV-jeiben.

A teljesítményben legutóbb romlott lekérdezések

Az alábbi lekérdezési példa az összes olyan lekérdezést adja vissza, amelynek végrehajtási ideje az elmúlt 48 órában megkétszereződött egy tervválasztási változás miatt. Ez a lekérdezés az összes futásidejű statisztikát egymás mellett hasonlítja össze:

SELECT qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    rs1.runtime_stats_id AS runtime_stats_id_1,
    rsi1.start_time AS interval_1,
    p1.plan_id AS plan_1,
    rs1.avg_duration AS avg_duration_1,
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2,
    rsi2.start_time AS interval_2,
    rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi2
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
    AND rsi2.start_time > rsi1.start_time
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > 2 * rs1.avg_duration
ORDER BY q.query_id,
    rsi1.start_time,
    rsi2.start_time;

Ha az összes teljesítményregressziót látni szeretné (nem csak a tervválasztás módosításához kapcsolódó regressziókat), távolítsa el a feltételt AND p1.plan_id <> p2.plan_id az előző lekérdezésből.

Teljesítménycsökkenést mutató történelmi lekérdezések

Ha a legutóbbi végrehajtást az előzményvégrehajtással szeretné összehasonlítani, az alábbi lekérdezés a lekérdezés végrehajtását hasonlítja össze a végrehajtási időszak alapján. Ebben a konkrét példában a lekérdezés összehasonlítja az elmúlt időszak (1 óra) és a történelmi időszak (utolsó nap) végrehajtását, és azonosítja azokat, amelyek bemutatták additional_duration_workload. Ez a metrika a legutóbbi átlagos végrehajtás és az előzmények átlagos végrehajtásának és a legutóbbi végrehajtások számának szorzataként van kiszámítva. Ez azt jelzi, hogy a legutóbbi végrehajtások mennyi extra időtartamot vezettek be az előzményekhez képest:

--- "Recent" workload - last 1 hour
DECLARE @recent_start_time DATETIMEOFFSET;
DECLARE @recent_end_time DATETIMEOFFSET;

SET @recent_start_time = DATEADD(hour, - 1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload
DECLARE @history_start_time DATETIMEOFFSET;
DECLARE @history_end_time DATETIMEOFFSET;

SET @history_start_time = DATEADD(hour, - 24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();

WITH hist AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @history_start_time
        AND rs.last_execution_time < @history_end_time
    )
    OR (
        rs.first_execution_time <= @history_start_time
        AND rs.last_execution_time > @history_start_time
    )
    OR (
        rs.first_execution_time <= @history_end_time
        AND rs.last_execution_time > @history_end_time
    )
    GROUP BY p.query_id
),
recent AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @recent_start_time
        AND rs.last_execution_time < @recent_end_time
    )
    OR (
        rs.first_execution_time <= @recent_start_time
        AND rs.last_execution_time > @recent_start_time
    )
    OR (
        rs.first_execution_time <= @recent_end_time
        AND rs.last_execution_time > @recent_end_time
    )
    GROUP BY p.query_id
    )
SELECT results.query_id AS query_id,
    results.query_text AS query_text,
    results.additional_duration_workload AS additional_duration_workload,
    results.total_duration_recent AS total_duration_recent,
    results.total_duration_hist AS total_duration_hist,
    ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
    ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM (
    SELECT hist.query_id AS query_id,
        qt.query_sql_text AS query_text,
        ROUND(CONVERT(FLOAT, recent.total_duration / recent.count_executions - hist.total_duration / hist.count_executions) * (recent.count_executions), 2) AS additional_duration_workload,
        ROUND(recent.total_duration, 2) AS total_duration_recent,
        ROUND(hist.total_duration, 2) AS total_duration_hist,
        recent.count_executions AS count_executions_recent,
        hist.count_executions AS count_executions_hist
    FROM hist
    INNER JOIN recent
        ON hist.query_id = recent.query_id
    INNER JOIN sys.query_store_query AS q
        ON q.query_id = hist.query_id
    INNER JOIN sys.query_store_query_text AS qt
        ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);

A lekérdezési teljesítmény stabilitásának fenntartása

A többször végrehajtott lekérdezések esetében előfordulhat, hogy az SQL Server különböző csomagokat használ, ami eltérő erőforrás-kihasználtságot és időtartamot eredményez. A Lekérdezéstár segítségével észlelheti, hogy a lekérdezési teljesítmény mikor omlott vissza, és meghatározható az optimális terv egy adott időszakon belül. Ezután kényszerítheti ezt az optimális tervet a lekérdezések későbbi végrehajtásához.

A lekérdezések inkonzisztens lekérdezési teljesítményét paraméterekkel is azonosíthatja (automatikusan vagy manuálisan paraméterezve). A különböző tervek között azonosíthatja azt a tervet, amely elég gyors és optimális a paraméterértékek egészéhez vagy többségéhez, és kényszerítheti a tervet. Ez a felhasználói forgatókönyvek szélesebb halmaza számára is kiszámítható teljesítményt nyújt.

Terv kényszerítése a lekérdezéshez (kényszerítési szabályzat alkalmazása)

Amikor egy tervet kényszerítenek egy adott lekérdezéshez, az SQL Server megpróbálja ezt a tervet az optimalizálóban érvényesíteni. Ha a terv kényszerítése sikertelen, a rendszer aktivál egy kiterjesztett eseményt, és az optimalizálónak a szokásos módon kell optimalizálnia.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

Ha a sp_query_store_force_plan-t használja, csak a Lekérdezéstár által rögzített terveket kényszerítheti a lekérdezéshez tervként. Más szóval a lekérdezésekhez csak olyan csomagok érhetők el, amelyek már a lekérdezés végrehajtásához voltak használva, miközben a Lekérdezéstár aktív volt.

Note

Az Azure Synapse Analytics nem támogatja a csomagok lekérdezéstárban való kényszerítését.

A gyors előreléptetés és a statikus kurzorok támogatásának tervszerű kényszerítése

Az SQL Server 2019 (15.x) és újabb verzióiban, valamint az Azure SQL Database -ben (az összes üzembe helyezési modellben) a Lekérdezéstár támogatja a lekérdezés-végrehajtási tervek gyors előre- és statikus Transact-SQL és API-kurzorok kényszerítésének lehetőségét. A kényszerítés a sp_query_store_force_plan vagy az SQL Server Management Studio Query Store jelentéseken keresztül valósítható meg.

Lekérdezés tervkényszerítésének eltávolítása

Ha ismét az SQL Server lekérdezésoptimalizálójára szeretne támaszkodni az optimális lekérdezési terv kiszámításához, a sp_query_store_unforce_plan használatával oldja fel a lekérdezéshez kiválasztott terv kényszerítettét.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

Kapcsolódó tartalom

• Teljesítmény monitorozása a Lekérdezéstár használatával
• Bevett gyakorlat a Lekérdezési tárolóval
• Lekérdezés tároló használata In-Memory OLTP
• Lekérdezéstár használati forgatókönyvei
• Hogyan gyűjti a Lekérdezéstár az adatokat
• Lekérdezéstár tárolt eljárásai (Transact-SQL)
• Lekérdezéstár katalógusnézetei (Transact-SQL)
• Nyissa meg a Tevékenységfigyelőt (SQL Server Management Studio)
• élő lekérdezési statisztikák
• Tevékenységfigyelő
• sys.database_query_store_options (Transact-SQL)
• Teljesítmény figyelése és hangolása
• teljesítményfigyelő és hangoló eszközök
• Lekérdezési tár utasítások
• Adatbázis finomhangolása a Lekérdezéstár számítási feladataival az adatbázismotor finomhangolási tanácsadójával