sys.dm_db_tuning_recommendations (T-SQL)

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

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

Untuk informasi selengkapnya, lihat Memantau dan menyetel performa di Azure SQL Database dan Azure SQL Managed Instance.

Di Azure SQL Database, tampilan manajemen dinamis tidak dapat mengekspos informasi yang 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
name	nvarchar(4000)	Nama rekomendasi yang unik.
type	nvarchar(4000)	Nama opsi penyetelan otomatis yang menghasilkan rekomendasi, misalnya, FORCE_LAST_GOOD_PLAN.
reason	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 dieksekusidapat 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.
score	int	Estimasi nilai atau efek untuk rekomendasi ini pada skala 0-100 (semakin besar semakin baik).
details	nvarchar(maks)	Dokumen JSON yang berisi detail selengkapnya tentang rekomendasi. Bidang berikut ini tersedia. planForceDetails: - queryId - query_id kueri yang diregresi. - regressedPlanId - plan_id dari rencana yang diregresi. - 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 mikrodetik) 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 mikrodetik) 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. Mesin database menyimpan rekomendasi hanya sampai 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 di state kolom berisi alasan yang menjelaskan mengapa rekomendasi berada dalam status saat ini. Nilai di bidang alasan mungkin:

Reason	Deskripsi
SchemaChanged	Rekomendasi kedaluwarsa karena skema tabel yang direferensikan berubah. Rekomendasi baru dibuat jika regresi rencana kueri baru terdeteksi pada skema baru.
StatisticsChanged	Rekomendasi kedaluwarsa karena perubahan statistik pada tabel yang direferensikan. Rekomendasi baru 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 ALTER DATABASE SET pernyataan AUTOMATIC_TUNING atau paksa rencana 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. Untuk informasi selengkapnya, lihat ALTER DATABASE SET AUTOMATIC_TUNING. 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 . 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 . Karena pengguna secara eksplisit mengembalikan paket yang direkomendasikan, mesin database terus 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 . 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] BIGINT '$.queryId',
    regressed_plan_id BIGINT '$.regressedPlanId',
    last_good_plan_id BIGINT '$.recommendedPlanId'
) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';

Contoh 2

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

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

Contoh 3

Kueri berikut 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,
       JSON_VALUE(STATE, '$.currentValue') AS current_state,
       JSON_VALUE(STATE, '$.reason') AS current_state_reason,
       JSON_VALUE(details, '$.implementationDetails.script') AS script,
       (regressedPlanExecutionCount + recommendedPlanExecutionCount) * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000 AS estimated_gain,
       IIF (regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO') AS error_prone
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (details, '$.planForceDetails') WITH (
    [query_id] BIGINT '$.queryId',
    regressedPlanId BIGINT '$.regressedPlanId',
    recommendedPlanId BIGINT '$.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 bisa Anda gunakan untuk mengkueri nilai dalam tampilan rekomendasi, lihat data JSON di SQL Server.

Permissions

SQL Server 2022 (16.x) dan versi yang lebih lama memerlukan VIEW SERVER STATE izin di SQL Server.

SQL Server 2022 (16.x) dan Azure SQL Database memerlukan VIEW SERVER PERFORMANCE STATE izin di server.

Konten terkait

• Penyetelan otomatis
• sys.database_automatic_tuning_options (T-SQL)
• sys.database_query_store_options (Transact-SQL)
• Data JSON dalam SQL Server
• sys.dm_os_sys_info (T-SQL)