SQL Server'da yetersiz bellek sorunlarını giderme

Belirtiler

SQL Server, karmaşık ve zengin özellik kümesine karşılık gelen karmaşık bir bellek mimarisi kullanır. Çeşitli bellek gereksinimleri nedeniyle birçok bellek tüketimi ve bellek baskısı kaynağı olabilir ve sonuç olarak yetersiz bellek koşullarına neden olabilir.

SQL Server'da belleğin düşük olduğunu gösteren yaygın hatalar vardır. Hata örnekleri şunlardır:

• 701: Sorguyu çalıştırmak için yeterli bellek ayrılamaması.
• 802: Arabellek havuzundaki sayfaları (veri veya dizin sayfaları) ayırmak için bellek alınamaması.
• 1204: Kilitler için bellek ayrılamaması.
• 6322: XML ayrıştırıcısı için bellek ayrılamaması.
• 6513:Bellek baskısından dolayı CLR başlatılamadı.
• 6533: AppDomain bellek yetersiz olduğundan kaldırıldı.
• 8318: Yetersiz bellek nedeniyle SQL performans sayaçları yüklenemedi.
• 8356 veya 8359: ETW veya SQL izlemesi yetersiz bellek nedeniyle çalıştırılamıyor.
• 8556: Yetersiz bellek nedeniyle MSDTC yüklenemedi.
• 8645: Bellek verme (sıralama ve karma oluşturma) için bellek olmadığından sorgu yürütülememesi daha fazla bilgi için bkz . SQL Server hatası 8645 sorunlarını giderme.
• 8902: DBCC yürütmesi sırasında bellek ayrılamaması.
• 9695 veya 9696: Hizmet Aracısı işlemleri için bellek ayrılamaması.
• 17131 veya 17132: Yetersiz bellek nedeniyle sunucu başlatma hatası.
• 17890: İşletim sistemi tarafından disk belleğine alınan SQL belleği nedeniyle bellek ayrılamaması.
• 18053: Biçimlendirme sırasında hata oluştuğundan hata terse modunda yazdırıldı. İzleme, ETW, bildirimler vb. atlanır.
• 22986 veya 22987: Yetersiz bellek nedeniyle veri yakalama hatalarını değiştirin.
• 25601: Xevent altyapısının belleği yetersiz.
• 26053: YETERSIZ bellek nedeniyle SQL ağ arabirimleri başlatılamıyor.
• 30085, 30086, 30094: SQL tam metin işlemleri yetersiz bellek nedeniyle başarısız oldu.

Neden

Birçok faktör yetersiz belleğe neden olabilir. Bu faktörler arasında işletim sistemi ayarları, fiziksel bellek kullanılabilirliği, SQL Server içinde bellek kullanan bileşenler ve geçerli iş yükündeki bellek sınırları bulunur. Çoğu durumda, bu hatanın nedeni bellek yetersiz hatasıyla başarısız olan sorgu değildir. Genel olarak, nedenler üç kategoride gruplandırılabilir:

Neden 1: Dış veya işletim sistemi bellek baskısı

Dış basınç, sql server için yetersiz belleğe yol açan işlemin dışındaki bir bileşenden gelen yüksek bellek kullanımını ifade eder. Sistemdeki diğer uygulamaların bellek tüketip tüketmediğini ve düşük bellek kullanılabilirliğine katkıda bulunup bulunmadiğini öğrenmeniz gerekir. SQL Server, bellek kullanımını geri alarak işletim sistemi bellek baskısına yanıt vermek için tasarlanmış çok az uygulamadan biridir. Bu, bir uygulama veya sürücü bellek isterse işletim sisteminin tüm uygulamalara bellek boşaltması için bir sinyal gönderdiği ve SQL Server'ın kendi bellek kullanımını azaltarak yanıt vereceği anlamına gelir. Diğer birkaç uygulama bu bildirimi dinleyecek şekilde tasarlanmamış olduğundan yanıt verir. Bu nedenle, SQL Server bellek kullanımını geri almaya başlarsa, bellek havuzu azalır ve belleğe ihtiyaç duyan bileşenler bunu alamayabilir. Sonuç olarak, 701 veya bellekle ilgili diğer hataları almaya başlarsınız. SQL'in belleği dinamik olarak ayırma ve boşaltma hakkında daha fazla bilgi için bkz . SQL Server Bellek Mimarisi. Sorunun daha ayrıntılı tanılamaları ve çözümleri için bu makaledeki Dış bellek baskısı bölümüne bakın.

İşletim sistemi bellek baskısına neden olabilecek üç geniş sorun kategorisi vardır:

• Uygulamayla ilgili sorunlar: Bir veya birden çok uygulama birlikte kullanılabilir fiziksel belleği tüketer. İşletim sistemi, biraz bellek boşaltmaya çalışarak kaynaklar için yeni uygulama isteklerine yanıt verir. Yaygın yaklaşım, belleği tüketen uygulamaları bulmak ve RAM tükenmesine neden olmadan aralarındaki belleği dengelemek için gerekli adımları atmaktır.
• Cihaz sürücüsü sorunları: Sürücü yanlışlıkla bir bellek ayırma işlevini çağırırsa cihaz sürücüleri tüm işlemlerin çalışma kümesi disk belleğine neden olabilir.
• İşletim sistemi ürün sorunları.

Bu ve sorun giderme adımlarının ayrıntılı açıklaması için bkz. MSSQLSERVER_17890.

Neden 2: SQL Server'dan gelmeyen iç bellek baskısı

İç bellek baskısı, SQL Server işlemi içindeki faktörlerin neden olduğu düşük bellek kullanılabilirliğini ifade eder. SQL Server işleminin içinde çalışabilen bazı bileşenler SQL Server altyapısına "dıştır". Örnek olarak bağlı sunucular, SQLCLR yordamları veya işlevleri, genişletilmiş yordamlar (XP' ler) ve OLE Otomasyonu () gibi OLE DB sağlayıcıları (sp_OA*DLL'ler) verilebilir. Diğerleri, izleme amacıyla bir işlemin içine DLL'ler ekleyen virüsten koruma veya diğer güvenlik programlarıdır. Bu bileşenlerin herhangi birinde bir sorun veya zayıf tasarım büyük bellek tüketimine neden olabilir. Örneğin, bir dış kaynaktan SQL Server belleğine 20 milyon veri satırını önbelleğe alarak bağlantılı bir sunucu düşünün. SQL Server söz konusu olduğunda, hiçbir bellek katibi yüksek bellek kullanımını bildirmez, ancak SQL Server işlemi içinde tüketilen bellek yüksek olur. Örneğin bağlı sunucu DLL'sinden gelen bu bellek büyümesi, SQL Server'ın bellek kullanımını kesmeye başlamasına neden olur (yukarıya bakın) ve SQL Server içindeki bileşenler için yetersiz bellek koşulları oluşturarak bellek yetersiz hatalarına neden olur. Sorunla ilgili daha ayrıntılı tanılamalar ve çözümler için bkz . SQL Server'dan gelmeyen iç bellek baskısı.

Not

SQL Server işlem alanında kullanılan birkaç Microsoft DLL'leri (örneğin, MSOLEDBSQL, SQL Yerel İstemcisi), raporlama ve ayırma için SQL Server bellek altyapısıyla arabirim yapabilir. Bunların listesini almak ve bazı ayırmaları için bu bellek tüketimini izlemek için komutunu çalıştırabilirsiniz select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' .

Neden 3: SQL Server bileşenlerinden gelen iç bellek baskısı

SQL Server altyapısının içindeki bileşenlerden gelen iç bellek baskısı da yetersiz bellek hatalarına yol açabilir. SQL Server'da bellek ayıran bellek katipleri aracılığıyla izlenen yüzlerce bileşen vardır. Bu sorunu çözmek için en büyük bellek ayırmalarından hangi bellek katiplerinin sorumlu olduğunu belirlemeniz gerekir. Örneğin, bellek katibinin OBJECTSTORE_LOCK_MANAGER büyük bir bellek ayırması gösterdiğini fark ederseniz, Kilit Yöneticisi'nin neden bu kadar fazla bellek tüketildiğini anlamanız gerekir. Birçok kilit elde eden sorgular olduğunu fark edebilirsiniz. Dizinleri kullanarak, kilitleri uzun süre tutan işlemleri kısaltarak veya kilit yükseltmenin devre dışı bırakılıp bırakılmadığını denetleyerek bu sorguları iyileştirebilirsiniz. Her bellek katibi veya bileşeni, belleğe erişmek ve bellek kullanmak için benzersiz bir yönteme sahiptir. Daha fazla bilgi için bkz . bellek memuru türleri ve açıklamaları. Sorunla ilgili daha ayrıntılı tanılamalar ve çözümler için bkz . SQL Server altyapısı tarafından iç bellek kullanımı.

Bellek baskı türlerinin görsel gösterimi

Aşağıdaki grafikte, SQL Server'da bellek yetersizlik koşullarına yol açabilecek baskı türleri gösterilmektedir:

Sorun giderme verilerini toplamak için tanılama araçları

Sorun giderme verilerini toplamak için aşağıdaki tanılama araçlarını kullanabilirsiniz:

Performans İzleyicisi

Performans İzleyicisi ile aşağıdaki sayaçları yapılandırın ve toplayın:

• Bellek:Kullanılabilir MBytes
• İşlem:Çalışma Kümesi
• İşlem:Özel Baytlar
• SQL Server:Memory Manager: (tüm sayaçlar)
• SQL Server:Buffer Manager: (tüm sayaçlar)

DMV'ler veya DBCC MEMORYSTATUS

SQL Server'da genel bellek kullanımını gözlemlemek için sys.dm_os_memory_clerks veya DBCC MEMORYSTATUS kullanabilirsiniz.

SSMS'de Bellek Tüketimi Standart Raporu

SQL Server Management Studio'da bellek kullanımını görüntüleme:

1. SQL Server Management Studio'yu başlatın ve bir sunucuya bağlanın.
2. Nesne Gezgini'da SQL Server örneği adına sağ tıklayın.
3. Bağlam menüsünde Raporlar Standart Raporlar>>Bellek Tüketimi'ni seçin.

PSSDiag veya SQL LogScout

Bu veri noktalarını yakalamanın alternatif ve otomatik bir yolu, PSSDiag veya SQL LogScout gibi araçları kullanmaktır.

• PSSDiag kullanıyorsanız Perfmon toplayıcısını ve Özel Tanılama\SQL Bellek Hatası toplayıcısını yakalayacak şekilde yapılandırın.

• SQL LogScout kullanıyorsanız Bellek senaryoyu yakalamak için yapılandırın.

Aşağıdaki bölümlerde her senaryo (dış veya iç bellek baskısı) için daha ayrıntılı adımlar açıklanmaktadır.

Sorun giderme metodolojisi

Zaman zaman veya kısa bir süre için yetersiz bellek hatası görünürse, kendisini çözen kısa süreli bir bellek sorunu olabilir. Bu gibi durumlarda işlem yapmanız gerekmeyebilir. Ancak, hata birden çok bağlantıda birden çok kez oluşursa ve saniye veya daha uzun süre devam ederse, bellek hatalarını daha fazla gidermek için aşağıdaki bölümlerde yer alan tanılamaları ve çözümleri izleyin.

Dış bellek baskısı

SQL Server işleminin dışındaki sistemde yetersiz bellek koşullarını tanılamak için aşağıdaki yöntemleri kullanın:

• Performans İzleyicisi sayaçlarını toplayın. Şu sayaçlara bakarak SQL Server dışındaki uygulamaların veya hizmetlerin bu sunucuda bellek tüketip tüketmediğini araştırın:

  • Bellek:Kullanılabilir MBytes
  • İşlem:Çalışma Kümesi
  • İşlem:Özel Baytlar

  PowerShell kullanarak Perfmon günlük toplama örneği aşağıda verilmiştir:

  clear
  $serverName = $env:COMPUTERNAME
  $Counters = @(
     ("\\$serverName" +"\Memory\Available MBytes"),
     ("\\$serverName" +"\Process(*)\Working Set"),
     ("\\$serverName" +"\Process(*)\Private Bytes")
  )
  
  Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object  {
  $_.CounterSamples | ForEach-Object   {
     [pscustomobject]@{
        TimeStamp = $_.TimeStamp
        Path = $_.Path
        Value = ([Math]::Round($_.CookedValue, 3)) }
  }
  }
  

• Sistem Olay günlüğünü gözden geçirin ve bellekle ilgili hataları (örneğin, düşük sanal bellek) arayın.

• Uygulamayla ilgili bellek sorunları için Uygulama Olay günlüğünü gözden geçirin.

  "memory" anahtar sözcüğü için Sistem ve Uygulama Olay günlüklerini sorgulamaya yönelik bir PowerShell betiği örneği aşağıda verilmiştir. Aramanız için "resource" gibi diğer dizeleri kullanmaktan çekinmeyin:

  Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
  Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
  

• Bellek kullanımını azaltmak için daha az kritik uygulamalara veya hizmetlere yönelik kod veya yapılandırma sorunlarını giderin.

• SQL Server dışındaki uygulamalar kaynakları kullanıyorsa, bu uygulamaları durdurmayı veya yeniden zamanlamasını deneyin ya da bunları ayrı bir sunucuda çalıştırmayı deneyin. Bu adımlar dış bellek baskısını ortadan kaldırır.

İç bellek baskısı, SQL Server'dan gelmiyor

SQL Server içindeki modüllerin (DLL) neden olduğu iç bellek baskısını tanılamak için aşağıdaki yöntemleri kullanın:

• SQL Server Bellekte Kilitli Sayfalar (AWE API'sini) kullanmıyorsa, belleğinin çoğu Performans İzleyicisi'daki Process:Private Bytes sayacına (SQLServrörnek) yansıtılır. SQL Server altyapısının içinden gelen genel bellek kullanımı SQL Server:Memory Manager: Total Server Memory (KB) sayacına yansıtılır. Process:Private Bytes ve SQL Server:Memory Manager: Total Server Memory (KB) değeri arasında önemli bir fark bulursanız, bu fark büyük olasılıkla bir DLL'den (bağlı sunucu, XP, SQLCLR vb.) geliyor olabilir. Örneğin, Özel bayt 300 GB ve Toplam Sunucu Belleği 250 GB ise, işlemdeki toplam belleğin yaklaşık 50 GB'ı SQL Server altyapısı dışından geliyordur.

• SQL Server Bellekte Kilitli Sayfalar (AWE API' sini) kullanıyorsa, Performans İzleyicisi tek tek işlemler için bellek kullanımını izleyen AWE sayaçları sunmadığından sorunu belirlemek daha zordur. SQL Server altyapısındaki genel bellek kullanımı SQL Server:Memory Manager: Total Server Memory (KB) sayacına yansıtılır. Tipik İşlem:Özel Bayt değerleri genel olarak 300 MB ile 1-2 GB arasında değişebilir. Bu tipik kullanımın ötesinde önemli bir Process:Private Bytes kullanımı bulursanız, fark büyük olasılıkla bir DLL'den (bağlı sunucu, XP, SQLCLR vb.) geliyordur. Örneğin, Özel bayt sayacı 4-5 GB ise ve SQL Server Bellekte Kilitli Sayfalar (AWE) kullanıyorsa, Özel baytların büyük bir bölümü SQL Server altyapısının dışından geliyor olabilir. Bu bir tahmin tekniğidir.

• SQL Server alanının içine yüklenen DLL'leri tanımlamak için Görev Listesi yardımcı programını kullanın:

  tasklist /M /FI "IMAGENAME eq sqlservr.exe"
  

• Yüklenen modülleri (DLL' ler) incelemek ve beklenmeyen bir şey olup olmadığını görmek için aşağıdaki sorguyu da kullanabilirsiniz.

  SELECT * FROM sys.dm_os_loaded_modules
  

• Bağlı Sunucu modülünün önemli miktarda bellek tüketimine neden olduğundan şüpheleniyorsanız, İşleme izin ver seçeneğini devre dışı bırakarak bu modülü işlem dışı olacak şekilde yapılandırabilirsiniz. Daha fazla bilgi için bkz . Bağlı Sunucular Oluşturma. Tüm bağlı sunucu OLE DB sağlayıcılarının işlemi tükenmeyebilir. Daha fazla bilgi için ürün üreticisine başvurun.

• OLE otomasyon nesnelerinin (sp_OA*) kullanıldığı nadir durumlarda, 4 bağlam değeri (yalnızca Yerel (.exe) OLE sunucusu belirterek nesneyi SQL Server dışındaki bir işlemde çalışacak şekilde yapılandırabilirsiniz. Daha fazla bilgi için bkz . sp_OACreate.

SQL Server altyapısı tarafından iç bellek kullanımı

SQL Server altyapısı içindeki bileşenlerden gelen iç bellek baskısını tanılamak için aşağıdaki yöntemleri kullanın:

• SQL Server için Performans İzleyicisi sayaçları toplamaya başlayın: SQL Server:Buffer Manager ve SQL Server: Memory Manager.

• Altyapıda en yüksek bellek tüketiminin nerede gerçekleştiğini görmek için SQL Server bellek katipleri DMV'sini birden çok kez sorgula:

  SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
  FROM sys.dm_os_memory_clerks
  ORDER BY pages_kb DESC
  

• Alternatif olarak, bu hata iletilerini gördüğünüzde daha ayrıntılı DBCC MEMORYSTATUS çıktıyı ve bu çıkışın nasıl değiştiğini gözlemleyebilirsiniz.

  DBCC MEMORYSTATUS
  

• Bellek katipleri arasında net bir suçlu belirlerseniz, bu bileşen için bellek tüketiminin ayrıntılarını ele alma konusunda odaklanın. İşte birkaç örnek:

  • Bellek katibi MEMORYCLERK_SQLQERESERVATIONS bellek kullanıyorsa, büyük bellek atamaları kullanan sorguları belirleyin ve bunları dizinler aracılığıyla iyileştirin, yeniden yazın (örneğin, kaldırın ORDER by) veya bellek verme sorgusu ipuçlarını uygulayın (bkz . min_grant_percent ve max_grant_percent ipuçları ). Bellek verme belleği kullanımını denetlemek için bir kaynak yöneticisi havuzu da oluşturabilirsiniz. Bellek izinleri hakkında ayrıntılı bilgi için bkz . SQL Server'da bellek atamalarının neden olduğu yavaş performans veya düşük bellek sorunlarını giderme.
  • Çok sayıda geçici sorgu planı önbelleğe alınırsa, CACHESTORE_SQLCP bellek katibi büyük miktarda bellek kullanır. Sorgu planları yeniden kullanılamayan parametrelenmemiş sorguları belirleyin ve saklı yordamlara dönüştürerek, kullanarak sp_executesqlveya parametreleştirme kullanarak FORCED bunları parametreleştirin. İzleme bayrağı 174'i etkinleştirdiyseniz, bunun sorunu çözip çözmediğini görmek için bu bayrağı devre dışı bırakabilirsiniz.
  • Nesne planı önbellek deposu CACHESTORE_OBJCP çok fazla bellek kullanıyorsa, hangi saklı yordamların, işlevlerin veya tetikleyicilerin büyük miktarda bellek kullandığını belirleyin ve büyük olasılıkla uygulamayı yeniden tasarlayın. Genellikle, her birinde yüzlerce yordam bulunan büyük miktarda veritabanı veya şema nedeniyle bu durum oluşabilir.
  • OBJECTSTORE_LOCK_MANAGER Bellek katibi büyük bellek ayırmaları gösteriyorsa, birçok kilit uygulayan sorguları tanımlayın ve dizinleri kullanarak bunları iyileştirin. Kilitlerin belirli yalıtım düzeylerinde uzun süre serbest bırakılmamasına neden olan işlemleri kısaltın veya kilit yükseltmenin devre dışı bırakılıp bırakılmadığını denetleyin.
  • Çok büyük TokenAndPermUserStore ()select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore' gözlemlerseniz, önbelleğin boyutunu sınırlamak için 4618 izleme bayrağını kullanabilirsiniz.
  • Bellek içi OLTP'nin bellek katibinden MEMORYCLERK_XTP geldiğini görürseniz Bellek İçi OLTP ve Bellek için iyileştirilmiş tempdb meta verileri (HkTempDB) yetersiz bellek hataları için Bellek Kullanımını İzleme ve Sorunlarını Giderme konusuna başvurabilirsiniz.

Belleği kullanılabilir hale getirebilecek hızlı rahatlama

Aşağıdaki eylemler biraz bellek boşaltabilir ve SQL Server'ın kullanımına sunabilir:

Bellek yapılandırma ayarlarını değiştirme

Aşağıdaki SQL Server bellek yapılandırma parametrelerini denetleyin ve mümkünse maksimum sunucu belleğini artırmayı göz önünde bulundurun:

• en fazla sunucu belleği
• en az sunucu belleği

Not

Olağan dışı ayarlar fark ederseniz, bunları gerektiği gibi düzeltin ve artan bellek gereksinimlerini hesaba katın. Varsayılan ayarlar Sunucu bellek yapılandırma seçeneklerinde listelenir.

Özellikle Bellekteki Kilitli Sayfalar ile en fazla sunucu belleğini yapılandırmadıysanız işletim sistemi için bellek miktarına izin vermek için bunu belirli bir değere ayarlamayı göz önünde bulundurun. Bellek sunucusu yapılandırmasında Kilitli Sayfalar seçeneğine bakın.

İş yükünü sistemden değiştirme veya taşıma

Sorgu iş yükünü araştırın: eşzamanlı oturum sayısı, şu anda sorgular yürütülüyor ve geçici olarak durdurulabilecek veya başka bir SQL Server'a taşınabilecek daha az kritik uygulama olup olmadığına bakın.

Salt okunur iş yükleri için, bunları Always On ortamında salt okunur ikincil çoğaltmaya taşımayı göz önünde bulundurun. Daha fazla bilgi için bkz. AlwaysOn kullanılabilirlik grubunun ikincil çoğaltmasına salt okunur iş yükünü boşaltma ve Always On kullanılabilirlik grubunun ikincil çoğaltmasına salt okunur erişimi yapılandırma.

Sanal makineler için uygun bellek yapılandırmasını sağlama

SQL Server'ı bir sanal makinede (VM) çalıştırıyorsanız, VM belleğinin aşırı yüklenmediğinden emin olun. VM'ler için belleği yapılandırma hakkında fikirler için bkz . Sanallaştırma - Aşırı bellek kullanma ve vm içinde belleği algılama ve ESX/ESXi sanal makine performansı sorunlarını giderme (bellek aşırı kullanımı).

SQL Server'ın içindeki bellek serbest bırak

Birkaç SQL Server bellek önbelleğini boşaltmak için aşağıdaki DBCC komutlarından birini veya daha fazlasını çalıştırabilirsiniz:

• DBCC FREESYSTEMCACHE

• DBCC FREESESSIONCACHE

• DBCC FREEPROCCACHE

SQL Server hizmetini yeniden başlatma

Bazı durumlarda, kritik bellek tükenmesiyle ilgilenmeniz gerekiyorsa ve SQL Server sorguları işleyemiyorsa hizmeti yeniden başlatmayı düşünebilirsiniz.

Belirli senaryolar için Resource Governor kullanmayı göz önünde bulundurun

Resource Governor kullanıyorsanız, kaynak havuzu ve iş yükü grubu ayarlarını denetledikten sonra belleği çok önemli ölçüde sınırlayıp sınırlamadıklarını görmenizi öneririz.

Fiziksel veya sanal sunucuya daha fazla RAM ekleme

Sorun devam ederse, daha fazla araştırma yapmanız ve büyük olasılıkla sunucu kaynaklarını (RAM) artırmanız gerekir.