Советы по эффективному обслуживанию баз данных SQL Server - Управление файлами данных и журналов

 

Управление файлами данных и журналов

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

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

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

Файлы журнала внутренне разделены на разделы, именуемые виртуальными файлами журнала (Virtual Log Files – VLF), и чем выше фрагментация в файле журнала (я использую здесь единственное число, поскольку наличие нескольких файлов журнала не имеет большого смысла – в базе данных следует держать только один), тем больше число VLF. После того, как число VLF в файле журнала превышает, скажем, 200, может ухудшиться производительность связанных с журналом операций, таких как чтение журнала (скажем, для транзакционной репликации/отката), резервное копирование журнала и даже триггеров в SQL Server 2000 (реализация триггеров изменилось в SQL Server 2005 с журнала транзакций на инфраструктуру версий строк).

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

С файлами журналов дело, увы, обстоит несколько сложнее – необходимо учитывать такие факторы, как размер транзакции (долгосрочные транзакции не могут быть удалены из журнала до своего завершения) и частоту резервного копирования журнала (поскольку именно при нем удаляются неактивные части журнала). Дополнительные сведения приведены в "8 Steps to Better Transaction Log Throughput («8 шагов к улучшению пропускной способности журнала транзакций»)", популярной записи в блоге на SQLskills.com, написанной моей женой, Кимберли Трипп (Kimberly Tripp).

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

Размер автоматического увеличения следует установить на определенное значение, а не на процент, чтобы ограничивать время и место, необходимые для выполнения автоматического увеличения, если оно происходит. Например, в случае 100-гигабайтного файла данных желательно зафиксировать размер автоматического увеличения как 5 ГБ, а не, скажем, 10%. Это значит, что он всегда будет увеличиваться на 5 ГБ вне зависимости от текущего размера файла, а не на объем, увеличивающийся после каждого увеличения файла (10 ГБ, 11 ГБ, 12 ГБ и так далее).

Когда журнал транзакций увеличивается (либо вручную, либо через автоматическое увеличение), он всегда инициализируется нулями. Файлы данных имеют то же поведение по умолчанию в SQL Server 2000, но начиная с SQL Server 2005 в них можно включить мгновенную инициализацию файла, которая пропускает инициализацию файлов нулями и, как следствие, делает увеличение и автоматическое увеличение практически мгновенными. В противоположность распространенным представлениям, эта функция доступна во всех выпусках SQL Server. Дополнительные сведения можно найти, введя "instant file initialization" («мгновенная инициализация файла») в указателе электронной документации для SQL Server 2005 или SQL Server 2008.

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

Автоматическое сжатие особенно вредно, поскольку оно запускается каждые 30 минут в фоновом режиме и пытается сжимать базы данных, для которых выставлен параметр автоматического сжатия. Этот процесс не вполне предсказуем в том, что он сжимает лишь базы данных с более чем 25% свободного места. Автоматическое сжатие использует массу ресурсов и вызывает понижающую производительность фрагментацию, так что оно нежелательно при любых обстоятельствах. Его всегда следует отключать с помощью:

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF; 

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

Лучше всего позволить базе данных вырасти до размера стабильного состояния и избегать сжатия вообще. Дополнительные сведения о недостатках использования сжатия, а также кое-какие комментарии по новым алгоритмам в SQL Server 2005 можно найти в моем старом блоге MSDN® на blogs.msdn.com/sqlserverstorageengine/archive/tags/Shrink/default.aspx.