Sorgu Deposu ile performansı ayarlama

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümleri Azure SQL VeritabanıAzure SQL Yönetilen ÖrneğiAzure Synapse Analytics (yalnızca ayrılmış SQL havuzu)Microsoft Fabric'teki SQL veritabanı

SQL Server Sorgu Deposu özelliği, SQL Server Management Studio görsel arabirimi ve T-SQL sorguları aracılığıyla iş yükünüzdeki sorguları bulma ve ayarlama gücü sağlar. Bu makalede, sorguları kullanım istatistiklerine göre belirleme ve planları zorlama dahil olmak üzere veritabanınızda sorgu performansını geliştirmek için nasıl eyleme dönüştürülebilir bilgiler alabileceğiniz açıklanabilir. Ayrıca Sorgu Deposu ipuçlarını özelliğini kullanarak sorguları tanımlayabilir ve uygulama kodunu değiştirmeden sorgu planlarını şekillendirebilirsiniz.

• Bu verilerin nasıl toplandığı hakkında daha fazla bilgi için bkz. Query Storenasıl veri toplar?
• Sorgu Deposu ile yapılandırma ve yönetme hakkında daha fazla bilgi için bkz. Sorgu Deposukullanarak performansı izleme.
• Azure SQL Veritabanı'nda Sorgu Deposu'yu çalıştırma hakkında bilgi için bkz. Azure SQL Veritabanı'nde Sorgu Deposunu çalıştırma.

Performans ayarlama örnek sorguları

Sorgu Deposu, sorgu yürütmeleri boyunca derleme ve çalışma zamanı ölçümlerinin geçmişini tutar ve iş yükünüz hakkında sorular sormanıza olanak tanır.

Aşağıdaki örnek sorgular, performans temeli ve sorgu performansı araştırmanızda yararlı olabilir:

Veritabanında yürütülen son sorgular

Son bir saat içinde veritabanında yürütülen son n sorgusu:

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;

Yürütme sayıları

Son bir saat içindeki her sorgu için yürütme sayısı:

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;

En uzun ortalama yürütme süresi

Son bir saat içinde ortalama süresi en yüksek olan sorgu sayısı:

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;

En yüksek ortalama fiziksel G/Ç okuması

Son 24 saat içinde en büyük ortalama fiziksel G/Ç okuması olan sorguların sayısı ve buna karşılık gelen ortalama satır sayısı ve yürütme sayısı:

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;

Birden çok planı olan sorgular

Birden fazla planı olan sorgular özellikle ilgi çekicidir, çünkü plan seçimindeki bir değişiklik nedeniyle performansta bir regresyon için aday olabilirler.

Aşağıdaki sorgu, son bir saat içinde en fazla plan sayısına sahip sorguları tanımlar:

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;

Aşağıdaki sorgu, son bir saat içindeki tüm planlarla birlikte bu sorguları tanımlar:

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;

En yüksek bekleme süreleri

Bu sorgu, son bir saat için en yüksek bekleme süresine sahip ilk 10 sorguyu döndürür:

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

Azure Synapse Analytics'te bu bölümdeki Sorgu Deposu örnek sorguları, Azure Synapse Analytics Sorgu Deposu DMV'lerinde bulunmayan bekleme istatistikleri dışında desteklenir.

Son dönemlerde performansta gerileyen sorgular

Aşağıdaki sorgu örneği, bir plan seçimi değişikliği nedeniyle yürütme süresi son 48 saat içinde iki katına çıkan tüm sorguları döndürür. Bu sorgu tüm çalışma zamanı istatistikleri aralıklarını yan yana karşılaştırır:

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;

Tüm performans regresyonlarını görmek istiyorsanız (yalnızca plan seçimi değişikliğiyle ilgili regresyonlar değil), önceki sorgudan koşul AND p1.plan_id <> p2.plan_id kaldırın.

Performansta tarihsel gerileme yaşayan sorgular

Son yürütmeyi geçmiş yürütmeyle karşılaştırmak istediğinizde, aşağıdaki sorgu yürütme süresini temel alarak sorgu yürütmeyi karşılaştırır. Bu örnekte, sorgu son dönemdeki (1 saat) yürütmeyi geçmiş dönemle (son gün) karşılaştırır ve additional_duration_workloadekleyenleri tanımlar. Bu ölçüm, son ortalama yürütme ile geçmiş ortalama yürütmesi arasındaki bir fark olarak hesaplanır ve son yürütme sayısıyla çarpılır. Bu son işlemlerin, geçmişe kıyasla ne kadar ek süre getirdiğini temsil eder.

--- "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);

Sorgu performansı kararlılığını koruma

Birden çok kez yürütülen sorgular için SQL Server'ın farklı planlar kullandığını ve bunun sonucunda farklı kaynak kullanımı ve süresi olduğunu fark edebilirsiniz. Sorgu Deposu ile sorgu performansının ne zaman gerilediğini algılayabilir ve ilgilendiğiniz bir süre içinde en uygun planı belirleyebilirsiniz. Daha sonra bu en uygun planı gelecekteki sorgu yürütme için zorlayabilirsiniz.

Ayrıca, parametrelerle (otomatik parametreleştirilmiş veya el ile parametreleştirilmiş) sorgu için tutarsız sorgu performansını belirleyebilirsiniz. Farklı planlar arasında, parametre değerlerinin tümü veya çoğu için yeterince hızlı ve en uygun planı belirleyebilir ve bu planı zorlayabilirsiniz. Bu, daha geniş kullanıcı senaryoları kümesi için öngörülebilir performansı korur.

Sorgu için planı zorlamak (zorlama ilkesini uygulama)

Bir plan belirli bir sorgu için zorlandığında, SQL Server planı iyileştiricide zorlamaya çalışır. Plan zorlama başarısız olursa, bir Genişletilmiş Olay tetiklenir ve optimizatöre normal şekilde optimize etmesi talimatı verilir.

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

sp_query_store_force_plankullandığınızda, yalnızca Sorgu Deposu tarafından o sorgu için plan olarak kaydedilen planları zorlayabilirsiniz. Başka bir deyişle, sorgu için kullanılabilen tek plan, Sorgu Deposu etkinken bu sorguyu yürütmek için zaten kullanılmış olan planlardır.

Note

Sorgu Deposu'nda planları zorlamak Azure Synapse Analytics'te desteklenmez.

Hızlı ileri ve statik imleçler için desteği zorunlu kılmayı planlama

SQL Server 2019 (15.x) ve sonraki sürümlerde ve Azure SQL Veritabanı'nda (tüm dağıtım modelleri), Sorgu Deposu hızlı ve statik Transact-SQL ve API imleçleri için sorgu yürütme planlarını zorlama özelliğini destekler. Zorlama, sp_query_store_force_plan veya SQL Server Management Studio Sorgu Deposu raporları aracılığıyla desteklenir.

Sorgu için plan zorlamayı kaldırma

En uygun sorgu planını hesaplamak üzere SQL Server sorgu iyileştiricisine yeniden güvenmek için sp_query_store_unforce_plan kullanarak sorgu için seçilen planı kaldırın.

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

İlgili içerik

• sorgu deposu kullanarak izleme performansını
• Sorgu Deposu ile En İyi Uygulama
• Sorgu Deposu'nu In-Memory OLTP ile Kullanma
• Sorgu Deposu Kullanım Senaryoları
• Sorgu Deposu Veri Toplama Yöntemi
• Sorgu Deposu saklı yordamları (Transact-SQL)
• Sorgu Deposu katalog görünümleri (Transact-SQL)
• Açık Etkinlik İzleyicisi (SQL Server Management Studio)
• Canlı Sorgu İstatistikleri
• Etkinlik İzleyicisi
• sys.database_query_store_options (Transact-SQL)
• Performans için İzleme ve Ayarlama
• Performans İzleme ve Ayarlama Araçları
• Sorgu Deposu ipuçları
• Veritabanı Altyapısı Ayarlama Danışmanı ile Sorgu Deposundan İş Yükü Kullanarak Veritabanını Ayarlama