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

Статья
06/19/2024

Применяется к: 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_SHPAGEIOLATCH_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. Значения 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которые управляются отложенными модулями записи, когда часто требуется хранить больше страниц базы данных в ограниченной доступной памяти.
Если вы обнаружите, что запись страниц является источником интенсивных операций ввода-вывода, проверьте 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 ожиданиям. Слишком много VLF может вызвать другие типы проблем, например длительное восстановление.
Слишком много небольших транзакций. В то время как крупные транзакции могут привести к блокировке, слишком много мелких транзакций может привести к еще одному набору проблем. Если явно не начать транзакцию, любая вставка, удаление или обновление приведет к транзакции (мы называем эту автоматическую транзакцию). Если выполнить 1000 вставок в цикле, будет создано 1000 транзакций. Каждая транзакция в этом примере должна зафиксироваться, что приводит к очистке журнала транзакций и 1000 транзакций. По возможности группирование отдельных обновлений, удаления или вставки в большую транзакцию, чтобы уменьшить количество сбросов журналов транзакций и повысить производительность. Эта операция может привести к меньшему количеству WRITELOG ожиданий.
Проблемы с планированием приводят к тому, что потоки модуля записи журналов не запланировались достаточно быстро. До SQL Server 2016 один поток записи журналов выполнял все записи журналов. Если возникли проблемы с планированием потоков (например, с высоким ЦП), поток записи журналов и сброс журналов могут быть отложены. В SQL Server 2016 было добавлено до четырех потоков записи журналов, чтобы увеличить пропускную способность записи журналов. См. статью SQL 2016 — просто работает быстрее: несколько рабочих ролей записи журналов. В SQL Server 2019 было добавлено до восьми потоков записи журналов, что еще больше повышает пропускную способность. Кроме того, в SQL Server 2019 каждый обычный рабочий поток может выполнять запись журналов напрямую, а не отправлять их в поток модуля записи журналов. Благодаря этим улучшениям WRITELOG ожидания редко запускаются из-за проблем с планированием.

ASYNC_IO_COMPLETION

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

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

IO_COMPLETION

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

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

BACKUPIO

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

Поделиться через

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

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

Методология

Вариант 1. Выполнение шагов непосредственно в записной книжке через Azure Data Studio

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

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

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

Статистика файлов в sys.dm_io_virtual_file_stats

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

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

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

Шаг 4. Является ли SQL Server движущей силой операций ввода-вывода?

Причины

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

PAGEIOLATCH_EX

PAGEIOLATCH_SH

PAGEIOLATCH_UP

WRITELOG

ASYNC_IO_COMPLETION

IO_COMPLETION

BACKUPIO

Обратная связь

Дополнительные ресурсы

Поделиться через

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

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

Методология

Вариант 1. Выполнение шагов непосредственно в записной книжке через Azure Data Studio

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

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

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

Статистика файлов в sys.dm_io_virtual_file_stats

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

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

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

Шаг 4. Является ли SQL Server движущей силой операций ввода-вывода?

Причины

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

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

PAGEIOLATCH_EX

PAGEIOLATCH_SH

PAGEIOLATCH_UP

WRITELOG

ASYNC_IO_COMPLETION

IO_COMPLETION

BACKUPIO

Обратная связь

Дополнительные ресурсы