Рекомендации по настройке регламентных и вспомогательных процедур

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

Обновление статистик. MS SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, что MS SQL Server стабильно строил наиболее оптимальный план выполнения всех запросов.

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

или

exec sp_updatestats

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

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

DBCC FREEPROCCACHE

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

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

sp_msforeachtable N'DBCC INDEXDEFRAG (<имя базы данных>, ''?'')'

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

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

sp_msforeachtable N'DBCC DBREINDEX (''?'')'

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

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

ALTER DATABASE <Имя базы данных> SET RECOVERY SIMPLE

DBCC SHRINKFILE('Логическое имя файла',<оставляемый размер лога в мегабайтах>)

ALTER DATABASE <Имя базы данных> SET RECOVERY FULL

Также, если используется MS SQL Server 2005 или 2000, то допускается такой вариант

BACKUP LOG <Имя базы данных> WITH TRUNCATE_ONLY

DBCC SHRINKFILE('Логическое имя файла',<оставляемый размер лога в мегабайтах>)

«Логическое имя файла» журнала транзакций можно узнать, выполнив команду

sp_helpdb 'Имя базы данных'. В колонке «name» и будет это имя.

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

ALTER DATABASE [TRADE_COMPLEX_BASE] SET RECOVERY SIMPLE

DBCC SHRINKFILE (N'TRADE_COMPLEX_BASE_log' , 128, TRUNCATEONLY )

ALTER DATABASE [TRADE_COMPLEX_BASE] SET RECOVERY FULL

--

BACKUP DATABASE [TRADE_COMPLEX_BASE]

TO DISK = N'K:\BACKUPS\TRADE_COMPLEX_BASE_backup.bak'

WITH NOFORMAT, NOINIT, NAME = N'TRADE_COMPLEX_BASE_full_backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

После выполнения данного запроса размер файла журнала останется равным 128 Мб.

Самый простой пример расписания выполнения регламентных заданий: ежедневно в 03:00 последовательно выполнить следующие процедуры:

  • Уменьшение размера файла транзакций и резервное копирование БД.
  • Обновление статистик и очистка процедурного КЭШ-а.
  • Реиндексация таблиц базы данных.

Настройку процедур можно произвести с помощью SQL Server Management Studio в разделе «Управление» (Management) -> «Планы обслуживания» (Maintenance Plans).

Наши рекомендации