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

Dotyczy: SQL Server

Podsumowanie

Ten artykuł zawiera metodologię krok po kroku, aby zdiagnozować i rozwiązać problemy z niską wydajnością SQL Server spowodowaną wąskimi gardłami we/wy dysku. Wyjaśniono w nim, jak identyfikować opóźnienie we/wy przy użyciu typów oczekiwania SQL Server, dynamicznego sys.dm_io_virtual_file_stats widoku zarządzania i liczników Windows monitor wydajności. Dowiesz się, jak określić, czy podsystem we/wy jest przytłoczony, czy SQL Server jest głównym sterownikiem we/wy, oraz jaki sprzęt, zapytanie, sterownik filtru lub poziom aplikacji powoduje zbadanie. Skorzystaj z tych wskazówek, aby odizolować źródło opóźnienia we/wy i zastosować właściwą poprawkę dla wdrożeń SQL Server lokalnych.

Definiowanie niskiej wydajności we/wy

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

W programie SQL Server elementy działają w taki sam sposób. Często sprawdzasz, czy SQL Server zgłasza jakiekolwiek wąskie gardła I/O, które są mierzone w czasie rzeczywistym (w milisekundach). Program SQL Server wysyła żądania we/wy do systemu operacyjnego, wywołując funkcje Win32, takie jak WriteFile(), ReadFile(), WriteFileGather()i ReadFileScatter(). Gdy publikowane jest żądanie we/wy, SQL Server mierzy czas trwania żądania i zgłasza go, używając typów oczekiwania. SQL Server używa typów oczekiwania, aby wskazać oczekiwania I/O w różnych miejscach systemu. Oczekiwania związane z I/O są następujące:

• / PAGEIOLATCH_SHPAGEIOLATCH_EX
• WRITELOG
• IO_COMPLETION
• ASYNC_IO_COMPLETION
• BACKUPIO

Jeśli czas oczekiwania stale przekracza 10–15 milisekund, I/O jest uznawane za wąskie gardło.

Uwaga

Aby zapewnić kontekst, Microsoft zaobserwował SQL Server systemów, w których żądanie we/wy przejęło jedną sekundę, a nawet 15 sekund na transfer. Takie systemy we/wy wymagają optymalizacji. Z drugiej strony, Microsoft widział również systemy, w których przepływność jest poniżej jednej milisekundy na transfer. Dzięki dzisiejszej technologii SSD i NVMe anonsowany zakres szybkości przepływności w dziesiątkach mikrosekund na transfer.

Liczba 10–15 milisekund na transfer jest przybliżonym progiem wybranym na podstawie doświadczenia zbiorowego między inżynierami Windows i SQL Server. Zazwyczaj gdy liczby wykraczają poza ten próg, SQL Server użytkownicy zaczynają widzieć opóźnienia w obciążeniach. Ostatecznie oczekiwana przepływność podsystemu we/wy jest definiowana przez producenta, model, konfigurację, obciążenie i inne czynniki.

Metodologia izolowania wąskich gardeł we/wy

Wykres blokowy na końcu tego artykułu opisuje metodologię często używaną do podejścia do powolnych problemów we/wy z SQL Server. Ta metodologia nie jest wyczerpująca ani wyłączna, ale jest przydatna do izolowania i rozwiązywania problemu.

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

Sprawdź, czy typy oczekiwania SQL Server zgłaszają opóźnienie we/wy. Wartości PAGEIOLATCH_*, WRITELOGi ASYNC_IO_COMPLETION, wraz z wartościami kilku innych mniej typowych typów oczekiwania, powinny pozostać poniżej 10–15 milisekund na żądanie we/wy. Jeśli te wartości stale przekraczają ten zakres, istnieje problem z wydajnością we/wy i wymaga dalszej analizy. Poniższe 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 zgodnie z raportem 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 można sprawdzić 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 wskazują opóźnienie wejścia/wyjścia?

Jeśli program SQL Server zgłasza opóźnienie we/wy, zapoznaj się z licznikami systemu operacyjnego. Możesz określić, czy występuje problem z we/wy, sprawdzając licznik opóźnień Avg Disk Sec/Transfer. Poniższy fragment kodu wskazuje jeden ze sposobów zbierania tych informacji za pomocą programu PowerShell. Zbiera liczniki na wszystkich woluminach dysków: "_total". Zmień na określony wolumin dysku (na przykład "D:"). Aby znaleźć woluminy hostowania plików 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 są stale powyżej 10–15 milisekund, należy zbadać dokładniej. Okazjonalne 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 bardziej płaskowyż niż skok.

Jeśli liczniki monitor wydajności nie zgłaszają opóźnienia, ale SQL Server, problem występuje między SQL Server a Menedżerem partycji, czyli sterownikami filtrów. Menedżer partycji to warstwa we/wy, w której system operacyjny zbiera liczniki wydajności . Aby rozwiązać problemy z opóźnieniem, upewnij się, że występują odpowiednie wykluczenia sterowników filtrów i rozwiąż problemy ze sterownikiem filtru. Programy takie jak oprogramowanie antywirusowe, rozwiązania do tworzenia kopii zapasowych, szyfrowanie, kompresja i podobne oprogramowanie używają sterowników filtrów. Użyj tego polecenia, aby wyświetlić listę sterowników filtrów w systemach i dołączonych woluminach. Następnie wyszukaj 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 uruchamiania na komputerach z uruchomionym programem SQL Server.

Unikaj używania systemu szyfrowania plików (EFS) i kompresji systemu plików, ponieważ powodują one asynchroniczne operacje we/wy, aby stały się synchroniczne i w związku z tym wolniejsze. Aby uzyskać więcej informacji, zobacz artykuł Asynchroniczny dysk I/O jest traktowany jako synchroniczny w systemie Windows.

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

Jeśli program SQL Server i system operacyjny wskazują, że podsystem we/wy jest powolny, sprawdź, czy przyczyną jest przeciążenie systemu poza pojemnością. Możesz sprawdzić pojemność, przeglądając liczniki we/wy Disk Bytes/Sec, Disk Read Bytes/Seclub Disk Write Bytes/Sec. Zapoznaj się z administratorem systemu lub dostawcą sprzętu, aby uzyskać oczekiwane specyfikacje przepływności dla sieci SAN (lub innego podsystemu we/wy). Na przykład można przesyłać maksymalnie 200 MB/s I/O za pośrednictwem karty HBA 2 GB/s lub dedykowanego portu 2 GB/s na przełączniku SAN. Oczekiwana pojemność przepływności zdefiniowana przez producenta sprzętu definiuje sposób wykonywania tego działania.

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 napędza duże działanie we/wy?

Jeśli podsystem we/wy jest przeciążony poza możliwości, sprawdź, czy SQL Server jest winowajcą, patrząc na Buffer Manager: Page Reads/Sec (najczęstszy winowajca) i Page Writes/Sec (znacznie rzadszy) dla konkretnego 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:

• Optymalizacja zapytań, na przykład: lepsze indeksy, aktualizacja statystyk, przepisywanie zapytań i przeprojektowanie bazy danych.
• Zwiększ maksymalną pamięć serwera lub dodaj więcej pamięci RAM w systemie. Więcej pamięci RAM będzie buforować więcej stron danych lub indeksów bez częstego ponownego odczytywania z dysku, co zmniejszy aktywność operacji we/wy. Zwiększona ilość pamięci może również zmniejszyć Lazy Writes/sec, który to proces jest napędzany przez opróżnianie funkcji Lazy Writer, gdy często trzeba przechowywać więcej stron bazy danych w ograniczonej pamięci dostępnej.
• Jeśli okaże się, że zapisy stron są źródłem dużego działania 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. Możesz użyć pośrednich punktów kontrolnych, aby wyrównać liczbę operacji we/wy w czasie lub zwiększyć przepływność operacji we/wy sprzętu.

Typowe główne przyczyny opóźnienia operacji we/wy SQL Server

Ogólnie rzecz biorąc, następujące problemy są ogólnymi przyczynami, dla których zapytania programu SQL Server cierpią z powodu opóźnień we/wy:

• Problemy sprzętowe:

  • Błędna konfiguracja sieci SAN (przełącznik, kable, HBA, pamięć masowa)

  • Przekroczono pojemność we/wy (niezrównoważone w całej sieci SAN, nie tylko w pamięci masowej zaplecza)

  • Problemy ze sterownikami lub oprogramowaniem układowym

  Na tym etapie skontaktuj się z dostawcami sprzętu i administratorami systemu.

• Problemy z zapytaniami: SQL Server saturację woluminów dysków przy użyciu żądań we/wy i wypycha podsystem we/wy poza pojemność, co powoduje wysokie szybkości transferu we/wy. W takim przypadku znajdź zapytania, które powodują dużą liczbę odczytów logicznych (lub zapisów), a następnie dostosuj te zapytania, aby zminimalizować operacje we/wy dysku. Korzystanie z odpowiednich indeksów jest pierwszym krokiem do wykonania. Ponadto zachowaj zaktualizowane statystyki, ponieważ udostępniają optymalizatorowi zapytań wystarczające informacje, aby wybrać najlepszy plan. Nieprawidłowy projekt bazy danych i projekt zapytań może prowadzić do zwiększenia liczby problemów z we/wy. W związku z tym przeprojektowanie zapytań i czasami tabel może pomóc ulepszyć operacje we/wy.

• Sterowniki filtrów: Jeśli sterowniki filtrów systemu plików przetwarzają duży ruch we/wy, mogą one poważnie wpłynąć na SQL Server odpowiedzi we/wy. Aby zapobiec wpływowi na wydajność we/wy, należy prawidłowo wykluczyć pliki ze skanowania antywirusowego i zapewnić poprawne projektowanie sterowników filtrów przez dostawców oprogramowania.

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

Graficzna reprezentacja metodologii

Informacje o typach oczekiwania związanych z I/O

Poniższe opisy obejmują typowe typy oczekiwania widoczne w SQL Server w przypadku zgłaszania problemów z we/wy dysku.

PAGEIOLATCH_EX

Występuje, gdy zadanie oczekuje na zatrzask na stronie danych lub indeksu (buforze) w żądaniu we/wy. Żądanie zatrzaszania jest w trybie wyłącznym. Tryb wyłączny jest używany, gdy bufor jest zapisywany na dysku. Długie oczekiwania mogą wskazywać na problemy z podsystemem dysków.

PAGEIOLATCH_SH

Występuje, gdy zadanie oczekuje na zatrzask na stronie danych lub indeksu (buforze) w żądaniu we/wy. Żądanie zatrzaszania 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 dysków.

PAGEIOLATCH_UP

Występuje, gdy zadanie czeka na zatrzask buforu w ramach żądania I/O. Żądanie zatrzaszania jest w trybie aktualizacji. Długie oczekiwania mogą wskazywać na problemy z podsystemem dysków.

WRITELOG

Występuje, gdy zadanie oczekuje na opróżnienie bufora dla dziennika transakcji. Opróżnienie występuje, gdy Menedżer dzienników zapisuje jego tymczasową zawartość na dysku. Typowe operacje, które powodują opróżnienia dzienników, to zatwierdzenia transakcji i punkty kontrolne.

Typowe przyczyny długiego oczekiwania na WRITELOG są następujące:

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

• Zbyt wiele plików VLF: Zbyt wiele plików dziennika wirtualnego może powodować WRITELOG oczekiwania. Zbyt wiele VLF może powodować inne typy problemów, takich jak długi czas odzyskiwania.

• 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 transakcji jawnie, każda operacja wstawiania, usuwania lub aktualizacji spowoduje transakcję (nazywamy to transakcją automatyczną). Jeśli zrobisz 1000 wstawień w pętli, zostanie wygenerowanych 1000 transakcji. Każda transakcja w tym przykładzie musi zostać zatwierdzona, co powoduje zapisanie danych w dzienniku transakcji i 1000 zapisów transakcji. Jeśli to możliwe, pogrupuj pojedynczą aktualizację, usuń lub wstaw do większej transakcji, aby zmniejszyć liczbę opróżnień dziennika transakcji i zwiększyć wydajność. Ta operacja może prowadzić do mniejszej liczby WRITELOG oczekiwań.

• Problemy z planowaniem powodują, że wątki Log Writer nie są wystarczająco szybko zaplanowane: w wersjach przed SQL Server 2016 pojedynczy wątek Log Writer wykonywał wszystkie operacje zapisu dziennika. Jeśli wystąpiły problemy z planowaniem wątków (na przykład wysokie użycie procesora), zarówno wątek zapisu dziennika, jak i opróżnienia dziennika mogą zostać opóźnione. W programie SQL Server 2016 dodano do czterech wątków modułu zapisywania dzienników w celu zwiększenia przepływności zapisywania dzienników. Zobacz SQL 2016 — działa po prostu szybciej: wiele procesów zapisywania dzienników. W programie SQL Server 2019 dodano maksymalnie osiem wątków modułu zapisywania dzienników, co zwiększa przepływność. Ponadto w programie SQL Server 2019 każdy zwykły wątek roboczy może wykonywać operacje zapisu dziennika bezpośrednio zamiast przekazywać do wątku zapisującego dzienniki. Dzięki tym ulepszeniom WRITELOG problemy z planowaniem rzadko będą wywoływać oczekiwania.

ASYNC_IO_COMPLETION

Występuje, gdy mają miejsce niektóre z następujących działań I/O:

• Dostawca Wstawiania Zbiorczego ("Wstawianie zbiorcze") stosuje ten typ oczekiwania podczas wykonywania operacji we/wy (input/output).
• Odczytywanie pliku przywracania w Log Shipping i zarządzanie asynchronicznym we/wy dla wysyłki dziennika.
• 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 wejścia/wyjścia niezwiązane ze stronami danych (buforami). Oto kilka przykładów:

• Odczytywanie i zapisywanie wyników sortowania/haszowania z/do dysku podczas przepełnienia (sprawdź wydajność magazynu tempdb).
• Odczytywanie i zapisywanie eager spools na dysku (sprawdź magazyn tempdb).
• Odczytywanie bloków dziennika z dziennika transakcji (podczas każdej operacji, która powoduje odczyt dziennika z dysku — na przykład odzyskiwanie).
• Odczytywanie strony z dysku, gdy baza danych nie jest jeszcze skonfigurowana.
• Kopiowanie stron do migawki bazy danych (kopiowanie na zapis).
• Zamykanie pliku bazy danych i rozpakowywanie pliku.

BACKUPIO

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

Treści powiązane

• typy oczekiwania SQL Server (sys.dm_os_wait_stats)
• Dziennik transakcji (SQL Server)
• baza danych tempdb
• błąd MSSQLSERVER_833 Database Engine