Rozwiązywanie problemów z niską wydajnością SQL Server spowodowaną przez problemy z we/wy

Dotyczy: SQL Server

Ten artykuł zawiera wskazówki dotyczące problemów z we/wy, które powodują powolne SQL Server wydajności i sposobu rozwiązywania problemów.

Definiowanie niskiej wydajności operacji we/wy

Liczniki monitora wydajności są używane do określania niskiej wydajności operacji we/wy. Liczniki te mierzą, jak szybko podsystem we/wy obsługuje średnio każde żądanie we/wy pod względem czasu zegara. Konkretne liczniki monitora wydajności , które mierzą opóźnienie we/wy w systemie Windows, to Avg Disk sec/ Read, Avg. Disk sec/Writei Avg. Disk sec/Transfer (skumulowane zarówno odczyty, jak i zapisy).

W SQL Server wszystko działa w ten sam sposób. Często sprawdzasz, czy SQL Server zgłasza wąskie gardła we/wy mierzone w czasie zegara (milisekundy). SQL Server wysyła żądania we/wy do systemu operacyjnego, wywołując funkcje Win32, takie jak WriteFile(), ReadFile(), WriteFileGather()i ReadFileScatter(). Gdy wysyła żądanie we/wy, SQL Server razy żądanie i raportuje czas trwania żądania przy użyciu typów oczekiwania. SQL Server używa typów oczekiwania, aby wskazać oczekiwania we/wy w różnych miejscach produktu. Oczekiwania związane z operacjami we/wy to:

• / PAGEIOLATCH_SHPAGEIOLATCH_EX
• WRITELOG
• IO_COMPLETION
• ASYNC_IO_COMPLETION
• BACKUPIO

Jeśli te oczekiwania stale przekraczają 10–15 milisekund, operacje we/wy są uznawane za wąskie gardło.

Uwaga

Aby zapewnić kontekst i perspektywę, w świecie rozwiązywania problemów SQL Server usługa Microsoft CSS zaobserwowała przypadki, w których żądanie we/wy trwało ponad jedną sekundę i aż 15 sekund na takie systemy we/wy transferu wymagają optymalizacji. Z drugiej strony w usłudze Microsoft CSS przedstawiono systemy, w których przepływność jest mniejsza niż jeden milisekunda/transfer. Dzięki dzisiejszej technologii SSD/NVMe, anonsowane szybkości przepływności wahają się w dziesiątkach mikrosekund na transfer. W związku z tym liczba 10-15 milisekund/transferu jest bardzo przybliżonym progiem wybranym na podstawie zbiorowego doświadczenia między inżynierami systemu Windows i SQL Server na przestrzeni lat. Zwykle, gdy liczby przekraczają ten przybliżony próg, SQL Server użytkownicy zaczynają widzieć opóźnienia w swoich obciążeniach i zgłaszać je. Ostatecznie oczekiwana przepływność podsystemu we/wy jest definiowana przez producenta, model, konfigurację, obciążenie i potencjalnie wiele innych czynników.

Metodologii

Wykres blokowy na końcu tego artykułu opisuje metodologię używaną przez usługę Microsoft CSS w celu podejścia do powolnych problemów we/wy z SQL Server. Nie jest to wyczerpujące ani wyłączne podejście, ale okazało się przydatne w izolowaniu problemu i jego rozwiązywaniu.

Aby rozwiązać ten problem, możesz wybrać jedną z następujących dwóch opcji:

Opcja 1. Wykonywanie kroków bezpośrednio w notesie za pośrednictwem usługi Azure Data Studio

Uwaga

Przed podjęciem próby otwarcia tego notesu upewnij się, że usługa Azure Data Studio jest zainstalowana na komputerze lokalnym. Aby go zainstalować, przejdź do artykułu Dowiedz się, jak zainstalować usługę Azure Data Studio.

Otwieranie notesu w usłudze Azure Data Studio

Opcja 2. Wykonaj kroki ręcznie

Metodologia została opisana w następujących krokach:

Krok 1. Czy raporty SQL Server powolne we/wy?

SQL Server mogą zgłaszać opóźnienie operacji we/wy na kilka sposobów:

• Typy oczekiwania we/wy
• DMV sys.dm_io_virtual_file_stats
• Dziennik błędów lub dziennik zdarzeń aplikacji

Typy oczekiwania we/wy

Ustal, czy występuje opóźnienie we/wy zgłaszane przez typy oczekiwania SQL Server. Wartości PAGEIOLATCH_*, WRITELOGi ASYNC_IO_COMPLETION wartości kilku innych typów mniej typowych typów oczekiwania powinny zazwyczaj pozostawać poniżej 10–15 milisekund na żądanie we/wy. Jeśli te wartości są spójniej większe, występuje problem z wydajnością we/wy i wymaga dalszego zbadania. Następujące zapytanie może pomóc w zebraniu tych informacji diagnostycznych w systemie:

#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
}

Statystyki plików w sys.dm_io_virtual_file_stats

Aby wyświetlić opóźnienie na poziomie pliku bazy danych zgłoszone w SQL Server, uruchom następujące zapytanie:

#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"

Przyjrzyj się kolumnom AvgLatency i LatencyAssessment , aby zrozumieć szczegóły opóźnienia.

Błąd 833 zgłoszony w dzienniku błędów lub dzienniku zdarzeń aplikacji

W niektórych przypadkach w dzienniku błędów może wystąpić błąd 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) . Możesz sprawdzić, SQL Server dzienniki błędów w systemie, uruchamiając następujące polecenie programu PowerShell:

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"

Aby uzyskać więcej informacji na temat tego błędu, zobacz sekcję MSSQLSERVER_833 .

Krok 2. Czy liczniki perfmonów wskazują opóźnienie operacji we/wy?

Jeśli SQL Server zgłasza opóźnienie operacji we/wy, zapoznaj się z licznikami systemu operacyjnego. Możesz określić, czy występuje problem we/wy, sprawdzając licznik Avg Disk Sec/Transferopóźnień . Poniższy fragment kodu wskazuje jeden ze sposobów zbierania tych informacji za pośrednictwem programu PowerShell. Zbiera liczniki na wszystkich woluminach dysków: "_total". Zmień na określony wolumin dysku (na przykład "D:"). Aby znaleźć woluminy hostowane w plikach bazy danych, uruchom następujące zapytanie w SQL Server:

#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"

Zbierz Avg Disk Sec/Transfer metryki na wybranym woluminie:

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"
   }

Jeśli wartości tego licznika stale przekraczają 10–15 milisekund, należy przyjrzeć się problemowi. Sporadyczne skoki nie są liczone w większości przypadków, ale pamiętaj, aby dokładnie sprawdzić czas trwania skoku. Jeśli skok trwał co najmniej jedną minutę, jest to więcej płaskowyżu niż skok.

Jeśli liczniki monitora wydajności nie zgłaszają opóźnienia, ale SQL Server to robi, problem występuje między SQL Server a Menedżerem partycji, czyli sterownikami filtru. Menedżer partycji to warstwa we/wy, w której system operacyjny zbiera liczniki narzędzia Perfmon . Aby rozwiązać problem z opóźnieniem, upewnij się, że sterowniki filtrów są odpowiednie, i rozwiąż problemy ze sterownikami filtru. Sterowniki filtrów są używane przez programy, takie jak oprogramowanie antywirusowe, rozwiązania do tworzenia kopii zapasowych, szyfrowanie, kompresja itd. Za pomocą tego polecenia można wyświetlić listę sterowników filtrów w systemach i woluminach, do których się dołączają. Następnie możesz wyszukać nazwy sterowników i dostawców oprogramowania w artykule Przydzielone wysokości filtru .

fltmc instances

Aby uzyskać więcej informacji, zobacz Jak wybrać oprogramowanie antywirusowe do uruchomienia na komputerach z systemem SQL Server.

Unikaj używania szyfrowania systemu plików (EFS) i kompresji systemu plików, ponieważ powodują one synchroniczne asynchroniczne operacje we/wy, a tym samym wolniej. Aby uzyskać więcej informacji, zobacz artykuł Asynchroniczne operacje we/wy dysku wyświetlane jako synchroniczne w systemie Windows .

Krok 3. Czy podsystem we/wy jest przeciążony ponad pojemność?

Jeśli SQL Server i system operacyjny wskazują, że podsystem we/wy działa wolno, sprawdź, czy przyczyną jest przeciążona wydajność systemu. Pojemność można sprawdzić, przeglądając liczniki we/wy Disk Bytes/Sec, , Disk Read Bytes/Seclub Disk Write Bytes/Sec. Pamiętaj, aby skontaktować się z administratorem systemu lub dostawcą sprzętu pod kątem oczekiwanych specyfikacji przepływności dla sieci SAN (lub innego podsystemu we/wy). Na przykład można wypchnąć nie więcej niż 200 MB/s operacji we/wy za pośrednictwem karty HBA 2 GB/s lub dedykowanego portu 2 GB/s na przełączniku SIECI SAN. Oczekiwana pojemność przepływności zdefiniowana przez producenta sprzętu definiuje sposób kontynuowania w tym miejscu.

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)) }
    }
 }

Krok 4. Czy SQL Server napędzać dużą aktywność we/wy?

Jeśli podsystem we/wy jest przeciążony ponad pojemność, dowiedz się, czy SQL Server jest winowajcą, patrząc na Buffer Manager: Page Reads/Sec (najczęstszego winowajcę) i Page Writes/Sec (o wiele mniej powszechne) dla określonego wystąpienia. Jeśli SQL Server jest głównym sterownikiem we/wy, a wolumin we/wy wykracza poza to, co system może obsłużyć, skontaktuj się z zespołami deweloperów aplikacji lub dostawcą aplikacji, aby:

• Dostrajanie zapytań, na przykład: lepsze indeksy, aktualizowanie statystyk, ponowne zapisywanie zapytań i ponowne przeprojektowanie bazy danych.
• Zwiększ maksymalną ilość pamięci serwera lub dodaj więcej pamięci RAM w systemie. Więcej pamięci RAM będzie buforować więcej danych lub stron indeksu bez częstego ponownego odczytu z dysku, co zmniejszy aktywność we/wy.

Przyczyny

Ogólnie rzecz biorąc, następujące problemy to ogólne powody, dla których zapytania SQL Server cierpią z powodu opóźnienia operacji we/wy:

• Problemy sprzętowe:

  • Błędna konfiguracja sieci SAN (przełącznik, kable, karta HBA, magazyn)

  • Przekroczono pojemność we/wy (niezrównoważoną w całej sieci SAN, a nie tylko w magazynie zaplecza)

  • Problemy ze sterownikami lub oprogramowaniem układowym

  Na tym etapie należy zaangażować dostawców sprzętu i/lub administratorów systemu.

• Problemy z zapytaniami: SQL Server nasyca woluminy dysków za pomocą żądań we/wy i wypycha podsystem we/wy poza pojemność, co powoduje wysokie szybkości transferu operacji we/wy. W takim przypadku rozwiązaniem jest znalezienie zapytań, które powodują dużą liczbę odczytów logicznych (lub zapisów) i dostrojenie tych zapytań w celu zminimalizowania użycia odpowiednich indeksów we/wy dysku. Ponadto należy aktualizować statystyki, ponieważ udostępniają one optymalizatorowi zapytań wystarczające informacje, aby wybrać najlepszy plan. Ponadto nieprawidłowy projekt bazy danych i projektowanie zapytań mogą prowadzić do wzrostu problemów z we/wy. W związku z tym przeprojektowanie zapytań i czasami tabel może pomóc w ulepszeniu operacji we/wy.

• Sterowniki filtru: Odpowiedź we/wy SQL Server może mieć poważny wpływ, jeśli sterowniki filtrów systemu plików przetwarzają duży ruch we/wy. Odpowiednie wykluczenia plików ze skanowania antywirusowego i prawidłowego projektu sterownika filtru przez dostawców oprogramowania są zalecane, aby zapobiec wpływowi na wydajność operacji we/wy.

• Inne aplikacje: Inna aplikacja na tym samym komputerze z SQL Server może nasycić ścieżkę we/wy nadmiernymi żądaniami odczytu lub zapisu. Taka sytuacja może spowodować wypchnięcie podsystemu We/Wy poza limity pojemności i spowodować spowolnienie operacji we/wy dla SQL Server. Zidentyfikuj aplikację i dostosuj ją lub przenieś w innym miejscu, aby wyeliminować jej wpływ na stos we/wy.

Graficzna reprezentacja metodologii

Informacje o typach oczekiwania związanych z operacjami we/wy

Poniżej przedstawiono opisy typowych typów oczekiwania obserwowanych w SQL Server w przypadku zgłaszania problemów z operacjami we/wy dysku.

PAGEIOLATCH_EX

Występuje, gdy zadanie czeka na zatrzaśnięcie strony danych lub indeksu (bufor) w żądaniu we/wy. Żądanie zatrzaśnienia jest w trybie wyłączności. Tryb wyłączności jest używany, gdy bufor jest zapisywany na dysku. Długie oczekiwania mogą wskazywać na problemy z podsystemem dysku.

PAGEIOLATCH_SH

Występuje, gdy zadanie czeka na zatrzaśnięcie strony danych lub indeksu (bufor) w żądaniu we/wy. Żądanie zatrzaśnięcie jest w trybie udostępnionym. Tryb udostępniony jest używany, gdy bufor jest odczytywany z dysku. Długie oczekiwania mogą wskazywać na problemy z podsystemem dysku.

PAGEIOLATCH_UP

Występuje, gdy zadanie czeka na zatrzaśnięcie buforu w żądaniu we/wy. Żądanie zatrzaśnienia jest w trybie aktualizacji. Długie oczekiwania mogą wskazywać na problemy z podsystemem dysku.

WRITELOG

Występuje, gdy zadanie czeka na ukończenie opróżniania dziennika transakcji. Opróżnienie występuje, gdy menedżer dzienników zapisuje tymczasową zawartość na dysku. Typowe operacje powodujące opróżnianie dziennika to zatwierdzenia transakcji i punkty kontrolne.

Typowe przyczyny długiego WRITELOG oczekiwania:

• Opóźnienie dysku dziennika transakcji: jest to najczęstsza przyczyna WRITELOG oczekiwania. Ogólnie rzecz biorąc, zaleca się przechowywanie plików danych i dzienników na oddzielnych woluminach. Zapisy dzienników transakcji są zapisami sekwencyjnymi, podczas gdy odczytywanie lub zapisywanie danych z pliku danych jest losowe. Mieszanie danych i plików dziennika na jednym woluminie dysku (zwłaszcza konwencjonalnych dyskach wirujących) spowoduje nadmierne przenoszenie głowy dysku.

• Zbyt wiele plików VLF: zbyt wiele wirtualnych plików dziennika (VLF) może powodować WRITELOG oczekiwania. Zbyt wiele plików VLF może powodować inne typy problemów, takie jak długie odzyskiwanie.

• Zbyt wiele małych transakcji: podczas gdy duże transakcje mogą prowadzić do blokowania, zbyt wiele małych transakcji może prowadzić do innego zestawu problemów. Jeśli nie rozpoczniesz jawnie transakcji, wszelkie operacje wstawiania, usuwania lub aktualizacji spowodują transakcję (nazywamy to automatyczną transakcją). Jeśli wykonasz 1000 wstawień w pętli, zostanie wygenerowanych 1000 transakcji. Każda transakcja w tym przykładzie musi zostać zatwierdzone, co spowoduje opróżnienie dziennika transakcji i opróżnienie 1000 transakcji. Jeśli to możliwe, zgrupuj poszczególne aktualizacje, usuń lub włóż do większej transakcji, aby zmniejszyć opróżnienia dziennika transakcji i zwiększyć wydajność. Ta operacja może prowadzić do mniejszej liczby WRITELOG oczekujących.

• Problemy z planowaniem powodują, że wątki zapisywania dzienników nie są wystarczająco szybkie: przed SQL Server 2016 r. jeden wątek zapisywania dzienników wykonał wszystkie zapisy dzienników. Jeśli wystąpiły problemy z planowaniem wątków (na przykład wysokie użycie procesora CPU), zarówno wątek zapisywania dzienników, jak i opróżnienia dziennika mogą stać się opóźnione. W SQL Server 2016 r. dodano maksymalnie cztery wątki zapisywania dzienników w celu zwiększenia przepływności zapisu dzienników. Zobacz SQL 2016 — po prostu działa szybciej: wiele procesów roboczych zapisywania dzienników. W SQL Server 2019 r. dodano maksymalnie osiem wątków zapisywania dzienników, co jeszcze bardziej zwiększa przepływność. Ponadto w SQL Server 2019 r. każdy zwykły wątek roboczy może wykonywać zapisy dzienników bezpośrednio zamiast publikować w wątku zapisywania dziennika. Dzięki tym ulepszeniom WRITELOG czas oczekiwania rzadko jest wyzwalany przez problemy z planowaniem.

ASYNC_IO_COMPLETION

Występuje, gdy wystąpią niektóre z następujących działań we/wy:

• Dostawca wstawiania zbiorczego ("Wstaw zbiorczo") używa tego typu oczekiwania podczas wykonywania operacji we/wy.
• Odczytywanie pliku Cofnij w usłudze LogShipping i kierowanie operacji we/wy asynchroniczej na potrzeby wysyłania dzienników.
• Odczytywanie rzeczywistych danych z plików danych podczas tworzenia kopii zapasowej danych.

IO_COMPLETION

Występuje podczas oczekiwania na ukończenie operacji we/wy. Ten typ oczekiwania zwykle obejmuje operacje we/wy niezwiązane ze stronami danych (buforami). Na przykład:

• Odczytywanie i zapisywanie wyników sortowania/skrótu z/na dysk podczas wycieku (sprawdzanie wydajności magazynu bazy danych tempdb ).
• Odczytywanie i pisanie chętnych buforów na dysku (sprawdź magazyn bazy danych tempdb ).
• Odczytywanie bloków dziennika z dziennika transakcji (podczas dowolnej operacji powodującej odczyt dziennika z dysku — na przykład odzyskiwania).
• Odczytywanie strony z dysku, gdy baza danych nie jest jeszcze skonfigurowana.
• Kopiowanie stron do migawki bazy danych (kopiowanie przy zapisie).
• Zamykanie pliku bazy danych i nieskompresowania plików.

BACKUPIO

Występuje, gdy zadanie tworzenia kopii zapasowej czeka na dane lub oczekuje na przechowywanie danych przez bufor. Ten typ nie jest typowy, z wyjątkiem sytuacji, gdy zadanie czeka na instalację taśmy.