1С 8.3 : Регламентные операции на уровне субд для MS SQL Server, Оптимизация работы. Реиндексация базы sql 1с

1С 8.2 УП : Регламентные операции на уровне субд для MS SQL Server, Оптимизация работы » Администрирование » FAQ 1С 8.2 УП : » HelpF.pro

Выполнение регламентных процедур должно быть автоматизировано. Для автоматизации этих операций рекомендуется использовать встроенное средства 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 запрос:
Код 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 запрос:
Код SQL DBCC FREEPROCCACHE
Этот запрос следует выполнять непосредственно после обновления статистики. Соответственно, частота его выполнения должна совпадать с частотой обновления статистики.
Настройка очистки процедурного КЭШа

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

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

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

Код SQL sp_msforeachtable N'DBCC INDEXDEFRAG (<имя базы данных>, ''?'')'
Дефрагментация индексов не блокирует таблицы, и не будет мешать работе других пользователей, однако создает дополнительную нагрузку на SQL Server. Оптимальная частота выполнения данной регламентной процедуры должна подбираться в соответствии с нагрузкой на систему и эффектом, получаемым от дефрагментации. Рекомендуется выполнять дефрагментацию индексов не реже одного раза в день.
Возможно выполнение дефрагментации для одной или нескольких таблиц, а не для всех таблиц базы данных.
Настройка дефрагментации индексов (MS SQL 2005)
В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Reorganize Index Task:
Задайте расписание выполнения для задачи дефрагментации индексов. Рекомендуется выполнять задачу не реже одного раза в неделю, а при высокой изменчивости данных в базе еще чаще – до одного раза в день.
Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.

Реиндексация таблиц включает полное перестроение индексов таблиц базы данных, что приводит к существенной оптимизации их работы. Рекомендуется выполнять регулярную переиндексацию таблиц базы данных. Для реиндексации всех таблиц базы данных необходимо выполнить следующий SQL запрос:
Код SQL sp_msforeachtable N'DBCC DBREINDEX (''?'')'
Реиндексация таблиц блокирует их на все время своей работы, что может существенно сказаться на работе пользователей. В связи с этим реиндексацию рекомендуется выполнять во время минимальной загрузки системы.
После выполнения реиндексации нет необходимости делать дефрагментацию индексов.

В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Rebuild Index Task:
Задайте расписание выполнения для задачи реиндексирования таблиц. Рекомендуется выполнять задачу во время минимальной нагрузки на систему, не реже одного раза в неделю.
Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.

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

Необходимо осуществлять регулярный контроль выполнения регламентных процедур на уровне СУБД. Ниже приведен пример контроля выполнения плана обслуживания для MS SQL Server 2005.
Откройте созданный вами план обслуживания и выберите из контекстного меню пункт «View History»:
Откроется окно с протоколом выполнения всех заданных регламентных процедур.
Успешно выполненные задачи и задачи, выполненные с ошибками, будут помечены соответствующими иконками. Для задач, выполненных с ошибками, доступна подробная информация об ошибке.
Источник: Регламентные операции MS SQL Server. Оптимизация работы
helpf.pro

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

Общая информация

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

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

Выполняться регламентные процедуры должны полностью автоматически. С целью автоматизации операций рекомендовано использовать специальные средства, встроенные в MS SQL Server: MaintenancePlan. Несмотря на то, что существуют и другие способы автоматизировать выполнение процедур, в этой статье для всех регламентных процедур даны примеры настройки с помощью MaintenancePlan от MS SQL Server 2008.

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

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

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

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

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

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

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

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

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

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

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

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

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

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

Теперь создавайте субплан (команда Add Sublan) и называйте его, к примеру, «Обновление статистик». Добавляйте задачу Update Statistics Task, воспользовавшись панелью задач:

Теперь нужно настроить расписание обновления статистик. Обновляйте статистики, как уже упоминалось, не реже раза в 24 часа. В определенных случаях частоту обновления статистик нужно будет увеличить.

Осталось настроить параметры задачи. Нужно два раза кликнуть по задаче в нижнем правом углу окна. В форме, которая появится на экране, необходимо указать имя вашей базы (либо нескольких баз), для которых будет осуществляться процесс обновления статистик. Также вы сможете указать, для каких конкретно таблиц необходимо обновлять статистики (устанавливайте значение 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 (<здесь имя базы данных>, ''?'')'

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

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

Настройка дефрагментации индексов для 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»:

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

Задачи, выполненные с ошибками, и успешно выполненные задачи будут отмечены характерными иконками. Для задач, которые были выполнены с ошибками, доступен просмотр подробной информации об ошибке.

www.compline-ufa.ru

Особенности эксплуатации SQLServer 2005-2008 для 1C-Предприятие 8

Все админы делятся на тех, кто еще не делает бэкапы и тех, кто уже делает бекапы.

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

Если в работающей системе наблюдаются какие-либо симптомы проблем с производительностью, следует проверить, что в системе правильно настроены и регулярно выполняются все рекомендуемые регламентные операции на уровне СУБД.Выполнение регламентных процедур должно быть автоматизировано. Для автоматизации этих операций рекомендуется использовать встроенное средства MS SQL Server: Maintenance Plan. Существуют так же другие способы автоматизации выполнения этих процедур.Для 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.

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

Если заставлять ЛЮБУЮ СУБД выполнять много “ненужной работы”, то как бы вы не “тюнили” настройки, эта СУБД все равно в какой-то момент достигнет порога своих возможностей.

Материал основан на Базе знаний 1С:Предприятие 1С:Предприятие 8. База знанийПроверено на личном опыте.Может быть интересно по этой теме:Проектирование сервера под 1С Параметры сервера для работы в 1С

osergey.ru

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

Одной из распространенных причин не оптимальной работы СУБД MS SQL являет неправильная настройка выполнения регламентных операций в системе управления базы данных. Обычно правильная настройка сразу же оказывает положительный эффект на производительность системы в целом. Эту информацию должен знать каждый программист 1С.

Ниже в рамках подготовки к сертификации «Эксперт по технологическим вопросам» я опишу ряд мер, с помощью которых без привлечения дополнительных ресурсов можно повысить производительность.

Виды регламентных операций СУБД MS SQL

обновление статистик;
очистка процедурного кеша;
дефрагментация индексов;
реиндексация таблиц БД.

Для успешной сдачи сертификата «1С Эксперт» необходимо знать эти виды операций и как их правильно настроить. Далее подробно остановимся на каждой: зачем необходимо выполнять их и с какой периодичностью.

Получите 267 видеоуроков по 1С бесплатно:

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

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

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

По умолчанию SQL Server настроен на частичное автоматическое обновление статистики. Однако желательно периодически запускать полное обновление статистики (full scan).

Обновление статистики не требует монопольного режима — оно не блокирует таблицы БД.

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

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

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

Для очистки кэша 1С не нужно выгонять пользователей, она работает в фоновом режиме.

Время выполнения — сразу после обновления статистики (не реже раза в день).

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

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

Дефрагментацию можно проводить с работающими пользователями — она не блокирует БД.

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

Реиндексация таблиц БД

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

Выполнение операции доступно только в монопольном режиме — она блокирует таблицу БД MS SQL.

Данную регламентную операцию рекомендует производить не реже одного раза в неделю.

К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.

programmist1s.ru

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

Опубликовал @de1 | Дата 8 июня, 2012

Продолжение статей:

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

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

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

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

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

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

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

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

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

В следующей статье напишу о контроле выполнения регламентных процедур на уровне СУБД.

Затвитить пост!

Рейтинг: Загрузка...

Популярность: Просмотров: 855

aminovadel.com

1С 8.3 : Регламентные операции на уровне субд для MS SQL Server, Оптимизация работы » Администрирование » FAQ 1С 8.3 : » HelpF.pro

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

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

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

MS SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, что MS SQL Server стабильно строил наиболее оптимальный план выполнения всех запросов.
В этом случае возможно проявление проблем с производительностью запросов. При этом в планах запросов наблюдаются характерные признаки неоптимальной работы (неоптимальные операции).
Для того, чтобы гарантировать максимально правильную работу оптимизатора MS SQL Server рекомендуется регулярно обновлять статистики базы данных MS SQL.
Для обновления статистик по всем таблицам базы данных необходимо выполнить следующий 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 запрос:
Код SQL DBCC FREEPROCCACHE
Этот запрос следует выполнять непосредственно после обновления статистики. Соответственно, частота его выполнения должна совпадать с частотой обновления статистики.
Настройка очистки процедурного КЭШа

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

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

При интенсивной работе с таблицами базы данных возникает эффект фрагментации индексов, который может привести к снижению эффективности работы запросов.
Рекомендуется регулярное выполнение дефрагментации индексов. Для дефрагментации всех индексов всех таблиц базы данных необходимо использовать следующий SQL запрос (предварительно подставив имя базы):
Код SQL sp_msforeachtable N'DBCC INDEXDEFRAG (<имя базы данных>, ''?'')'
Дефрагментация индексов не блокирует таблицы, и не будет мешать работе других пользователей, однако создает дополнительную нагрузку на SQL Server. Оптимальная частота выполнения данной регламентной процедуры должна подбираться в соответствии с нагрузкой на систему и эффектом, получаемым от дефрагментации. Рекомендуется выполнять дефрагментацию индексов не реже одного раза в день.
Возможно выполнение дефрагментации для одной или нескольких таблиц, а не для всех таблиц базы данных.
Настройка дефрагментации индексов (MS SQL 2005)
В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Reorganize Index Task:
Задайте расписание выполнения для задачи дефрагментации индексов. Рекомендуется выполнять задачу не реже одного раза в неделю, а при высокой изменчивости данных в базе еще чаще – до одного раза в день.
Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.

Реиндексация таблиц включает полное перестроение индексов таблиц базы данных, что приводит к существенной оптимизации их работы. Рекомендуется выполнять регулярную переиндексацию таблиц базы данных. Для реиндексации всех таблиц базы данных необходимо выполнить следующий SQL запрос:
Код SQL sp_msforeachtable N'DBCC DBREINDEX (''?'')'
Реиндексация таблиц блокирует их на все время своей работы, что может существенно сказаться на работе пользователей. В связи с этим реиндексацию рекомендуется выполнять во время минимальной загрузки системы.
После выполнения реиндексации нет необходимости делать дефрагментацию индексов.

В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Rebuild Index Task:
Задайте расписание выполнения для задачи реиндексирования таблиц. Рекомендуется выполнять задачу во время минимальной нагрузки на систему, не реже одного раза в неделю.
Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.

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

Необходимо осуществлять регулярный контроль выполнения регламентных процедур на уровне СУБД. Ниже приведен пример контроля выполнения плана обслуживания для MS SQL Server 2005.
Откройте созданный вами план обслуживания и выберите из контекстного меню пункт «View History»:
Откроется окно с протоколом выполнения всех заданных регламентных процедур.
Успешно выполненные задачи и задачи, выполненные с ошибками, будут помечены соответствующими иконками. Для задач, выполненных с ошибками, доступна подробная информация об ошибке.
Источник: Регламентные операции MS SQL Server. Оптимизация работы
helpf.pro

Как переиндексировать, проверить, восстановить базу на MS SQL Server

Чтобы произвести переиндексацию необходимо выполнить такой запрос (например в QA):USE base_1cEXEC _1sp_DBReindexгде base_1c - имя базы данных. Если хранимой процедуры _1sp_DBReindex нет в базе данных (база не 1С), то можно выполнить такой код (это и есть код хранимой процедуры _1sp_DBReindex):USE base_1cDECLARE @TableName char(32)DECLARE SysCur CURSOR FOR SELECT name FROM sysobjects WHERE type='U'OPEN SysCurFETCH NEXT FROM SysCur INTO @TableNameWHILE @@FETCH_STATUS=0 BEGINDBCC DBREINDEX(@TableName)FETCH NEXT FROM SysCur INTO @TableNameENDCLOSE SysCurDEALLOCATE SysCurгде base_1c - имя базы данных. Соответсвенно код для создания хранимой процедуры:if exists (select * from sysobjects where id = object_id('dbo._1sp_DBReindex') and sysstat & 0xf = 4)drop procedure dbo._1sp_DBReindexCREATE PROCEDURE _1sp_DBReindex ASSET NOCOUNT ONDECLARE @TableName char(32)DECLARE SysCur CURSOR FOR SELECT name FROM sysobjects WHERE type='U'OPEN SysCurFETCH NEXT FROM SysCur INTO @TableNameWHILE @@FETCH_STATUS=0 BEGINDBCC DBREINDEX(@TableName)FETCH NEXT FROM SysCur INTO @TableNameENDCLOSE SysCurDEALLOCATE SysCurКак проверить (восстановить) базу на MS SQL Server средствами сервера

Проверку логической целостности нужно выполнять штатными средствами 1С:Предприятия (Тестирование и исправление ИБ). В случае, если такую проверку не удается выполнить, следует проверить физическую целостность БД средствами MS SQL. Для проверки целостности средствами MS SQL нужно выполнить следующую команду:

DBCC CHECKDB ("имя базы", REPAIR_REBUILD) Перед выполнением этой команды нужно базу данных перевести в режим "single user":sp_dboption "имя базы", single user', trueВ процессе работы DBCC CHECKDB могут быть обнаружены ошибки и часть может быть сразу же исправлена. Если ошибки остались, то по всей видимости их нельзя восстановить без потери некоторых данных. В этом случае нужно запустить DBCC CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS (перед запуском желательно сделать копию файлов базы данных).DBCC CHECKDB ("имя базы", REPAIR_ALLOW_DATA_LOSS)После выполнения DBCC CHECKDB нужно не забыть вернуться в нормальный режим (выйти из режима "single user"):sp_dboption "имя базы",'single user',false Переиндексацию базы данных на MS SQL не нужно делать так часто, как в случае с DBF-версией 1С:Предприятия (например, при аварийном завершении работы пользователя). MS SQL автоматически поддерживает индексы в актуальном состоянии. Пересоздавать индексы имеет смысл в одном из следующих случаев: 1) Индекс физически поврежден. Это случается довольно редко и для восстановления нужно использовать вышеупомянутый DBCC CHECKDB. 2) Страницы индекса сильно фрагментированы и требуется их упорядочить. 3) Нужно изменить степень заполнения индексных страниц (fill factor). 4) Требуется изменить тип индекса (кластерный/некластерный). При использовании 1С это обычно неактуально

Оригинал: http://forum.mista.ru/topic.php?id=255611#15

mitkin.blogspot.com