Диагностика в SQL Server помочь в обнаружении запавшая и приостановке операций ввода-вывода

Применимо к: SQL Server 2014 Business IntelligenceSQL Server 2014 Business IntelligenceSQL Server 2014 Developer

Обзор


Система управления базами данных (СУБД), например, SQL Server использует своевременность файл ввода-вывода (I/O) операций. Один из следующих элементов может привести к приостановке или запавшая операций ввода-вывода и отрицательно сказаться на производительности и скорости отклика SQL Server:

  • Неисправность оборудования
  • Неправильно настроенное оборудование
  • Настройки встроенного по
  • Драйверы фильтра
  • Сжатие
  • Ошибки
  • Другие условия в пути ввода-вывода
Эти проблемы ввода-вывода может вызвать возникает следующее поведение:

  • Блокировка
  • Конфликтов кратковременной блокировки и тайм-ауты
  • Медленное время отклика
  • Растяжение границ ресурсов
Начиная с Microsoft SQL Server 2000 Пакет обновления 4 (SP4), SQL Server содержит логику, которая помогает обнаружить неполадки и застряла условия для операций чтения ввода-вывода базы данных и записи и чтения ввода-вывода файлов журнала и записи. Когда была ожидающие операции ввода-вывода, 15 секунд или больше, SQL Server выполняет следующие действия:

  1. Определяет, что был ожидающие операции
  2. Записывает информационное сообщение в журнал ошибок SQL Server

    Текст сообщения журнала будет выглядеть примерно так:

Объяснение информационное сообщение

Текст сообщенияОписание
<Номер > случаев выполненияЧисло запросов ввода-вывода, которые не удалось завершить чтение или операция записи в менее чем на 15 секунд.
Сведения о файлахПолное имя файла, имя базы данных и идентификатор (DBID) базы данных.
МаркерДескриптор файла операционной системы. Дескриптор операционной системы можно использовать с отладчиками или других программ для отслеживания запросов (IRP) пакет запроса ввода-вывода.
Корр.Застрявшая смещение последней операции ввода-вывода или остановлены последней операции ввода-вывода. Можно использовать смещение с отладчиками или других программ для отслеживания запросы IRP.

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

  • Рабочая нагрузка превышает возможности пути ввода-вывода.
  • Рабочая нагрузка превышает возможности текущей системы.
  • Пути ввода-вывода имеет неработающее программное обеспечение; Возможно встроенного программного обеспечения или драйвера проблемы.
  • Пути ввода-вывода с неисправными компонентами оборудования.
Дополнительные сведения о шаблонах ввода-вывода SQL Server 2000 посетите следующий веб-узел корпорации Майкрософт:Примечание. В данной статье TechNet также применяется к Microsoft SQL Server 2005 и более поздних версий.

Дополнительные сведения


Запавшая ввода-вывода и приостановке операций ввода-вывода

Запавшая ввода/вывода

Запавшая ввода-вывода — это запрос ввода/вывода, не завершена. Как правило запавшая ввода-вывода Указывает запавшая IRP. Для устранения запавшая условие ввода-вывода, обычно необходимо перезагрузить компьютер или выполнить аналогичные действия. Запавшая условие ввода-вывода обычно указывает одно из следующих:

  • Неисправность оборудования
  • Ошибка в компоненте пути ввода-вывода

Невыполненный ввода/вывода

Невыполненный ввода-вывода — это запрос ввода-вывода, Готово или который занимает чрезмерное время. Невыполненный ввода-вывода обычно происходит по одной из следующих причин:

  • Конфигурации оборудования
  • Настройки встроенного по
  • Проблему драйвер фильтра, которая необходима помощь от оборудования или поставщику программного обеспечения для отслеживания и устранения

SQL Server остановлены ввода-вывода и застряла ввода-вывода, запись и создание отчетов

Поддержка Microsoft SQL Server обрабатывает много случаев каждый год, включающих запавшая или приостановке проблем ввода-вывода. Эти проблемы ввода/вывода отображаются по-разному. Проблем ввода-вывода являются некоторые из наиболее сложных проблем для диагностики и отладки, и они требуют значительных затрат времени и ресурсов для отладки корпорации Майкрософт и клиента. Функции отчетности, добавленные в SQL Server 2000 SP4 и более поздних версий значительно сократить время, необходимое для идентификации ошибка ввода-вывода.

Отчеты и записи запросов ввода-вывода предназначены для каждого файла по отдельности. Обнаружение и отчетов о приостановке и запавшая запросов ввода-вывода — это два отдельных действия.

Запись

Существуют два позже, когда происходит запись действий в SQL Server. Во-первых, по завершении операции ввода-вывода фактически. Если запрос ввода/вывода использует более чем на 15 секунд для завершения, происходит операция записи. Второй момент — при выполнении отложенной записи. При выполнении отложенной записи, отложенной записи проверяет все отложенные данные и все журнала, ожидающие запросы ввода-вывода файла. Если превышено пороговое значение 15 секунд, происходит операция записи.

Отчетность

Отчетов происходит в интервалах, 5 минут или более друг от друга. Отчетов происходит при следующего запроса ввода-вывода в файл. Если произошло действие записи и 5 минут или более прошедших произошла последнего отчета, в журнал ошибок SQL Server записывается информационное сообщение, описанное в разделе «Аннотация».

Пороговое значение 15 секунд не является настраиваемым. Тем не менее приостановке или запавшая обнаружение ввода/вывода можно отключить с помощью флага трассировки 830, несмотря на то, что мы не рекомендуем это сделать.

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

Инструкция DBCC traceoff (830, -1)
Этот параметр действует только в течение времени жизни процесса SQL Server.

Примечание. Запрос ввода/вывода, становится остановлены или приклеивается выводится только один раз. Например если в сообщении указывается, что будут остановлены 10 запросов ввода-вывода, эти 10 отчеты не произойдет снова. Если следующее сообщение о том, что 15 запросов ввода-вывода будут остановлены, это означает, что становятся остановлены 15 новых запросов ввода-вывода.

Отслеживание пакет запроса ввода-вывода (IRP)

SQL Server использует стандартные вызовы Microsoft Windows API для чтения и записи данных. Например SQL Server использует следующие функции:

  • WriteFile
  • ReadFile
  • WriteFileScatter
  • ReadFileGather
Запрос чтения или записи обрабатывается операционной системой Windows как пакет запроса ввода-вывода (IRP). Для определения состояния запроса ввода-ВЫВОДА, можно используйте следующие:

  • Поддержка платформы Microsoft помощника
  • Отладчик ядра
Дополнительные сведения о запроса ввода-ВЫВОДА и ВЫВОДА трассировки, перейдите на следующий веб-узел Майкрософт и поиска по ключевому слову «IRP»:Примечание. Ядро отладки может быть Агрессивный процесс, поскольку отладки ядра может потребовать остановки системы для завершения отладки действия. Рекомендуется проверять наличие доступных обновлений для следующих элементов:

  • BIOS
  • Встроенное по
  • Другие компоненты пути ввода-вывода
Обратитесь к поставщикам оборудования, перед выполнением дополнительных действий для отладки. Сеанс отладки Нередко бывает связана с стороннего драйвера, микропрограммы или компонент драйвера фильтра.

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

Общая производительность системы может играют ключевую роль в обработке ввода-вывода. Общие работоспособности системы следует принять во внимание при изучении отчеты о приостановке или запавшая операций ввода-вывода. Чрезмерных нагрузок может привести к общей системы как медленное. Это включает в себя обработку операций ввода-вывода. Поведение системы в то время, проблема может быть ключевым фактором определения основной причины проблемы. Например если становится высокая загрузка ЦП или ЦП остается высокой, когда возникает проблема, это может означать использования процесса в системе большого количества ЦП что неблагоприятного воздействия других процессов.

Счетчики производительности

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

  • Средняя Disk Sec/Transfer
  • Средняя длина очереди диска
  • Текущая длина очереди диска
Например среднее Disk Sec/Transfer времени на компьютере, на котором выполняется SQL Server является обычно не превышает 15 миллисекунд. Если покоряет значение Средняя Disk Sec/Transfer, это означает, что подсистема ввода-вывода является не справляется с запросом ввода-вывода.

Будьте внимательны при использовании счетчиков производительности, так как SQL Server использует все преимущества возможностей асинхронного ввода-вывода, сильно push длина очереди диска. Таким образом больше времени длина очереди диска отдельно не указывает на проблему.

В системном мониторе Windows можно просмотреть счетчики «физический диск: диск байт/сек» для каждого влияет на диске и сравните скорость работы со счетчиками «процесс: ввода-ВЫВОДА данных байт/сек» и «процесс: ввода-ВЫВОДА других байт/сек» каждого процесса для идентификации ли определенный набор процессов создает чрезмерное ввода-вывода запросов. Существуют различные I/O связанных объектов счетчиков, доступных в процессе, раскрывает более подробные сведения. Если выяснится, что экземпляр SQL Server является ответственным за чрезмерной нагрузки ввода-ВЫВОДА на сервере, просмотрите следующий подраздел в «Индексы и параллелизма». Подробное обсуждение на обнаружение и Устранение узких мест ввода-вывода просмотрите раздел «Узкие места ввода-вывода» в документе MSDN Устранение проблем с производительностью в SQL Server 2008 или Устранение проблем с производительностью в SQL Server 2005.

Индексы и параллелизм

Как правило ввода-вывода образуются, так как отсутствует индекс. Такое поведение может серьезно push пути ввода-вывода. Этап, который использует мастер включения индекса (ITW) может помочь устранить недостаток ввода-вывода. Если запрос использует преимущества из индекса вместо просмотра таблицы или возможно он использует сортировку или хэш-, система может получить следующие преимущества:

  • Уменьшение физического ввода-вывода, необходимые для выполнения действия, непосредственно создает преимущества в производительности запроса производится.
  • Нужно вернуть меньше страниц в кэше данных. Таким образом эти страницы, находящиеся в кэше данные оставались актуальными для активных запросов.

  • Сортировка и хэш-коды используются, потому что индекс может отсутствовать или статистические данные являются устаревшими. Использование базы данных tempdb и конфликт может снизить путем добавления одного или нескольких индексов.
  • Сокращение производится в ресурсы и параллельных операций. Поскольку SQL Server не гарантирует параллельного выполнения запроса, и считается нагрузку на систему, лучше оптимизировать все запросы для последовательного выполнения. Для оптимизации запроса, откройте анализатор запросов и параметр max degree of parallelism хранимой процедуры sp_configure значение равным 1. Если все запросы настроены для запуска незамедлительно последовательной операции, параллельное выполнение чаще всего наилучшего результата. Тем не менее многие время параллельного выполнения выбирается из-за большой объем данных. Для отсутствующих индексов большой сортировки может потребоваться происходят. Скорость реакции создаст несколько сотрудников, которые выполняют операции сортировки. Однако это действие может значительно увеличить нагрузку на систему. Большие запросы чтения из многих сотрудников может привести к пакетов ввода-вывода с повышенной загрузке ЦП из нескольких работников. Много раз запрос может быть настроена работать быстрее и использовать меньше ресурсов Если добавляется индекс или возникает другое действие настройки.

Практические примеры из службы поддержки Microsoft SQL Server

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

Пример 1: Записи в журнал, застрял на 45 секунд

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

Приложение отправлен commit, чтобы SQL Server и фиксации стал работать как ожидающие записи журнала. В результате запрос продолжать удерживать блокировки и блокирования входящих запросов от других клиентов. Затем другие клиенты начните тайм-аута. Это усложняет проблему, так как приложение не выполняет откат открытых проводок при возникновении тайм-аута запроса. Это создает сотни открытых проводок, которые, удерживающих блокировки. Таким образом возникает серьезная ситуации блокирующие.

Дополнительные сведения об операции обработки и блокирование обратитесь к следующей статье Microsoft Knowledge Base:

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

Приблизительно 45 секунд после завершения записи журнала. Тем не менее в настоящее время сотен подключений включаются в резервную копию. Проблемы с блокировками привести несколько минут времени восстановления SQL Server и приложения. В сочетании с проблемы приложения, приостановке операций ввода-вывода имеет очень отрицательно влияет на работу системы.
Решение
Эта проблема была отслежена запавшая запрос ввода-вывода драйвера адаптера шины хоста (HBA). Компьютер имеет несколько адаптеров HBA с поддержкой переключения. Когда один адаптер HBA отставала от или не было связи с сети хранения (SAN), значение тайм-аута «повторить до перехода на другой ресурс» было настроено на 45 секунд. Если тайм-аут превышен, запроса ввода-вывода было перенаправлено к второй HBA. Второй адаптер главной ШИНЫ обрабатывается запрос и быстро завершена. Чтобы предотвратить такие условия машиноместа изготовителя оборудования рекомендуется использовать параметр «повторить до перехода на другой ресурс» 5 секунд.

Пример 2: Вмешательство фильтров

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

Такое условие было драйвер фильтра для резервного копирования обработки, разрешенный резервную копию файлов, которые были открыты при с момента резервного копирования. Системный администратор содержит каталог файлов данных SQL Server в выбранных файлов. Если с момента резервного копирования, резервного копирования пытался получить правильное изображение из файла на момент начала резервного копирования. При этом отложенные запросы ввода-вывода. Запросы ввода-вывода было разрешено завершить только одной записи за раз, как они были обработаны программой.

При запуске резервного копирования производительности SQL Server удалить существенно, поскольку операции ввода-вывода SQL Server были вынуждены завершить по одному. Проблема усугубляется, логика «по одному» был таким образом, что операции ввода-вывода не может быть выполнена асинхронно. Таким образом когда SQL Server требуется для отправки запроса на ввод-вывод и продолжить работу, работник застряла в вызов записи или чтения до завершения запроса. Обработка задач, таких как SQL Server опережающего считывания эффективно отключено по действия драйвера фильтра. Кроме того другая ошибка в драйвере фильтра слева действия «по одному» в процессе, даже после завершения резервного копирования. Единственный способ восстановить производительность SQL Server было закройте и снова откройте базу данных или перезапустить SQL Server, чтобы дескриптор файла был выпущен и была получена без участия драйвер фильтра.
Решение
Для решения этой проблемы, процесс резервного копирования файла были удалены файлы данных SQL Server. Производство программного обеспечения также исправлена проблема, оставить файл в режиме «по одному».

Пример 3: Скрытые ошибки

Множество высокопроизводительных систем имеют многоканальный пути ввода-вывода для обработки балансировки нагрузки или подобные действия. Службу технической поддержки Майкрософт обнаружила проблемы с программами, когда происходит сбой запроса ввода-вывода, но программное обеспечение неправильно обрабатывает ошибку балансировки нагрузки. Программное обеспечение может попытаться бесконечных повторов. Операции ввода-вывода перестает отвечать, а SQL Server не удается завершить указанное действие. Подобно журнала написать условие, которое было описано выше, во многих случаях поведение плохой системы может произойти после таких условий wedges системы.
Решение
Для решения этой проблемы, перезапуска SQL Server часто является обязательным. Тем не менее иногда необходимо перезагрузить операционную систему для восстановления обработки. Мы также рекомендуем получить обновления программного обеспечения от поставщика ввода-вывода.

Пример 4: Внешнее хранилище, зеркальное отображение и дисков raid

Во многих системах использования зеркального отображения или выполнить аналогичные шаги для предотвращения потери данных. Некоторые системы, использующие зеркального отображения основаны на программное обеспечение и некоторые аппаратные. Ситуация, которая обычно выявляются службой поддержки корпорации Майкрософт для этих систем является увеличение задержки.

Увеличение общего времени ввода-вывода происходит, когда необходимо завершить ввод-вывод на зеркальный сервер перед завершением ввода-вывода. Для установки удаленного зеркало можно вовлечены сети повторов. Когда происходят отказы жесткого диска и восстановление системы raid, шаблон ввода/вывода также может быть прервано.
Решение
Строгие настройки необходимы для уменьшения задержки для зеркала или операций перестроения raid.


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

Пример 5: сжатие

Корпорация Майкрософт не поддерживает Microsoft SQL Server 7.0 или Microsoft SQL Server 2000 файлов данных и файлов журнала на сжатых дисках. Сжатие NTFS не является безопасным для SQL Server, поскольку сжатие NTFS нарушает протокол записи вперед ведение журнала Упреждающей записью. Сжатие NTFS также требует повышения обработки для каждой операции ввода-вывода. Сжатие «по одному» создается как поведение, которое вызывает существенное снижение производительности возникает.
Решение
Чтобы устранить эту проблему, распакуйте данные и файлы журнала.

Для получения дополнительных сведений просмотрите Описание поддержки для баз данных SQL Server на сжатых томах .

Дополнительные данные точек

PAGEIOLATCH_ * и writelog ожидания в sys.dm_os_wait_stats динамические административные представления (DMV) являются индикаторами ключа для исследования производительности пути ввода-вывода. Если значительных задержек в PAGEIOLATCH, это означает, что SQL Server ожидает подсистемы ввода-вывода. Некоторое PAGEIOLATCH ожидания обычная и ожидаемое поведение. Тем не менее если среднее время ожидания PAGEIOLATCH постоянно превышает 10 миллисекунд (мс), следует изучить, почему давление выполняется подсистемой ввода/вывода. Дополнительные сведения приведены в следующих источниках:



SQL Server требует, что системы поддерживают «гарантированная доставка стабильной носитель», как описано в разделе Требования программы надежность SQL Server ввода/вывода. Дополнительные сведения о требованиях к входным и выходным компонент SQL Server database engine, перейдите к следующей статье Microsoft Knowledge Base: