Основы ввода-вывода SQL Server

Применимо к:SQL ServerAzure SQL Managed InstanceSQL Server на виртуальных машинах Azure

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

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

SQL Server требует, чтобы системы поддерживали гарантированную доставку на стабильный носитель , как описано в соответствии с требованиями программы надежности операций ввода-вывода SQL Server. Для получения дополнительной информации о требованиях к входным и выходным данным для движка базы данных SQL Server см. раздел SQL Server Database Engine Disk Input/Output (I/O) requirements.

Операции дискового ввода-вывода

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

Дисковые операции ввода-вывода, выполняемые диспетчером буферов, имеют следующие характеристики.

• Операции ввода-вывода обычно выполняются асинхронно, что позволяет вызывающему потоку продолжать обработку, пока операция ввода-вывода выполняется в фоновом режиме. В некоторых случаях (например, неправильное ведение журнала ввода-вывода) может произойти синхронный ввод-вывод.

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

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

Запросы на длительные операции ввода-вывода

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

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

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

Причины длинных запросов ввода-вывода

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

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

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

Компонент в пути ввода-вывода (например, драйвер, контроллер или встроенное ПО) может привести к длительному выполнению операций ввода-вывода, постоянно отложив обслуживание старого запроса ввода-вывода в пользу обслуживания новых запросов. Эта проблема может возникать в взаимосвязанных средах, таких как сети iSCSI и Fibre Channel (из-за неправильной настройки или сбоя пути). Средство монитора производительности может затруднить подтверждение этой проблемы, так как большинство устройств ввода-вывода оперативно обслуживаются. Рабочие нагрузки, выполняющие большие объемы последовательных операций ввода-вывода, таких как резервное копирование и восстановление, сканирование таблиц, сортировка, создание индексов, массовая загрузка и отсчитывание файлов, может усугубить длительные запросы ввода-вывода.

Изолированный длинный интерфейс ввода-вывода, не связанный с какими-либо предыдущими условиями, может быть вызван проблемой оборудования или драйвера. Системный журнал событий может содержать связанное событие, которое помогает диагностировать проблему.

Проблемы с производительностью ввода-вывода, вызванные неэффективными запросами или драйверами фильтров

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

Подробные сведения об устранении этих проблем см. в статье "Устранение проблем с медленной производительностью SQL Server, вызванной проблемами ввода-вывода".

Сведения о настройке антивирусной защиты на SQL Server см. в статье "Настройка антивирусного программного обеспечения для работы с SQL Server".

Кэширование записи в контроллерах хранения

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

Примечание.

Некоторые поставщики хранилища используют постоянную память (PMEM) в качестве хранилища, а не кэша, что может повысить общую производительность. Дополнительные сведения см. в статье "Настройка постоянной памяти (PMEM) для SQL Server в Windows и настройка постоянной памяти (PMEM) для SQL Server на Linux.

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

Important

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

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

Рекомендации по безопасности подсистемы кэша

Контроллеры кэширования обратной записи могут повысить производительность, если они соответствуют определенным требованиям безопасности:

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

Important

Не стоит полагаться только на внешний ИБП. Ошибки, не связанные с питанием, такие как ошибки встроенного ПО или сбой оборудования, по-прежнему могут привести к потере кэша.

Ведение журнала накануне записи

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

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

Термин протокол это отличный способ описать WAL. WAL, используемый в SQL Server, называется ARIES (Алгоритм восстановления и изоляции с использованием семантики). Дополнительные сведения см. в разделе "Управление ускорением восстановления базы данных".

Это конкретный и определенный набор шагов реализации, необходимый для обеспечения правильного хранения и обмена данными и их восстановление в известном состоянии в случае сбоя. Так же, как сеть содержит определенный протокол для обмена данными согласованным и защищенным способом, поэтому wal описывает протокол для защиты данных. Все версии SQL Server открывают файлы журналов и данных с помощью функции Win32 CreateFile . Член dwFlagsAndAttributes включает параметр FILE_FLAG_WRITE_THROUGH, когда он открыт в SQL Server.

FILE_FLAG_WRITE_THROUGH

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

Этот FILE_FLAG_WRITE_THROUGH параметр гарантирует, что при успешном завершении операции записи данные хранятся в стабильном хранилище. Эта функция соответствует спецификации протокола журналирования WAL (Write-Ahead Logging), чтобы обеспечить целостность данных. Многие устройства хранения (NVMe, PCIe, SATA, ATA, SCSI и IDE) содержат встроенные кэши размером 512 КБ, 1 МБ и больше. Кэши хранилища обычно полагаются на конденсатор, а не на решение с резервным питанием от батареи. Эти механизмы кэширования не могут гарантировать сохранение записей при отключении питания или аналогичных сбоях. Они гарантируют только завершение операций записи в секторе. По мере того как устройства хранения продолжают увеличиваться, кэши становятся больше, и они могут предоставлять большие объемы данных во время сбоя.

Дополнительные сведения о поддержке FUA дистрибутивами Linux и о его влиянии на SQL Server см. в статье SQL Server On Linux: внутренние аспекты принудительного доступа к единицам (FUA).

Целостность транзакций и восстановление SQL Server

Целостность транзакций является одной из основных концепций реляционной системы баз данных. Транзакции являются атомарными единицами выполнения, которые полностью применены или полностью отменены. Журнал транзакций с предварительной записью SQL Server является важным компонентом реализации целостности транзакций.

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

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

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

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

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

Риски кэширования записи на диске

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

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

Другая возможная проблема, связанная с контроллером кэширования записи, может возникнуть при завершении работы системы. Обычно перезагружают ОС или рестартуют систему во время изменений конфигурации. Даже если осторожный оператор следует рекомендации операционной системы, чтобы ждать, пока вся активность хранилища не будет прекращена до перезапуска системы, кэшированные записи по-прежнему могут присутствовать в контроллере. Ctrl + Alt + Del При нажатии сочетания клавиш или нажатии кнопки сброса оборудования кэшированные записи могут быть удалены, что может привести к повреждению базы данных.

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

Использование кэшей хранилища с SQL Server

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

Система должна гарантировать атомарность и устойчивость транзакций, одновременно учитывая текущее выполнение, множественные транзакции и различные точки отказа. Это свойство часто называется свойстваМИ ACID (Атомарность, согласованность, изоляция и устойчивость).

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

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

Также просмотрите следующее архивное содержимое:

• Основы ввода-вывода SQL Server 2000
• Основы ввода-вывода SQL Server глава 2

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

Решения кэширования с поддержкой батареи

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

Реализации подсистемы хранилища

Подсистемы хранения существуют во многих типах. Два распространенных примера: RAID (избыточный массив независимых дисков) и SAN (сеть зоны хранения). Эти системы обычно используют диски на основе SCSI. В следующем разделе описаны общие рекомендации по хранению.

SCSI, SAS и NVMe

Устройства хранения SCSI, SAS и NVMe:

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

Примечание.

SQL Server поддерживает компоненты технологии iSCSI, которые соответствуют требованиям программы совместимости оборудования Windows. Хотя SQL Server не взаимодействует напрямую с iSCSI, он работает без проблем, так как Windows представляет хранилище iSCSI как стандартные диски. ЗАТЕМ SQL Server может считывать данные и записывать данные в удаленное хранилище на уровне блоков в ip-сетях. Так как iSCSI зависит от сетей, вы можете столкнуться с задержками или узкими местами. Убедитесь, что производительность кэширования сервера оптимальна, а задержка сведена к минимуму. Дополнительные сведения см. в разделе об ограничениях масштабируемости целевого сервера iSCSI.

Несовместимо с SCSI

Другие реализации дисков, такие как IDE, ATA и SATA:

• Обычно предназначены для лёгкой и средней нагрузки.
• Обычно предназначены для однопользовательских приложений.

Контроллеры для настольных ПК, не относящиеся к SCSI, требуют больше ресурсов основного процессора (ЦП) и часто ограничиваются одной активной командой. Например, если диск, не относящийся к SCSI, исправляет сбойный блок, диск вынуждает команды хоста ожидать. Шина ATA представляет еще один пример: шина ATA поддерживает два устройства, но только одна команда может быть активной. Это ограничение приводит к тому, что один диск простаивает, пока другой выполняет ожидающую команду. Системы RAID, созданные на основе настольных технологий, могут столкнуться с этими симптомами и значительно пострадать от самого медленного отклика. Если эти системы не используют передовые разработки, их эффективность ниже, чем у систем на основе SCSI.

Рекомендации по работе с хранилищем

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

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

• Схема пути доступа
• Требование отключения кэша на диске

Механические жесткие диски

Механические накопители используют вращающиеся магнитные тарелки для хранения данных. Они доступны в нескольких вариантах емкости и форм-факторов, таких как интерфейс IDE, SATA, SCSI и последовательный интерфейс SCSI (SAS). Некоторые диски SATA включают конструкции прогнозирования сбоев. Диски SCSI предназначены для более тяжелых циклов работы и снижения частоты сбоев.

IDE и системы на основе ATA могут откладывать команды хостов при выполнении таких действий, как корректировка поврежденных блоков, что приводит к периодам приостановки операций ввода-вывода.

Преимущества SAS включают расширенные очереди до 256 уровней, а также очередь с приоритетом и неупорядоченные очереди. Серверная планка SAS разработана таким образом, что позволяет использовать диски SAS и SATA в одной системе.

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

Твердотельное хранилище

Накопитель на твердотельных элементах имеет преимущества по сравнению с механическими (вращающимися) жесткими дисками, но подвержен тем же шаблонам поломок, что и механические носители. Хранилище с твердым состоянием можно подключить к серверу с помощью различных интерфейсов, включая NVM Express (NVMe), PCI Express (PCIe) и SATA. Обращайтесь с твердотельными носителями так же, как с вращающимися носителями, и убедитесь, что предусмотрены необходимые меры безопасности на случай сбоя питания, например, использование контроллера кэширования с резервным питанием от батареи.

К распространенным проблемам, вызванным сбоем питания, относятся:

• Битовое повреждение: журналы показывают случайные битовые ошибки.
• Летающие записи: Хорошо сформированные записи в конечном итоге в неправильном месте.
• Шорн пишет: операции частично выполняются на уровне ниже ожидаемого размера сектора.
• Повреждение метаданных: метаданные на уровне преобразования флэш-памяти (FTL) повреждены.
• Неответственное устройство: устройство не работает вообще или в основном не работает.
• Несериализируемость: окончательное состояние хранилища не приводит к сериализуемому порядку операций.

512e

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

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

0xFFFFFFFF

Недавно отформатированный диск обычно содержит все нули. Стертый блок твердотельного устройства состоит из всех 1s, что приводит к тому, что при необработанном чтении стертый блок отображается как все 0xFF символы. Тем не менее, для пользователя необычно читать стертый блок в ходе стандартных операций ввода-вывода.

Штампование узоров

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

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

Firmware (Встроенное ПО)

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

Чтение повреждения данных и выравнивание износа

Распространенный подход к сбору мусора (GC) для твердотельных накопителей помогает предотвращать повторное повреждение данных при чтении. При повторном чтении одной и той же ячейки возможно, что электронная активность может утекать и причинить ущерб соседним ячейкам. Твердотельное хранилище защищает данные с различными уровнями исправления ошибок кода (ECC) и другими механизмами.

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

Другой побочный эффект выравнивания износа может произойти с SQL Server. Предположим, что вы выполняете DBCC CHECKDB и сообщает об ошибке. Если вы запускаете его во второй раз, существует небольшая вероятность того, что DBCC CHECKDB может сообщать о дополнительных ошибках или иной схеме ошибок, так как GC активность твердотельного хранилища может внести изменения между DBCC CHECKDB выполнениями.

Ошибка ОС 665 и дефрагментация

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

Примечание.

Твердотельное хранилище извлекает пользу из функции TRIM — команды уровня операционной системы (ОС), которая стирает блоки, считающиеся более не используемыми. В Windows используйте средство оптимизации дисков , чтобы задать еженедельное расписание оптимизации дисков.

Рекомендации.

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

• Рекомендуется использовать файловую систему ReFS , чтобы избежать ограничений атрибутов NTFS.

• Убедитесь, что параметры роста файлов соответствуют требованиям.

Дополнительные сведения об устранении ошибки ОС 665, связанной с фрагментацией, см. в разделе об ошибках ОС 665 и 1450 для файлов SQL Server.

Сжатие

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

Итоги

• Обеспечение правильного резервного копирования и аварийного восстановления процедур и процессов.
• Обновляйте встроенное ПО.
• Внимательно слушайте рекомендации производителя оборудования.

Конфигурация кэша диска

Чтобы использовать диск с SQL Server, отключите кэширование дисков. По умолчанию кэш дисков включен. В Windows Server перейдите на вкладку Свойства диска>Оборудование>Политика, чтобы отключить кэширование записи на уровне ОС.

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

Примечание.

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

Рекомендации по кэшу и SQLIOSim

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

Примечание.

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

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

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

Риски неправильного кэширования записей

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

• Потеря данных, где зафиксированные транзакции никогда не сохраняются.
• Повреждение базы данных из-за нарушенных гарантий порядка записи.

Important

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

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

Внешние устройства UPS недостаточно, так как они могут не защищаться от всех режимов сбоя, таких как ошибка встроенного ПО контроллера.

Связанный контент

• Руководство по архитектуре страниц и экстентов
• Руководство по архитектуре управления памятью
• Хранилище: Рекомендации по производительности для SQL Server на виртуальных машинах Azure
• SQL Server на Linux: внутренние элементы принудительного доступа к блокам (FUA)
• Основные операции ввода-вывода в SQL Server, раздел 2