Sorgu Profili Oluşturma Altyapısı

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

SQL Server Veritabanı Altyapısı, sorgu yürütme planlarıyla ilgili çalışma zamanı bilgilerine erişme olanağı 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. Bunun için 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ı

sorgu yürütme istatistikleri profil altyapısıveya standart profil oluşturma, satır sayısı, CPU ve G/Ç kullanımı gibi yürütme planları hakkında bilgi toplamak için etkinleştirilmelidir. hedef oturumu için yürütme planı bilgilerini toplamak adına aşağıdaki yöntemler, standart profil oluşturma altyapısından yararlanır.

• SET STATISTICS XML
• İSTATİSTİK PROFİLİNİ AYARLA
• Canlı Sorgu İstatistikleri

Tüm oturumları için yürütme planı bilgilerini genel olarak toplamanın aşağıdaki yöntemleri standart profil oluşturma altyapısından yararlanılır:

• genişletilmiş olay query_post_execution_showplan. 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ırken, sys.dm_exec_query_profiles DMV de doldurulur ve bu da Etkinlik İzleyicisi kullanılarak veya doğrudan DMV'yi sorgulayarak tüm oturumlar için canlı sorgu istatistiklerini etkinleştirir. 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,altyapı profili oluşturma veya basit profil oluşturma yeni bir basit sorgu yürütme istatistikleri tanıtıldı.

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

için geçerlidir: SQL Server (SQL Server 2014 (12.x) SP2 ile SQL Server 2016 (13.x)).

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.

Basit profil oluşturmadan yararlanan yeni bir query_thread_profile genişletilmiş olayı da kullanıma sunulmuştur. 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ılırken, sys.dm_exec_query_profiles DMV de tüm oturumlar için canlı sorgu istatistiklerini etkinleştiren, Etkinlik İzleyicisi veya doğrudan DMV'yi sorgulayan basit profil oluşturma kullanılarak doldurulur.

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

için geçerlidir: SQL Server (SQL Server 2016 (13.x) SP1 ile SQL Server 2017 (14.x)).

SQL Server 2016 (13.x) SP1, minimum ek yükle basit profil oluşturmanın düzeltilmiş bir sürümünü içerir. Küresel olarak hafif profil oluşturma, yukarıda belirtilen sürümler için izleme bayrağı 7412 kullanılarak genel olarak etkinleştirilebilir ve bu özelliğin hangi sürümler için geçerli olduğunu görmek için Uygulama alanıbölümüne bakılabilir. 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ş olaya benzer gerçek bir yürütme planı XML'i sağlayan yeni bir query_plan_profile genişletilmiş olay da çıktılır.

genişletilmiş query_plan_profile olayı kullanan ö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 (SQL Server 2019 (15.x) ile başlayarak) 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) ile başlayarak 7412 izleme bayrağının hiçbir etkisi yoktur. Basit profil oluşturma, LIGHTWEIGHT_QUERY_PROFILING veritabanı kapsamlı yapılandırma: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;kullanılarak veritabanı düzeyinde devre dışı bırakılabilir.

Ç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_STATS veritabanı 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 aksine basit profil oluşturma temelinde gerçek bir yürütme planının eşdeğerini toplar. SQL Server 2017 (14.x), CU14 ile başlayan bu olayı da sunar. genişletilmiş query_post_execution_plan_profile olayı kullanan ö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.

Açıklamalar

Basit profil oluşturma v2'den ve düşük yükünden başlayarak, henüz CPU sınırı olmayan tüm sunucular sürekli olarakbasit profil oluşturma çalıştırabilir ve veritabanı uzmanlarının herhangi bir zamanda çalışan herhangi bir yürütmeye (örneğin Etkinlik İzleyicisi'ni kullanarak veya sys.dm_exec_query_profilesdoğrudan sorgulayarak) ve çalışma zamanı istatistikleriyle sorgu planını almalarına izin verebilir.

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

Ayrıca Bkz.

Performans için İzleme ve Ayarlama
Performans İzleme ve Ayarlama Araçları
Açık Etkinlik İzleyicisi (SQL Server Management Studio)
Etkinlik İzleyicisi
Sorgu Deposu Kullanarak İzleme Performansını
Genişletilmiş Olayları kullanarak Sistem Etkinliğini İzleme
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
İzleme bayrakları
Showplan Mantıksal ve Fiziksel İşleçler Referansı
Gerçek yürütme planı
Canlı Sorgu İstatistikleri