Описание алгоритмов ведения журнала и хранилища данных, которые расширяют надежность данных в SQL Server

Исходная версия продукта: SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005
Исходный номер базы знаний: 230785

Итоги

В этой статье описывается, как алгоритмы ведения журналов и данных Microsoft SQL Server расширяют надежность и целостность данных.

Дополнительные сведения о базовых понятиях обработчиков и алгоритмах восстановления и изоляции семантики эксплойтов (ARIES) см. в следующем документе ACM Transactions on Database Systems (в разделе "Том 17, Число 1, март 1992):

Внешняя ссылка: ARIES: метод восстановления транзакций, поддерживающий блокировку тонкой детализации и частичные откаты с помощью ведения журнала накануне записи

В документе рассматриваются методы SQL Server для расширения надежности и целостности данных в связи с сбоями.

Мы рекомендуем ознакомиться со следующими статьями в Базе знаний Майкрософт для получения дополнительных сведений о кэшировании и альтернативных обсуждениях в режиме сбоя:

• Описание кэширования контроллеров дисков в SQL Server

• Сведения об использовании кэшей дисков с SQL Server, которые должны знать каждый администратор базы данных

Термины, используемые в этой статье

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

Термин	Определение
Батареи с поддержкой батареи	Отдельное и локализованное средство резервного копирования батареи напрямую доступно и контролируется механизмом кэширования, чтобы предотвратить потерю данных. Это не является неинтерпретируемым питанием (UPS). UpS не гарантирует никаких действий записи и может быть отключен от устройства кэширования.
Cache	Промежуточный механизм хранения, используемый для оптимизации физических операций ввода-вывода и повышения производительности.
Грязная страница	Страница, содержащая изменения данных, которые еще не были промыты в стабильное хранилище. Дополнительные сведения о грязных буферах страниц см. в статье "Написание страниц" в электронной документации по SQL Server. Содержимое также относится к Microsoft SQL Server 2012 и более поздним версиям.
Сбой	Все, что может вызвать непредвиденный сбой процесса SQL Server. Примеры: сбой питания, сброс компьютера, ошибки памяти, другие проблемы с оборудованием, плохие секторы, сбои диска, сбои системы и т. д.
Очистка	Принудительное применение буфера кэша к стабильному хранилищу.
Кратковременная блокировка	Объект синхронизации, используемый для защиты физической согласованности ресурса.
Ненулевое хранилище	Любой носитель, который остается доступным в системных сбоях.
Закрепленная страница	Страница, которая остается в кэше данных и не может быть удалена в стабильное хранилище, пока все связанные записи журналов не будут защищены в стабильном расположении хранилища.
Стабильное хранилище	То же самое, что и ненулевое хранилище.
Нестабильное хранилище	Любой носитель, который не останется неизменным во время сбоев.

Протокол ведения журнала на основе записи (WAL)

Протокол терминов является отличным способом описания WAL. Это конкретный и определенный набор шагов реализации, необходимый для обеспечения правильного хранения и обмена данными в известном состоянии, если произошел сбой. Так же, как сеть содержит определенный протокол для обмена данными согласованным и защищенным способом, поэтому wal описывает протокол для защиты данных.

Документ ARIES определяет WAL следующим образом:

Протокол WAL утверждает, что записи журнала, представляющие изменения некоторых данных, уже должны находиться в стабильном хранилище, прежде чем измененные данные могут заменить предыдущую версию данных в неразрешемом хранилище. То есть система не может записывать обновленную страницу в версию нестандартного хранилища страницы до тех пор, пока не будут записаны по крайней мере части журнала отмены, описывающие обновления страницы, записаны в стабильное хранилище.

Дополнительные сведения о ведении журналов на этапе записи см. в разделе журнала транзакций "Запись вперед" в электронной документации ПО SQL Server.

SQL Server и WAL

SQL Server использует протокол WAL. Чтобы убедиться, что транзакция правильно зафиксирована, все записи журнала, связанные с транзакцией, должны быть защищены в стабильном хранилище.

Чтобы уточнить эту ситуацию, рассмотрим следующий конкретный пример.

Примечание.

В этом примере предположим, что нет индекса и что затронутая страница — страница 150.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

Затем разбиите действие на упрощенные шаги ведения журнала, как описано в следующей таблице.

Оператор	Действия, выполненные
BEGIN TRANSACTION	Записывается в область кэша журналов. Однако не нужно сбрасывать данные в стабильное хранилище, так как SQL Server не внес никаких физических изменений.
INSERT INTO TblTest	1. Страница данных 150 извлекается в кэш данных SQL Server, если он еще недоступен. 2. Страница зажата, закреплена и помечена как грязная, и получаются соответствующие блокировки. 3. Запись журнала вставки создается и добавляется в кэш журналов. 4. На страницу данных добавляется новая строка. 5. Отпустится блокировка. 6. Записи журнала, связанные с транзакцией или страницей, не должны быть удалены на данный момент, так как все изменения остаются в нестабильном хранилище.
COMMIT TRANSACTION	1. Запись журнала фиксации формируется, и записи журнала, связанные с транзакцией, должны быть записаны в стабильное хранилище. Транзакция не считается зафиксированной, пока записи журнала правильно не назначены стабильному хранилищу. 2. Страница данных 150 остается в кэше данных SQL Server и не сразу сбрасывается в стабильное хранилище. При правильной защите записей журнала восстановление может повторно выполнить операцию, если это необходимо. 3. Освобождены блокировки транзакций.

Не путайте термины "блокировка" и "ведение журнала". Хотя важно, блокировка и ведение журнала являются отдельными проблемами при использовании WAL. В предыдущем примере SQL Server обычно хранит блокировку на странице 150 за время, необходимое для выполнения физических изменений вставки на странице, а не всего времени транзакции. Соответствующий тип блокировки устанавливается для защиты строки, диапазона, страницы или таблицы при необходимости. Дополнительные сведения о типах блокировки см. в разделах электронной документации ПО SQL Server.

Более подробно рассмотрим пример, вы можете задать вопрос о том, что происходит при выполнении процессов LazyWriter или CheckPoint. SQL Server выдает все соответствующие сбросы в стабильное хранилище для записей журнала транзакций, связанных с грязной и закрепленной страницей. Это гарантирует, что страница данных протокола WAL никогда не может быть записана в стабильное хранилище до тех пор, пока связанные записи журнала транзакций не будут удалены.

SQL Server и стабильное хранилище

SQL Server улучшает операции журналов и страниц данных, включая знания о размерах сектора диска (обычно 4096 байт или 512 байт).

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

SQL Server использует страницы данных размером 8 КБ и журнал (при очистке) для нескольких размеров сектора. (Большинство дисков используют 512 байт в качестве размера сектора по умолчанию.) Если произошел сбой, SQL Server может учитывать операции записи, превышающие сектор, используя методы четности журналов и разорвать методы записи.

Обнаружение разорванной страницы

Этот параметр позволяет SQL Server обнаруживать неполные операции ввода-вывода, вызванные сбоями питания или другими системными сбоями. Если значение true, это приводит к перевернутию бит для каждого 512-байтового сектора на странице базы данных 8 килобайт (КБ), когда страница записывается на диск. Если бит находится в неправильном состоянии при последующем чтении страницы SQL Server, страница была написана неправильно; Обнаружена разорванная страница. Разрываемые страницы обнаруживаются во время восстановления, так как любая страница, написанная неправильно, скорее всего, будет считываться путем восстановления.

Хотя страницы базы данных SQL Server имеют 8 КБ, диски выполняют операции ввода-вывода с помощью 512-байтового сектора. Таким образом, 16 секторов записываются на страницу базы данных. Разорванная страница может произойти, если система завершается сбоем (например, из-за сбоя питания) между временем записи первого 512-байтового сектора на диск и завершения операции ввода-вывода 8 КБ. Если первый сектор страницы базы данных успешно записан до сбоя, страница базы данных на диске будет отображаться как обновленная, хотя она, возможно, не выполнена.

Используя кэши контроллера диска с поддержкой батареи, вы можете убедиться, что данные успешно записываются на диск или не записываются вообще. В этой ситуации не устанавливайте для обнаружения страницы значение true, так как это не обязательно.

Примечание.

Обнаружение разорванной страницы не включено по умолчанию в SQL Server. Дополнительные сведения см. в статье Параметры ALTER DATABASE SET (Transact-SQL).

Четность журналов

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

Влияние на производительность

Все версии SQL Server открывают файлы журналов и данных с помощью функции Win32 CreateFile. Член dwFlagsAndAttributes включает FILE_FLAG_WRITE_THROUGH параметр при открытии SQL Server.

FILE_FLAG_WRITE_THROUGH Указывает системе записывать данные через любой промежуточный кэш и перейти непосредственно на диск. Система по-прежнему может кэшировать операции записи, но не может не жалеть их.

Этот FILE_FLAG_WRITE_THROUGH параметр гарантирует, что при успешном завершении операции записи данные хранятся в стабильном хранилище. Это соответствует протоколу WAL, обеспечивающему данные.

Многие диски (SCSI и IDE) содержат кэши подключения размером 512 КБ, 1 МБ или больше. Однако кэши дисков обычно полагаются на capacitor, а не на решение с поддержкой батареи. Эти механизмы кэширования не могут гарантировать запись в цикле питания или аналогичной точке сбоя. Они гарантируют только завершение операций записи в секторе. Именно поэтому обнаружение разорванной записи и четности журналов было встроено в SQL Server 7.0 и более поздних версий. По мере того как диски продолжают увеличиваться, кэши становятся больше, и они могут предоставлять большие объемы данных во время сбоя.

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

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

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

Порядок секторов

Распространенный метод, используемый для повышения производительности ввода-вывода, — упорядочение секторов. Чтобы избежать механического перемещения головы, запросы на чтение и запись сортируются, что позволяет более согласованное движение головы извлекать или хранить данные.

Кэш может хранить несколько запросов на запись журналов и данных одновременно. Протокол WAL и реализация протокола WAL Server требуют очистки журналов записи в стабильное хранилище, прежде чем запись страницы может быть выдана. Однако использование кэша может возвращать успешное выполнение из запроса записи журнала без записи данных на фактический диск (то есть записывается в стабильное хранилище). Это может привести к тому, что SQL Server выдает запрос на запись страницы данных.

При участии кэша записи данные по-прежнему считаются в нестабильном хранилище. Однако из вызова WriteFile API Win32 именно то, как SQL Server видит действие, получен успешный код возврата. SQL Server или любой процесс, использующий вызов API WriteFile, может определить только то, что данные правильно получили стабильное хранилище.

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

При оценке оптимальных факторов производительности сервера базы данных следует учитывать множество факторов. Самое важное из них: "Разрешает ли моя система допустимые FILE_FLAG_WRITE_THROUGH возможности?"

Примечание.

Любой кэш, который вы используете, должен полностью поддерживать решение с поддержкой батареи. Все остальные механизмы кэширования подвержены повреждению данных и потере данных. SQL Server делает все усилия, чтобы обеспечить wal, включив FILE_FLAG_WRITE_THROUGH.

Тестирование показало, что многие конфигурации дискового диска могут содержать кэширование записи без соответствующей резервной копии батареи. Диски SCSI, IDE и EIDE используют все преимущества кэшей записи. Дополнительные сведения о том, как SSD работают вместе с SQL Server, см. в следующей статье блога инженеров SQL Server CSS:

SQL Server и SSDs — заметки об обучении RDORR — часть 1

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

Диски SCSI также имеют кэши записи. Однако эти кэши обычно могут быть отключены операционной системой. Если есть какой-либо вопрос, обратитесь к изготовителю диска для соответствующих служебных программ.

Запись стека кэша

Стек кэша записи аналогичен упорядочению секторов. Следующее определение было взято непосредственно с веб-сайта ведущего производителя диска IDE:

Обычно этот режим активен. Режим кэширования записи принимает данные записи узла в буфер, пока буфер не будет заполнен или передача узла завершена.

Задача записи диска начинает хранить данные узла на диск. Команды записи узла продолжают приниматься и данные передаются в буфер, пока не будет заполнен стек команд записи или буфер данных заполнен. Диск может изменить порядок команд записи для оптимизации пропускной способности диска.

Автоматическое размещение операций записи (AWR)

Другой распространенный способ защиты данных — обнаружение плохих секторов во время обработки данных. Следующее объяснение исходит от веб-сайта ведущего производителя диска IDE:

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

Это может быть мощная функция, если для кэша предоставляется резервное копирование батареи. Это обеспечивает соответствующее изменение при перезапуске. Предпочтительнее обнаружить ошибки диска, но безопасность данных протокола WAL снова потребует, чтобы это было сделано в режиме реального времени, а не в отложенном режиме. В параметрах WAL метод AWR не может учитывать ситуацию, в которой запись журнала завершается сбоем из-за ошибки сектора, но диск заполнен. Ядро СУБД должно немедленно знать о сбое, чтобы транзакция была правильно прервана, администратор может быть оповещен, а также правильные шаги, предпринятые для защиты данных и исправления ситуации с сбоем мультимедиа.

Безопасность данных

Существует несколько мер предосторожности, которые должен принять администратор базы данных, чтобы обеспечить безопасность данных.

• Всегда рекомендуется убедиться, что стратегия резервного копирования достаточно для восстановления после катастрофического сбоя. Хранилище вне сайта и другие меры предосторожности соответствуют требованиям.
• Проверка операции восстановления базы данных в вторичной или тестовой базе данных на частой основе.
• Убедитесь, что любые устройства кэширования могут обрабатывать все ситуации сбоя (сбой питания, плохие секторы, плохие диски, системный сбой, блокировки, пик питания и т. д.).
• Убедитесь, что устройство кэширования:
  • Встроенная резервная копия батареи
  • Может повторно выполнять записи в power-up
  • Может быть полностью отключен, если это необходимо
  • Обрабатывает повторную обработку плохого сектора в режиме реального времени
• Включите обнаружение разорванной страницы. (Это мало влияет на производительность.)
• Настройте диски RAID, позволяющие использовать горячий обмен плохим диском, если это возможно.
• Используйте более новые контроллеры кэширования, которые позволяют добавлять больше места на диске без перезапуска ОС. Это может быть идеальным решением.

Тестовые диски

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

Примечание.

Убедитесь, что альтернативный механизм кэширования может правильно обрабатывать несколько типов сбоев.

Корпорация Майкрософт провела тестирование на нескольких дисках SCSI и интегрированной среды разработки с помощью служебной SQLIOSim программы. Эта программа имитирует интенсивное асинхронное действие чтения и записи на имитированное устройство данных и устройство журнала. Статистика производительности тестирования показывает средние операции записи в секунду от 50 до 70 для диска с отключенным кэшированием записи и диапазоном RPM от 5200 до 7200.

Дополнительные сведения о служебной SQLIOSim программе см. в следующей статье базы знаний Майкрософт:

Использование служебной программы SQLIOSim для имитации действий SQL Server в подсистеме диска

Многие производители компьютеров заказывают диски, отключив кэш записи. Однако тестирование показывает, что это может быть не всегда так. Поэтому всегда тестировать полностью.

Устройства с данными

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

Страницы данных могут оставаться в кэше, пока процесс LazyWriter или CheckPoint не сбрасывает их в стабильное хранилище. Использование протокола WAL, чтобы убедиться, что записи журнала правильно хранятся, убедитесь, что восстановление может восстановить страницу данных до известного состояния.

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

Повышение производительности

Первый вопрос, который может возникнуть у вас: "У меня есть диск интегрированной среды разработки, который был кэшированием. Но когда я отключил его, моя производительность стала меньше ожидаемой. Почему?"

Многие диски интегрированной среды разработки, тестируемые корпорацией Майкрософт, выполняются на уровне 5200 RPM, и диски SCSI на 7200 RPM. При отключении кэширования записи диска интегрированной среды разработки производительность может стать фактором.

Чтобы устранить разницу в производительности, метод, который следует выполнить, ясно: "Адрес скорости транзакций".

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

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

Тестирование показывает, что высокая активность операций записи буферов меньше 512 КБ или больше 2 МБ может привести к замедлению производительности.

Рассмотрим следующий пример:

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

Ниже приведены примеры результатов теста для SQL Server:

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

Процесс упаковки всей серии операций INSERT в одну транзакцию выполняется примерно в четыре секунды во всех конфигурациях. Это связано с количеством необходимых сбросов журналов. Если вы не создаете одну транзакцию, каждая INSERT транзакция обрабатывается как отдельная транзакция. Поэтому все записи журнала для транзакции должны быть промыты. Каждая очистка составляет 512 байтов. Для этого требуется значительное вмешательство механических дисков.

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

Предупреждение

Рекомендуется не увеличивать область транзакций. Длительные транзакции могут привести к чрезмерной и нежелательной блокировке и увеличению затрат. Используйте счетчики производительности SQL Server:Database SQL Server для просмотра счетчиков на основе журнала транзакций. В частности, поток байтов журнала может указывать на множество небольших транзакций, которые могут привести к высокой активности механических дисков.

Изучите инструкции, связанные с очисткой журнала, чтобы определить, можно ли уменьшить значение flushed/sec в байтах журнала. В предыдущем примере использовался одна транзакция. Однако во многих сценариях это может привести к нежелательному поведению блокировки. Изучите структуру транзакции. Для выполнения пакетов можно использовать код, аналогичный следующему коду, чтобы уменьшить частое и небольшое действие очистки журналов:

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

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