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

Dotyczy: SQL Server

Ten artykuł zawiera wskazówki dotyczące problemów z we/wy, które powodują niską wydajność programu SQL Server i jak rozwiązywać problemy.

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 programie SQL Server wszystko działa w ten sam sposób. Często sprawdza się, czy program SQL Server zgłasza wąskie gardła we/wy mierzone w czasie zegara (milisekundy). Program 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, program SQL Server czasy żądania i raportuje czas trwania żądania przy użyciu typów oczekiwania. Program SQL Server używa typów oczekiwania, aby wskazać oczekiwania we/wy w różnych miejscach w produkcie. 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 z programem SQL Server usługa Microsoft CSS zaobserwowała przypadki, w których żądanie we/wy trwało ponad sekundę i aż 15 sekund na transfer, takie systemy we/wy 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 inżynierów systemu Windows i programu SQL Server na przestrzeni lat. Zazwyczaj, gdy liczby przekraczają ten przybliżony próg, użytkownicy programu SQL Server 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.

Metodologia

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 programem 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 program SQL Server zgłasza powolne we/wy?

Program SQL Server może 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 istnieje opóźnienie we/wy zgłaszane przez typy oczekiwania programu 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 programie 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) . Dzienniki błędów programu SQL Server w systemie można sprawdzić, 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 program 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 programie 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 program SQL Server to robi, problem występuje między programem 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 uruchomionym programem 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 program 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 program SQL Server powoduje dużą aktywność we/wy?

Jeśli podsystem we/wy jest przeciążony ponad pojemność, sprawdź, czy program SQL Server jest winowajcą, patrząc na Buffer Manager: Page Reads/Sec (najczęstszą przyczynę) i Page Writes/Sec (o wiele mniej powszechne) dla określonego wystąpienia. Jeśli program 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. Zwiększona ilość pamięci może również zmniejszyć Lazy Writes/secliczbę elementów , które są sterowane przez operacje opróżniania składnika Lazy Writer, gdy istnieje częsta potrzeba przechowywania większej liczby stron bazy danych w ograniczonej dostępnej pamięci.
• Jeśli okaże się, że zapisy stron są źródłem dużej aktywności we/wy, sprawdź Buffer Manager: Checkpoint pages/sec , czy jest to spowodowane ogromnymi opróżnieniami stron wymaganymi do spełnienia wymagań konfiguracji interwału odzyskiwania. Pośrednie punkty kontrolne umożliwiają wyrównywanie operacji we/wy w czasie lub zwiększanie przepływności operacji we/wy sprzętu.

Przyczyny

Ogólnie rzecz biorąc, następujące problemy to przyczyny wysokiego poziomu, dla których zapytania programu SQL Server cierpią z powodu opóźnienia we/wy:

• Problemy sprzętowe:

  • Błędna konfiguracja sieci SAN (przełącznik,, 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: Program SQL Server nasyca woluminy dysków za pomocą żądań we/wy i wypycha podsystem we/wy poza pojemność, co powoduje wysokie szybkości transferu 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 programu 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 programem 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 programu 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 programie SQL Server podczas 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ą zaplanowane wystarczająco szybko: przed programem SQL Server 2016 jeden wątek zapisywania dzienników wykonywał 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 programie SQL Server 2016 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 programie SQL Server 2019 dodano maksymalnie osiem wątków zapisywania dzienników, co jeszcze bardziej zwiększa przepływność. Ponadto w programie SQL Server 2019 każdy zwykły wątek roboczy może wykonywać zapisy dzienników bezpośrednio zamiast księgować 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 (). 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 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.