SQL Server'de 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 atamaları, 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ğini 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 verilirse 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 işlemi diğer işler için kullanmak üzere SQL Server geri serbest bırakılır. 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 bir sorgu örneği
Yürütme belleğini kullanan bir sorgu örneği ve verme işlemini gösteren sorgu planı aşağıda verilmiştir:
SELECT *
FROM sys.messages
ORDER BY message_id
Bu sorgu 300.000'den fazla satırlık 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 SELECT soldaki işlecini seçtiğinizde, sorgunun bellek verme bilgilerini görüntüleyebilirsiniz (Özellikler'i göstermek için F4 tuşuna basın):
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çıklanması gerekir. Sorgu belirli bir miktarda yürütme belleği (DesiredMemory) isteyebilir ve genellikle bu miktarı (RequestedMemory) ister. Ç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. başlangıçta istenen boyuttan daha fazla bellek ayırmak yerine SQL Server fazladan satırları diske gönderir ve geçici bir çalışma alanı olarak kullanır. Daha fazla bilgi için bkz. Bellek VermeYle İlgili Önemli Noktalar'da Workfiles ve Worktable'lar.
Terminoloji
Şimdi bu bellek tüketicisi ile ilgili karşılaşabileceğiniz farklı terimleri gözden geçirelim. Yine, tüm bunlar aynı bellek ayırmalarıyla ilgili kavramları açıklar.
Sorgu Yürütme Belleği (QE Belleği): Bu terim, sorgunun yürütülmesi sırasında sıralama veya karma belleğin kullanıldığı gerçeğini vurgulamak için kullanılır. Genellikle QE bellek, bir 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 bir 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ı takip eden 'MEMORYCLERK_SQLQERESERVATIONS' adlı bir bellek katibi (bellek muhasebecisi) vardır ( DBCC MEMORYSTATUS veya sys.dm_os_memory_clerks).
Bellek Atamaları: 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 OutstandingveMemory Grants Pending, karşılanan veya bekleyen bellek atamalarının sayısını görüntüler. Bellek verme boyutunu hesaba ayırmaz. Tek başına bir sorgu sıralama gerçekleştirmek için 4 GB bellek tüketmiş olabilir, ancak bu sayaçların ikisine de yansıtılmıyor.Çalışma Alanı Belleği , aynı belleği açıklayan başka bir terimdir. Bu terimi genellikle Perfmon sayacında
Granted Workspace Memory (KB)görebilirsiniz. Bu terim, şu anda kb cinsinden ifade edilen sıralama, karma, toplu kopyalama ve dizin oluşturma işlemleri için kullanılan 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 tüm 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ığı bir işi halletmek için SQL Server içinde 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 bellek kullanılabilir duruma gelene kadar beklediği birkaç özel durum senaryosu vardır. Bu senaryolardan biri bellek verme, diğeri 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ğin önemli bir yüzdesini almasına izin verildiğinden, bu alanda bellek beklemenin performans açısından ciddi 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 istekleri için yer açmak için büyük olasılıkla boşaltılmıştır. 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 olmasını bekliyor ve tamamlanması uzun sürüyor. Başka bir deyişle, son kullanıcı için 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 oluşuyorsa 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ında kullanılamaz). Sorunu belirledikten sonra, hangi sorguların ayarlanması veya yeniden yazılmasının gerektiğini görmek için tek tek sorgu düzeyinde detaya gitmeniz gerekir.
Sunucu düzeyinde aşağıdaki yöntemleri kullanın:
- Kaynak semaforu DMV sys.dm_exec_query_resource_semaphores Daha fazla bilgi için bkz. sys.dm_exec_query_resource_semaphores.
- Performans İzleyicisi sayaçları Daha fazla bilgi için bkz. Bellek Yöneticisi nesnesini SQL Server.
- DBCC MEMORYSTATUS Daha fazla bilgi için bkz. DBCC MEMORYSTATUS.
- Bellek katipleri DMV sys.dm_os_memory_clerks Daha fazla bilgi için bkz. sys.dm_os_memory_clerks.
- Genişletilmiş Olaylar (XEvents) kullanarak bellek atamalarını tanımlama Daha fazla bilgi için bkz. Genişletilmiş Olaylar (XEvents).
Tek tek sorgu düzeyinde aşağıdaki yöntemleri kullanın:
- sys.dm_exec_query_memory_grants ile belirli sorguları tanımlama: Şu anda sorgular yürütülüyor. Daha fazla bilgi için bkz. sys.dm_exec_query_memory_grants.
- sys.dm_exec_requests ile belirli sorguları tanımlama: Şu anda sorgular yürütülüyor. Daha fazla bilgi için bkz. sys.dm_exec_requests.
- sys.dm_exec_query_stats ile belirli sorguları tanımlama: Sorgularla ilgili geçmiş istatistikleri. Daha fazla bilgi için bkz. sys.dm_exec_query_stats.
- sys.query_store_runtime_stats ile Sorgu Deposu(QDS) kullanarak belirli sorguları tanımlayın: QDS ile sorgularla ilgili geçmiş istatistikleri. Daha fazla bilgi için bkz. sys.query_store_runtime_stats.
Bellek kullanım istatistiklerini toplama
Kaynak semaforu 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 resource_semaphore (normal ve küçük sorgu istekleri) 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ı, 22 istek tarafından yaklaşık 900 MB sorgu yürütme belleğinin kullanıldığını ve 3'ün daha beklediğini göstermektedir. 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 verme isteklerini ()Memory Grants Pending ve bellek vermeleriGranted Workspace Memory (KB) () tarafından kullanılan bellek miktarını gözlemleyebilirsiniz. Aşağıdaki resimde, bekleyen izinler 18, bekleyen izinler 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.
Daha fazla bilgi için bkz. Bellek Yöneticisi nesnesini SQL Server.
- SQLServer, Bellek Yöneticisi: En Fazla Çalışma Alanı Belleği (KB)
- SQLServer, Bellek Yöneticisi: Bellek Verme Bekleyen
- 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ının çıktısını Query Memory Objects (default) görebilirsiniz. Resource Governor PoolAdmin adlı bir kaynak havuzuyla etkinleştirdiyseniz, hem hem de Query Memory Objects (default)Query Memory Objects (PoolAdmin)öğ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ği kalmadığını gösterir. Bu olgu, bekleyen iki isteği açıklar. , Wait Time bir isteğin bekleme kuyruğuna konulduktan sonra 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 katibini 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 bir izleme aracılığıyla 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 izni 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 verme geri bildirim tarafından güncelleştirildiğinde gerçekleşir.
Bellek atamalarıyla 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. Performansı etkileyen çekişme sorunlarını gidermek için beklemeleri tanımlayan olaylarla birlikte bu olayı 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 plandaki hangi işlemin karma taşmaya neden olduğunu belirlemek için bu olayı veya
query_post_execution_showplanolaylarındanquery_pre_execution_showplanherhangi biriyle birlikte kullanın. - sqlserver.hash_warning: Karma birleştirmenin derleme girişini işlemek için yetersiz bellek olduğunda gerçekleşir. Bu, 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 plandaki hangi işlemin karma uyarıya
query_pre_execution_showplanneden olduğunu belirlemek için bu olayı veyaquery_post_execution_showplanolayları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 BySelectdeyiminde.) Sıralama işlemi nedeniyle, özelliklewarning_type= 2 olduğunda, sıralama için verilerin üzerinden birden çok geçişin gerekli olduğunu belirten yavaş performans gösteren sorguları tanımlamak için bu olayı kullanın.
Bellek verme bilgilerini 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 kapsanan 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 durumu algılandı 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 odaklayabilirsiniz. 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 atamalarını 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ı belirleme
İ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)
Burada etkin QE bellek tüketimi sırasında sorgunun kısaltılmış bir örnek çıktısı verilmiştır. Çoğu sorguda, null olmayan sayısal değerler ve tarafından granted_memory_kbused_memory_kb gösterildiği gibi bellekleri verilir. İsteklerini almayan sorgular yürütme belleğini ve granted_memory_kb = NULLöğesini bekliyor. Ayrıca, = 6 ile queue_id bir bekleme kuyruğuna yerleştirilirler. Yaklaşık wait_time_ms 37 saniye beklediklerini gösteriyor. 72. oturum, = 1 tarafından wait_order belirtilen şekilde bir izin almak için sırada yer alırken, 74. oturum = 2 ile wait_order bu iznin ardından 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 sorguların bellek izni 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. Ayrıca SQL Server düzeyinde toplanan veri noktaları olarak sys.dm_os_wait_stats beklemeyi gözlemleyebilirsinizRESOURCE_SEMAPHORE. Bu bekleme türü, belleği kullanan diğer eşzamanlı sorgular nedeniyle bir sorgu bellek isteği verilemiyorsa gösterilir. Bekleyen isteklerin sayısı ve uzun bekleme süreleri, yürütme belleği veya büyük bellek isteği boyutları kullanan çok fazla sayıda eşzamanlı sorgu olduğunu gösterir.
Not
Bellek atamaları için bekleme süresi sınırlıdır. Aşırı bir beklemeden sonra (örneğin, 20 dakikadan fazla), SQL Server sorgunun zaman aşımına uğradı ve 8645 hatasını oluşturur: "Bellek kaynaklarının sorguyu yürütmesi beklenirken zaman aşımı oluştu. Sorguyu yeniden çalıştırın." içinde öğesine bakarak timeout_secsys.dm_exec_query_memory_grantssunucu düzeyinde ayarlanan zaman aşımı değerini görebilirsiniz. Zaman aşımı değeri SQL Server sürümler 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 şu anda hangi sorguların RESOURCE_SEMAPHORE 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ğe bellek verildiği ve iki düzine kişinin de hibe beklediği gösterilmektedir. Sütun boyutu granted_query_memory 8 KB'lık sayfalarda 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ı bir plan önbelleği yayınına neden olduktan sonra korunmamış olan bellek istatistiklerini tutar. Ancak buradaki bilgileri, özellikle de toplam sorgu istatistikleri için değerli bulabilirsiniz. Yakın zamanda birisi sorgulardan büyük bellek atamaları 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. Aşağıda, en büyük miktarda yürütme belleği kullanan ilk 20 deyimi bulmanıza yardımcı olabilecek bir örnek 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 bir sorgu deyiminin tüm örneklerinde ortalama, maksimum ve minimum 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)
Sample_Statement_Text sütunu, 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. verilerinin sys.dm_exec_query_statsaksine, bu istatistikler veritabanında depolandığından 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 ilgili verileri tutmabölümlerine bakın.
Bu sorguyu kullanarak veritabanlarınızda Sorgu Deposu'un etkinleştirilip etkinleştirilmediğini belirleyin:
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Araş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 tüm SQL Server değil, yalnızca bu veritabanı kapsamındaki sorgulara bakıyor olmanı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üzeyi istatistiklerine ek olarak ve sys.dm_exec_query_memory_grantsaracılığıyla sys.dm_exec_requests oturumların ve bunların hibelerinin 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 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 hiçbir ORDER BY yan tümce kullanılmaz. Tanılama sorgusu bir vermenin kendisini beklerse, bellek vermelerini tanılama amacı yenilir. İpucu LOOP JOIN , tanılama sorgusunun yavaşlaması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 istediği 9 MB bellek iznini başarıyla aldığını ancak sorgu yürütmeyi başarıyla başlatmak için yalnızca 7 MB gerektiğini açıkça gösterir. 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_SEMAPHOREwait_typeolduğunu gösterir. Bekleme süreleri 1.300 saniyenin (21 dakika) üzerindedir ve bekleme granted_memory_mb süreleridir 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. Ardından YourServer_PerfStats.out dosyasını el ile inceleyebilir ve çıkışları -- dm_exec_query_memory_grants -- arayabilirsiniz-- dm_exec_query_resource_semaphores --. Ya da 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_semaphorestbl_dm_exec_query_memory_grantsbellek atamalarını tanılamak için gereken bilgileri içeren ve olmak üzere iki tablo oluşturur. SQL LogScout ve PSSDIAG, Perfmon günlüklerini biçiminde de toplar. Performans İzleyicisisayaç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 performans sorunlarına neden olabilecek basit görünen sorgular yürütür. Bu tür sorgular yalnızca çok fazla SQL Server bellek kullanmakla kalmaz, aynı zamanda diğer sorguların belleğin kullanılabilir olmasını beklemesine neden olur, bu nedenle performans sorunu oluşur.
Burada açıklanan 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. Ardından ç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 neler yapabilir?
Büyük miktarda sorgu ayırma belleği tüketen belirli sorguları tanımladıktan sonra, bu sorguları yeniden tasarlayarak bellek atamalarını azaltmaya yönelik adımlar atabilirsiniz.
Sorgularda sıralama ve karma işlemlerine neden olan işlemler
İlk adım, sorgudaki hangi işlemlerin bellek atamalarına yol açabileceğinin farkında olmaktır.
Sorguda SORT işlecinin 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 sıralama işlecini gösterebilir.
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ı dizinSortarama veya tarama nedeniyle henüz sıralanmamışsa, iyileştirici bu işleç öncesinde bir işleç kullanır.Birleştirmeyi Birleştir işleci, sorgu iyileştiricisi tarafından seçildiğinde her iki birleştirilmiş girişin de sıralanmış olmasını gerektirir. SQL Server, tablolardan birinde birleştirme sütununda kümelenmiş dizin yoksa bir sıralama tetikleyebilir.
Sorguda KARMA 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.
JOIN (T-SQL): Tabloları birleştirirken, SQL Server üç fiziksel işleç arasında bir seçim vardır:
Nested Loop,Merge JoinveHash Join. SQL Server Karma Birleştirme'yi seçersen, ara sonuçların depolanması ve işlenmesi için QE belleği gerekir. Genellikle, iyi dizinlerin olmaması bu en pahalı kaynak birleştirme işlecine yol açabilir.Hash JointanımlamakHash Matchiçin sorgu planını incelemek için bkz. Mantıksal ve Fiziksel işleçler başvurusu.DISTINCT (T-SQL): Satır
Hash Aggregatekümesindeki yinelemeleri ortadan kaldırmak için bir işleç kullanılabilir. Sorgu planında (Hash MatchAggregate) aramak için bkz. Mantıksal ve Fiziksel işleçler başvurusu.UNION (T-SQL): Bu, öğesine
DISTINCTbenzer.Hash AggregateBu işlecin yinelemelerini kaldırmak için bir kullanılabilir.SUM/AVG/MAX/MIN (T-SQL): Herhangi bir
Hash Aggregatetoplama işlemi olarak gerçekleştirilebilir. Sorgu planında (Hash MatchAggregate) aramak için bkz. Mantıksal ve Fiziksel işleçler başvurusu.
Bu yaygın nedenleri bilmek, SQL Server 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, verme isteğinin fazla tahmin edilmesine veya çümsenmesine neden olabilir ve bu nedenle sırasıyla gereksiz derecede yüksek bir verme 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 yönerge karşılanmıştır.
- 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 dizine alınmamış 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 öbekleri gerektirebilir. Sıralı sonuçlar sağlayan bir dizin kullanılamıyorsa, sorgu planında otomatik olarak bir sıralama yapılır. Çok büyük bir sonuç pahalı bir bellek verilmesine yol açabilir. - İhtiyacınız yoksa öğesini
ORDER BYkaldırın. Sonuçları kendi kendine 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 sıralama yapmanız gerekmez. Yalnızca verileri, sunucunun ürettiği sırayla uygulamaya 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ıralamalarına olanak sağlayan bu tür uygulamalara örnek olarak verilebilir. - T-SQL sorgusunda birleştirmeler olduğunda LOOP JOIN ipucunun kullanımını dikkatli bir şekilde göz önünde bulundurun. Bu teknik, bellek atamaları 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ı" hata 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 mevcut. 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 )
Özellikle sorgunuzun birçok örneğinin eşzamanlı olarak yürütülmesini beklediğiniz durumlarda, burada muhafazakar değerler kullanmanızı öneririz. İş yükünüzü üretim ortamınızla eşleşecek şekilde test edip hangi değerleri kullanacağınızı saptadığınızdan emin olun.
Daha fazla bilgi için bkz. MAX_GRANT_PERCENT ve MIN_GRANT_PERCENT.
Resource Governor
QE Bellek, MIN_MEMORY_PERCENT ve MAX_MEMORY_PERCENT ayarları kullanıldığında Resource Governor aslı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 bir sorgunun default SQL Server bir örnekte 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ınlanmıştır:
- SQL Server 2017'de toplu iş modu bellek verme geri bildirimi
- SQL Server 2019'da satır modu bellek verme geri bildirimi
- SQL Server 2022'de Sorgu Deposu ve yüzdebirlik izni kullanarak diskte bellek verme geri bildirimi kalıcılığı
Daha fazla bilgi için bkz . Bellek verme geri bildirimi. Bellek verme özelliği, yürütme sırasında sorgular için bellek verme boyutunu azaltabilir ve bu nedenle büyük verme isteklerinden kaynaklanan sorunları azaltabilir. Özellikle satır modu uyarlamalı işlemenin kullanılabildiği SQL Server 2019 ve sonraki sürümlerde bu özellik kullanıma sunulduğundan, 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ı kullanarak max server memory SQL Server için belleği artırmayı göz önünde bulundurun. İşletim sistemi ve diğer ihtiyaçlar 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 veya bir sanal makineyse VM'niz için ayrılmış RAM'i artırmayı göz önünde bulundurun.
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 örnekte sorgu yürütme belleğinin büyük tüketiminin benzetimini ve bekleyen RESOURCE_SEMAPHOREsorguların nasıl tanıtılması gösterilmektedir. 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.
Bir test sunucusuna RML Yardımcı Programları ve SQL Server yükleyin.
SQL Server 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 RECONFIGUREBir Komut İstemi açın ve dizini RML yardımcı programları klasörüne değiştirin:
cd C:\Program Files\Microsoft Corporation\RMLUtilsostress.exe kullanarak test SQL Server birden çok eşzamanlı istek oluşturun. 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)" -n30Bellek verme sorunlarını belirlemek için daha önce açıklanan tanılama araçlarını kullanın.
Büyük bellek atamalarıyla 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
JOINve vermelerin boyutunu azaltabilir veya bunları tamamen ortadan kaldırabilir. 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, bellek verme sorunlarını ilk etapta engelleyebilir.
- SQL Server veya işletim sistemi belleğini artırın.
Geri Bildirim
Çok yakında: 2024 boyunca, içerik için geri bildirim mekanizması olarak GitHub Sorunları’nı kullanımdan kaldıracak ve yeni bir geri bildirim sistemiyle değiştireceğiz. Daha fazla bilgi için bkz. https://aka.ms/ContentUserFeedback.
Gönderin ve geri bildirimi görüntüleyin