Устранение неполадок, снижающих производительность SQL Server вследствие проблем с вводом-выводом

Применяется к: SQL Server

В этой статье приводятся рекомендации по проблемам ввода-вывода, которые приводят к замедлению производительности SQL Server и устранению неполадок.

Определение медленной производительности ввода-вывода

Счетчики монитора производительности используются для определения медленной производительности ввода-вывода. Эти счетчики измеряют, насколько быстро подсистема ввода-вывода обслуживает каждый запрос ввода-вывода в среднем с точки зрения времени ввода-вывода. Конкретные счетчики монитора производительности, которые измеряют задержку ввода-вывода в Windows, Avg. Disk sec/Writeи Avg Disk sec/ ReadAvg. Disk sec/Transfer (совокупный объем операций чтения и записи).

В SQL Server все работает так же. Как правило, вы посмотрите, сообщает ли SQL Server о каких-либо узких местах ввода-вывода, измеряемых в часовом времени (миллисекундах). SQL Server выполняет запросы ввода-вывода к ОС, вызывая такие функции Win32, как WriteFile(), WriteFileGather()ReadFile()и ReadFileScatter(). При публикации запроса ввода-вывода SQL Server разыгрывайте запрос и сообщает длительность запроса с помощью типов ожидания. SQL Server использует типы ожидания для указания ожиданий ввода-вывода в различных местах в продукте. Связанные с вводом-выводом ожидания:

• / PAGEIOLATCH_SH PAGEIOLATCH_EX
• WRITELOG
• IO_COMPLETION
• ASYNC_IO_COMPLETION
• BACKUPIO

Если эти ожидания превышают 10-15 миллисекундах последовательно, число операций ввода-вывода считается узким местом.

Примечание.

Чтобы обеспечить контекст и перспективу, в мире устранения неполадок SQL Server microsoft CSS наблюдала случаи, когда запрос ввода-вывода занял одну секунду и более 15 секунд на передачу таких систем ввода-вывода нуждается в оптимизации. И наоборот, Microsoft CSS видел системы, где пропускная способность ниже одной миллисекунда/передачи. Благодаря современной технологии SSD/NVMe, объявленный диапазон скоростей пропускной способности в десятках микросекунд на передачу. Таким образом, 10-15 миллисекунд/передачи является очень приблизительным пороговым значением, выбранным на основе коллективного опыта между инженерами Windows и SQL Server в течение многих лет. Как правило, когда числа выходят за рамки этого приблизительного порогового значения, пользователи SQL Server начинают видеть задержку в рабочих нагрузках и сообщать о них. В конечном счете ожидаемая пропускная способность подсистемы ввода-вывода определяется производителем, моделью, конфигурацией, рабочей нагрузкой и потенциально несколькими другими факторами.

Методология

Блок-диаграмма в конце этой статьи описывает методологию использования Microsoft CSS для подхода к медленным проблемам ввода-вывода с SQL Server. Это не исчерпывающий или эксклюзивный подход, но оказался полезным в изоляции проблемы и ее разрешении.

Чтобы устранить проблему, можно выбрать один из следующих двух вариантов:

Вариант 1. Выполнение действий непосредственно в записной книжке с помощью Azure Data Studio

Примечание.

Прежде чем пытаться открыть эту записную книжку, убедитесь, что Azure Data Studio установлена на локальном компьютере. Чтобы установить его, перейдите к разделу "Узнайте, как установить Azure Data Studio".

Откройте записную книжку в Azure Data Studio

Вариант 2. Выполните действия вручную.

Методология описана в следующих шагах:

Шаг 1. Отчеты SQL Server медленного ввода-вывода?

SQL Server может сообщать о задержке ввода-вывода несколькими способами:

• Типы ожидания ввода-вывода
• Динамическое административное представление sys.dm_io_virtual_file_stats
• Журнал ошибок или журнал событий приложения

Типы ожидания ввода-вывода

Определите, существует ли задержка ввода-вывода, сообщаемая типами ожидания SQL Server. Значения и WRITELOGASYNC_IO_COMPLETION значения PAGEIOLATCH_*нескольких других менее распространенных типов ожидания должны оставаться ниже 10–15 миллисекунд на запрос ввода-вывода. Если эти значения являются более последовательными, проблема производительности ввода-вывода существует и требует дальнейшего изучения. Следующий запрос может помочь вам собрать эти диагностические сведения в системе:

#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

Чтобы просмотреть задержку на уровне файла базы данных, как указано в SQL Server, выполните следующий запрос:

#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 и LatencyAssessment столбцы, чтобы понять сведения о задержке.

Ошибка 833, сообщаемая в журнале ошибок или журнале событий приложения

В некоторых случаях в журнале ошибок может возникнуть ошибка 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) . Журналы ошибок SQL Server можно проверить в системе, выполнив следующую команду 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"

Кроме того, дополнительные сведения об этой ошибке см. в разделе MSSQLSERVER_833 .

Шаг 2. Указывает ли счетчики perfmon задержку ввода-вывода?

Если SQL Server сообщает о задержке ввода-вывода, обратитесь к счетчикам ОС. Вы можете определить, возникла ли проблема ввода-вывода, проверив счетчик Avg Disk Sec/Transferзадержки. В следующем фрагменте кода указывается один из способов сбора этих сведений с помощью PowerShell. Он собирает счетчики на всех томах диска: "_total". Перейдите на определенный том диска (например, "D:"). Чтобы найти тома, в которых размещаются файлы базы данных, выполните следующий запрос в 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"

Соберите 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"
   }

Если значения этого счетчика последовательно превышают 10–15 миллисекундах, необходимо изучить проблему дальше. Случайные пики не учитываются в большинстве случаев, но обязательно дважды проверьте длительность пиков. Если пик длился одну минуту или несколько, это больше плато, чем пик.

Если счетчики монитора производительности не сообщают о задержке, но SQL Server делает, проблема возникает между SQL Server и диспетчером секций, то есть драйверами фильтров. Диспетчер секций — это уровень ввода-вывода, в котором ОС собирает счетчики Perfmon . Чтобы устранить задержку, убедитесь в правильном исключении драйверов фильтров и устраните проблемы с драйвером фильтра. Драйверы фильтров используются программами, такими как антивирусное программное обеспечение, решения резервного копирования, шифрование, сжатие и т. д. Эту команду можно использовать для перечисления драйверов фильтров в системах и томах, к которому они присоединяются. Затем вы можете просмотреть имена драйверов и поставщиков программного обеспечения в статье "Выделенные высоты фильтра".

fltmc instances

Дополнительные сведения см. в статье "Выбор антивирусного программного обеспечения для запуска на компьютерах под управлением SQL Server".

Избегайте использования шифрования файловой системы (EFS) и сжатия файловой системы, так как они вызывают синхронную синхронизацию асинхронного ввода-вывода и, следовательно, медленнее. Дополнительные сведения см. в статье о синхронном вводе-выводе асинхронного диска в Windows .

Шаг 3. Перегружена ли подсистема ввода-вывода за пределы емкости?

Если SQL Server и ОС указывают, что подсистема ввода-вывода медленна, проверьте, перегружена ли система за пределами емкости. Вы можете проверить емкость, просмотрев счетчики Disk Bytes/Secввода-вывода, Disk Read Bytes/Secили Disk Write Bytes/Sec. Обязательно обратитесь к поставщику системного администратора или оборудования, чтобы получить ожидаемые спецификации пропускной способности для сети SAN (или другой подсистемы ввода-вывода). Например, вы можете отправлять не более 200 МБ/с ввода-вывода через карточку HBA 2 ГБ/с или выделенный порт 2 ГБ/с на коммутаторе SAN. Ожидаемая пропускная способность, определяемая производителем оборудования, определяет, как вы идете отсюда.

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. Является ли SQL Server интенсивным действием ввода-вывода?

Если подсистема ввода-вывода перегружена за пределы емкости, узнайте, является ли SQL Server виновным, просматривая Buffer Manager: Page Reads/Sec (наиболее распространенный виновник) и Page Writes/Sec (гораздо менее распространенный) для конкретного экземпляра. Если SQL Server является основным драйвером ввода-вывода и томом ввода-вывода выходит за рамки того, что система может обрабатывать, то обратитесь к командам разработчиков приложений или поставщику приложений:

• Настройте запросы, например лучшие индексы, обновление статистики, перезапись запросов и изменение базы данных.
• Увеличьте максимальную память сервера или добавьте в систему больше ОЗУ. Больше ОЗУ будет кэшировать больше данных или страниц индексов без частого чтения с диска, что приведет к снижению активности ввода-вывода. Увеличение памяти также может уменьшить Lazy Writes/sec, что обусловлено отложенными сбросами записи, когда часто требуется хранить больше страниц базы данных в доступной ограниченной памяти.
• Если вы обнаружите, что записи на странице являются источником тяжелых операций ввода-вывода, изучите Buffer Manager: Checkpoint pages/sec , связано ли это с массовыми сбросами страниц, необходимыми для удовлетворения требований к конфигурации интервала восстановления. С течением времени можно использовать косвенные контрольные точки для равномерного ввода-вывода или увеличения пропускной способности ввода-вывода оборудования.

Причины

Как правило, следующие проблемы являются высоким уровнем причин, по которым запросы SQL Server страдают от задержки ввода-вывода:

• Проблемы с оборудованием:

  • Неправильное настройка SAN (коммутатор, кабели, HBA, хранилище)

  • Превышена емкость ввода-вывода (разбалансирована во всей сети SAN, а не только в серверном хранилище).

  • Проблемы с драйверами или встроенного ПО

  Поставщики оборудования и (или) системные администраторы должны заниматься на этом этапе.

• Проблемы с запросами: SQL Server насыщает тома дисков с запросами ввода-вывода и отправляет подсистему ввода-вывода за пределы емкости, что приводит к высокой скорости передачи ввода-вывода. В этом случае решение состоит в том, чтобы найти запросы, которые вызывают большое количество логических операций чтения (или записи) и настроить эти запросы для минимизации операций ввода-вывода диска с использованием соответствующих индексов является первым шагом для этого. Кроме того, следует обновлять статистику по мере предоставления оптимизатора запросов достаточной информации, чтобы выбрать лучший план. Кроме того, неправильный дизайн базы данных и проектирование запросов может привести к увеличению проблем ввода-вывода. Таким образом, изменение запросов и иногда таблиц может помочь в улучшении ввода-вывода.

• Драйверы фильтров. Ответ ввода-вывода SQL Server может быть сильно затронут, если драйверы фильтра файловой системы обрабатывают большой трафик ввода-вывода. Для предотвращения влияния на производительность ввода-вывода рекомендуется правильное исключение из антивирусного сканирования и правильного проектирования драйвера фильтра поставщиками программного обеспечения.

• Другие приложения. Другое приложение на том же компьютере с SQL Server может насыщать путь ввода-вывода чрезмерными запросами на чтение или запись. Эта ситуация может привести подсистему ввода-вывода за пределы емкости и привести к замедлению ввода-вывода для SQL Server. Определите приложение и настройте его или переместите его в другое место, чтобы устранить его влияние на стек ввода-вывода.

Графическое представление методологии

Сведения о типах ожидания, связанных с вводом-выводом

Ниже приведены описания распространенных типов ожидания, наблюдаемых в SQL Server, когда сообщаются проблемы с диском ввода-вывода.

PAGEIOLATCH_EX

Происходит, когда задача ожидает блокировки данных или страницы индекса (буфера) в запросе ввода-вывода. Запрос на блокировку находится в эксклюзивном режиме. Монопольный режим используется при записи буфера на диск. Длительное время ожидания может указывать на проблемы с дисковой подсистемой.

PAGEIOLATCH_SH

Происходит, когда задача ожидает блокировки данных или страницы индекса (буфера) в запросе ввода-вывода. Запрос на блокировку находится в режиме общего доступа. Общий режим используется при чтении буфера с диска. Длительное время ожидания может указывать на проблемы с дисковой подсистемой.

PAGEIOLATCH_UP

Происходит, когда задача ожидает блокировки буфера в запросе ввода-вывода. Запрос на блокировку находится в режиме обновления. Длительное время ожидания может указывать на проблемы с дисковой подсистемой.

WRITELOG

Происходит, когда задача ожидает завершения очистки журнала транзакций. Очистка возникает, когда диспетчер журналов записывает временное содержимое на диск. Распространенные операции, которые вызывают очистку журналов, являются фиксациями транзакций и контрольными точками.

Распространенные причины длительных ожиданий WRITELOG :

• Задержка диска журнала транзакций: это наиболее распространенная причина WRITELOG ожидания. Как правило, рекомендация заключается в сохранении файлов данных и журналов в отдельных томах. Записи журнала транзакций являются последовательными при чтении или записи данных из файла данных случайным образом. Перемешивание данных и файлов журналов на одном томе диска (особенно обычных дисках спиннинга) приведет к чрезмерному перемещению головы диска.

• Слишком много VLFs: слишком много виртуальных файлов журналов (VLFs) может вызвать WRITELOG ожидание. Слишком много VLFs может привести к другим типам проблем, таких как длительное восстановление.

• Слишком много небольших транзакций: в то время как большие транзакции могут привести к блокировке, слишком много небольших транзакций может привести к другому набору проблем. Если вы явно не начинаете транзакцию, какие-либо вставки, удаления или обновления приведет к транзакции (мы называем эту автоматическую транзакцию). Если вы выполняете 1000 вставок в цикл, будет создано 1000 транзакций. Каждая транзакция в этом примере должна зафиксироваться, что приводит к очистке журнала транзакций и очистке 1000 транзакций. По возможности группировать отдельное обновление, удаление или вставку в большую транзакцию, чтобы уменьшить потоки журналов транзакций и повысить производительность. Эта операция может привести к меньшему WRITELOG ждать.

• Проблемы с планированием приводят к тому, что потоки записи журналов не могут быть запланированы достаточно быстро: до SQL Server 2016 один поток записи журналов выполнил все записи журналов. Если возникли проблемы с планированием потоков (например, высокой ЦП), поток записи журналов и очистки журналов могут быть отложены. В SQL Server 2016 добавлены до четырех потоков записи журналов, чтобы увеличить пропускную способность записи журналов. См. статью SQL 2016— просто выполняется быстрее: несколько рабочих ролей записи журналов. В SQL Server 2019 добавлены до восьми потоков записи журналов, что повышает пропускную способность еще больше. Кроме того, в SQL Server 2019 каждый обычный рабочий поток может выполнять запись журналов непосредственно вместо публикации в поток записи журналов. С этими улучшениями WRITELOG ожидания редко будут запускаться с помощью проблем планирования.

ASYNC_IO_COMPLETION

Происходит, когда выполняются некоторые из следующих действий ввода-вывода:

• Поставщик массового вставки ("Insert Bulk") использует этот тип ожидания при выполнении операций ввода-вывода.
• Чтение файла отмены в LogShipping и направление асинхронного ввода-вывода для доставки журналов.
• Чтение фактических данных из файлов данных во время резервного копирования данных.

IO_COMPLETION

Имеет место при ожидании завершения операций ввода-вывода. Этот тип ожидания обычно включает операции ввода-вывода, не связанные с страницами данных (буферами). Вот некоторые примеры.

• Чтение и запись результатов сортировки и хэша на диск во время разлива (проверка производительности хранилища tempdb ).
• Чтение и запись готовых spools на диск (проверка хранилища tempdb ).
• Чтение блоков журнала из журнала транзакций (во время любой операции, из-за которой журнал считывается с диска , например, восстановление).
• Чтение страницы с диска, когда база данных еще не настроена.
• Копирование страниц в моментальный снимок базы данных (копирование по записи).
• Закрытие файла базы данных и распаковки файлов.

BACKUPIO

Происходит, когда задача резервного копирования ожидает данных или ожидает буфера для хранения данных. Этот тип не является типичным, за исключением случаев, когда задача ожидает подключения ленты.