sys.dm_db_tuning_recommendations (Transact-SQL)

Şunlar için geçerlidir: SQL Server 2017 (14.x) ve sonraki sürümleri Microsoft Fabric'teAzure SQL VeritabanıAzure SQL Yönetilen Örneği SQL veritabanı

Otomatik ayarlama önerileri hakkında ayrıntılı bilgi döndürür. Daha fazla bilgi için bkz. Otomatik ayarlama.

Daha fazla bilgi için bkz. Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği'nde izleme ve performans ayarlama.

Azure SQL Veritabanı'nda dinamik yönetim görünümleri, veritabanı kapsamasını etkileyen veya kullanıcının erişimi olan diğer veritabanları hakkındaki bilgileri kullanıma sunan bilgileri kullanıma sunmaz. Bu bilgilerin açığa çıkmasını önlemek için, bağlı kiracıya ait olmayan verileri içeren her satır filtrelenir.

Sütun adı	Veri türü	Description
name	nvarchar(4000)	Önerinin benzersiz adı.
type	nvarchar(4000)	Öneriyi oluşturan otomatik ayarlama seçeneğinin adı, örneğin, FORCE_LAST_GOOD_PLAN.
reason	nvarchar(4000)	Bu önerinin neden sağlandığının nedeni.
valid_since	datetime2	Bu öneri ilk kez oluşturuldu.
last_refresh	datetime2	Bu önerinin en son oluşturulduğu zaman.
state	nvarchar(4000)	Önerinin durumunu açıklayan JSON belgesi. Aşağıdaki alanlar kullanılabilir: - currentValue - önerinin geçerli durumu. - reason - önerinin neden geçerli durumda olduğunu açıklayan sabit.
is_executable_action	bit	1 = Öneri, Transact-SQL betiği aracılığıyla veritabanına karşı yürütülebilir. 0 = Öneri veritabanında yürütülemez (örneğin: yalnızca bilgi veya geri alınan öneri).
is_revertable_action	bit	1 = Öneri Veritabanı altyapısı tarafından otomatik olarak izlenebilir ve geri döndürülebilir. 0 = Öneri otomatik olarak izlenemez ve geri döndürülemez. Yürütülebilir eylemlerin çoğu geri döndürülebilir.
execute_action_start_time	datetime2	Önerinin uygulandığı tarih.
execute_action_duration	time	Yürütme eyleminin süresi.
execute_action_initiated_by	nvarchar(4000)	User = Öneride kullanıcı tarafından el ile zorlanan plan. System = Sistem otomatik olarak uygulanan öneri.
execute_action_initiated_time	datetime2	Önerinin uygulandığı tarih.
revert_action_start_time	datetime2	Önerinin geri alındığı tarih.
revert_action_duration	time	Geri döndürme eyleminin süresi.
revert_action_initiated_by	nvarchar(4000)	User = Kullanıcı el ile uygulanamayan önerilen plan. System = Sistem öneriyi otomatik olarak geri döndürüyor.
revert_action_initiated_time	datetime2	Önerinin geri alındığı tarih.
score	int	Bu önerinin 0-100 ölçeği üzerindeki tahmini değeri veya etkisi (ne kadar büyük ise o kadar iyi).
details	nvarchar(max)	Öneri hakkında daha fazla ayrıntı içeren bir JSON belgesi. Aşağıdaki alanlar kullanılabilir. planForceDetails: - queryId - query_id değerini seçin. - regressedPlanId - plan_id gerilemiş planın. - regressedPlanExecutionCount - Regresyon algılanana kadar gerileyen planlı sorgu yürütme sayısı. - regressedPlanAbortedCount - Gerileyen planın yürütülmesi sırasında algılanan hataların sayısı. - regressedPlanCpuTimeAverage - Regresyon algılanmasından önce regresyon sorgusu tarafından tüketilen ortalama CPU süresi (mikrosaniye olarak). - regressedPlanCpuTimeStddev - Regresyon algılanana kadar gerileyen sorgu tarafından tüketilen CPU süresinin standart sapması. - recommendedPlanId - plan_id zorunlu olması gereken planın. - recommendedPlanExecutionCount- Regresyon algılanmadan önce zorlanması gereken planlı sorgu yürütme sayısı. - recommendedPlanAbortedCount - Planın yürütülmesi sırasında algılanan ve zorlanması gereken hata sayısı. - recommendedPlanCpuTimeAverage - Zorlanması gereken planla yürütülen sorgu tarafından tüketilen ortalama CPU süresi (mikrosaniye olarak) (regresyon algılanmadan önce hesaplanır). - recommendedPlanCpuTimeStddev Regresyon algılanana kadar gerileyen sorgu tarafından tüketilen CPU süresinin standart sapması. implementationDetails: - method - Regresyonu düzeltmek için kullanılması gereken yöntem. Değer her zaman TSqlşeklindedir. - script - Önerilen planı zorlamak için yürütülmesi gereken Transact-SQL betik.

Açıklamalar

tarafından sys.dm_db_tuning_recommendations döndürülen bilgiler, veritabanı altyapısı olası bir sorgu performansı regresyonu tanımladığında ve kalıcı olmadığında güncelleştirilir. Veritabanı altyapısı, önerileri yalnızca yeniden başlatılana kadar tutar. sqlserver_start_time Son veritabanı altyapısı başlangıç zamanını bulmak için sys.dm_os_sys_info sütununu kullanın. Veritabanı yöneticileri, sunucu geri dönüşümünün ardından saklamak istediklerinde ayarlama önerisinin yedek kopyalarını düzenli aralıklarla oluşturmalıdır.

currentValue Sütundaki state alanda aşağıdaki değerler olabilir:

Statü	Description
Active	Öneri etkin ve henüz uygulanmadı. Kullanıcı öneri betiğini alabilir ve el ile yürütebilir.
Verifying	Öneri Veritabanı Altyapısı tarafından uygulanır ve iç doğrulama işlemi zorlanan planın performansını gerileyen planla karşılaştırır.
Success	Öneri başarıyla uygulandı.
Reverted	Önemli bir performans kazancı olmadığından öneri geri döndürülür.
Expired	Önerinin süresi doldu ve artık uygulanamıyor.

Sütundaki state JSON belgesi, önerinin neden geçerli durumda olduğunu açıklayan nedeni içerir. Neden alanındaki değerler:

Reason	Description
SchemaChanged	Başvurulan bir tablonun şeması değiştiğinden önerinin süresi doldu. Yeni şemada yeni bir sorgu planı regresyonu algılanırsa yeni bir öneri oluşturulur.
StatisticsChanged	Başvurulan tablodaki istatistik değişikliği nedeniyle önerinin süresi doldu. Yeni istatistikler temelinde yeni bir sorgu planı regresyonu algılanırsa yeni bir öneri oluşturulur.
ForcingFailed	Önerilen plan sorguda zorlanamaz. last_force_failure_reason Hatanın nedenini bulmak için sys.query_store_plan görünümünde öğesini bulun.
AutomaticTuningOptionDisabled	FORCE_LAST_GOOD_PLAN seçeneği doğrulama işlemi sırasında kullanıcı tarafından devre dışı bırakılır. ALTER DATABASE SET AUTOMATIC_TUNING deyimini kullanarak seçeneği etkinleştirin FORCE_LAST_GOOD_PLAN veya sütundaki details betiği kullanarak planı el ile zorlayın.
UnsupportedStatementType	Plan sorguda zorlanamaz. Desteklenmeyen sorgulara örnek olarak imleçler ve INSERT BULK deyim verilebilir.
LastGoodPlanForced	Öneri başarıyla uygulandı.
AutomaticTuningOptionNotEnabled	Veritabanı Altyapısı olası performans gerilemesi olduğunu belirledi, ancak FORCE_LAST_GOOD_PLAN seçenek etkinleştirilmedi. Daha fazla bilgi için bkz. ALTER DATABASE SET AUTOMATIC_TUNING. Öneriyi el ile uygulayın veya seçeneği etkinleştirin FORCE_LAST_GOOD_PLAN .
VerificationAborted	Yeniden başlatma veya Sorgu Deposu temizlemesi nedeniyle doğrulama işlemi durduruldu.
VerificationForcedQueryRecompile	Önemli bir performans artışı olmadığından sorgu yeniden derlendi.
PlanForcedByUser	Kullanıcı , sp_query_store_force_plan yordamı kullanarak planı el ile zorunlu bıraktı. Kullanıcı açıkça bir planı zorlamaya karar verirse veritabanı altyapısı öneriyi uygulamaz.
PlanUnforcedByUser	Kullanıcı , sp_query_store_unforce_plan yordamı kullanarak planı el ile kaldırmış. Kullanıcı önerilen planı açıkça geri döndürdiğinden, veritabanı altyapısı geçerli planı kullanmaya devam eder ve gelecekte bir plan regresyonu oluşursa yeni bir öneri oluşturur.
UserForcedDifferentPlan	Kullanıcı , sp_query_store_force_plan yordamı kullanarak el ile farklı bir plana zorlandı. Kullanıcı açıkça bir planı zorlamaya karar verirse veritabanı altyapısı öneriyi uygulamaz.
TempTableChanged	Planda kullanılan geçici bir tablo değiştirilir.

Sütundaki details istatistikler çalışma zamanı planı istatistiklerini (örneğin, geçerli CPU zamanı) göstermez. Öneri ayrıntıları regresyon algılama sırasında alınır ve Veritabanı Altyapısı'nın performans regresyonunu neden tanımladığını açıklar. Tam çalışma zamanı planı istatistiklerini bulmak için regressedPlanId sorgulamak için ve recommendedPlanId kullanın.

Ayarlama önerileri bilgilerini kullanma örnekleri

Örnek 1

Aşağıdaki örnek kod, belirli bir sorgu için iyi bir planı zorlayan oluşturulan Transact-SQL betiğini alır:

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

Örnek 2

Aşağıdaki sorgu, belirli bir sorgu için iyi bir plan yapmayı zorlayan oluşturulan Transact-SQL betiğini ve tahmini kazanç hakkında ek bilgileri alır:

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;

Örnek 3

Aşağıdaki sorgu, belirli bir sorgu için iyi bir plan ve sorgu metnini ve Sorgu Deposu'nda depolanan sorgu planlarını içeren ek bilgileri zorlayan oluşturulan Transact-SQL betiğini alır:

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;

Öneri görünümünde değerleri sorgulamak için kullanabileceğiniz JSON işlevleri hakkında daha fazla bilgi için bkz. SQL Server'da JSON verileri.

Permissions

SQL Server 2022 (16.x) ve önceki sürümler SQL Server'da izin gerektirir VIEW SERVER STATE .

SQL Server 2022 (16.x) ve Azure SQL Veritabanı sunucuda izin gerektirir VIEW SERVER PERFORMANCE STATE .

İlgili içerik

• Otomatik ayarlama
• sys.database_automatic_tuning_options (Transact-SQL) - veritabanı otomatik ayarlama seçenekleri
• sys.database_query_store_options (Transact-SQL)
• SQL Server içindeki JSON verileri
• sys.dm_os_sys_info (Transact-SQL)