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

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

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

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

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

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

PAGEIOLATCH_SH / PAGEIOLATCH_EX
WRITELOG
IO_COMPLETION
ASYNC_IO_COMPLETION
BACKUPIO

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

Примечание.

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

Методология

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

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

Шаг 1: Сообщает ли SQL Server о медленной работе ввода-вывода?

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

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

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

Определите, существует ли задержка ввода-вывода, сообщаемая типами ожидания SQL Server. Значения PAGEIOLATCH_*, WRITELOG и ASYNC_IO_COMPLETION, а также значения нескольких других менее распространенных типов ожидания должны, как правило, оставаться ниже 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, обусловленные сбросами записи, выполняемыми Lazy Writer, когда часто возникает необходимость хранить больше страниц базы данных в условиях ограниченной доступной памяти.
Если вы обнаружите, что записи на страницах являются источником тяжелых операций ввода-вывода, изучите Buffer Manager: Checkpoint pages/sec, чтобы понять, связано ли это с массовыми сбросами страниц, необходимыми для удовлетворения требований конфигурации интервала восстановления. С течением времени можно использовать косвенные контрольные точки для равномерного ввода-вывода или увеличения пропускной способности ввода-вывода оборудования.

Причины

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

Проблемы с оборудованием:
- Неправильная настройка SAN (коммутатор, кабели, HBA, хранилище)
- Превышена емкость ввода-вывода (разбалансирована во всей сети SAN, а не только в серверном хранилище).
- Проблемы с драйверами или встроенного ПО
Поставщики оборудования и/или системные администраторы должны быть вовлечены на данном этапе.
Проблемы с запросами: SQL Server насыщает тома дисков с запросами ввода-вывода и отправляет подсистему ввода-вывода за пределы емкости, что приводит к высокой скорости передачи ввода-вывода. В этом случае решение состоит в том, чтобы найти запросы, которые вызывают большое количество логических операций чтения (или записи) и настроить эти запросы для минимизации операций ввода-вывода диска с использованием соответствующих индексов является первым шагом для этого. Кроме того, следует обновлять статистику по мере предоставления оптимизатора запросов достаточной информации, чтобы выбрать лучший план. Кроме того, неправильный дизайн базы данных и проектирование запросов может привести к увеличению проблем ввода-вывода. Таким образом, изменение запросов и иногда таблиц может помочь в улучшении ввода-вывода.
Драйверы фильтров. Ответ ввода-вывода 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).
Чтение и запись спулов с предварительной загрузкой на жесткий диск. Проверьте хранилище tempdb.
Чтение блоков журнала из журнала транзакций (во время любой операции, из-за которой журнал считывается с диска , например, восстановление).
Чтение страницы с диска, когда база данных еще не настроена.
Копирование страниц в снимок базы данных (копирование при записи).
Закрытие файла базы данных и распаковка файлов.

BACKUPIO

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

Кері байланыс

Бет пайдалы болды ма?

Last updated on 2025-01-10