Регламентные операции на уровне СУБД для MS SQL Server

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition')
9.00.4035.00 SP3 Standard Edition (64-bit)

Уменьшение размера журнала транзакций Microsoft SQL Server

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

Проблема

Рост файла журнала транзакций. С помощью команды DBCC SHRINKFILE не удается уменьшить размер файла журнала транзакций до нужного размера .

Решение

Для решения описанной проблемы необходимо предварительно удалить неактивные записи журнала транзакций с помощью команды BACKUP LOG, а затем уже с помощью команды DBCC SHRINKFILE уменьшить размер файла журнала транзакций.
Последовательность команд, которую нужно исполнить в Query Analyzer, выглядит следующим образом:
BACKUP LOG Имя_Базы_Данных WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE(Имя_Файла_Журнала_Транзакций) 
go
USE "torg"
BACKUP LOG "torg" WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE("torg_log")
go

Более подробное описание и рекомендации по использованию этих команд можно найти в документации по Microsoft SQL Server.

"C:\Program Files (x86)\1cv81\bin\1cv8.exe" DESIGNER /S"storm\DANPRT" /WA+ /NGilV /Out"C:\logs\log1c.txt" /DumpResult"C:\logs\result1c.txt" /DisableStartupMessages /CheckConfig -ConfigLogicalIntegrity /IBCheckAndRepair  -LogAndRefsIntegrity -RecalcTotals -Rebuild -BadRefCreate -BadDataCreate 
Расшифрую, что выполняет этот файл.

C:\Program Files (x86)\1cv81\bin\1cv8.exe" DESIGNER - запуск клиентской части 1С:Предприятие 8.1 в режиме "Конфигуратора";

/S"storm\DANPRT" - указываю Информационную Базу на сервере приложений 1С:Предприятие 8.1;

/WA+ - указываю авторизацию средствами Windows (это позволяет избегать указания в открытом виде пароля, как это бы пришлось сделать при авторизации средствами 1С:Предприятие);

/NGilV - пользователь GilV , созданный в информационной базе и имеющий авторизацию средствами Windows;

/Out"C:\logs\log1c.txt" - пишется в лог 0 или 1 -- как признак успешности выполнения задачи;

/DumpResult"C:\logs\result1c.txt" - пишется лог с результатами "Тестирования и Исправления";

/DisableStartupMessages - Подавляет стартовые сообщения вроде "Конфигурация базы данных не соответствует сохраненной конфигурации. Продолжить?";

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

 /IBCheckAndRepair - операция "тестирование и исправление" информационной базы;
-LogAndRefsIntegrity - проверка логической и ссылочной целостности;
-RecalcTotals - пересчет итогов;
-Rebuild - реструкторизация таблиц информационной базы (это процедура фактически означает "перестройку всех данных и приемлема только для небольших объемов данных);
-BadRefCreate - опция исправления "Создавать ссылки" (исхожу из логики, что удалить никогда не поздно);
-BadDataCreate - опция исправления "Создавать объекты" (исхожу из логики, что удалить никогда не поздно);

Особенности организации резервного копирования баз данных Microsoft SQL Server на сетевой диск

При организации резервного копирования стандартными средствами Enterprise Manager невозможно создать устройство резервного копирования, расположенное на сетевом диске, поскольку Enterprise Manager видит только локальные, физически подключенные диски. Поэтому выполнение резервного копирования возможно только на локальные диски компьютера, на котором установлен Microsoft SQL Server. Использование этого варианта организации резервного копирования существенно снижает надежность, поскольку в случае выхода компьютера из строя становится недоступной как рабочая база данных, так и резервная копия.
Решить эту проблему можно путем создания сетевого устройства резервного копирования с помощью системной процедуры SP_ADDUMPDEVICE. Для этого нужно в Query Analyzer выполнить следующую команду:

EXEC SP_ADDUMPDEVICE 'disk', 'Логическое_Имя_Сетевого_Устройства_Резервного_Копирования', '\\Имя_Компьютера\Имя_Каталога\Имя_Файла.bak'

Необходимым условием использования сетевого устройства резервного копирования является запуск Microsoft SQL Server с использованием доменной учетной записи. Проверить и изменить учетную запись, используемую для запуска Microsoft SQL Server можно в Enterprise Manager, на закладке Security диалога редактирования свойств  Microsoft SQL Server.

После создания сетевого устройства резервного копирования его можно использовать при выполнении резервного копирования средствами Enterprise Manager или с помощью команды BACKUP DATABASE выполняемой в Query Analyzer, например:

BACKUP DATABASE Имя_Базы_Данных ТО Устройства_Резервного_Копирования

Более подробное описание и рекомендации по использованию этих команд можно найти в документации по Microsoft SQL Server.

Общие сведения

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

Если в работающей системе наблюдаются какие-либо симптомы проблем с производительностью, следует проверить, что в системе правильно настроены и регулярно выполняются все рекомендуемые регламентные операции на уровне СУБД.

Выполнение регламентных процедур должно быть автоматизировано. Для автоматизации этих операций рекомендуется использовать встроенное средства MS SQL Server: Maintenance Plan. Существуют так же другие способы автоматизации выполнения этих процедур. В настоящей статье для каждой регламентной процедуры дан пример ее настройки при помощи Maintenance Plan для MS SQL Server 2005.

Для MS SQL Server рекомендуется выполнять следующие регламентные операции:

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

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

Обновление статистик

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

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

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

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

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

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

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

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

Настройка автоматического обновления статистик (MS SQL 2005)

Запустите MS SQL Server Management Studio и подключитесь к серверу СУБД. Откройте папку Management и создайте новый план обслуживания:



Создайте субплан (Add Sublan) и назовите его «Обновление статистик». Добавьте в него задачу Update Statistics Task из панели задач:



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

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

Обновление статистик необходимо проводить с включенной опцией Full Scan.



Сохраните созданный план. При наступлении указанного в расписании срока обновление статистик будет запущено автоматически.

Очистка процедурного КЭШа

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

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

DBCC FREEPROCCACHE

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

Настройка очистки процедурного КЭШа (MS SQL 2005)

Поскольку процедурный КЭШ необходимо очищать при каждом обновлении статистики, данную операцию рекомендуется добавить в уже созданный субплан «Обновление статистик». Для этого следует открыть субплан и добавить в его схему задачу Execute T-SQL Statement Task. Затем следует соединить задачу Update Statistics Task стрелочкой с новой задачей.



В тексте созданной задачи Execute T-SQL Statement Task следует указать запрос «DBCC FREEPROCCACHE»:

Дефрагментация индексов

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

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

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

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

Возможно выполнение дефрагментации для одной или нескольких таблиц, а не для всех таблиц базы данных.
Настройка дефрагментации индексов (MS SQL 2005) В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Reorganize Index Task:



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

Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.

Реиндексация таблиц базы данных

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

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

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

После выполнения реиндексации нет необходимости делать дефрагментацию индексов.

Настройка реиндексации таблиц (MS SQL 2005)

В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Rebuild Index Task:



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

Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.

Контроль выполнения регламентных процедур на уровне СУБД

Необходимо осуществлять регулярный контроль выполнения регламентных процедур на уровне СУБД. Ниже приведен пример контроля выполнения плана обслуживания для MS SQL Server 2005.

Откройте созданный вами план обслуживания и выберите из контекстного меню пункт «View History»:



Откроется окно с протоколом выполнения всех заданных регламентных процедур.



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

sp_dbcmptlevel @dbname = pubs, @new_cmptlevel = 65 GO BACKUP LOG pubs WITH TRUNCATE_ONLY

и так

sp_dbcmptlevel @dbname = pubs, @new_cmptlevel = 80 GO BACKUP LOG pubs WITH TRUNCATE_ONLY

источник