Устранение неполадок с низкой производительностью 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_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.
Вариант 2. Выполните действия вручную
Методология описана в следующих шагах:
Шаг 1. Сообщает ли SQL Server о медленном вводе-выводе?
SQL Server может сообщать о задержке ввода-вывода несколькими способами:
- Типы ожидания ввода-вывода
- DMV
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 МБ/с ввода-вывода через 2 ГБ/с HBA карта или 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 является main драйвером ввода-вывода, а том ввода-вывода выходит за рамки того, что может обрабатывать система, обратитесь к группам разработчиков приложений или поставщику приложений, чтобы:
- Настройка запросов, например улучшение индексов, обновление статистики, перезапись запросов и изменение базы данных.
- Увеличьте максимальное количество памяти сервера или увеличьте объем ОЗУ в системе. Больше ОЗУ будет кэшировать больше страниц данных или индексов без частого повторного считывания с диска, что снизит активность ввода-вывода.
Причины
Как правило, следующие проблемы являются общими причинами, по которым 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ожиданиям. Слишком много 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).
- Чтение журналов блокируется из журнала транзакций (во время любой операции, которая вызывает чтение журнала с диска, например при восстановлении).
- Чтение страницы с диска, если база данных еще не настроена.
- Копирование страниц в базу данных snapshot (копирование при записи).
- Закрытие файла базы данных и распаковка файла.
BACKUPIO
Происходит, когда задача резервного копирования ожидает данных или ожидает буфера для хранения данных. Этот тип не является типичным, за исключением случаев, когда задача ожидает подключения на ленте.
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по