Рекомендации по настройке регламентных и вспомогательных процедур
В данном разделе будет приведен перечень процедур, которые необходимо выполнять при эксплуатации системы. К сожалению, данная информация полезна только при использовании СУБД 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).