sys.dm_db_tuning_recommendations (T-SQL)

  • Artikel

Berlaku untuk: SQL Server 2017 (14.x) dan azure SQL DatabaseAzure SQL Managed Instance yang lebih baru

Mengembalikan informasi terperinci tentang rekomendasi penyetelan otomatis. Untuk informasi selengkapnya, lihat Penyetelan otomatis

Untuk informasi selengkapnya, lihat Pemantauan dan penyetelan performa di Azure SQL Database dan Azure SQL Managed Instance.

Di Azure SQL Database, tampilan manajemen dinamis tidak dapat mengekspos informasi yang akan memengaruhi penahanan database atau mengekspos informasi tentang database lain yang dapat diakses pengguna. Untuk menghindari mengekspos informasi ini, setiap baris yang berisi data yang bukan milik penyewa yang tersambung difilter.

Nama kolom Jenis data Deskripsi
nama nvarchar(4000) Nama rekomendasi yang unik.
jenis nvarchar(4000) Nama opsi penyetelan otomatis yang menghasilkan rekomendasi, misalnya, FORCE_LAST_GOOD_PLAN
Alasan nvarchar(4000) Alasan mengapa rekomendasi ini diberikan.
valid_since datetime2 Pertama kali rekomendasi ini dibuat.
last_refresh datetime2 Terakhir kali rekomendasi ini dihasilkan.
state nvarchar(4000) Dokumen JSON yang menjelaskan status rekomendasi. Bidang berikut ini tersedia:
- currentValue - status rekomendasi saat ini.
- reason - konstanta yang menjelaskan mengapa rekomendasi berada dalam keadaan saat ini.
is_executable_action bit 1 = Rekomendasi dapat dijalankan terhadap database melalui skrip Transact-SQL.
0 = Rekomendasi tidak dapat dijalankan terhadap database (misalnya: informasi saja atau rekomendasi yang dikembalikan)
is_revertable_action bit 1 = Rekomendasi dapat dipantau dan dikembalikan secara otomatis oleh mesin Database.
0 = Rekomendasi tidak dapat dipantau dan dikembalikan secara otomatis. Sebagian besar tindakan yang dapat dieksekusi akan dapat dibalik.
execute_action_start_time datetime2 Tanggal rekomendasi diterapkan.
execute_action_duration time Durasi tindakan eksekusi.
execute_action_initiated_by nvarchar(4000) User = Pengguna secara manual memaksa rencana dalam rekomendasi.
System = Sistem secara otomatis menerapkan rekomendasi.
execute_action_initiated_time datetime2 Tanggal rekomendasi diterapkan.
revert_action_start_time datetime2 Tanggal rekomendasi dikembalikan.
revert_action_duration time Durasi tindakan kembali.
revert_action_initiated_by nvarchar(4000) User = Paket yang direkomendasikan pengguna yang tidak diberlakukan secara manual.
System = Sistem secara otomatis mengembalikan rekomendasi.
revert_action_initiated_time datetime2 Tanggal rekomendasi dikembalikan.
skor int Estimasi nilai/efek untuk rekomendasi ini pada skala 0-100 (semakin besar semakin baik)
Rincian nvarchar(maks) Dokumen JSON yang berisi detail selengkapnya tentang rekomendasi. Bidang berikut ini tersedia:

planForceDetails
- queryId - query_id kueri yang diregresi.
- regressedPlanId - plan_id rencana kemunculan.
- regressedPlanExecutionCount - Jumlah eksekusi kueri dengan rencana yang diregresi sebelum regresi terdeteksi.
- regressedPlanAbortedCount - Jumlah kesalahan yang terdeteksi selama eksekusi rencana yang diregresi.
- regressedPlanCpuTimeAverage - Waktu CPU rata-rata (dalam detik mikro) yang dikonsumsi oleh kueri yang diregresi sebelum regresi terdeteksi.
- regressedPlanCpuTimeStddev - Simpangan baku waktu CPU yang dikonsumsi oleh kueri yang diregresi sebelum regresi terdeteksi.
- recommendedPlanId - plan_id rencana yang harus dipaksakan.
- recommendedPlanExecutionCount- Jumlah eksekusi kueri dengan rencana yang harus dipaksakan sebelum regresi terdeteksi.
- recommendedPlanAbortedCount - Jumlah kesalahan yang terdeteksi selama eksekusi rencana yang harus dipaksakan.
- recommendedPlanCpuTimeAverage - Waktu CPU rata-rata (dalam detik mikro) yang dikonsumsi oleh kueri yang dijalankan dengan rencana yang harus dipaksakan (dihitung sebelum regresi terdeteksi).
- recommendedPlanCpuTimeStddev Simpangan baku waktu CPU yang dikonsumsi oleh kueri yang diregresi sebelum regresi terdeteksi.

implementationDetails
- method - Metode yang harus digunakan untuk memperbaiki regresi. Nilai selalu TSql.
- script - Skrip Transact-SQL yang harus dijalankan untuk memaksa rencana yang direkomendasikan.

Keterangan

Informasi yang dikembalikan oleh sys.dm_db_tuning_recommendations diperbarui saat mesin database mengidentifikasi potensi regresi performa kueri, dan tidak bertahan. Rekomendasi disimpan hanya sampai mesin database dimulai ulang. sqlserver_start_time Gunakan kolom di sys.dm_os_sys_info untuk menemukan waktu mulai mesin database terakhir. Administrator database harus secara berkala membuat salinan cadangan rekomendasi penyetelan jika mereka ingin menyimpannya setelah daur ulang server.

Bidang currentValue dalam state kolom mungkin memiliki nilai berikut:

Status Deskripsi
Active Rekomendasi aktif dan belum diterapkan. Pengguna dapat mengambil skrip rekomendasi dan menjalankannya secara manual.
Verifying Rekomendasi diterapkan oleh Mesin Database dan proses verifikasi internal membandingkan performa rencana paksa dengan rencana yang mengalami kemunduran.
Success Rekomendasi berhasil diterapkan.
Reverted Rekomendasi dikembalikan karena tidak ada keuntungan performa yang signifikan.
Expired Rekomendasi telah kedaluwarsa dan tidak dapat diterapkan lagi.

Dokumen JSON dalam state kolom berisi alasan yang menjelaskan mengapa rekomendasi dalam status saat ini. Nilai di bidang alasan mungkin:

Alasan Deskripsi
SchemaChanged Rekomendasi kedaluwarsa karena skema tabel yang direferensikan diubah. Rekomendasi baru akan dibuat jika regresi rencana kueri baru terdeteksi pada skema baru.
StatisticsChanged Rekomendasi kedaluwarsa karena perubahan statistik pada tabel yang direferensikan. Rekomendasi baru akan dibuat jika regresi rencana kueri baru terdeteksi berdasarkan statistik baru.
ForcingFailed Paket yang direkomendasikan tidak dapat dipaksakan pada kueri. last_force_failure_reason Temukan dalam tampilan sys.query_store_plan untuk menemukan alasan kegagalan.
AutomaticTuningOptionDisabled FORCE_LAST_GOOD_PLAN opsi dinonaktifkan oleh pengguna selama proses verifikasi. Aktifkan FORCE_LAST_GOOD_PLAN opsi menggunakan pernyataan ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) atau paksa paket secara manual menggunakan skrip di details kolom.
UnsupportedStatementType Paket tidak dapat dipaksakan pada kueri. Contoh kueri yang tidak didukung adalah kursor dan INSERT BULK pernyataan.
LastGoodPlanForced Rekomendasi berhasil diterapkan.
AutomaticTuningOptionNotEnabled Mesin Database mengidentifikasi potensi regresi performa, tetapi FORCE_LAST_GOOD_PLAN opsi tidak diaktifkan - lihat MENGUBAH DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Terapkan rekomendasi secara manual atau aktifkan FORCE_LAST_GOOD_PLAN opsi.
VerificationAborted Proses verifikasi dibatalkan karena mulai ulang atau pembersihan Penyimpanan Kueri.
VerificationForcedQueryRecompile Kueri dikompresi ulang karena tidak ada peningkatan performa yang signifikan.
PlanForcedByUser Pengguna secara manual memaksa paket menggunakan prosedur sp_query_store_force_plan (Transact-SQL). Mesin database tidak akan menerapkan rekomendasi jika pengguna secara eksplisit memutuskan untuk memaksa beberapa paket.
PlanUnforcedByUser Pengguna secara manual membatalkan paket menggunakan prosedur sp_query_store_unforce_plan (Transact-SQL). Karena pengguna secara eksplisit mengembalikan paket yang direkomendasikan, mesin database akan tetap menggunakan paket saat ini dan menghasilkan rekomendasi baru jika beberapa regresi rencana terjadi di masa mendatang.
UserForcedDifferentPlan Pengguna secara manual memaksa paket yang berbeda menggunakan prosedur sp_query_store_force_plan (Transact-SQL). Mesin database tidak akan menerapkan rekomendasi jika pengguna secara eksplisit memutuskan untuk memaksa beberapa paket.
TempTableChanged Tabel sementara yang digunakan dalam paket diubah.

Statistik di details kolom tidak menampilkan statistik rencana runtime (misalnya, waktu CPU saat ini). Detail rekomendasi diambil pada saat deteksi regresi dan menjelaskan mengapa Mesin Database mengidentifikasi regresi performa. Gunakan regressedPlanId dan recommendedPlanId untuk mengkueri tampilan katalog Penyimpanan Kueri untuk menemukan statistik rencana runtime yang tepat.

Contoh penggunaan informasi rekomendasi penyetelan

Contoh 1

Kode sampel berikut mendapatkan skrip Transact-SQL yang dihasilkan yang memaksa rencana yang baik untuk kueri tertentu:

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';

Contoh 2

Berikut ini mendapatkan skrip Transact-SQL yang dihasilkan yang memaksa rencana yang baik untuk kueri tertentu dan informasi tambahan tentang perkiraan perolehan:

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;

Contoh 3

Berikut ini mendapatkan skrip Transact-SQL yang dihasilkan yang memaksa rencana yang baik untuk kueri tertentu dan informasi tambahan yang menyertakan teks kueri dan rencana kueri yang disimpan di Penyimpanan Kueri:

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;

Untuk informasi selengkapnya tentang fungsi JSON yang dapat digunakan untuk mengkueri nilai dalam tampilan rekomendasi, lihat Dukungan JSON di Mesin Database.

Izin

VIEW SERVER STATE Memerlukan izin di SQL Server.

VIEW DATABASE STATE Memerlukan izin untuk database di Azure SQL Database.

Izin untuk SQL Server 2022 dan yang lebih baru

VIEW SERVER PERFORMANCE STATE Memerlukan izin pada server.

Langkah berikutnya