G/Ç sorunlarının neden olduğu yavaş SQL Server performansını giderme

Şunlar için geçerlidir: SQL Server

Bu makalede, SQL Server performansının yavaşlamasına neden olan G/Ç sorunları ve sorunların nasıl giderleneceği konusunda rehberlik sağlanmaktadır.

Yavaş G/Ç performansını tanımlama

Performans izleyici sayaçları yavaş G/Ç performansını belirlemek için kullanılır. Bu sayaçlar, G/Ç alt sistemi hizmetlerinin her G/Ç isteğinin ortalama olarak saat süresi açısından ne kadar hızlı olduğunu ölçer. Windows'ta G/Ç gecikme süresini ölçen belirli Performans izleyicisi sayaçları , Avg. Disk sec/Writeve Avg. Disk sec/Transfer 'tir Avg Disk sec/ Read(hem okumaların hem de yazmaların kümülatifi).

SQL Server'da işler aynı şekilde çalışır. Genellikle SQL Server'ın saat süresi (milisaniye) cinsinden ölçülen G/Ç performans sorunlarını bildirip bildirmediğine bakarsınız. SQL Server, , ReadFile(), WriteFileGather()ve ReadFileScatter()gibi WriteFile()Win32 işlevlerini çağırarak işletim sistemine G/Ç istekleri gönderir. G/Ç isteği gönderildiğinde, SQL Server isteği zamanlar ve bekleme türlerini kullanarak isteğin süresini bildirir. SQL Server, ürünün farklı yerlerinde G/Ç beklemelerini belirtmek için bekleme türlerini kullanır. G/Ç ile ilgili bekleme süreleri şunlardır:

• / PAGEIOLATCH_SHPAGEIOLATCH_EX
• YAZMA GÜNLÜĞÜ
• IO_COMPLETION
• ASYNC_IO_COMPLETION
• BACKUPIO

Bu beklemeler tutarlı olarak 10-15 milisaniyeyi aşarsa G/Ç performans sorunu olarak kabul edilir.

Metodoloji

Bu makalenin sonundaki akış grafiğinde , Microsoft CSS'nin SQL Server ile ilgili yavaş G/Ç sorunlarına yaklaşmak için kullandığı metodoloji açıklanmaktadır. Bu kapsamlı veya özel bir yaklaşım değildir, ancak sorunu yalıtma ve çözme konusunda yararlı olduğu kanıtlanmıştır.

Sorunu çözmek için aşağıdaki iki seçenek arasından birini belirleyebilirsiniz:

1. Seçenek: Azure Data Studio aracılığıyla adımları doğrudan bir not defterinde yürütme

2. Seçenek: Adımları el ile izleyin

Metodoloji şu adımlarda özetlenmiştir:

1. Adım: SQL Server yavaş G/Ç bildiriliyor mu?

SQL Server G/Ç gecikme süresini çeşitli yollarla bildirebilir:

• G/Ç bekleme türleri
• DMV sys.dm_io_virtual_file_stats
• Hata günlüğü veya Uygulama Olay günlüğü

G/Ç bekleme türleri

SQL Server bekleme türleri tarafından bildirilen G/Ç gecikmesi olup olmadığını belirleyin. PAGEIOLATCH_*Diğer birkaç daha az yaygın bekleme türünün , WRITELOGve ASYNC_IO_COMPLETION değerleri genellikle G/Ç isteği başına 10-15 milisaniyenin altında kalmalıdır. Bu değerler tutarlı olarak daha büyükse, G/Ç performans sorunu vardır ve daha fazla araştırma gerektirir. Aşağıdaki sorgu, sisteminizde bu tanılama bilgilerini toplamanıza yardımcı olabilir:

#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance" 

for ([int]$i = 0; $i -lt 100; $i++)
{
   
  sqlcmd -E -S $sqlserver_instance -Q "SELECT r.session_id, r.wait_type, r.wait_time as wait_time_ms`
                                       FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s `
                                        ON r.session_id = s.session_id `
                                       WHERE wait_type in ('PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'WRITELOG', `
                                        'IO_COMPLETION', 'ASYNC_IO_COMPLETION', 'BACKUPIO')`
                                       AND is_user_process = 1"

  Start-Sleep -s 2
}

sys.dm_io_virtual_file_stats dosya istatistikleri

SQL Server'da bildirilen veritabanı dosya düzeyinde gecikme süresini görüntülemek için aşağıdaki sorguyu çalıştırın:

#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance" 

sqlcmd -E -S $sqlserver_instance -Q "SELECT   LEFT(mf.physical_name,100),   `
         ReadLatency = CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms / num_of_reads) END, `
         WriteLatency = CASE WHEN num_of_writes = 0 THEN 0 ELSE (io_stall_write_ms / num_of_writes) END, `
         AvgLatency =  CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 `
                        ELSE (io_stall / (num_of_reads + num_of_writes)) END,`
         LatencyAssessment = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 'No data' ELSE `
               CASE WHEN (io_stall / (num_of_reads + num_of_writes)) < 2 THEN 'Excellent' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 2 AND 5 THEN 'Very good' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 6 AND 15 THEN 'Good' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 16 AND 100 THEN 'Poor' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 100 AND 500 THEN  'Bad' `
                    ELSE 'Deplorable' END  END, `
         [Avg KBs/Transfer] =  CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 `
                    ELSE ((([num_of_bytes_read] + [num_of_bytes_written]) / (num_of_reads + num_of_writes)) / 1024) END, `
         LEFT (mf.physical_name, 2) AS Volume, `
         LEFT(DB_NAME (vfs.database_id),32) AS [Database Name]`
       FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs  `
       JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id `
         AND vfs.file_id = mf.file_id `
       ORDER BY AvgLatency DESC"

AvgLatency Gecikme süresi ayrıntılarını anlamak için ve LatencyAssessment sütunlarına bakın.

Hata Günlüğü veya Uygulama Olay günlüğünde bildirilen hata 833

Bazı durumlarda, hata günlüğünde 833 SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d) hatasını gözlemleyebilirsiniz. Aşağıdaki PowerShell komutunu çalıştırarak sisteminizdeki SQL Server hata günlüklerini de kontrol edebilirsiniz:

Get-ChildItem -Path "c:\program files\microsoft sql server\mssql*" -Recurse -Include Errorlog |
   Select-String "occurrence(s) of I/O requests taking longer than Longer than 15 secs"

Ayrıca, bu hata hakkında daha fazla bilgi için MSSQLSERVER_833 bölümüne bakın.

2. Adım: Perfmon Sayaçları G/Ç gecikmesini gösteriyor mu?

SQL Server G/Ç gecikmesi bildiriyorsa işletim sistemi sayaçlarına bakın. Gecikme sayacını Avg Disk Sec/Transferinceleyerek G/Ç sorunu olup olmadığını belirleyebilirsiniz. Aşağıdaki kod parçacığı, bu bilgileri PowerShell aracılığıyla toplamanın bir yolunu gösterir. Tüm disk birimlerinde sayaçları toplar: "_total". Belirli bir sürücü birimine geçin (örneğin, "D:"). Veritabanı dosyalarınızı barındıran birimleri bulmak için SQL Server'ınızda aşağıdaki sorguyu çalıştırın:

#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance" 
sqlcmd -E -S $sqlserver_instance -Q "SELECT DISTINCT LEFT(volume_mount_point, 32) AS volume_mount_point `
                                     FROM sys.master_files f `
                                     CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs"

Seçtiğiniz hacimle ilgili ölçümleri toplayın Avg Disk Sec/Transfer :

clear
$cntr = 0 

# replace with your server name, unless local computer
$serverName = $env:COMPUTERNAME

# replace with your volume name - C: , D:, etc
$volumeName = "_total"

$Counters = @(("\\$serverName" +"\LogicalDisk($volumeName)\Avg. disk sec/transfer"))

$disksectransfer = Get-Counter -Counter $Counters -MaxSamples 1 
$avg = $($disksectransfer.CounterSamples | Select-Object CookedValue).CookedValue

Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 30 | ForEach-Object {
$_.CounterSamples | ForEach-Object {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 5))
         turn = $cntr = $cntr +1
         running_avg = [Math]::Round(($avg = (($_.CookedValue + $avg) / 2)), 5)  
         
   } | Format-Table
     }
   }

   write-host "Final_Running_Average: $([Math]::Round( $avg, 5)) sec/transfer`n"
  
   if ($avg -gt 0.01)
   {
     Write-Host "There ARE indications of slow I/O performance on your system"
   }
   else
   {
     Write-Host "There is NO indication of slow I/O performance on your system"
   }

Bu sayacın değerleri tutarlı olarak 10-15 milisaniyenin üzerindeyse, soruna daha fazla bakmanız gerekir. Bazı ani artışlar çoğu durumda sayılmaz, ancak ani artışın süresini bir kez daha kontrol edin. Ani artış bir dakika veya daha fazla sürse, bu bir sıçramadan çok platoya benzer.

Performans izleyici sayaçları gecikme süresi bildirmiyorsa ancak SQL Server bildiriyorsa, sorun SQL Server ile Bölüm Yöneticisi arasında, yani filtre sürücüleri arasındadır. Bölüm Yöneticisi, işletim sisteminin Perfmon sayaçlarını topladığı bir G/Ç katmanıdır. Gecikme süresini gidermek için filtre sürücülerinin düzgün dışlanmasını sağlayın ve filtre sürücüsü sorunlarını çözün. Filtre sürücüleri Virüsten koruma yazılımı, Yedekleme çözümleri, Şifreleme, Sıkıştırma vb. gibi programlar tarafından kullanılır. Sistemlerdeki filtre sürücülerini ve bunların bağlı olduğu birimleri listelemek için bu komutu kullanabilirsiniz. Ardından, Ayrılan filtre yükseklikleri makalesinde sürücü adlarını ve yazılım satıcılarını arayabilirsiniz.

fltmc instances

Daha fazla bilgi için bkz. SQL Server çalıştıran bilgisayarlarda çalıştırılacak virüsten koruma yazılımını seçme.

Zaman uyumsuz G/Ç'nin zaman uyumlu ve dolayısıyla yavaşlamasına neden olduklarından Şifreleme Dosya Sistemi (EFS) ve dosya sistemi sıkıştırma kullanmaktan kaçının. Daha fazla bilgi için Windows'da zaman uyumsuz disk G/Ç zaman uyumlu olarak görünüyor makalesine bakın.

3. Adım: G/Ç alt sistemi kapasitenin ötesinde bunalmış mı?

SQL Server ve işletim sistemi G/Ç alt sisteminin yavaş olduğunu gösteriyorsa, bunun nedeninin sistemin kapasitenin ötesinde aşırı yüklenmiş olup olmadığını denetleyin. G/Ç sayaçlarına Disk Bytes/Sec, Disk Read Bytes/Secveya Disk Write Bytes/Secbakarak kapasiteyi de kontrol edebilirsiniz. SAN'nız (veya diğer G/Ç alt sisteminiz) için beklenen aktarım hızı belirtimleri için Sistem Yöneticinize veya donanım satıcınıza danışın. Örneğin, SAN anahtarındaki 2 GB/sn HBA kartı veya 2 GB/sn ayrılmış bağlantı noktası üzerinden en fazla 200 MB/sn G/Ç gönderebilirsiniz. Donanım üreticisi tarafından tanımlanan beklenen aktarım hızı kapasitesi, buradan nasıl devam ettiğinizi tanımlar.

clear

$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Bytes/sec"),
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Read Bytes/sec"),
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Write Bytes/sec")
   )
Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 20 | ForEach-Object  {
$_.CounterSamples | ForEach-Object       {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 3)) }
    }
 }

4. Adım: SQL Server yoğun G/Ç etkinliğini mi yönlendirdi?

G/Ç alt sistemi kapasitenin ötesinde aşırı yüklenmişse, belirli bir örnek için (en yaygın suçlu) ve Page Writes/Sec (çok daha az yaygın) bakarak Buffer Manager: Page Reads/Sec SQL Server'ın suçlu olup olmadığını öğrenin. SQL Server ana G/Ç sürücüsüyse ve G/Ç birimi sistemin işleyebildiğinin ötesindeyse, Uygulama Geliştirme ekipleriyle veya uygulama satıcısıyla birlikte çalışarak şunları yapın:

• Sorguları ayarlama, örneğin: daha iyi dizinler, güncelleştirme istatistikleri, sorguları yeniden yazma ve veritabanını yeniden tasarlama.
• Maksimum sunucu belleğini artırın veya sisteme daha fazla RAM ekleyin. Daha fazla RAM, diskten sık sık yeniden okuma yapmadan daha fazla veri veya dizin sayfasını önbelleğe alır ve bu da G/Ç etkinliğini azaltır. Artan bellek, kullanılabilir sınırlı bellekte daha fazla veritabanı sayfası depolamak için sık sık ihtiyaç duyulduğunda Gecikmeli Yazıcı boşaltmaları tarafından yönlendirilen 'yi de azaltabilir Lazy Writes/sec.
• Sayfa yazma işleminin yoğun G/Ç etkinliğinin kaynağı olduğunu fark ederseniz, bunun kurtarma aralığı yapılandırma taleplerini karşılamak için gereken çok büyük sayfa boşaltmalarından kaynaklandığını görmek için inceleyin Buffer Manager: Checkpoint pages/sec . G/Ç'yi zaman içinde eşitlemek veya donanım G/Ç aktarım hızını artırmak için Dolaylı denetim noktalarını kullanabilirsiniz.

Nedenler

Genel olarak aşağıdaki sorunlar SQL Server sorgularının G/Ç gecikmesinden etkilenmesinin üst düzey nedenleridir:

• Donanım sorunları:

  • SAN yanlış yapılandırması (anahtar, kablolar, HBA, depolama)

  • G/Ç kapasitesi aşıldı (yalnızca arka uç depolama alanı değil, tüm SAN ağı boyunca dengesiz)

  • Sürücüler veya üretici yazılımı sorunları

  Donanım satıcılarının ve/veya sistem yöneticilerinin bu aşamada devreye alınması gerekir.

• Sorgu sorunları: SQL Server, G/Ç istekleriyle disk birimlerini doygunluğa çıkarır ve G/Ç alt sistemini kapasitenin ötesine gönderir ve bu da G/Ç aktarım hızlarının yüksek olmasını sağlar. Bu durumda çözüm, çok sayıda mantıksal okumaya (veya yazmaya) neden olan sorguları bulmak ve uygun dizinleri kullanan disk G/Ç'sini en aza indirmek için bu sorguları ayarlamaktır. Ayrıca, sorgu iyileştiricisine en iyi planı seçmek için yeterli bilgiyi sağladığından istatistikleri güncel tutun. Ayrıca, yanlış veritabanı tasarımı ve sorgu tasarımı G/Ç sorunlarında artışa yol açabilir. Bu nedenle sorguların ve bazen tabloların yeniden tasarlanması iyileştirilmiş G/Ç konusunda yardımcı olabilir.

• Filtre sürücüleri: Dosya sistemi filtre sürücüleri yoğun G/Ç trafiğini işlerse SQL Server G/Ç yanıtı ciddi şekilde etkilenebilir. G/Ç performansı üzerindeki etkiyi önlemek için virüsten koruma taramasından ve yazılım satıcıları tarafından doğru filtre sürücüsü tasarımından uygun dosya dışlamaları önerilir.

• Diğer uygulamalar: SQL Server ile aynı makinedeki başka bir uygulama, G/Ç yolunu aşırı okuma veya yazma istekleriyle doyabilir. Bu durum G/Ç alt sistemini kapasite sınırlarının ötesine itebilir ve SQL Server için G/Ç yavaşlığına neden olabilir. G/Ç yığını üzerindeki etkisini ortadan kaldırmak için uygulamayı tanımlayın ve ayarlayın veya başka bir yere taşıyın.

Metodolojinin grafik gösterimi

G/Ç ile ilgili bekleme türleri hakkında bilgi

Aşağıda, disk G/Ç sorunları bildirildiğinde SQL Server'da gözlemlenen yaygın bekleme türlerinin açıklamaları yer alır.

PAGEIOLATCH_EX

Bir görev G/Ç isteğindeki bir veri veya dizin sayfası (arabellek) için bir mandalı beklediğinde gerçekleşir. Mandal isteği Özel Kullanım modundadır. Arabellek diske yazılırken Özel Kullanım modu kullanılır. Uzun bekleme süreleri disk alt sistemiyle ilgili sorunları gösterebilir.

PAGEIOLATCH_SH

Bir görev G/Ç isteğindeki bir veri veya dizin sayfası (arabellek) için bir mandalı beklediğinde gerçekleşir. Mandal isteği Paylaşılan modundadır. Arabellek diskten okunurken Paylaşılan modu kullanılır. Uzun bekleme süreleri disk alt sistemiyle ilgili sorunları gösterebilir.

PAGEIOLATCH_UP

Bir görev G/Ç isteğindeki arabellek için bir mandalı beklediğinde gerçekleşir. Mandal isteği Güncelleştirme modundadır. Uzun bekleme süreleri disk alt sistemiyle ilgili sorunları gösterebilir.

YAZMA GÜNLÜĞÜ

Görev işlem günlüğü boşaltma işleminin tamamlanmasını beklediğinde gerçekleşir. Log Manager geçici içeriğini diske yazdığında bir boşaltma gerçekleşir. Günlük boşaltmalara neden olan yaygın işlemler işlem işlemeleri ve denetim noktalarıdır.

Uzun bekleme sürelerinin WRITELOG yaygın nedenleri şunlardır:

• İşlem günlüğü diski gecikme süresi: Beklemelerin en yaygın nedeni WRITELOG budur. Genel olarak, veri ve günlük dosyalarının ayrı birimlerde tutulması önerilmektedir. İşlem günlüğü yazma işlemleri, bir veri dosyasından veri okuma veya yazma işlemi rastgele yapılırken sıralı yazma işlemleridir. Bir sürücü biriminde (özellikle geleneksel dönen disk sürücüleri) veri ve günlük dosyalarının karıştırılması aşırı disk kafası hareketlerine neden olur.

• Çok fazla VFS: Çok fazla sanal günlük dosyası (VLF) beklemeye neden WRITELOG olabilir. Çok fazla VLF, uzun kurtarma gibi başka tür sorunlara neden olabilir.

• Çok fazla küçük işlem: Büyük işlemler engellemeye neden olsa da, çok fazla küçük işlem başka bir sorun kümesine yol açabilir. Bir işlemi açıkça başlatmazsanız, herhangi bir ekleme, silme veya güncelleştirme işlemine neden olur (bu otomatik işlem diyoruz). Döngüde 1.000 ekleme yaparsanız 1.000 işlem oluşturulur. Bu örnekteki her işlemin işlenmesi gerekir ve bu da işlem günlüğünün boşaltılması ve 1.000 işlem boşaltılmasıyla sonuçlanan bir işlemdir. Mümkün olduğunda, işlem günlüğü boşaltmalarını azaltmak ve performansı artırmak için tek tek güncelleştirmeyi, silmeyi veya daha büyük bir işleme eklemeyi gruplandırma. Bu işlem daha WRITELOG az beklemeye yol açabilir.

• Zamanlama sorunları, Günlük Yazıcı iş parçacıklarının yeterince hızlı zamanlanmamasına neden oluyor: SQL Server 2016'ya kadar, tek bir Günlük Yazıcı iş parçacığı tüm günlük yazma işlemlerini gerçekleştiriyor. İş parçacığı zamanlaması (örneğin, yüksek CPU) ile ilgili sorunlar varsa, hem Günlük Yazıcı iş parçacığı hem de günlük boşaltmaları gecikebilir. SQL Server 2016'da, günlük yazma aktarım hızını artırmak için dört adede kadar Günlük Yazıcı iş parçacığı eklendi. Bkz . SQL 2016 - Yalnızca Daha Hızlı Çalışır: Birden Çok Günlük Yazıcı Çalışanı. SQL Server 2019'da sekiz adede kadar Günlük Yazıcı iş parçacığı eklendi ve bu da aktarım hızını daha da iyileştirir. Ayrıca, SQL Server 2019'da her normal çalışan iş parçacığı Günlük yazıcı iş parçacığına göndermek yerine doğrudan günlük yazma işlemleri yapabilir. Bu geliştirmelerle, WRITELOG beklemeler zamanlama sorunlarıyla nadiren tetiklenebilir.

ASYNC_IO_COMPLETION

Aşağıdaki G/Ç etkinliklerinden bazıları gerçekleştiğinde gerçekleşir:

• Toplu Ekleme Sağlayıcısı ("Toplu Ekle"), G/Ç gerçekleştirirken bu bekleme türünü kullanır.
• LogShipping'de Geri Al dosyasını okuma ve Günlük Gönderimi için Zaman Uyumsuz G/Ç'yi yönlendirme.
• Veri yedeklemesi sırasında veri dosyalarından gerçek verileri okuma.

IO_COMPLETION

G/Ç işlemlerinin tamamlanmasını beklerken gerçekleşir. Bu bekleme türü genellikle veri sayfalarıyla (arabellekler) ilgili olmayan G/Ç'leri içerir. Örnekler şunları içerir:

• Taşma sırasında diskten/diske sıralama/karma sonuçlarını okuma ve yazma ( tempdb depolama performansını denetleyin).
• Diske hevesli biriktiricileri okuma ve yazma ( tempdb depolama alanını denetleyin).
• İşlem günlüğünden okuma günlüğü blokları (günlüğün diskten okunmasını sağlayan herhangi bir işlem sırasında , örneğin kurtarma).
• Veritabanı henüz ayarlanmamışken diskten sayfa okuma.
• Sayfaları veritabanı anlık görüntüsüne kopyalama (YazmaDa Kopyalama).
• Veritabanı dosyası ve dosya sıkıştırması kapatılıyor.

BACKUPIO

Bir yedekleme görevi verileri beklediğinde veya verileri depolamak için bir arabellek beklediğinde gerçekleşir. Bir görevin bant bağlamayı beklediği durumlar dışında bu tür tipik değildir.