Поскольку на рынке продолжают появляться новые устройства и решения для дисковых хранилищ, условия, в которых работает Microsoft SQL Server, стали чрезвычайно разнообразным. Чтобы гарантировать целостность данных, которые обслуживает SQL Server, очень важно, что бы подсистема I/O обладала соответствующими функциональными возможностями.
Цель этой статьи состоит в том, чтобы описать требования к I/O для операций с файлами баз данных SQL Server, на основании чего поставщики решений и пользователи могли бы проанализировать и оптимизировать свои системы с SQL Server.

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

Microsoft Knowledge Base и Microsoft SQL Server Books Online (BOL) содержат много подробной информации, связанной с операциями I/O в SQL Server. В тексте, при необходимости, автором указываются ссылки на информацию, которая важна для понимания предлагаемого в этой статье материала.
Важно: Автор рекомендует полностью ознакомиться с материалом, на который он ссылается, перед тем, как Вы начнёте изучать следующие за ссылкой главы.
Справочная система Books Online (BOL): Все ссылки на BOL, используемые в этой статье, взяты из Microsoft SQL Server 2000 Books Online (обновлённая редакция, с учётом новшеств SP3), который можно скачать по этой ссылке: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp. Если в открывшейся странице нажать на ссылку Product Documentation, можно выбрать альтернативный вариант просмотра BOL в интерактивном режиме.

Терминология

В этой главе представлены наиболее распространённые термины, которые используются в этой статье и в других документах, ссылки на которые автор использует по ходу изложения материала.

Требования ACID

Это аббревиатура от Atomicity, Consistency, Isolation и Durability, которая применяется для описания основных требований к SQL Server по организации транзакций, предъявляемых к надежным серверам баз данных.
Атомарность: Atomicity - транзакция должна быть атомарной для исполняемого блока команд. Или все её изменения данных будут исполнены, или не будет исполнено ни одно из них.
Последовательность: Consistency - после завершения транзакции данные должны оставаться в непротиворечивом состоянии. В реляционной базе данных, должны быть предприняты все меры к тому, чтобы поддерживать целостность данных во время исполнения модифицирующих данные транзакций. Все внутренние структуры данных, например: бинарные деревья индексов или дважды связанные списки, не должны нарушаться после завершения транзакции.
Изоляция: Isolation - изменения, сделанные в параллельных транзакциях, должны быть изолированы от изменений, сделанных другими исполняемыми параллельно транзакциями. Транзакция должна видеть данные в том состоянии, какими они были до изменения их другой параллельной транзакцией, или в том состоянии, в каком данные окажутся после завершения второй транзакции, но не должна видеть их промежуточное состояние. Всё это называется сериализацией, потому что предоставляет системе возможность перезагружать изначальные данные и повторять серию транзакций, чтобы в результате данные оказались в том состоянии, в каком они должны быть после исполнения первичной транзакции.
Неизменность: Durability - после того, как транзакция завершена, результаты её работы в системе не должны изменяться. Изменения должны сохраняться даже в случае отказа системы.

Write-Ahead Logging протокол (WAL)

Write-Ahead Logging - Упреждающее журналирование является ключевым методом обеспечения требований ACID. WAL позволяет обеспечить сброс на диск записей из журнала транзакций, относящихся к изменениям данных, раньше того, как будут сброшены на диск сами эти изменённые страницы данных. Ниже, в этой статье, механика WAL будет описана более подробно.

Метка времени

Зафиксированное на заданный момент времени состояние, как будто время было остановлено в этот момент.

Долговременные носители

Долговременные носители часто путают с физической памятью. SQL Server использует долговременные носители в качестве памяти, которая может пережить рестарт системы или её отказ. Долговременными носителями обычно считают память физического диска, однако к ним относятся и другие устройства, а также некоторые средства кэширования. Многие высокопроизводительные дисковые подсистемы имеют высокоскоростные средства кэширования, позволяющие уменьшить время ожидания для операций чтения и записи. Этот кэш часто резервируется и обладает автономным питанием от собственной батареи. Автономная батарея обеспечивает питание и соответственно сохранность данных в кэше до нескольких дней, но реализация такого резервирования отличается у разных производителей. Производители могут поставлять батареи опционально, чтобы увеличить жизнь кэша, если это необходимо заказчику.
Идея такого решения состоит в том, что бы после того, как проблема с системой будет устранена, сохранённые в кэше записи отработались бы так, как будто никогда не было отказа или рестарта. Реализация такого решения у большинства производителей подразумевает немедленный сброс на диск содержимого кэша, в то время, пока система будет перезапускаться.
Ниже представлены примеры ситуаций успешного разрешения описанных выше проблем, с которыми реально сталкивается персонал Microsoft SQL Server Product Support Services.

Пример 1: Аппаратный отказ (материнская плата)
У контроллера была встроенная батарея для защиты кэша данных. Был проинсталлирован новый компьютер, и к нему были подключены контроллер с подсистемой I/O. Во время старта, контроллер сбросил на диск всё содержимое кэша, после чего команда DBCC показала отсутствие проблем с целостностью данных.

Пример 2: Отказ электропитания
Батарейка на контроллере позволила сохранить данные кэша в течение четырех дней (с заменой батареек), пока электропитание не было восстановлено к норме. Во время старта, контроллер сбросил на диск всё содержимое кэша, после чего команда DBCC показала отсутствие проблем с целостностью данных.

Важно: Необходимо всегда консультироваться с поставщиком/изготовителем аппаратных средств для того, что бы выбрать правильную стратегию использования долговременных носителей.

Если происходит сбой аппаратных средства или питания, после восстановления Microsoft рекомендует обязательно выполнить полную проверку данных командой DBCC CHECKDB и всегда иметь полный набор резервных копий, что позволит гарантировать целостность данных.

Порядок записи

Порядок записи или последовательность записи (write ordering / write dependency) - это сохранение подсистемой I/O порядка операций ввода-вывода. Как было сказано ранее, долговременные носители могут использовать кэширование. Если отследить метки времени, долговременные носители должны обеспечить порядок их сохранения для операций I/O.
Порядок операций I/O, связанных с SQL Server, должен строго соблюдаться. Система должна обеспечить соответствующее упорядочение записи, иначе она нарушит протокол WAL, который подробно будет описан ниже (записи в журнале должны сохраняться в правильном порядке, и они всегда должны сохранятся на устройстве долговременного носителя до сохранения страниц данных, которые соответствуют эти записям в журнале). После того, как записи из transaction log будут успешно сброшены на диск, связанные с ними страницы данных тоже могут быть туда записаны. Если подсистема ввода-вывода разместит страницы данных на долговременном носителей раньше, чем записи журнала, целостность данных может быть нарушена.
Например, если компьютер, на котором работает SQL Server, будет перезагружен после того, как страницы данных сохранятся на долговременном носителе, но до того, как туда попадут соответствующие им записи журнала, во время следующего запуска процесса реорганизации (recovery) для базы данных - могут возникнуть ошибки. Поскольку в журнале не будут обнаружены записи об изменении страниц, процесс recovery не сможет определить для этих страниц реальное состояние транзакций. Поскольку записи журнала не сохранена на диск долговременного носителя, процесс recovery не сможет определить, что эти страницы должны быть откачены к предыдущему состоянию, и не будет пытаться исправить эту проблему, оставляя, таким образом, базу данных в несогласованном состоянии.

Многоканальные и балансирующие нагрузку системы

Многие высокопроизводительные дисковые подсистемы балансируют нагрузку по нескольким, имеющимся каналам передачи запросов I/O. Эти системы должны обеспечивать порядок следования запросов I/O. Многие из таких систем поддерживают порядок I/O за счёт использования кэша долговременного носителя и последующего комбинирования и/или разбиения запросов на I/O между доступными ресурсами подсистемы, чтобы потом сохранить их на физических носителях.
Для получения дополнительной информации о балансировании I/O ознакомьтесь с этой статьёй: NT Server and Disk Subsystem Performance

Прерванный I/O

Прерванный I/O (Torn I/O) в документации SQL Server часто упоминается ещё как оборванные страницы (torn page). Прерванный I/O происходит при частичной записи, после чего данные остаются в несогласованном состоянии. В SQL Server 2000/7.0 страницы данных имеют размер 8 Кбайт. Разрыв страниц данных в SQL Server происходит тогда, когда только часть из 8 Кбайт будет правильно записана или прочитана с долговременного носителя.
SQL Server всегда проверяет состояние данных после завершения I/O, что бы идентифицировать любые ошибки операционной системы, и соответствие размера передаваемых данных, а затем обрабатывает возможные ошибки. Оборванные страницы могут появиться после отказа системы, в случае если подсистема ввода - вывода при запросе I/O не завершит полностью запись/чтение 8 Кбайт данных.
Производители дисков ограничивают передачу данных размером сектора диска, равным 512 байт, поэтому, если долговременным носителем является физический диск, и его контроллер не имеет кэша с автономной батареей, запрос I/O в итоге ограничивается скоростью вращения шпинделя диска. Таким образом, если I/O пытается записать 8 Кбайт (имея шестнадцать 512-байтных секторов), но успешно на долговременный носитель запишутся только первые три сектора, в этом случае страница становится оборванной, что приведёт к нарушению целостности данных, т.к. последующее чтение страницы размером в 8 Кбайт получило бы 3 сектора новой версии страницы данных и 13 секторов старой версии.
SQL Server умеет обнаруживать оборванные страницы, анализируя базу данных. Часть первого 512-байтного сектора страницы содержит заголовок, в котором содержится информация каждом из 512-байтных секторов являющимися частями 8-ми Кбайтной страницы. Обнаружить обрыв страницы можно анализируя заголовок оборванной страницы.
Обнаружение оборванных страниц требует минимальных ресурсных затрат и является рекомендуемой практикой администрирования SQL Server. Чтобы прочитать больше об обнаружении оборванных страниц, см. статью "Torn Page Detection" в SQL Server Books Online.

Флаг чётности журнала

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

Зеркальное отражение и удалённое зеркальное отражение дисков

Зеркальное отражение - это распространённая практика обеспечения избыточности данных и очень важный инструмент защиты данных. Зеркальное отражение можно реализовать на программном или аппаратном уровне.
Наиболее распространённой является аппаратное решение, зеркально отражающие образы дисков на физическом уровне. Развитие этой технологии привело к возможности отражения на диски, находящиеся друг от друга на большом расстоянии.
Сегодня на рынке доступны несколько типов реализаций зеркального отражения. Некоторые из них базируются на использовании кэша, другие направляют I/O на все зеркальные тома данных и контролируют, что бы запросы I/O были выполнены полностью на всех томах. В любом случае, все эти реализации должны соблюдать порядок записи.
SQL Server считает зеркало долговременным носителем, копирование данных на который осуществляется по меткам времени, что является одним из ключевых моментов этой технологии. На отраженной подсистеме должен строго соблюдаться протокол WAL, без чего невозможно будет обеспечить исполнение требований ACID. Отраженная подсистема должна в точности повторять все метки времени, как они были в первичных данных.
Например, многие высокопроизводительные дисковые подсистемы могут обслуживать несколько каналов I/O. Если журналы базы данных помещены на одно зеркало, а файлы данных на другое, поддержка порядка записи уже не может быть обеспечена только одним аппаратным компонентом. Без дополнительных механизмов, порядок записи страниц данных и журнала транзакций на зеркальные диски не может быть реализован таким образом, что бы соблюсти порядок меток времени. Эти дополнительные механизмы отражения необходимы, чтобы гарантировать порядок записи на несколько физических, зеркальных устройств. Иногда, их называют зеркальными группами (Mirror Groups) или последовательными группами (Consistency Groups).
Для получения дополнительной информации, прочтите эту статью: Implementing Remote Mirroring and Stretch Clustering

Forced Unit Access

Forced Unit Access - сквозной доступ (FUA) происходит тогда, когда файл открыт (используется CreateFile) с флагом FILE_FLAG_WRITETHROUGH. SQL Server открывает все базы данных и журналы с этим флагом.
Флаг указывает на то, что любой запрос на запись с битом FUA нужно посылать непосредственно подсистеме I/O. Этот бит указывает подсистеме, что данные должны попасть на долговременный носитель до того, как I/O будет считаться законченным, т.е. операционная система выдаст сообщение о завершении I/O. При этом не должен использоваться промежуточный кэш, который не относиться к долговременному носителю. Другими словами, запись должна идти непосредственно в долговременный носитель, и такой процесс принято называть сквозной записью (writethrough).
Такой доступ позволяет предотвратить проблемы, которые могут возникнуть, когда кэш (например, кэш операционной системы, который не является автономным, как кэш с батарейкой), принимает I/O и сообщает SQL Server, что I/O завершен успешно, а фактически данные ещё не были сохранены на долговременном носителе. Без такого доступа, SQL Server не смог бы полагаться на систему для обеспечения протокола WAL.
Стоит поинтересоваться у изготовителя по поводу поддержки FUA его оборудованием. Некоторые аппаратные средства обрабатывают состояние FUA как индикатор того, что данные должны быть сохранены на физическом диске (даже если он оборудован автономным кэшем с батарейкой). Другие устройства полагаются на то, что кэш с батарейкой можно считать долговременным носителями, который поддерживает FUA. Разница в таких подходах может сильно влиять на производительность SQL Server. Подсистема, которая поддерживает кэширование долговременного носителя, как правило, намного быстрее осуществляет запись, чем подсистема, которая требует, чтобы данные были записаны на физический носитель.
Важно: Спецификации и реализации IDE дисков не имеют ясных стандартов того, как они обслуживают FUA запросы. Спецификации SCSI дисков и их реализации предусматривают использование FUA запросов, отключают кэши физической дисковой системы и другие механизмы кэширования. У многих IDE систем, запрос FUA просто будет отвергнут аппаратными средствами IDE дисков, делая, таким образом, этот тип дисковых подсистем опасным для работы SQL Server или других программ, которые полагаются на поддержку FUA. Из-за необходимости обеспечения реакции на установку FUA, некоторый производители IDE дисков создают утилиты, которые блокируют кэш IDE диска, что позволяет обезопасить работу SQL Server.
Важно: Некоторые версии Microsoft Windows не всегда передают бит FUA аппаратным средствам. Соответствующие исправления были внесены, начиная с Windows 2000 Service Pack 3 (SP3), после которого бит FUA теперь всегда обрабатывается правильно. Для обратной совместимости, Microsoft выпустил утилиту Dskcache.exe, которая позволяет управлять этим поведением операционной системы.
Используйте эту утилиту, чтобы управлять реакцией на флаг FUA, если компьютер обслуживает SQL Server, а диски имеют кэш. Утилита может быть получена у Microsoft, для получения более подробной информации, изучите статью: Obtain the Dskcache.exe Tool to Configure the "Power Protected" Write Cache Option

Примечание переводчика: В Windows 2000 Advanced Server службы Active Directory при запуске отключают кэширование записи для жестких дисков.

Страницы данных

Размер страницы базы данных SQL Server равен 8 Кбайт. Каждая страница содержит заголовок с полями номера страницы, идентификатора объекта, LSN, идентификатора индекса, бита чётности (Torn bits) и типа страницы. Сами строки данных расположены на оставшейся части страницы. Внутренние механизмы базы данных отслеживают состояние распределения страниц данных в базе.
Страницы данных часто просто называют страницами.

Номер страницы

Номера страниц могут принимать значения от 0 до ((Максимальный размер файла / 8 Кб)-1). Номер страницы, умноженный на 8 Кбайт, даёт смещение в файле к первому байту страницы.
Когда страница считывается с диска, номер страницы сразу же проверяется, чтобы определить правильность заданного смещения (номер страницы в заголовке по сравнению с ожидаемым номером страницы). Если номер не совпадает, SQL Server генерирует ошибку 823.

ID объекта

Это идентификатор объекта, который назначен странице в схеме базы данных. Страница может быть назначена только на единственный объект. Когда страница читается с диска, у страницы проверяется ID объекта. Если ID объекта не соответствует ожидаемому, SQL Server генерирует ошибку 605.
SQL Server часто осуществляет запись кратно размеру страницы, 8 Кбайт или больше.

Экстенты

Обычно SQL Server (если исключить не смешанные экстенты) распределяет место не только страницами, но и экстентами. Экстент - это блок из восьми страниц по 8 Кб, всего 64 Кб. SQL Server часто читает сразу экстентами (64 Кб или 128 Кб).

Буферный пул

Буферный пул - Buffer Pool (BPool) занимает наибольшую часть адресного пространства непривилегированного режима, оставляя только около 100 Мбайт для диапазона виртуальных адресов, используемых для стеков потока, библиотек DLL и др. Буферный пул резервируется большими кусками, но кратными рабочему размеру страницы базы данных - 8 Кб.

Аппаратный кэш чтения

Аппаратный кэш чтения - это обычный кэш упреждающего чтения, используемый контроллерами. В зависимости от размера доступного кэша, кэш упреждающего чтения используется для повышения производительности извлечения данных, которых помещается в кэш больше, чем фактически запрашивается для чтения.
Аппаратный кэш чтения и упреждающее чтение бывает более выигрышен для приложений, данные которых обычно имеют непрерывный характер и читаются практически непрерывными кусками, например, это могут быть OLAP запросы или приложения отчётности.
Поскольку аппаратный кэш чтения утилизирует часть памяти кэша, которая могла бы использоваться для буферизации запросов на запись, его использование может мешать оперативным транзакциям (OLTP), для которых требуется высокая скорость записи.
Важно: Некоторые контроллеры не выполняют упреждающее чтение, если размер запроса на чтение превышает 16 Кб. В таком случае, для серверов с Microsoft SQL Server, аппаратное упреждающее чтение не даёт заметных преимуществ, потому что запросы I/O на чтение, как правило, больше 16 Кбайт. Изучите документацию или свяжитесь с производителем Вашей дисковой подсистемы, что бы получить рекомендации по настройке аппаратной части для поддержки работы SQL Server.

Аппаратный кэш записи

Аппаратный кэш записи обслуживает не только запросы на запись, но и запросы на чтение, если данные все еще находятся в аппаратном кэше записи. Это распространённый механизм кэширования I/O.
Возможность аппаратного кэширования записи является очень важной в поддержании высокой производительности OLTP систем. С поддержкой автономного питания от встроенной батареи и соответствующих, безопасных алгоритмов работы, аппаратный кэш записи может обеспечить безопасность данных (на долговременных носителях), а так же повысить быстродействие подобных SQL Server приложений, реально сокращая расходы на физические операции ввода-вывода.

Ошибка 823

SQL Server error 832, "I/O error <error> detected during <operation> at offset <offset> in file '<file>'"

Происходит когда:

• операции ReadFile, WriteFile, ReadFileScatter, WriteFileGather или GetOverlappedResult приводят к одной из ошибок операционной системы.

• номер страницы при чтении страницы с диска не совпадает с ожидаемым ID страницы.

• не правильный размер передаваемых данных.

• обнаружено прерванное чтение, когда включено определение оборванных страниц.

• обнаружено чтение устаревших данных (Stale Read), когда включено определение такого чтения.

Примечание переводчика: Stale Read возникает при запросах ReadFile API, если операционная система, драйвер или кэширующий контроллер ошибочно возвращает старую версию кешируемых данных.

Для получения подробностей об ошибке 823, см. статью:
Error message 823 may indicate hardware problems or system problems

Ошибка 605

SQL Server error 605, "Attempt to fetch logical page (x:yyy) in database 'dddd' belongs to object 'aaa', not to object 'tttt'."

Происходит когда:

• ID объекта на странице не соответствует ID объекта, который ожидалось прочитать в заголовке страницы.

Ошибка 605 у SQL Server проявляется, когда к странице обращаются для сканирования. Сканирование ассоциируется с конкретным объектом. Если при сканировании ID не соответствует ID объекта, который хранится в заголовке страницы, генерируется эта ошибка. Это происходит в момент первого использования страницы и может произойти при последующем поиске страницы в оперативной памяти.

Планирование I/O в Microsoft SQL Server

Для полного понимания дизайна ввода - вывода в SQL Server важно знать основные принципы операций I/O с файлами баз данных, журналами транзакций и последовательность обслуживания транзакций.
Дизайн I/O и транзакций в SQL Server гарантируют, что правила ACID будут полностью выполнены. Эта глава посвящена свойству "Неизменности" результатов исполнения транзакций и его поддержке средствами операционной системы и аппаратными средами.