Aracılığıyla paylaş

SQL Server'da bellek atamalarının neden olduğu yavaş performans veya düşük bellek sorunlarını giderme

Bellek izinleri nedir?

Sorgu Yürütme (QE) Ayırmaları, Sorgu Yürütme Belleği, Çalışma Alanı Belleği ve Bellek Ayırmaları olarak da adlandırılan bellek izinleri, sorgu yürütme zamanında bellek kullanımını açıklar. SQL Server, sorgu yürütme sırasında bu belleği aşağıdaki amaçlardan biri veya daha fazlası için ayırır:

  • Sıralama işlemleri
  • Karma işlemleri
  • Toplu kopyalama işlemleri (yaygın bir sorun değildir)
  • Dizin oluşturma için çalışma zamanında karma sözlükler/tablolar kullanıldığından COLUMNSTORE dizinlerine ekleme de dahil olmak üzere dizin oluşturma (yaygın bir sorun değildir)

Bir bağlam sağlamak için, bir sorgu yaşam süresi boyunca ne yapması gerektiğine bağlı olarak farklı bellek ayırıcılarından veya katiplerinden bellek isteyebilir. Örneğin, bir sorgu başlangıçta ayrıştırılıp derlendiğinde derleme belleği tüketir. Sorgu derlendiğinde, bu bellek serbest bırakılır ve sonuçta elde edilen sorgu planı plan önbelleği belleğinde depolanır. Bir plan önbelleğe alındıktan sonra sorgu yürütülmeye hazırdır. Sorgu herhangi bir sıralama işlemi, karma eşleştirme işlemi (JOIN veya toplama) veya COLUMNSTORE dizinlerine eklemeler yaparsa, sorgu yürütme ayırıcısından bellek kullanır. Başlangıçta sorgu bu yürütme belleğini ister ve daha sonra bu bellek verildiğinde sorgu sonuçları veya karma demetleri sıralamak için belleğin tamamını veya bir bölümünü kullanır. Sorgu yürütme sırasında ayrılan bu bellek, bellek verme olarak adlandırılır. Tahmin edebileceğiniz gibi, sorgu yürütme işlemi tamamlandıktan sonra bellek verme, diğer çalışmalarda kullanmak üzere SQL Server'a geri gönderilir. Bu nedenle, bellek verme ayırmaları doğası gereği geçicidir, ancak yine de uzun sürebilir. Örneğin, bir sorgu yürütmesi bellekteki çok büyük bir satır kümesinde sıralama işlemi gerçekleştirirse, sıralama birkaç saniye veya dakika sürebilir ve verilen bellek sorgunun ömrü boyunca kullanılır.

Bellek izni olan sorgu örneği

Aşağıda yürütme belleğini kullanan bir sorgu örneği ve verme işlemini gösteren sorgu planı verilmiştir:

SELECT * 
FROM sys.messages
ORDER BY message_id

Bu sorgu 300.000'den fazla satırdan oluşan bir satır kümesi seçer ve sıralar. Sıralama işlemi bir bellek verme isteğine neden oldu. Bu sorguyu SSMS'de çalıştırırsanız, sorgu planını görüntüleyebilirsiniz. Sorgu planının en soldaki SELECT işlecini seçtiğinizde, sorgunun bellek verme bilgilerini görüntüleyebilirsiniz (Özellikler'i göstermek için F4 tuşuna basın):

Bellek verme ve sorgu planı içeren bir sorgunun ekran görüntüsü.

Ayrıca, sorgu planındaki boşluklara sağ tıklarsanız Yürütme Planı XML'sini Göster... öğesini seçebilir ve aynı bellek verme bilgilerini gösteren bir XML öğesi bulabilirsiniz.

 <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />

Çeşitli terimlerin burada açıklaması gerekir. Sorgu belirli bir miktarda yürütme belleği (DesiredMemory) isteyebilir ve genellikle bu miktarı (RequestedMemory) isteyebilir. Çalışma zamanında, SQL Server kullanılabilirliğe (GrantedMemory) bağlı olarak istenen belleğin tamamını veya bir bölümünü verir. Sonunda, sorgu başlangıçta istenen belleğin (MaxUsedMemory) daha fazlasını veya daha azını kullanabilir. Sorgu iyileştiricisi gereken bellek miktarını fazla tahmin ettiyse, istenen boyuttan daha azını kullanır. Ancak bu bellek başka bir istek tarafından kullanılabileceğinden boşa harcanmıştır. Öte yandan, iyileştirici gereken belleğin boyutunu hafife aldıysa, yürütme zamanında işi yapmak için fazla satırlar diske dökülebilir. SQL Server, başlangıçta istenen boyuttan daha fazla bellek tahsis etmek yerine fazladan satırları diske gönderir ve geçici bir çalışma alanı olarak kullanır. Daha fazla bilgi için bkz. Bellek Verme Konusunda Dikkat Edilmesi Gerekenler bölümünde Workfile'lar ve Worktable'lar.

Terminoloji

Şimdi bu bellek tüketicisi ile ilgili karşılaşabileceğiniz farklı terimleri gözden geçirelim. Tüm bunlar da aynı bellek ayırmalarıyla ilgili kavramları açıklar.

  • Sorgu Yürütme Belleği (QE Bellek): Bu terim, sorgunun yürütülmesi sırasında sıralama veya karma bellek kullanıldığı gerçeğini vurgulamak için kullanılır. Genellikle QE bellek, sorgunun ömrü boyunca en büyük bellek tüketicisidir.

  • Sorgu Yürütme (QE) Ayırmaları veya Bellek Ayırmaları: Sorgu sıralama veya karma işlemleri için belleğe ihtiyaç duyduğunda, bellek için rezervasyon isteğinde bulunur. Bu rezervasyon isteği, tahmini kardinaliteye göre derleme zamanında hesaplanır. Daha sonra, sorgu yürütürken SQL Server bu isteği bellek kullanılabilirliğine bağlı olarak kısmen veya tamamen verir. Sonunda, sorgu verilen belleğin yüzdesini kullanabilir. Bu bellek ayırmalarını izleyen 'MEMORYCLERK_SQLQERESERVATIONS' adlı bir bellek katibi (bellek muhasebecisi) vardır (DBCC MEMORYSTATUS veya sys.dm_os_memory_clerks göz atın).

  • Bellek Verme: SQL Server, yürütülen bir sorguya istenen belleği verince, bellek verme işleminin gerçekleştiği söylenir. "Grant" terimini kullanan birkaç performans sayacı vardır. Bu sayaçlar Memory Grants Outstanding ve Memory Grants Pending, karşılanan veya bekleyen bellek atamalarının sayısını görüntüler. Bellek verme boyutunu hesaba katın. Tek başına bir sorgu, sıralama gerçekleştirmek için 4 GB bellek tüketebilirdi, ancak bu sayaçların ikisine de yansıtılmıyordu.

  • Çalışma Alanı Belleği , aynı belleği tanımlayan başka bir terimdir. Bu terimi genellikle Perfmon sayacında Granted Workspace Memory (KB)görebilirsiniz. Bu terim, şu anda sıralama, karma, toplu kopyalama ve dizin oluşturma işlemleri için kullanılmakta olan ve KB cinsinden ifade edilen toplam bellek miktarını yansıtır. Maximum Workspace Memory (KB), başka bir sayaç, bu tür karma, sıralama, toplu kopyalama ve dizin oluşturma işlemleri yapması gerekebilecek istekler için kullanılabilir en fazla çalışma alanı belleği miktarını hesaplar. Çalışma Alanı Belleği terimi, bu iki sayacın dışında seyrek karşılaşılan bir durumdur.

Büyük QE bellek kullanımının performans etkisi

Çoğu durumda, bir iş parçacığı sql server içinde bir şeyler yapmak için bellek istediğinde ve bellek kullanılabilir olmadığında istek yetersiz bellek hatasıyla başarısız olur. Ancak, iş parçacığının başarısız olmadığı ancak belleğin kullanılabilir duruma gelmesini beklediği birkaç özel durum senaryosu vardır. Bu senaryolardan biri bellek izinleri, diğeri de sorgu derleme belleğidir. SQL Server, sorgu yürütme için ne kadar bellek verildiğini izlemek için semafor adlı bir iş parçacığı eşitleme nesnesi kullanır. SQL Server önceden tanımlanmış QE çalışma alanı tükenirse, sorguyu yetersiz bellek hatasıyla başarısız yapmak yerine sorgunun beklemesine neden olur. Çalışma alanı belleğinin genel SQL Server belleğinin önemli bir yüzdesini almasına izin verildiğinden, bu alanda bellek beklemenin ciddi performans etkileri vardır. Çok sayıda eşzamanlı sorgu yürütme belleği istedi ve birlikte QE bellek havuzunu tüketti veya birkaç eşzamanlı sorgu çok büyük izinler istedi. Her iki durumda da, sonuçta ortaya çıkan performans sorunları aşağıdaki belirtilere sahip olabilir:

  • Arabellek önbelleğindeki veriler ve dizin sayfaları büyük bellek verme isteklerine yer açmak için boşaltılmış olabilir. Bu, sorgu isteklerinden gelen sayfa okumalarının diskten (önemli ölçüde daha yavaş bir işlem) karşılanması gerektiğini gösterir.
  • Kaynak sıralama, karma veya dizin oluşturma işlemleriyle bağlı olduğundan diğer bellek ayırma istekleri yetersiz bellek hatalarıyla başarısız olabilir.
  • Yürütme belleği gerektiren istekler kaynağın kullanılabilir duruma gelmesini bekliyor ve tamamlanması uzun sürüyor. Başka bir deyişle, son kullanıcıya göre bu sorgular yavaştır.

Bu nedenle, Perfmon, dinamik yönetim görünümleri (DMV) veya DBCC MEMORYSTATUSiçinde sorgu yürütme belleğinde beklemeler gözlemlerseniz, özellikle de sorun sık yaşanıyorsa bu sorunu çözmek için harekete geçmelisiniz. Daha fazla bilgi için bkz . Bir geliştirici sıralama ve karma işlemleri hakkında neler yapabilir?

Sorgu yürütme belleği için beklemeleri tanımlama

QE rezervasyonları için beklemeleri belirlemenin birden çok yolu vardır. Daha büyük resmi sunucu düzeyinde görmek için size en uygun olanları seçin. Bu araçlardan bazıları sizin kullanımınıza sunulmayabilir (örneğin, Perfmon Azure SQL Veritabanı'da kullanılamaz). Sorunu belirledikten sonra, hangi sorguların ayarlanması veya yeniden yazılmasını gerektiğini görmek için tek tek sorgu düzeyinde detaya gitmeniz gerekir.

Bellek kullanımı istatistiklerini toplama

Kaynak semafor DMV sys.dm_exec_query_resource_semaphores

Bu DMV, sorgu ayırma belleğini kaynak havuzuna (iç, varsayılan ve kullanıcı tarafından oluşturulan) ve (normal ve resource_semaphore küçük sorgu isteklerine) göre ayırır. Yararlı bir sorgu şunlar olabilir:

SELECT 
  pool_id
  ,total_memory_kb
  ,available_memory_kb
  ,granted_memory_kb
  ,used_memory_kb
  ,grantee_count, waiter_count 
  ,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs

Aşağıdaki örnek çıktıda 22 istek tarafından yaklaşık 900 MB sorgu yürütme belleği kullanıldığı ve 3'ün daha beklediği gösterilmiştir. Bu, varsayılan havuzda (pool_id = 2) ve normal sorgu semaforunda (resource_semaphore_id = 0) gerçekleşir.

pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1       30880           30880               0                 0              0             0            0
1       5120            5120                0                 0              0             0            1
2       907104          0                   907104            898656         22            3            0
2       40960           40960               0                 0              0             0            1

(4 rows affected)

Performans İzleyicisi sayaçları

Benzer bilgiler Performans İzleyicisi sayaçları aracılığıyla sağlanır; burada şu anda verilen istekleri (Memory Grants Outstanding), bekleyen izin isteklerini ()Memory Grants Pending ve bellek vermeleriGranted Workspace Memory (KB) ( tarafından kullanılan bellek miktarını gözlemleyebilirsiniz). Aşağıdaki resimde, bekleyen hibeler 18, bekleyen hibeler 2 ve verilen çalışma alanı belleği 828.288 KB'tır. Sıfır Memory Grants Pending olmayan bir değere sahip Perfmon sayacı, belleğin tükendiğini gösterir.

Bekleyen ve karşılanan bellek izinlerinin ekran görüntüsü.

Daha fazla bilgi için bkz . SQL Server Bellek Yöneticisi nesnesi.

  • SQLServer, Bellek Yöneticisi: En Fazla Çalışma Alanı Belleği (KB)
  • SQLServer, Bellek Yöneticisi: Bekleyen Bellek İzinleri
  • SQLServer, Bellek Yöneticisi: Bellek Verme Beklemede
  • SQLServer, Bellek Yöneticisi: Verilen Çalışma Alanı Belleği (KB)

DBCC MEMORYSTATUS

Sorgu ayırma belleğiyle ilgili ayrıntıları görebileceğiniz başka bir yer de DBCC MEMORYSTATUS (Sorgu Belleği Nesneleri bölümü). Kullanıcı sorguları için çıkışa Query Memory Objects (default) bakabilirsiniz. Resource Governor'ı PoolAdmin adlı bir kaynak havuzuyla etkinleştirdiyseniz, hem hem Query Memory Objects (PoolAdmin)de Query Memory Objects (default) öğesine bakabilirsiniz.

Burada, 18 isteğe sorgu yürütme belleği verildiği ve 2 isteğin bellek beklediği bir sistemden alınan örnek çıktı verilmiştir. Kullanılabilir sayaç sıfırdır ve bu da kullanılabilir çalışma alanı belleğinin kalmadığını gösterir. Bu olgu, iki bekleme isteğini açıklar. , Wait Time bir isteğin bekleme kuyruğuna konmasının üzerinden geçen süreyi milisaniye cinsinden gösterir. Bu sayaçlar hakkında daha fazla bilgi için bkz . Bellek nesnelerini sorgulama.

Query Memory Objects (default)                                           Value
------------------------------------------------------------------------ -----------
Grants                                                                   18
Waiting                                                                  2
Available                                                                0
Current Max                                                              103536
Future Max                                                               97527
Physical Max                                                             139137
Next Request                                                             5752
Waiting For                                                              8628
Cost                                                                     16
Timeout                                                                  401
Wait Time                                                                2750

(11 rows affected)

Small Query Memory Objects (default)                                     Value
------------------------------------------------------------------------ -----------
Grants                                                                   0
Waiting                                                                  0
Available                                                                5133
Current Max                                                              5133
Future Max                                                               5133

DBCC MEMORYSTATUS ayrıca sorgu yürütme belleğini izleyen bellek katibi hakkındaki bilgileri görüntüler. Aşağıdaki çıkış, sorgu yürütme (QE) rezervasyonları için ayrılan sayfaların 800 MB'ı aştığını gösterir.

MEMORYCLERK_SQLQERESERVATIONS (node 0)                                   KB
------------------------------------------------------------------------ -----------
VM Reserved                                                              0
VM Committed                                                             0
Locked Pages Allocated                                                   0
SM Reserved                                                              0
SM Committed                                                             0
Pages Allocated                                                          824640

Bellek katipleri DMV sys.dm_os_memory_clerks

Bölüm tabanlı DBCC MEMORYSTATUS'dan farklı bir tablo sonuç kümesine daha fazla ihtiyacınız varsa, benzer bilgiler için sys.dm_os_memory_clerks kullanabilirsiniz. Hafıza görevlisini MEMORYCLERK_SQLQERESERVATIONS ara. Ancak Sorgu Bellek Nesneleri bu DMV'de kullanılamaz.

SELECT type, memory_node_id, pages_kb 
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'

Aşağıda örnek bir çıkış verilmişti:

type                                            memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS                   0              824640
MEMORYCLERK_SQLQERESERVATIONS                   64             0

Genişletilmiş Olayları (XEvents) kullanarak bellek atamalarını tanımlama

Bellek verme bilgileri sağlayan ve bu bilgileri izleme yoluyla yakalamanızı sağlayan birden çok genişletilmiş olay vardır:

  • sqlserver.additional_memory_grant: Sorgu yürütme sırasında daha fazla bellek izni almaya çalıştığında gerçekleşir. Bu ek bellek atamasının alınamaması sorgunun yavaşlamasına neden olabilir.
  • sqlserver.query_memory_grant_blocking: Bir sorgu bellek verilmesini beklerken diğer sorguları engellediğinde gerçekleşir.
  • sqlserver.query_memory_grant_info_sampling: Bellek verme bilgileri sağlayan rastgele örneklenen sorguların sonunda gerçekleşir (örneğin, telemetri için kullanılabilir).
  • sqlserver.query_memory_grant_resource_semaphores: Her kaynak yöneticisi kaynak havuzu için beş dakikalık aralıklarla gerçekleşir.
  • sqlserver.query_memory_grant_usage: Kullanıcılara bellek verme yanlışlıklarını bildirmek için 5 MB'ın üzerinde bellek iznine sahip sorgular için sorgu işlemenin sonunda gerçekleşir.
  • sqlserver.query_memory_grants: Bellek izni olan her sorgu için beş dakikalık aralıklarla gerçekleşir.
Bellek verme geri bildirimi genişletilmiş olayları

Sorgu işleme bellek verme geri bildirim özellikleri hakkında bilgi için bkz . Bellek verme geri bildirimi.

  • sqlserver.memory_grant_feedback_loop_disabled: Bellek verme geri bildirim döngüsü devre dışı bırakıldığında gerçekleşir.
  • sqlserver.memory_grant_updated_by_feedback: Bellek izni geri bildirimle güncelleştirildiğinde gerçekleşir.
Bellek vermeleriyle ilgili sorgu yürütme uyarıları
  • sqlserver.execution_warning: T-SQL deyimi veya saklı yordam bellek izni için bir saniyeden fazla beklediğinde veya ilk bellek alma girişimi başarısız olduğunda gerçekleşir. Bu olayı, performansı etkileyen çekişme sorunlarını gidermek için beklemeleri tanımlayan olaylarla birlikte kullanın.
  • sqlserver.hash_spill_details: Karma birleştirmenin derleme girişini işlemek için yeterli bellek yoksa karma işlemenin sonunda gerçekleşir. Oluşturulan planda karma taşmaya hangi işlemin neden olduğunu belirlemek için bu olayı veya query_post_execution_showplan olaylarından query_pre_execution_showplan herhangi biriyle birlikte kullanın.
  • sqlserver.hash_warning: Karma birleştirmenin derleme girişini işlemek için yeterli bellek olmadığında oluşur. Bu durum, derleme girişi bölümlendiğinde karma özyinelemeyle veya derleme girişinin bölümlenmesi en yüksek özyineleme düzeyini aştığında karma kurtarmayla sonuçlanır. Oluşturulan planda karma uyarıya query_pre_execution_showplan hangi işlemin neden olduğunu belirlemek için bu olayı veya query_post_execution_showplan olaylarından herhangi biriyle birlikte kullanın.
  • sqlserver.sort_warning: Yürütülen sorgudaki sıralama işlemi belleğe sığmadığında gerçekleşir. Bu olay dizin oluşturmanın neden olduğu sıralama işlemleri için değil, yalnızca sorgudaki sıralama işlemleri için oluşturulmaz. (Örneğin, bir Order By Select deyiminde.) Sıralama işlemi nedeniyle yavaş performans gösteren sorguları tanımlamak için bu olayı kullanın, özellikle warning_type = 2, sıralama için verilerin üzerinden birden çok geçişin gerekli olduğunu gösterir.
Bellek verme bilgileri içeren olaylar oluşturma planı

Genişletilmiş olaylar oluşturan aşağıdaki sorgu planı varsayılan olarak granted_memory_kb ve ideal_memory_kb alanları içerir:

  • sqlserver.query_plan_profile
  • sqlserver.query_post_execution_plan_profile
  • sqlserver.query_post_execution_showplan
  • sqlserver.query_pre_execution_showplan
Sütun deposu dizin oluşturma

XEvents aracılığıyla ele alınan alanlardan biri, sütun deposu oluşturma sırasında kullanılan yürütme belleğidir. Bu, kullanılabilir olayların listesidir:

  • sqlserver.column_store_index_build_low_memory: Depolama Altyapısı düşük bellek koşulu algılamıştı ve satır grubu boyutu küçültüldü. Burada ilgi çekici birkaç sütun vardır.
  • sqlserver.column_store_index_build_memory_trace: Dizin derlemesi sırasında bellek kullanımını izleme.
  • sqlserver.column_store_index_build_memory_usage_scale_down: Depolama Altyapısı ölçeği azaltıldı.
  • sqlserver.column_store_index_memory_estimation: COLUMNSTORE satır grubu derlemesi sırasında bellek tahmini sonucunu gösterir.

Belirli sorguları tanımlama

Tek tek istek düzeyine bakarken bulabileceğiniz iki tür sorgu vardır. Büyük miktarda sorgu yürütme belleği tüketen sorgular ve aynı belleği bekleyen sorgular. İkinci grup, bellek atamaları için mütevazı gereksinimleri olan isteklerden oluşabilir ve bu durumda dikkatinizi başka bir yere odaklamanız gerekebilir. Ancak çok büyük bellek boyutları talep ediyorlarsa suçlular da olabilirler. Böyle olduğunu fark ederseniz onlara odaklanın. Belirli bir sorgunun suçlu olduğunu bulmak yaygın olabilir, ancak birçok örneği oluşturulur. Bellek vermelerini alan bu örnekler, aynı sorgunun diğer örneklerinin izni beklemesine neden oluyor. Belirli koşullardan bağımsız olarak, sonuçta sorguları ve istenen yürütme belleğinin boyutunu tanımlamanız gerekir.

sys.dm_exec_query_memory_grants ile belirli sorguları tanımlama

İstekleri ve istenen ve verilen bellek boyutunu tek tek görüntülemek için dinamik yönetim görünümünü sorgulayabilirsiniz sys.dm_exec_query_memory_grants . Bu DMV, geçmiş bilgileri değil, şu anda yürütülen sorgular hakkındaki bilgileri gösterir.

Aşağıdaki deyim DMV'den veri alır ve sonuç olarak sorgu metnini ve sorgu planını getirir:

SELECT 
  session_id
  ,requested_memory_kb
  ,granted_memory_kb
  ,used_memory_kb
  ,queue_id
  ,wait_order
  ,wait_time_ms
  ,is_next_candidate
  ,pool_id
  ,text
  ,query_plan
FROM sys.dm_exec_query_memory_grants
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
  CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Aşağıda etkin QE bellek tüketimi sırasında sorgunun kısaltılmış örnek çıktısı verilmiştir. Çoğu sorguda gösterilen granted_memory_kb ve used_memory_kb NULL olmayan sayısal değerler olan bellekleri verilir. İsteklerini almayan sorgular yürütme belleğini ve granted_memory_kb = NULLbekliyor. Ayrıca, = 6 ile queue_id bekleme kuyruğuna yerleştirilirler. Yaklaşık wait_time_ms 37 saniye beklendiğini gösteriyor. Oturum 72, = 1 tarafından wait_order belirtilen şekilde bir izin almak için sırada yer alırken, 74. oturum = 2 ile wait_order ondan sonra gelir.

session_id requested_memory_kb  granted_memory_kb    used_memory_kb       queue_id wait_order  wait_time_ms         is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
83         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
84         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
74         41232                NULL                 NULL                 6        2           37438                0                 2      
78         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
81         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
71         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
75         41232                NULL                 NULL                 6        0           37438                1                 2      
82         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
76         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
79         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
85         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
70         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
55         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
59         41232                NULL                 NULL                 6        3           37438                0                 2      
62         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
54         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
77         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
52         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
72         41232                NULL                 NULL                 6        1           37438                0                 2      
69         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
73         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
66         41232                NULL                 NULL                 6        4           37438                0                 2      
68         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
63         41232                41232                40848                NULL     NULL        NULL                 NULL              2

sys.dm_exec_requests ile belirli sorguları tanımlama

SQL Server'da bir sorguyu bellek iznini RESOURCE_SEMAPHOREbeklediğini belirten bir bekleme türü vardır. Tek tek istekler sys.dm_exec_requests için bu bekleme türünü gözlemleyebilirsiniz. Bu ikinci DMV, yetersiz bellek vermenin kurbanı olan sorguları belirlemek için en iyi başlangıç noktasıdır. Sql Server düzeyinde toplanan veri noktaları olarak sys.dm_os_wait_stats beklemeyi de gözlemleyebilirsinizRESOURCE_SEMAPHORE. Bu bekleme türü, belleği kullanan diğer eşzamanlı sorgular nedeniyle bir sorgu bellek isteği verilemiyorsa gösterilir. Yüksek bekleme isteği sayısı ve uzun bekleme süreleri, yürütme belleği veya büyük bellek isteği boyutları kullanan aşırı sayıda eşzamanlı sorgu olduğunu gösterir.

Not

Bellek izinleri için bekleme süresi sınırlı. Aşırı bir bekleyişin ardından (örneğin, 20 dakikadan fazla), SQL Server sorguyu zaman aşımına uğradı ve 8645 "Bellek kaynaklarının sorguyu yürütmesi beklenirken zaman aşımı oluştu. Sorguyu yeniden çalıştırın." içinde bakarak timeout_sec sys.dm_exec_query_memory_grantssunucu düzeyinde ayarlanan zaman aşımı değerini görebilirsiniz. Zaman aşımı değeri SQL Server sürümleri arasında biraz farklılık gösterebilir.

kullanımıyla sys.dm_exec_requests, hangi sorgulara bellek verildiğini ve bu iznin boyutunu görebilirsiniz. Ayrıca, bekleme türünü arayarak RESOURCE_SEMAPHORE hangi sorguların şu anda bellek izni beklediğini belirleyebilirsiniz. Hem verilen hem de bekleyen istekleri gösteren bir sorgu aşağıda verilmiştir:

SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests 
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0 
       OR wait_type = 'RESOURCE_SEMAPHORE'

Örnek çıktıda iki isteğin bellek verildiği ve iki düzine kişinin de hibe beklediği gösterilmektedir. Sütun boyutu granted_query_memory 8 KB sayfa olarak bildirir. Örneğin, 34.709 değeri 34.709 * 8 KB = 277.672 KB bellek verildiği anlamına gelir.

session_id wait_type               wait_time   granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
66         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
67         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
68         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
69         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
70         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
71         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
72         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
73         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
74         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
75         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
76         ASYNC_NETWORK_IO        11          34709                select * from sys.messages order by message_id option (maxdop 1)
77         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
78         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
79         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
80         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
81         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
82         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
83         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
84         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
85         ASYNC_NETWORK_IO        14          34709                select * from sys.messages order by message_id option (maxdop 1)
86         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
87         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
88         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
89         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)

sys.dm_exec_query_stats ile belirli sorguları tanımlama

Bellek verme sorunu şu anda gerçekleşmiyorsa ancak sorunlu sorguları belirlemek istiyorsanız geçmiş sorgu verilerine aracılığıyla sys.dm_exec_query_statsbakabilirsiniz. Verilerin ömrü her sorgunun sorgu planına bağlıdır. Plan önbelleğinden bir plan kaldırıldığında, ilgili satırlar bu görünümden kaldırılır. Başka bir deyişle DMV, SQL Server yeniden başlatıldıktan sonra veya bellek baskısı plan önbelleği sürümüne neden olduktan sonra korunmamış istatistikleri bellekte tutar. Buna ek olarak, özellikle toplam sorgu istatistikleri için buradaki bilgileri değerli bulabilirsiniz. Birisi kısa süre önce sorgulardan büyük bellek izinleri gördüğünü bildirmiş olabilir, ancak sunucu iş yüküne baktığınızda sorunun gittiğini fark edebilirsiniz. Bu durumda, sys.dm_exec_query_stats diğer DVD'lerin sağlayabildiği içgörüleri sağlayabilir. Burada, en büyük miktarda yürütme belleği kullanan ilk 20 deyimi bulmanıza yardımcı olabilecek örnek bir sorgu verilmiştir. Bu çıkış, sorgu yapısı aynı olsa bile tek tek deyimleri görüntüler. Örneğin, SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 'den SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 ayrı bir satırdır (yalnızca filtre koşulu değeri değişir). Sorgu, en yüksek izin boyutu 5 MB'tan büyük olan ilk 20 deyimi alır.

SELECT TOP 20
  SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2) + 1) AS statement_text  
  ,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
  ,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
  ,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
  ,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
  ,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
  ,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count)  /1024.0) AS avg_grant_used_mb
  ,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count)  /1024.0) AS avg_ideal_grant_mb
  ,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
  ,execution_count
FROM sys.dm_exec_query_stats QS
  CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC

tarafından query_hashtoplanan sorgulara bakarak daha da güçlü içgörüler elde edilebilir. Bu örnekte, sorgu planı ilk önbelleğe alındıktan sonra sorgu deyiminin tüm örneklerinde ortalama, en yüksek ve en düşük izin boyutlarının nasıl bulunup bulunmadığı gösterilmektedir.

SELECT TOP 20
  MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2) + 1)) AS sample_statement_text  
  ,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
  ,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
  ,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
  ,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
  ,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
  ,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
  ,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count))  /1024.0) AS avg_ideal_grant_mb
  ,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
  ,SUM(execution_count) AS execution_count
  ,query_hash
FROM sys.dm_exec_query_stats QS
  CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)

sütun, Sample_Statement_Text sorgu karmasıyla eşleşen sorgu yapısının bir örneğini gösterir, ancak deyimindeki belirli değerler dikkate alınmadan okunmalıdır. Örneğin, bir deyimi içeriyorsa WHERE Id = 5, bunu daha genel biçiminde okuyabilirsiniz: WHERE Id = @any_value.

Aşağıda sorgunun yalnızca seçili sütunların gösterildiği kısaltılmış bir örnek çıktısı verilmiştir:

sample_statement_text                      max_grant_mb  avg_grant_mb  max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count 
-----------------------------------------  ------------  ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select     de.ObjectName,de.CounterName,d  282.45        282.45        6.50              6.50              282.45             282.45                        1               
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch  33.86         8.55          7.80              1.97              8.55               42.74                         5               
insert into #tmpCounterDateTime (CounterD  32.45         32.45         3.11              3.11              32.45              32.45                         1               
select db_id() dbid, db_name() dbname, *   20.80         1.30          5.75              0.36              1.30               20.80                         16              
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch  20.55         5.19          5.13              1.28              5.19               25.93                         5               
SELECT xmlplan FROM (SELECT ROW_NUMBER()   19.69         1.97          1.09              0.11              1.97               19.69                         10              
if ( select max(cast(countervalue as floa  16.39         8.20          0.77              0.38              8.20               16.39                         2               
SELECT udf.name AS [Name], udf.object_id   11.36         5.08          1.66              0.83              5.08               20.33                         4               
select --*                     Database_I  10.94         5.47          1.98              0.99              5.47               10.94                         2               
IF (select max(cast(dat.countervalue as f  8.00          1.00          0.00              0.00              0.53               8.00                          8               
insert into #tmpCounterDateTime (CounterD  5.72          2.86          1.98              0.99              2.86               5.72                          2               
INSERT INTO #tmp (CounterDateTime, Counte  5.39          1.08          1.64              0.33              1.08               6.47                          6

sys.query_store_runtime_stats ile Sorgu Deposu(QDS) kullanarak belirli sorguları tanımlama

Sorgu Deposu etkinleştirildiyse kalıcı geçmiş istatistiklerinden yararlanabilirsiniz. verilerinden sys.dm_exec_query_statsfarklı olarak, bu istatistikler bir veritabanında depolandığı için SQL Server yeniden başlatma veya bellek baskısından kurtulabilir. QDS'nin boyut sınırları ve bekletme ilkesi de vardır. Daha fazla bilgi için Sorgu Deposu'nu yönetmeye yönelik en iyi yöntemler bölümündeki En uygun Sorgu Deposu Yakalama Modunu Ayarlama ve Sorgu Deposu'nda en uygun verileri tutma bölümlerine bakın.

  1. Veritabanlarınızda şu sorguyu kullanarak Sorgu Deposu'un etkinleştirilip etkinleştirilmediğini belirleyin:

    SELECT name, is_query_store_on 
FROM sys.databases
WHERE is_query_store_on = 1

  2. Araştırmak istediğiniz belirli bir veritabanı bağlamında aşağıdaki tanılama sorgusunu çalıştırın:

    SELECT
   MAX(qtxt.query_sql_text) AS sample_sql_text
   ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb
   ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb
   ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb
   ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb
   ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb
   ,SUM(count_executions) AS count_query_executions
FROM sys.query_store_runtime_stats rts
JOIN sys.query_store_plan p
  ON p.plan_id = rts.plan_id
JOIN sys.query_store_query q
  ON p.query_id = q.query_id
LEFT OUTER JOIN sys.query_store_query_text qtxt
  ON q.query_text_id = qtxt.query_text_id
GROUP BY q.query_hash
HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB
ORDER BY SUM(avg_query_max_used_memory) DESC
OPTION (MAX_GRANT_PERCENT = 5)

    Buradaki ilkeler ile aynıdır sys.dm_exec_query_stats; deyimlerin toplam istatistiklerini görürsünüz. Ancak bir fark, QDS ile sql Server'ın tamamını değil, yalnızca bu veritabanı kapsamındaki sorgulara bakmanızdır. Bu nedenle, belirli bir bellek verme isteğinin yürütüldiği veritabanını bilmeniz gerekebilir. Aksi takdirde, büyük bellek atamalarını bulana kadar bu tanılama sorgusunu birden çok veritabanında çalıştırın.

    Aşağıda kısaltılmış bir örnek çıktı verilmişti:

    sample_sql_text                           avg_mem_grant_used_mb  min_mem_grant_used_mb  max_mem_grant_used_mb  stdev_mem_grant_used_mb  last_mem_grant_used_mb  count_query_executions
----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ----------------------
SELECT   qtxt.query_sql_text  ,CONVERT(D  550.16                 550.00                 550.00                 0.00                     550.00                  1
SELECT   qtxt.query_sql_text  ,rts.avg_q  61.00                  36.00                  65.00                  10.87                    51.00                   14
SELECT   qtxt.query_sql_text  ,q.*  ,rts  25.46                  25.00                  25.00                  0.00                     25.00                   2
insert into #tmpStats select 5 'Database  13.69                  13.00                  13.00                  0.03                     13.00                   16
SELECT   q.*  ,rts                        11.93                 11.00                  12.00                  0.23                     12.00                   2
SELECT *  ,rts.avg_query_max_used_memory  9.70                   9.00                   9.00                   0.00                     9.00                    1
SELECT   qtxt.query_sql_text  ,rts.avg_q  9.32                   9.00                   9.00                   0.00                     9.00                    1
select db_id() dbid, db_name() dbname, *  7.33                   7.00                   7.00                   0.00                     7.00                    9
SELECT q.*  ,rts.avg_query_max_used_memo  6.65                   6.00                   6.00                   0.00                     6.00                    1
(@_msparam_0 nvarchar(4000),@_msparam_1   5.17                   4.00                   5.00                   0.68                     4.00                    2

Özel tanılama sorgusu

Aşağıda, daha önce listelenen üç görünüm de dahil olmak üzere birden çok görünümdeki verileri birleştiren bir sorgu verilmiştir. tarafından sağlanan sys.dm_exec_query_resource_semaphoressunucu düzeyinde istatistiklere ek olarak ve sys.dm_exec_query_memory_grantsaracılığıyla sys.dm_exec_requests oturumların ve bunların izinlerinin daha kapsamlı bir görünümünü sağlar.

Not

Bu sorgu, kullanımı sys.dm_exec_query_resource_semaphores nedeniyle oturum başına iki satır döndürür (normal kaynak semaforu için bir satır ve küçük sorgu kaynak semaforu için başka bir satır).

SELECT    CONVERT (varchar(30), GETDATE(), 121) as runtime
         , r.session_id
         , r.wait_time
         , r.wait_type
         , mg.request_time 
         , mg.grant_time 
         , mg.requested_memory_kb
          / 1024 requested_memory_mb 
         , mg.granted_memory_kb
          / 1024 AS granted_memory_mb 
         , mg.required_memory_kb
          / 1024 AS required_memory_mb 
         , max_used_memory_kb
          / 1024 AS max_used_memory_mb
         , rs.pool_id as resource_pool_id
         , mg.query_cost 
         , mg.timeout_sec 
         , mg.resource_semaphore_id 
         , mg.wait_time_ms AS memory_grant_wait_time_ms 
         , CASE mg.is_next_candidate 
           WHEN 1 THEN 'Yes'
           WHEN 0 THEN 'No'
           ELSE 'Memory has been granted'
         END AS 'Next Candidate for Memory Grant'
         , r.command
         , ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
         , rs.target_memory_kb
          / 1024 AS server_target_grant_memory_mb 
         , rs.max_target_memory_kb
          / 1024 AS server_max_target_grant_memory_mb 
         , rs.total_memory_kb
          / 1024 AS server_total_resource_semaphore_memory_mb 
         , rs.available_memory_kb
          / 1024 AS server_available_memory_for_grants_mb 
         , rs.granted_memory_kb
          / 1024 AS server_total_granted_memory_mb 
         , rs.used_memory_kb
          / 1024 AS server_used_granted_memory_mb 
         , rs.grantee_count AS successful_grantee_count 
         , rs.waiter_count AS grant_waiters_count 
         , rs.timeout_error_count 
         , rs.forced_grant_count 
         , mg.dop 
         , r.blocking_session_id
         , r.cpu_time
         , r.total_elapsed_time
         , r.reads
         , r.writes
         , r.logical_reads
         , r.row_count
         , s.login_time
         , d.name
         , s.login_name
         , s.host_name
         , s.nt_domain
         , s.nt_user_name
         , s.status
         , c.client_net_address
         , s.program_name
         , s.client_interface_name
         , s.last_request_start_time
         , s.last_request_end_time
         , c.connect_time
         , c.last_read
         , c.last_write
         , qp.query_plan
FROM     sys.dm_exec_requests r
         INNER JOIN sys.dm_exec_connections c
           ON r.connection_id = c.connection_id
         INNER JOIN sys.dm_exec_sessions s
           ON c.session_id = s.session_id
         INNER JOIN sys.databases d
           ON r.database_id = d.database_id
         INNER JOIN sys.dm_exec_query_memory_grants mg
           ON s.session_id = mg.session_id
         INNER JOIN sys.dm_exec_query_resource_semaphores rs
           ON mg.resource_semaphore_id = rs.resource_semaphore_id
         CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
         CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )

Not

İpucu LOOP JOIN , sorgunun kendisi tarafından bellek verilmesini önlemek için bu tanılama sorgusunda kullanılır ve yan ORDER BY tümcesi kullanılmaz. Tanılama sorgusu bir vermenin kendisini beklerse, bellek vermelerini tanılama amacı yenilir. İpucu LOOP JOIN , tanılama sorgusunun yavaşmasına neden olabilir, ancak bu durumda tanılama sonuçlarını almak daha önemlidir.

Bu tanılama sorgusundan yalnızca seçili sütunları içeren kısaltılmış bir örnek çıktısı aşağıda verilmiştir.

session_id wait_time wait_type requested_memory_mb granted_memory_mb required_memory_mb max_used_memory_mb resource_pool_id
60 0 NULL 9 9 7 1 1
60 0 NULL 9 9 7 1 2
75 1310085 RESOURCE_SEMAPHORE 40 NULL 0 NULL 1
75 1310085 RESOURCE_SEMAPHORE 40 NULL 0 NULL 2
86 1310129 RESOURCE_SEMAPHORE 40 NULL 0 NULL 1
86 1310129 RESOURCE_SEMAPHORE 40 NULL 0 NULL 2

Örnek çıktı, = 60 tarafından session_id gönderilen bir sorgunun istenen 9 MB bellek iznini nasıl başarıyla aldığını açıkça gösterir, ancak sorgu yürütmeyi başarıyla başlatmak için yalnızca 7 MB gereklidir. Sonunda sorgu, sunucudan aldığı 9 MB'ın yalnızca 1 MB'ını kullandı. Çıkış ayrıca 75 ve 86 oturumlarının bellek atamalarını beklediğini ve bu nedenle öğesinin RESOURCE_SEMAPHORE wait_typeolduğunu gösterir. Bekleme süreleri 1.300 saniyenin (21 dakika) üzerindedir ve olur granted_memory_mb NULL.

Bu tanılama sorgusu bir örnektir, bu nedenle gereksinimlerinize uygun herhangi bir şekilde değiştirebilirsiniz. Bu sorgunun bir sürümü, Microsoft SQL Server desteğinin kullandığı tanılama araçlarında da kullanılır.

Tanılama araçları

Microsoft SQL Server teknik desteğinin günlükleri toplamak ve sorunları daha verimli bir şekilde gidermek için kullandığı tanılama araçları vardır. SQL LogScout ve Pssdiag Configuration Manager (SQLDiag ile birlikte), bellek verme sorunlarını tanılamanıza yardımcı olabilecek daha önce açıklanan DMV'lerin ve Performans İzleyicisi sayaçlarının çıkışlarını toplar.

SQL LogScout'u LightPerf, GeneralPerf veya DetailedPerf senaryolarıyla çalıştırırsanız, araç gerekli günlükleri toplar. Daha sonra YourServer_PerfStats.out dosyasını el ile inceleyebilir ve çıkışları -- dm_exec_query_memory_grants -- arayabilirsiniz-- dm_exec_query_resource_semaphores --. Veya el ile inceleme yerine SQL Nexus kullanarak SQL LogScout veya PSSDIAG'den gelen çıkışı SQL Server veritabanına aktarabilirsiniz. SQL Nexus, tbl_dm_exec_query_resource_semaphores tbl_dm_exec_query_memory_grantsbellek vermelerini tanılamak için gereken bilgileri içeren ve olmak üzere iki tablo oluşturur. SQL LogScout ve PSSDIAG ayrıca Perfmon günlüklerini biçiminde toplar. Performans İzleyicisi sayaçları bölümünde açıklanan performans sayaçlarını gözden geçirmek için kullanılabilen BLG dosyaları.

Bellek izinleri bir geliştirici veya DBA için neden önemlidir?

Microsoft destek deneyimine bağlı olarak, bellek verme sorunları genellikle bellekle ilgili en yaygın sorunlardan bazılarıdır. Uygulamalar genellikle çok büyük sıralama veya karma işlemleri nedeniyle SQL Server'da performans sorunlarına neden olabilecek görünüşte basit sorgular yürütür. Bu tür sorgular yalnızca çok fazla SQL Server belleği kullanmakla kalmaz, aynı zamanda diğer sorguların belleğin kullanılabilir olmasını beklemesine neden olur, bu nedenle performans sorunu olur.

Burada özetlenen araçları (DMV'ler, Perfmon sayaçları ve gerçek sorgu planları) kullanarak, hangi sorguların büyük hibe tüketicileri olduğunu belirleyebilirsiniz. Daha sonra çalışma alanı bellek kullanımını çözmek veya azaltmak için bu sorguları ayarlayabilir veya yeniden yazabilirsiniz.

Bir geliştirici sıralama ve karma işlemleri hakkında ne yapabilir?

Büyük miktarda sorgu ayırma belleği kullanan belirli sorguları belirledikten sonra, bu sorguları yeniden tasarlayarak bellek vermelerini azaltmaya yönelik adımlar atabilirsiniz.

Sorgularda sıralama ve karma işlemlerinin nedenleri

İlk adım, sorgudaki hangi işlemlerin bellek vermelere neden olabileceğinin farkında olmaktır.

Sorguda SORT işleci kullanılmasına neden olan nedenler:

  • ORDER BY (T-SQL), son sonuç olarak akışa alınmadan önce satırların sıralanmasını gerektirir.

  • GROUP BY (T-SQL), gruplandırılan sütunları sıralayan bir temel dizin yoksa gruplandırmadan önce sorgu planında bir sıralama işlecine neden olabilir.

  • DISTINCT (T-SQL) ile benzer şekilde GROUP BYdavranır. Ayrı satırları tanımlamak için ara sonuçlar sıralanır ve sonra yinelenenler kaldırılır. Veriler sıralı dizin Sort arama veya tarama nedeniyle henüz sıralanmamışsa, iyileştirici bu işleç öncesinde bir işleç kullanır.

  • Birleştirme Birleştirme işleci, sorgu iyileştiricisi tarafından seçildiğinde her iki birleştirilmiş girişin de sıralanmış olmasını gerektirir. Tablolardan birinde birleştirme sütununda kümelenmiş dizin yoksa SQL Server bir sıralama tetikleyebilir.

Sorguda HASH sorgu planı işlecinin kullanılmasına neden olan nedenler:

Bu liste kapsamlı değildir ancak Karma işlemleri için en sık karşılaşılan nedenleri içerir. Karma eşleştirme işlemlerini tanımlamak için sorgu planını analiz edin.

Bu yaygın nedenleri bilmek, SQL Server'a gelen büyük bellek verme isteklerini mümkün olduğunca ortadan kaldırmanıza yardımcı olabilir.

Sıralama ve karma işlemlerini veya verme boyutunu azaltmanın yolları

  • İstatistikleri güncel tutun. Birçok düzeydeki sorguların performansını geliştiren bu temel adım, sorgu planlarını seçerken sorgu iyileştiricisinin en doğru bilgilere sahip olmasını sağlar. SQL Server, istatistiği temel alarak bellek izni için hangi boyutun isteneceğini belirler. Güncel olmayan istatistikler, hibe isteğinin fazla sınanmasına veya sınanmasına neden olabilir ve bu nedenle gereksiz yere yüksek bir izin isteğine veya sonuçların diske taşmasına neden olabilir. Veritabanlarınızda otomatik güncelleştirme istatistiklerinin etkinleştirildiğinden emin olun ve/veya UPDATE STATISTICS veya sp_updatestats ile statikleri güncel tutun.
  • Tablolardan gelen satır sayısını azaltın. Daha kısıtlayıcı bir WHERE filtresi veya JOIN kullanır ve satır sayısını azaltırsanız, sorgu planında sonraki bir sıralama daha küçük bir sonuç kümesini sıralar veya toplar. Daha küçük bir ara sonuç kümesi daha az çalışma kümesi belleği gerektirir. Bu, geliştiricilerin yalnızca çalışma kümesi belleğini kaydetmek için değil CPU ve G/Ç'yi azaltmak için de uygulayabileceği genel bir kuraldır (bu adım her zaman mümkün değildir). İyi yazılmış ve kaynak verimli sorgular zaten mevcutsa, bu kılavuza uyuldu.
  • Birleştirme birleştirmelerine yardımcı olmak için birleştirme sütunlarında dizinler oluşturun. Sorgu planındaki ara işlemler, temel alınan tablodaki dizinlerden etkilenir. Örneğin, bir tabloda birleştirme sütununda dizin yoksa ve birleştirme birleştirme en uygun maliyetli birleştirme işleci olarak bulunursa, birleştirme gerçekleştirilmeden önce bu tablodaki tüm satırların sıralanması gerekir. Bunun yerine, sütunda bir dizin varsa, sıralama işlemi ortadan kaldırılabilir.
  • Karma işlemlerden kaçınmaya yardımcı olmak için dizinler oluşturun. Genellikle, temel sorgu ayarlama işlemleri, sorgularınızın okumaları azaltmalarına ve mümkün olduğunda büyük sıralamaları veya karma işlemleri en aza indirmelerine veya ortadan kaldırmalarına yardımcı olacak uygun dizinlere sahip olup olmadığını denetlemekle başlar. Karma birleşimler genellikle büyük, sıralanmamış ve dizinlenmemiş girişleri işlemek için seçilir. Dizinler oluşturmak bu iyileştirici stratejisini değiştirebilir ve veri alımını hızlandırabilir. Dizin oluşturma konusunda yardım için bkz. Veritabanı Altyapısı Ayarlama Danışmanı ve Eksik dizin önerileriyle kümelenmemiş dizinleri ayarlama.
  • kullanan toplama sorguları için uygun yerlerde COLUMNSTORE dizinlerini kullanın GROUP BY. Çok büyük satır kümeleriyle ilgilenen ve genellikle "gruplandırma ölçütü" toplamaları gerçekleştiren analiz sorguları, iş yapmak için büyük bellek öbeklerine ihtiyaç duyabilir. Sıralı sonuçlar sağlayan bir dizin yoksa, sorgu planında otomatik olarak bir sıralama yapılır. Çok büyük bir sonuç, pahalı bir bellek iznine neden olabilir.
  • İhtiyacınız yoksa öğesini ORDER BY kaldırın. Sonuçları kendi yöntemiyle sıralayan veya kullanıcının görüntülenen verilerin sırasını değiştirmesine izin veren bir uygulamaya sonuçların akışla aktarıldığı durumlarda, SQL Server tarafında bir sıralama yapmanız gerekmez. Yalnızca verilerin sunucu tarafından üretilmesi sırasına göre uygulamaya akışla aktarın ve son kullanıcının kendi başına sıralamasına izin verin. Power BI veya Reporting Services gibi raporlama uygulamaları, son kullanıcıların verilerini sıralamasına olanak sağlayan bu tür uygulamalara örnektir.
  • T-SQL sorgusunda birleştirmeler olduğunda LOOP JOIN ipucunun kullanımını dikkatli bir şekilde göz önünde bulundurun. Bu teknik, bellek vermeleri kullanan karma veya birleştirme birleşimlerinden kaçınabilir. Ancak bu seçenek yalnızca son çare olarak önerilir çünkü birleştirmeyi zorlamak sorguyu önemli ölçüde yavaşlatabilir. Bunun bir seçenek olduğundan emin olmak için iş yükünüzü stres testi yapın. Bazı durumlarda iç içe döngü birleşimi bir seçenek bile olmayabilir. Bu durumda SQL Server, "Sorgu işlemcisi bu sorguda tanımlanan ipuçları nedeniyle sorgu planı oluşturamadı" hatasını MSSQLSERVER_8622 başarısız olabilir.

Bellek verme sorgusu ipucu

SQL Server 2012 SP3'ten bu yana, sorgu başına bellek atamanızın boyutunu denetlemenize olanak tanıyan bir sorgu ipucu vardı. Bu ipucunu nasıl kullanabileceğinize dair bir örnek aşağıda verilmişti:

SELECT Column1,  Column2
FROM Table1 
ORDER BY Column1 
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )

Burada, özellikle sorgunuzun birçok örneğinin eşzamanlı olarak yürütülmesini beklediğiniz durumlarda muhafazakar değerler kullanmanızı öneririz. İş yükünüzü üretim ortamınızla eşleşecek şekilde stres testi yapıp hangi değerlerin kullanılacağını saptadığınızdan emin olun.

Daha fazla bilgi için bkz . MAX_GRANT_PERCENT ve MIN_GRANT_PERCENT.

Resource Governor

QE Belleği, Resource Governor'ın MIN_MEMORY_PERCENT ve MAX_MEMORY_PERCENT ayarları kullanıldığında sınırlayan bellektir. Büyük bellek verme işlemlerine neden olan sorguları tanımladıktan sonra oturumlar veya uygulamalar tarafından kullanılan belleği sınırlayabilirsiniz. İş yükü grubunun sorgunun default SQL Server örneğinde verilebilen belleğin %25'ini almasına izin verdiğini belirtmek gerekir. Daha fazla bilgi için bkz . Resource Governor Kaynak Havuzları ve CREATE WORKLOAD GROUP.

Uyarlamalı sorgu işleme ve bellek verme geri bildirimi

SQL Server 2017 bellek verme geri bildirim özelliğini kullanıma sunulmuştur. Sorgu yürütme altyapısının önceki geçmişe göre sorguya verilen izni ayarlamasına olanak tanır. Amaç, mümkün olduğunda verme boyutunu küçültmek veya daha fazla bellek gerektiğinde artırmaktır. Bu özellik üç dalga halinde yayımlandı:

  1. SQL Server 2017'de toplu iş modu bellek verme geri bildirimi
  2. SQL Server 2019'da satır modu bellek verme geri bildirimi
  3. SQL Server 2022'de Sorgu Deposu ve yüzdebirlik iznini kullanarak diskte bellek verme geri bildirimi

Daha fazla bilgi için bkz . Bellek verme geri bildirimi. Bellek verme özelliği, yürütme zamanında sorgular için bellek vermelerinin boyutunu azaltabilir ve bu nedenle büyük izin isteklerinden kaynaklanan sorunları azaltabilir. Özellikle satır modu uyarlamalı işlemenin kullanılabildiği SQL Server 2019 ve sonraki sürümlerde bu özellik mevcut olduğunda, sorgu yürütmeden gelen bellek sorunlarını bile fark edemeyebilirsiniz. Ancak, bu özelliğe sahipseniz (varsayılan olarak açık) ve yine de büyük QE bellek tüketimi görüyorsanız sorguları yeniden yazmak için daha önce açıklanan adımları uygulayın.

SQL Server veya işletim sistemi belleğini artırma

Sorgularınız için gereksiz bellek atamalarını azaltma adımlarını tamamladıktan sonra, hala ilgili düşük bellek sorunlarıyla karşılaşıyorsanız, iş yükü büyük olasılıkla daha fazla bellek gerektirir. Bu nedenle, sistemde bunu yapmak için yeterli fiziksel bellek varsa ayarını kullanarak max server memory SQL Server için belleği artırmayı göz önünde bulundurun. İşletim sistemi ve diğer gereksinimler için belleğin yaklaşık %25'ini bırakma önerilerini izleyin. Daha fazla bilgi için bkz . Sunucu belleği yapılandırma seçenekleri. Sistemde yeterli bellek yoksa fiziksel RAM eklemeyi göz önünde bulundurun veya sanal makineyse VM'niz için ayrılmış RAM'i artırın.

Bellek verme iç işlevleri

Sorgu yürütme belleğindeki bazı iç bileşenler hakkında daha fazla bilgi edinmek için BKZ. SQL server bellek verme blog gönderisini anlama.

Ağır bellek verme kullanımıyla performans senaryosu oluşturma

Son olarak, aşağıdaki örnek, büyük miktarda sorgu yürütme belleği tüketiminin benzetimini yapmayı ve bekleyen RESOURCE_SEMAPHOREsorguları tanıtmayı gösterir. Bu makalede açıklanan tanılama araçlarını ve tekniklerini kullanmayı öğrenmek için bunu yapabilirsiniz.

Uyarı

Bunu üretim sisteminde kullanmayın. Bu simülasyon, kavramı anlamanıza ve daha iyi öğrenmenize yardımcı olmak için sağlanır.

  1. Test sunucusunda RML Yardımcı Programları ve SQL Server'ı yükleyin.

  2. SQL Server'ınızın en yüksek sunucu belleği ayarını 1.500 MB'a düşürmek için SQL Server Management Studio gibi bir istemci uygulaması kullanın:

    EXEC sp_configure 'max server memory', 1500
RECONFIGURE

  3. Bir Komut İstemi açın ve dizini RML yardımcı programları klasörüne değiştirin:

    cd C:\Program Files\Microsoft Corporation\RMLUtils

  4. Test SQL Server'ınız için birden çok eşzamanlı istek ortaya ostress.exe kullanın. Bu örnekte 30 eşzamanlı oturum kullanılır, ancak bu değeri değiştirebilirsiniz:

    ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30

  5. Bellek verme sorunlarını belirlemek için daha önce açıklanan tanılama araçlarını kullanın.

Büyük bellek verme işlemleriyle başa çıkma yollarının özeti

  • Sorguları yeniden yazma.
  • İstatistikleri güncelleştirin ve düzenli olarak güncel tutun.
  • Tanımlanan sorgu veya sorgular için uygun dizinler oluşturun. Dizinler işlenen çok sayıda satırı azaltabilir, bu nedenle algoritmaları değiştirebilir JOIN ve vermelerin boyutunu azaltabilir veya bunları tamamen ortadan kaldırır.
  • OPTION (min_grant_percent = XX, max_grant_percent = XX) ipucunu kullanın.
  • Resource Governor kullanın.
  • SQL Server 2017 ve 2019 uyarlamalı sorgu işlemeyi kullanarak bellek verme geri bildirim mekanizmasının çalışma zamanında bellek verme boyutunu dinamik olarak ayarlamasını sağlar. Bu özellik ilk etapta bellek verme sorunlarını engelleyebilir.
  • SQL Server veya işletim sistemi belleğini artırın.