Перевод: Александра Гладченко

Изучение требований к операциям ввода-вывода (I/O) с файлами баз данных Microsoft SQL Server поможет Вам поднять производительность системы и избежать ошибок, связанных с I/O.

Содержание

Введение

Поскольку на рынке продолжают появляться новые устройства и решения для дисковых хранилищ, условия, в которых работает 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
Write-Ahead Logging протокол (WAL)
Метка времени
Долговременные носители
Порядок записи
Многоканальные и балансирующие нагрузку системы
Прерванный I/O
Флаг чётности журнала
Зеркальное отражение и удалённое зеркальное отражение дисков
Forced Unit Access
Страницы данных
Номер страницы
ID объекта
Экстенты
Буферный пул
Аппаратный кэш чтения
Аппаратный кэш записи
Ошибка 823
Ошибка 605

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

Требования 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>"

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

Примечание переводчика: 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."

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

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

[В начало]

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

В этой главе:

Протокол Write-Ahead Logging (WAL)
Log Sequence Number
Краткая блокировка
Сборка - распределение (Scatter-Gather)
Transaction Log I/O-WriteFile
Асинхронный I/O
Сброс страниц данных на диск
Программа отложенной записи (Lazy Writer)
Контрольная точка (Checkpoint)
Безотложная запись
Завершение работы и интервал регенерации
Работа в кластере
Корректировка интервала регенерации
Обзор моделей резервирования
Сброс на диск записей журнала транзакций
Упреждающее чтение (Read-Ahead)

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

[В начало]

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

Ключевым элементом обеспечения правил ACID является протокол WAL. Протокол WAL требует, чтобы все записи журнала транзакций, связанные с относящимися к ним страницами данных, были сброшены на диск долговременного носителя до того, как сами страницы данных будут сброшены на диск.
Microsoft SQL Server 2000 и Microsoft SQL Server 7.0 используют страницы данных размером 8 Кб и кратный размеру сектора диска размер буфера журнала транзакций. Более ранние версии SQL Server использовали страницы данных и журнала транзакций размером в 2 Кб.
Давайте рассмотрим пример кода, приведённый в статье Microsoft Knowledge Base:

SQL Server 7.0 and SQL Server 2000 Logging and Data Storage Algorithms Extend Data Reliability

Рассмотрим на примере, как SQL Server поддерживает протокол WAL для инструкции INSERT. В этом примере предполагается, что индексы не используются и страница, которая будет задействована, имеет номер 150.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

Блокировка и краткая блокировка являются обособленными вопросами при поддержке протокола WAL. Блокировка поддерживает целостность данных в транзакциях, в то время как краткая блокировка поддерживает физическую целостность данных. В предыдущем примере, SQL Server 7.0 и SQL Server 2000 используют краткую блокировку страницы 150 только в течение того времени, которое необходимо для физических изменений на странице, а не на всё время исполнения транзакции. Соответствующий тип блокировки устанавливается по мере необходимости для защиты строки, блока строк, страницы или таблицы целиком.
Для подробного ознакомления с разными типами блокировок, посмотрите посвящённые им главы Microsoft SQL Server Books Online.
Рассматривая предыдущий пример работы протокола WAL более подробно, может возникнуть вопрос, что случается если процесс отложенной записи или процесс контрольной точки отработает раньше, чем будет иметь место COMMIT, но уже после изменения страницы данных? Давайте снова вернёмся к используемому нами примеру и рассмотрим поведение протокола WAL в этом случае:

Обратите внимание на то, что в последнем примере не исполняется команда COMMIT. Если страница помечена, как "грязная", записи журнала могут быть сброшены на диск, а за ними и соответствующая страница данных, как это положено по протоколу WAL. Блокировки защищают завершение транзакции и, если произойдёт откат операции, процесс recovery компенсирует эти действия, восстановив страницу в надлежащее состояние. Если бы всё это не работало описанным выше способом, SQL Server не смог бы выполнить больше изменений, чем позволила бы разместить его физическая память.
Отложенная записи и контрольная точка разрешают подобные проблемы путём сброса на долговременный носитель всех записей журнала транзакций, связанных с "грязной" страницей. Это позволяет поддержать требования протокола WAL, согласно которым страница данных никогда не может быть сохранена на долговременном носителе до связанных с ней записей журнала транзакций.
Почитать про журнал транзакций и об упреждающей записи можно в главе: "Write-Ahead Transaction Log" из SQL Server Books Online.
Дополнительную информацию можно найти сайте Microsoft в документе: SQL Server 7.0 and SQL Server 2000 Logging and Data Storage Algorithms Extend Data Reliability

[В начало]

Log Sequence Number

Значение порядкового номера журнала (LSN) состоит из трех частей и представляет собой уникальное инкрементальное возрастающее значение. Оно используется для организации последовательности записей журнала транзакций базы данных. Это позволяет SQL Server соблюдать правила ACID и правильно выполнять операцию recovery.
При изменениях в данных, в журнале транзакций создаются новые значения LSN. То же самое значение LSN сохраняется (заменяя предыдущее значение) в заголовке страницы данных, в котором храниться номер последней записи в журнале транзакций, и этим самым страница данных получает соответствие записи в журнале.
Чтобы узнать больше об архитектуре журнала транзакций, смотрите статью "Transaction Log Logical Architecture" в SQL Server Books Online.

[В начало]

Краткая блокировка

SQL Server использует краткие блокировки во время синхронизации данных. Блокировки такого типа создаются SQL Server в непривилегированном режиме, для выполнения операций записи или чтения. Каждая, находящаяся в памяти страница данных имеет идентифицирующую буфер структуру (BUF). Массив BUF структур содержит информацию о состоянии (Dirty, On LRU, In I/O), и так же о кратких блокировках.
Блокировка решает соответствующие задачи блокирования, а краткая блокировка контролирует физический доступ. Например, блокировка может налагаться на страницу, которая не находится в памяти. Краткая же блокировка возможна только тогда, когда страница данных находится в памяти.
На представленной ниже иллюстрации показано представление верхнего уровня буферного пула SQL Server 2000.

Рис. 1

[В начало]

Сборка - распределение (Scatter-Gather)

Начиная с Microsoft SQL Server 7.0, используются Microsoft Win32 APIs: WriteFileGather и ReadFileScatter. Функция WriteFileGather собирает данные от множества разрозненных частей буфера и записывает эти данные в файл. Функция ReadFileScatter считывает данные из файла и распределяет их по нескольким рассредоточенным частям буфера.
Эти API не позволяют SQL Server множить запросы на физический I/O. Например, во время работы процесса контрольной точки с шестнадцатью страницами по 8 Кбайт, в итоге, всё может быть сброшено на диск за один вызов WriteFileGather. Перед использованием WriteFileGather, SQL Server должен был бы создавать запрос на I/O для каждой страницы данных, которые потом должны были быть отсортированы и буферизованы непосредственно в виде всего большого запроса.
Важно: Сборка и распределение зависят от специфики аппаратных возможностей. Если аппаратные средства их не поддерживают, возможности по сборке и распределению перекладываются на операционную систему, которая должна будет выделять запросы на I/O. Чтобы Microsoft SQL Server производительно работал с I/O, убедитесь, что ваша подсистема ввода - вывода изначально поддерживает операции по сборке и распределению I/O.
Что бы подробно изучить использование в SQL Server сборки и распределения для повышения его производительность, прочтите следующий документ: Performance Enhancements for SQL Server Under Windows NT

[В начало]

Transaction Log I/O-WriteFile

Для операций с журналом транзакций, SQL Server использует WriteFile вместо WriteFileGather. WriteFileGather используется для I/O в операционной системе, и ограничен размером страницы. Это ограничение подразумевает, что каждая запись в журнал будет иметь размер не менее 4 Кб. Поэтому для журнала используется WriteFile, что позволяет сократить размер записи до границ сектора диска.

[В начало]

Асинхронный I/O

Весь I/O журнала транзакций и файлов баз данных SQL Server выполняется с использованием структуры OVERLAPPED, которая позволяет облегчить использование асинхронного I/O. Буферный пул SQL Server и диспетчер файлов имеют очень сложные внутренние механизмы для обслуживания I/O. Для поддержки целостности данных во время асинхронных операций с I/O используются соответствующие краткие блокировки для чтения/записи.
SQL Server использует запросы к Win32 API следующим образом:

Обратите внимание: Сортировка и операции спулинга, для осуществления необходимых операции с I/O, совместно используют некоторые механизмы буферного пула SQL Server и диспетчера файлов. Также, обратите внимание, что SQL Server не всегда обслуживает завершение I/O в рамках того же самого рабочего потока, который отправлял этот запрос I/O. Завершение I/O в SQL Server выполняется унаследованным рабочим потоком в рамках того же самого User Mode Scheduler (UMS), в котором запрос I/O был отправлен на исполнение. Внутренние механизмы в SQL Server назначают подпрограммы повторного вызова, которые будут вызываться при завершении I/O. Повторный вызов - это специфический для SQL Server механизм, который не основывается на сообщениях, подобных функциям ReadFileEx или WriteFileEx. Например, если чтение страницы данных завершено, подпрограмма повторного вызова проверит, что код возврата операционной системы равен нулю (значение GetLastError), и это будет гарантировать, что все байты были переданы правильно, что выполнена проверка на ошибки оборванных страниц, и что гарантируется правильный номер страницы, и исполнены все другие разумные проверки.

[В начало]

Сброс страниц данных на диск

Речь пойдёт о трёх основных механизмах, которые провоцируют сброс страницы данных на диск. Однако, все они использует одну и ту же встроенную подпрограмму работы с буферным пулом, с помощью которой происходит передача данных:

Для эффективной записи при сбросе на диск используется WriteFileGather. Это позволяет SQL Server связывать последовательно расположенные грязные страницы в один запрос на запись.
SQL Server использует следующую последовательность сброса на диск одной страницы:

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

После того, как будет определен набор страниц, которые нужно сбросить на диск, вызывается функция WriteFileGather, которая отправляет (Async / OVERLAPPED) запрос на I/O с привязкой к функции повторного вызова, которая завершит операции I/O.
Когда SQL Server определяет, что HasOverlappedIoCompleted возвращает TRUE, используется функция GetOverlappedResults, которая собирает в системе информацию о завершении операции, и вызывает функцию повторного вызова. Функция повторного вызова делает соответствующее заключение об успешности операции I/O и снимает краткие блокировки на каждой из задействованных страниц.

[В начало]

Программа отложенной записи (Lazy Writer)

Программа отложенной записи в SQL Server 2000 и SQL Server 7.0 пытается определить расположение до 16-ти уникальных страниц нуждающихся в перемещении для возврата их в число свободных страниц. Если счётчик ссылок страницы дойдёт до нуля, она может быть возвращена в качестве свободной. Если страница отмечена как "грязная", её записи журнала и страницы данных будут сброшены на диск.
Таким образом, программа отложенной записи может единовременно сбросить на диск 16*16 страниц. Это будет достаточно эффективно, потому что многие из этих страниц останутся в буферном пуле SQL Server, но будут находиться после этого в состоянии - "свободна". I/O выполняется в фоновом режиме относительно основного SPID (идентификатор серверного процесса). Когда программа отложенной записи нуждается в дополнительных буферах для своего списка свободных страниц, нет нужды сбрасывать на диск буфера, просто выполняется высвобождение хэша и возврат в список свободных страниц.

[В начало]

Контрольная точка (Checkpoint)

Процесс контрольной точки в SQL Server 2000 периодически проходит по буферному пулу, анализирует буферы, которые содержат страницы указанной базы данных, и сбрасывает на диск долговременного носителя все "грязные" буферы. Это делает короче процесс регенерации (recovery), потому что операции отката назад потребуют для своего исполнения меньших физических затрат.
Как говорилось ранее, процесс контрольной точки использует тот же самый подход к I/O, отправляя до 16 страниц в одном запросе на I/O. Когда запрос на I/O отправлен (OVERLAPPED), контрольная точка не ждет немедленного исполнения каждого I/O. Контрольная точка продолжает отслеживать посылаемые и исполняемые запросы на I/O, но пытается при этом поддерживать высокий уровень исполнения незавершённых запросов на I/O (например, 100 постоянно обслуживающихся не завершённых запросов на запись). Это повышает производительность I/O и уменьшает время исполнения контрольной точки.
До появления WriteFileGather, SQL Server сортировал буферы обслуживаемой базы данных в порядке страниц и также в порядке страниц создавал запросы на I/O. Это порождало множество физических запросов I/O, потому что порядок страницы для сброса не соответствовал непрерывному расположению в памяти. Однако, довольно часто механизмы подсистем физического уровня предоставляли страницам такое физическое местоположение, которое располагало их в непосредственной близости, что способствовало достаточно быстрому исполнению запросов на I/O.
В более позднем дизайне, элеваторный поиск может быть проблематичен. Сбор нескольких запросов на I/O в порядке страниц обычно приводит к порядку, близкому к порядку на диске. При интенсивной нагрузке подсистем множеством запросов на I/O, упорядоченных таким образом, дисковод может обслужить эти запросы на I/O до запросов, которые могли быть не завершены до этого.
С появлением WriteFileGather, SQL Server может перемещать буферный пул, не требуя какого либо физического соотношения с порядком расположения страниц на диске. Собирая группы по 128 Кб (шестнадцать страниц по 8 Кб), SQL Server может передать блоки данных, используя для этого намного меньше физических запросов на I/O. Это позволяет процессу контрольной точки поддерживать хорошую скорость, и в то же время для запросов на I/O, которые носят случайный характер, поддерживать элеваторный поиск, который может повлиять на другие операции I/O. Все базы данных, кроме tempdb, обслуживаются контрольной точкой. Tempdb не требует регенерации (она пересоздаётся при каждом запуске SQL Server), поэтому сброс на диск страницы данных не оптимален для tempdb, и SQL Server старается избегать этого.
Контрольная точка защищает систему от наводнения операциями I/O, преобразуя их в последовательную форму посредством процессов контрольной точки. Единовременно, исполняться может только одна контрольная точка. Процессы контрольной точки и программа отложенной записи взаимодействуют друг с другом, чтобы управлять внутренними механизмами очереди на I/O.

[В начало]

Безотложная запись

Microsoft SQL Server 2000 использует безотложную запись для страниц данных, связанные с не регистрируемыми операциями (обычно это bulk insert / select into). Это предоставляет возможность экземплярам I/O асинхронно сохранить на диск "грязные" страницы без нежелательного образования больших частей грязного буферного пула. Процессами контрольной точки используется тот же самый механизм отправки запросов на операции I/O, как и у отложенной записи.
Microsoft SQL Server 7.0 не имеет возможности выполнять безотложную запись, вместо этого, контрольная точка проходит во время завершения транзакций, чтобы сбросить на диск все буфера базы данных. Это может спровоцировать много нерегистрируемых операций, которые будут преобразованы в последовательную форму, потому что может быть активна только одна контрольная точка.
Важно: Отложенная запись, контрольная точка и безотложная запись не ожидают немедленного завершения I/O. Они всегда отправляют запросы на I/O посредством WriteFileGather с опцией OVERLAPPED, и продолжают работать дальше, чуть позже проверяя успешность завершения I/O. Это позволяет SQL Server максимизировать ресурсы процессоров и I/O для соответствующих задач.

[В начало]

Завершение работы и интервал регенерации

Штатная операция завершения работы сервера баз данных запускает процесс контрольной точки для всех баз, закрывает все внутренние процессы проверки структур баз данных и завершает процесс SQL Server.
Интервал регенерации (recovery interval) управляет поведением контрольной точки. Когда этот интервал увеличивается, между вызовами контрольной точки в памяти появляется больше "грязных" страниц.
Современные версии Microsoft Windows рассчитывают, что завершение работы SQL Server успешно выполнится за 60 - 120 секунд.

[В начало]

Работа в кластере

Работа в кластере может повлиять на время завершения работы сервера баз данных.

[В начало]

Корректировка интервала регенерации

После корректировки интервал регенерации могут возникнуть несколько побочных эффектов. Обдумайте их тщательно перед тем, как корректировать интервала регенерации.

Время жизни "грязных" страниц - страница считается "грязной", когда имели место изменения её данных. Грязная страница не может быть удалена из буферного пула SQL Server, пока вначале связанные с ней записи журнала транзакций и затем сама страница не будут записаны непосредственно на долговременный носитель. Увеличение интервала прохождения контрольной точки (после увеличения интервала регенерации) под высокой нагрузкой системы провоцирует вытеснение обработки грязных страниц под управление кода программы отложенной записи. Это может привести к полной деградации производительности, потому что отложенная запись не предназначена для исполнения действий, подобных контрольной точке.
Программа отложенной записи действительно исполняет соответствующие её действия с грязными страницами, чтобы обеспечить гарантию целостности данных и высвобождение свободного места, но, в отличие от процесса контрольной точки, она не приспособлена для сокращения времени жизни грязных страниц. Контрольные точки позволяют более продуктивно сохранять грязные страницы. Передача функций контрольных точек программе отложенной записи увеличит время ожидания, потому что отложенная записи вынуждена исполнять I/O для отложенных буферов, вместо простых операций в памяти, обеспечивающих свободное место. Если Вы изменили интервал регенерации, нужно проверить показания счётчиков производительности, относящиеся к программе отложенной записи.

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

Продолжительность регенерации - Увеличение интервала регенерации может привести к увеличению времени регенерации. Если SQL Server не штатно завершает свою работу (неожиданное завершение процесса или, например, происходит падение напряжения), во время следующего старта, менеджер регенерации обеспечивает правильность состояния транзакций базы данных. Если контрольная точка запускается не часто, и это приводит к значительному увеличению числа грязных страниц, не штатное завершение потребует для менеджера регенерации исполнение большого числа откатов и проверки транзакций, необходимых для возврата базы данных к непротиворечивому состоянию транзакций. Основным фактором, влияющим на производительность этой операции, является то, что буферный пул SQL Server будет "холодным" (в памяти не будет страниц) во время регенерации. Процедура восстановления должна будет считать в память все соответствующие страницы базы данных и сделать необходимые изменения. Это может увеличить время ожидания регенерации, что является нежелательным для промышленных применений. Увеличение интервала регенерации часто приводит к потерям в производительности и к дискриминации требований к доступности приложений.
Например, предположим, что при штатной загрузке системы контрольная точка каждую минуту должна сбрасывать на диск 250 Мб грязных буферов. В соответствии с алгоритмом работы контрольной точки, при интервале регенерации в 10 минут, на диск будет сброшено 2500 Мб данных, если все другие влияющие факторы останутся постоянными. Если продолжительность контрольной точки превысить величину интервала, тогда для этого дискового массива стоит увеличить число дисков, что бы все страницы успевали обрабатываться, и возможности параллельной обработки были задействованы полностью. Однако, 2500 Мб грязных страниц сами по себе потребуют достаточно больших затрат для поддержания удовлетворительной работы процесса регенерации.

[В начало]

Обзор моделей резервирования

Microsoft SQL Server 2000 использует несколько моделей резервирования баз данных: Full, Bulk-Logged и Simple. Рабочая нагрузка прикладной системы при разных моделях резервирования может получить разное поведение I/O. Приложение, аппаратное решение и модель резервирования должны выбираться из бизнес - требований к развертываемой системе по резервированию и восстановлению.

[В начало]

Сброс на диск записей журнала транзакций

Записи журнала сбрасываются на диск аналогично страницам данных. За запись в журнале базы данных отвечает менеджер журнала транзакций. Вы можете сделать запрос к таблице sysprocesses, чтобы увидеть относящиеся к журналу транзакций SPID.
Когда запрашивается сброс на диск всех записей журнала до какого-нибудь номера LSN по требованию одного из системных потоков, такой запрос будет поставлен в очередь менеджера журнала. Процесс будет ждать ответа менеджера о том, что I/O завершился успешно. Менеджер журнала просматривает очередь и форматирует запросы. После этого он организует I/O кратными размеру сектора диска блоками.
I/O передаётся функции WriteFile, использующей OVERLAPPED (асинхронные) механизмы. После этого, менеджер журнала возвращается к обслуживанию других, поставленных в очередь запросов. Когда I/O будет закончен, отрабатывает завершающая его подпрограмма, которая проверяет успешность записи. Если запись прошла успешно, ожидающий процесс получит об этом сообщение, и может продолжить свои операции.
На этой стадии, порядок записи является критичным. Поскольку одному журналу может быть направлен запрос на несколько операций записи, должен соблюдаться порядок обслуживания LSN.
Например, если страницы 50, 100 и 200 изменяются в разных транзакциях, и сначала изменяется страница 50, потом 100, а затем 200. Тогда, при получении запросов на сброс LSN для страниц 50, 100 и 200, он будет выполнен в том же самом порядке. Если записи в журнале для страниц 50 и 200 будут сброшены на диск долговременного носителя, считается выполненным только сброс LSN для страницы 50, и SQL Server сбросит на диск только страницу 50. LSN для страницы 100 должен быть сброшен на диск долговременного носителям прежде, чем LSN 100, и только потом LSN 200 может считаться сброшенным на диск (журнал фиксируется).
Порядок записи - ключевой элемент поддержки целостности данных в SQL Server.

[В начало]

Упреждающее чтение (Read-Ahead)

Для упреждающего чтения SQL Server 2000 использует функцию ReadFileScatter. Для поиска страниц данных, которые могут скоро понадобиться, SQL Server использует довольно сложные алгоритмы.
Например, если выполняется запрос, который может использовать индекс, может быть принято решение о необходимости упреждающего чтения соответствующих ему строк из страниц фактических данных, которые необходимы для получения требуемой выборки. Поскольку элементы индекса идентифицированы, SQL Server может генерировать OVERLAPPED (async) операции I/O для страниц данных, которые планируются в следующих шагах плана исполнения запроса. Это демонстрирует то, как запрос использует упреждающее чтение для поискового оператора - bookmark lookup.
В этом примере продемонстрирована только одна из возможностей применения упреждающего чтения, которые задействованы в SQL Server. Извлечение во время поиска по индексу I/O страниц данных, повышает полезность утилизации в системе CPU и I/O. Зачастую этот I/O завершается к тому времени, когда это необходимо, и следующие шаги плана исполнения уже имеют прямой доступ к необходимым данным, располагающимся уже в памяти, и не нужно прерываться на ожидании извлекающего их I/O.
Когда вызывается упреждающее чтение, оно может охватить от 1 до 1024 страниц. Для большинства редакций SQL Server глубина запроса одного упреждающего чтения ограничивается 128 страницами. Однако, Microsoft SQL Server Enterprise Edition поднимает это ограничение до 1024 страниц.
SQL Server использует следующие шаги для организации упреждающего чтения: