SQL Server: Лучшие советы по эффективному обслуживанию баз данных

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

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

Управление файлами данных и журналов
Фрагментация индекса
Статистика
Обнаружение повреждений
Запасные копии

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

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

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

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

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

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

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

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

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

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

Размер автоматического роста следует установить на определенное значение, а не на процент, чтоб ограничивать время и место, нужные для выполнения автоматического роста, если оно происходит. К примеру, в случае 100-гигабайтного файла данных лучше зафиксировать размер автоматического роста как 5 ГБ, а не, скажем, 10%