Sorgu Profili Oluşturma Altyapısı

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen ÖrneğiMicrosoft Fabric'te SQL veritabanı

SQL Server Veritabanı Altyapısı, sorgu yürütme planlarıyla ilgili çalışma zamanı bilgilerine erişim sağlar. Bir performans sorunu oluştuğunda en önemli eylemlerden biri, yürütülen iş yükü ve kaynak kullanımının nasıl yönlendirildiğini tam olarak anlamaktır. Bu nedenle , gerçek yürütme planına erişim önemlidir.

Gerçek bir sorgu planının kullanılabilirliği için sorgu tamamlama önkoşulu olsa da canlı sorgu istatistikleri, veriler bir sorgu planı işlecinden diğerine akarken sorgu yürütme işlemiyle ilgili gerçek zamanlı içgörüler sağlayabilir. Canlı sorgu planı, genel sorgu ilerleme durumunu ve üretilen satır sayısı, geçen süre, işleç ilerlemesi gibi işleç düzeyinde çalışma zamanı yürütme istatistiklerini görüntüler. Bu veriler sorgunun tamamlanmasını beklemeye gerek kalmadan gerçek zamanlı olarak kullanılabilir olduğundan, bu yürütme istatistikleri uzun süre çalışan sorgular ve süresiz çalıştırılan ve hiç bitmeyen sorgular gibi sorgu performansı sorunlarının hatalarını ayıklamak için son derece kullanışlıdır.

Standart sorgu yürütme istatistikleri profil oluşturma altyapısı

Satır sayısı, CPU ve G/Ç kullanımı gibi yürütme planları hakkında bilgi toplamak için sorgu yürütme istatistikleri profil altyapısının veya standart profil oluşturmanın etkinleştirilmesi gerekir. Hedef oturum için yürütme planı bilgilerini toplamak için aşağıdaki yöntemler standart profil oluşturma altyapısını kullanır:

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Canlı Sorgu İstatistikleri

Tüm oturumlar için yürütme planı bilgilerini genel olarak toplamak için aşağıdaki yöntemler standart profil oluşturma altyapısını kullanır:

• query_post_execution_showplan genişletilmiş olay. Genişletilmiş Olayları etkinleştirmek için bkz. Genişletilmiş Olayları Kullanarak Sistem Etkinliğini İzleme.
• SQL Trace ve SQL Server Profileriçindeki Showplan XML izleme olayı. Bu izleme olayı hakkında daha fazla bilgi için bkz. Showplan XML Event Class.

query_post_execution_showplan olayını kullanan genişletilmiş bir olay oturumu çalıştırıldığında, sys.dm_exec_query_profiles DMV'si de doldurulur; bu da Activity Monitor kullanılarak veya doğrudan DMV sorgulanarak tüm oturumlar için canlı sorgu istatistiklerinin etkinleştirilmesini sağlar. Daha fazla bilgi için bkz. Canlı Sorgu İstatistikleri.

Hafif sorgu yürütme istatistikleri profilleme altyapısı

SQL Server 2014 (12.x) SP2 ve SQL Server 2016 (13.x) ile başlayarak, yeni bir hafif sorgu yürütme istatistikleri profilleme altyapısı, yani hafif profilleme tanıtıldı.

Basit sorgu yürütme istatistikleri profil oluşturma altyapısı v1

Şunlar için geçerlidir: SQL Server 2014 (12.x) SP2 ile SQL Server 2016 (13.x) arasında.

SQL Server 2014 (12.x) SP2 ve SQL Server 2016 (13.x) ile başlayarak, basit profil oluşturmanın kullanıma sunulmasıyla yürütme planları hakkında bilgi toplamaya yönelik performans yükü azaltıldı. Standart profil oluşturmanın aksine, basit profil oluşturma CPU çalışma zamanı bilgilerini toplamaz. Ancak, hafif profil oluşturma hala satır sayısını ve Girdi/Çıktı kullanım bilgilerini toplar.

Hafif profil oluşturma kullanan yeni query_thread_profile genişletilmiş olayı da ayrıca kullanıma sunuldu. Bu uzatılmış etkinlik, her operatör için yürütme istatistiklerini kullanıma sunarak her düğüm ve iş parçacığının performansı hakkında daha fazla içgörü sağlamaya olanak tanır. Bu genişletilmiş olayı kullanan örnek oturum aşağıdaki örnekte olduğu gibi yapılandırılabilir:

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile
    (
    ACTION (sqlos.scheduler_id,
            sqlserver.database_id,
            sqlserver.is_system,
            sqlserver.plan_handle,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.server_instance_name,
            sqlserver.session_id,
            sqlserver.session_nt_username,
            sqlserver.sql_text)
    )
ADD TARGET package0.ring_buffer (SET max_memory = (25600))
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

query_thread_profile olayını kullanan genişletilmiş bir olay oturumu çalıştırıldığında, sys.dm_exec_query_profiles DMV'si de hafif profil oluşturma kullanılarak doldurulur; bu da Activity Monitor kullanılarak veya doğrudan DMV sorgulanarak tüm oturumlar için canlı sorgu istatistiklerini etkinleştirir.

Basit sorgu yürütme istatistikleri profil oluşturma altyapısı v2

Şunlar için geçerlidir: SQL Server 2016 (13.x) SP1 ile SQL Server 2017 (14.x) arasında.

SQL Server 2016 (13.x) SP1, minimum ek yükle basit profil oluşturmanın düzeltilmiş bir sürümünü içerir. Hafif profil oluşturma, daha önce Uygulandığı sürümler için izleme bayrağı 7412 kullanılarak tüm sistemlerde de etkinleştirilebilir. Uçuş içi istekler için sorgu yürütme planını döndürmek için yeni bir DMF sys.dm_exec_query_statistics_xml kullanıma sunulmuştur.

SQL Server 2016 (13.x) SP2 CU3 ve SQL Server 2017 (14.x) CU11'den başlayarak, basit profil oluşturma genel olarak etkinleştirilmediyse, yeni USE HINT sorgu ipucu bağımsız değişkeni QUERY_PLAN_PROFILE tüm oturumlarda sorgu düzeyinde basit profil oluşturmayı etkinleştirmek için kullanılabilir. Bu yeni ipucunu içeren bir sorgu tamamlandığında, query_post_execution_showplan genişletilmiş olayına benzer bir gerçek yürütme planı XML’si sağlayan yeni bir query_plan_profile genişletilmiş olayı da oluşturulur.

Genişletilmiş olayı kullanan query_plan_profile örnek oturum aşağıdaki örnekte olduğu gibi yapılandırılabilir:

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile
    (
    ACTION (sqlos.scheduler_id,
            sqlserver.database_id,
            sqlserver.is_system,
            sqlserver.plan_handle,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.server_instance_name,
            sqlserver.session_id,
            sqlserver.session_nt_username,
            sqlserver.sql_text)
    )
ADD TARGET package0.ring_buffer (SET max_memory = (25600))
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

Basit sorgu yürütme istatistikleri profil oluşturma altyapısı v3

için geçerlidir: SQL Server 2019 (15.x) ve sonraki sürümler ve Azure SQL Veritabanı

SQL Server 2019 (15.x) ve Azure SQL Veritabanı, tüm yürütmeler için satır sayısı bilgilerini toplayan basit profil oluşturmanın yeni düzeltilmiş bir sürümünü içerir. Basit profil oluşturma, SQL Server 2019 (15.x) ve Azure SQL Veritabanı'nda varsayılan olarak etkindir. SQL Server 2019 (15.x) ve sonraki sürümlerinde izleme bayrağı 7412'nin hiçbir etkisi yoktur. Hafif profilleme, LIGHTWEIGHT_QUERY_PROFILINGveritabanı kapsamındaki yapılandırma kullanılarak veritabanı düzeyinde devre dışı bırakılabilir: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Çoğu sorgu için bilinen son gerçek yürütme planının eşdeğerini döndürmek için yeni bir DMF sys.dm_exec_query_plan_stats tanıtılır veson sorgu planı istatistikleri olarak adlandırılır. Son sorgu planı istatistikleri, LAST_QUERY_PLAN_STATSveritabanı kapsamlı yapılandırması kullanılarak veritabanı düzeyinde etkinleştirilebilir: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Yeni query_post_execution_plan_profile genişletilmiş olayı, standart profil oluşturma kullanan query_post_execution_showplan öğesinden farklı olarak, hafif profil oluşturmaya dayalı gerçek yürütme planına eşdeğer bir çıktı toplar. SQL Server 2017 (14.x), CU14 ile başlayan bu olayı da sunar. Genişletilmiş olayı kullanan query_post_execution_plan_profile örnek oturum aşağıdaki örnekte olduğu gibi yapılandırılabilir:

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile
    (
    ACTION (sqlos.scheduler_id,
            sqlserver.database_id,
            sqlserver.is_system,
            sqlserver.plan_handle,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.server_instance_name,
            sqlserver.session_id,
            sqlserver.session_nt_username,
            sqlserver.sql_text)
    )
ADD TARGET package0.ring_buffer (SET max_memory = (25600))
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

Örnek 1 - Standart profilleme kullanılarak Genişletilmiş Etkinlik oturumu

CREATE EVENT SESSION [QueryPlanOld] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan
    (
    ACTION (sqlos.task_time,
            sqlserver.database_id,
            sqlserver.database_name,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.sql_text)
    )
ADD TARGET package0.event_file
    (
    SET filename = N'C:\Temp\QueryPlanStd.xel'
    )
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

Örnek 2 - Hafif profil oluşturma kullanarak genişletilmiş Olay oturumu

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile
    (
    ACTION (sqlos.task_time,
            sqlserver.database_id,
            sqlserver.database_name,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.sql_text)
    )
ADD TARGET package0.event_file
    (
    SET filename = N'C:\Temp\QueryPlanLWP.xel'
    )
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

Sorgu Profili Oluşturma Altyapısı kullanım kılavuzu

Aşağıdaki tabloda, hem genel olarak (sunucu düzeyinde) hem de tek bir oturumda standart profil oluşturmayı veya basit profil oluşturmayı etkinleştirme eylemleri özetlenmiştir. Ayrıca eylemin erişilebilir olduğu sürümün en eski halini de içerir.

Remarks

Hafif profil oluşturma v2 ve onun düşük ek yükü sayesinde, zaten CPU darboğazı yaşamayan herhangi bir sunucu hafif profil oluşturmayı sürekli olarak çalıştırabilir ve veritabanı uzmanlarının, örneğin Etkinlik İzleyicisi’ni kullanarak veya sys.dm_exec_query_profiles öğesini doğrudan sorgulayarak, herhangi bir anda çalışmakta olan herhangi bir yürütmeyi inceleyip çalışma zamanı istatistikleriyle birlikte sorgu planını almasına olanak tanır.

Sorgu profili oluşturmanın performans yükü hakkında daha fazla bilgi için Developers Choice: Query progress - her zaman, her yerdeblog gönderisine bakın.

Basit profil oluşturma kullanan Genişletilmiş Olaylar, standart profil oluşturma altyapısının zaten etkin olması durumunda standart profil oluşturma bilgilerini kullanır. Örneğin, query_post_execution_showplan kullanan genişletilmiş bir olay oturumu çalıştırılır ve query_post_execution_plan_profile kullanan başka bir oturum başlatılır. İkinci oturum hala standart profil oluşturma bilgilerini kullanır.

İlgili içerik

• Performans için İzleme ve Ayarlama
• Performans izleme ve ayarlama araçları
• SQL Server Management Studio'da (SSMS) Açık Etkinlik İzleyicisi
• Etkinlik İzleyicisi
• Sorgu Deposu’nu kullanarak performansı izleme
• Genişletilmiş Olayları kullanarak Sistem Etkinliğini İzleme
• sys.dm_exec_query_statistics_xml
• sys.dm_exec_query_profiles
• DBCC TRACEON (Transact-SQL) ile izleme bayrakları ayarlama
• Mantıksal ve Fiziksel Showplan İşleç Referansı
• Gerçek yürütme planı
• Canlı Sorgu İstatistikleri