Ключевым элементом обеспечения правил 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), и это будет гарантировать, что все байты были переданы правильно, что выполнена проверка на ошибки оборванных страниц, и что гарантируется правильный номер страницы, и исполнены все другие разумные проверки.

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

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

• Отложенная запись (least recently used - LRU и основанная на вытеснении памяти)

• Контрольная точка (на основании recovery - интервала)

• Безотложная запись (базируется на нерегистрируемом I/O)

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

1. На страницу накладывается краткая блокировка, что позволяет предотвратить возможные её последующие изменения.

2. Гарантированный сброс на диск долговременного носителя записей журнала транзакций, относящихся к хранимому в заголовке страницы LSN.

3. Установка правильных параметров для вызова WriteFileGather.

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

1. Выполняется поиск по хэшу (hash lookup) следующей страницы. Например, если страница, которая сбрасывается на диск имеет номер 100, SQL Server ищет в массиве буферного хэша страницу с номером 101.

2. Если эта страница не найдена, то устанавливается конец цельного блока I/O, и отправляется запрос на этот I/O.

3. Если страница будет найдена, на неё налагается краткая блокировка, что позволяет предотвратить её дальнейшие изменения, т.к. она может быть "грязной".

4. Выполняется проверка, чтобы убедиться, что страница является "грязной" и должна быть сохранена. В противном случае снимается краткая блокировка и объявляется конец непрерывного блока I/O, как это задано и представлено асинхронным запросом на I/O.

5. Если страница "грязная", то всё повторяется с шага 1.

После того, как будет определен набор страниц, которые нужно сбросить на диск, вызывается функция 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, а также систем, участвует обеспечивающих отказоустойчивости.

• Кластерный ресурс SQL Server будет отмечен состоянием отказа SQL Server. При этом, в соответствии со сценарием перемещения группы, кластерный процесс физически передаст отказавшие ресурсы под управление другим узлом.

• Неумелый выбор интервала регенерации может привести к тому, что исполнение контрольной точки займёт больше времени, превысив заданные лимиты, и спровоцировав агрессивную реакцию системы поддержки отказоустойчивости. Агрессивные сценарии поддержки отказоустойчивости, таким образом, могут оказаться опасны.

• Microsoft строго рекомендует использовать установленные по умолчанию значения для интервала регенерации, это гарантирует оптимальную метрику регенерации и будет правильно работать с учётом типовых ограничений кластерных ресурсов. Если интервал регенерации превышает 60 секунд, велика вероятность того, что работающий на кластере экземпляр будет переведён агрессивным сценарием в автономное состояние. Хотя применение агрессивных сценариев поддержки отказоустойчивости и имеет множество плюсов, они нежелательны. Если процесс регенерации сопряжён со значительными сложностями, вызванными большим числом транзакций, Microsoft рекомендует оптимизировать размещение баз данных, физическое размещение их файлов и физических каналов I/O, а не корректировать интервал регенерации, в целях повышения его производительности.