1С:Предприятие 8.3: Администрирование и оптимизация MS SQL Server для поддержки системы 1С:Предприятие. Оптимизация ms sql для 1с

Настройка MS SQL сервер для правильной работы с 1С

Для того чтобы правильно настроить MS SQL сервер для работы с 1С, пришлось проштудировать не один десяток сайтов, обратиться за помощью к продвинутым пользователям, провести эксперимент на нескольких тысячах желающих. Итогом такой кропотливой работы можно только гордиться: работать с 1С посредством сервера MS SQL – одно удовольствие. Делимся с вами рецептом настройки MS SQL сервера.

Настроим сервер

Залог успешного функционирования 1С – работа исключительно с сервером. Поэтому в первую очередь следует отключить все второстепенные службы, которые только снижают скорость работы. Например, абсолютно бесполезной является FullText Search, так как 1С обладает собственным механизмом поиска. Полезными будут лишь такие функции, как SQL Server (sqlservr.exe), SQL Server Agent (SQLAGENT.exe), SQL Writer (sqlwriter.exe).

В свойствах сервера посредством Server Management Studio следует установить:

Посчитаем, сколько памяти можно отвести серверу. От суммарного количества оперативной памяти отнимем 4 Гб (или 2 Гб под Win2003) под систему, на каждый активный процесс rphost отведем 1.5 Гб, при условии, что SQL и 1С вращаются на одном сервере. То есть, если оперативная память сервера 36 Гб, ОС Windows 2008, запущено 5 процессов rphost, то ограничения для SQL будет составлять 24,5 Гб.

Для чего нужно ограничение? Для того чтобы sql сервер заранее освобождал нужное количество памяти. Если заблаговременно не ограничить объем, сервер будет пытаться отыскать бОльшее количество памяти, чем может предоставить сервер, что отразится на скорости его работы.

Максимальное число потоков не должно превышать 2048, в идеале оно должно быть равно 2048, так как при меньшем количестве потоков сервер работает медленнее, что было доказано опытным путем. Приоритет сервиса ставим высокий (Boost priority).

Основные настройки можно считать законченными. Переходим к следующему этапу.

Настроим базы данных

В первую очередь открываем свойства требуемой нам базы данных:

Если пока известен лишь примерный размер базы данных, а сама БД находится в .dt файле, то рационально указать размер первичного файла большим или равным по размеру базы, так как после «распечатывания» файла с БД он увеличится. Автоувеличение размера следует указывать с расчетом 200 Мб на каждую из БД и 50 Мб для лога, в противном случае сервер будет работать медленно, так как увеличение файла нужно будет осуществлять после каждой третьей транзакции. При неиспользовании RAID-массива места хранения файлов базы данных и лога следует указывать на разных физических дисках. И кстати, рекомендуем установить ограничения на логи, от 2 до 4 Гб будет вполне достаточно.

Подробные настройки отображены на скриншоте

На этом можно закончить настройки базы данных.

Настраиваем регламентные задания

В разделе Management следует создать Maintenance Plan:

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

Как настроить бэкап посредством SQL

Для этого нужно добавить Agent'у два новых задания: Full BackUp, периодичность один раз в сутки, два шага T-SQL скриптов:

1. BACKUP DATABASE [<ИмяБД>] TO DISK = N'<ПутьКПапке>\Backup\<ИмяБД>.bak' WITH NOFORMAT, INIT, NAME = N'<ИмяБД>-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

2. USE [<ИмяБД>]

DBCC SHRINKFILE (N'<ИмяБД>_log' , 0)

Периодичность второго задания – один раз в один-два часа Differencial BackUp, один T-SQL скрипт:

BACKUP DATABASE [<ИмяБД>] TO DISK = N'<ПутьКПапке>\Backup\<ИмяБД>Diff.bak' WITH DIFFERENTIAL , NOFORMAT, INIT, NAME = N'<ИмяБД>-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Этот бэкап занимает 4-6 минут, даже если пользователи активно работают, причем на быстродействии сервера это никак не сказывается.

Кроме очистки процедурного после еженедельной переиндексации, в задание, появившееся в агенте после сохранения Maintenance Plan, добавим еще одну задачку:

DBCC FREEPROCCACHE

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

4d.by

Оптимизация 1С Предприятие настройки и администрирование Microsoft SQL порог потребления памяти заказать в Екатеринбурге

Сегодня мы поговорим о настройках самой распространенной СУБД, это Microsoft SQL Server.

Установка порога потребляемой памяти

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

(Всего памяти на сервере – 1024 * Всего памяти на сервере / Всего памяти на сервере * 0,5) – оставить для ОС 1 гб на каждые 16 гб общего размера памяти.

Приоритет MS SQL сервера

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

Включение протокола Shared Memory

Если сервер 1С: Предприятия расположен вместе с Microsoft SQL Server - включить протокол Shared Memory. Данный протокол обменивается через оперативную память, минуя сетевое соединение.

Настройка находится в оснастке “Sql Server Configuration Manager”

Ограничить запросы по времени выполнения

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

На вкладке Database Setting можно задать пути файлов базы данных, логов транзакций и копий по умолчанию.

На этой же странице есть параметр – Default index fill factor. Это так называемый процент заполнения индекса по умолчанию. Когда мы создаем индекс, у него по умолчанию все страницы заполнены и у нас бывает такая проблема, как фрагментация индекса. Проблема фрагментации индексов – это ситуация, когда страницы индекса не до конца заполнены. Такой индекс увеличивается в размере, но при этом у него много страниц. Которые заполнены частично. Это может происходить например, когда страница некого индекса заполнена полностью и мы на нее хотим что-то вставить. Тогда страница разбивается на 2 части. Первая часть страницы остается на своем месте и создается новая страница, на которую переносится половина оставшихся данных. Если такая ситуация происходит часто, то это дополнительная нагрузка для расщепления данных. В итоге получается индекс с большим количеством не до заполненных страниц. Чтобы его прочитать или прочитать его диапазон, необходимо потратить больше времени. А если этот индекс после этого перестроить, либо выполнить операцию дефрагментации, индекс может начать работать быстрее за счет того, что он уменьшается в размере и все страницы располагаются физически ровно на диске.

Default index fill factor нужен для того, чтобы искусственно сделать так, чтобы страницы индекса были не до заполнены. То есть при создании нового индекса у нас страницы чуть-чуть не до заполнены. Это нужно для того, чтобы всегда место на страницах оставалось, и мы могли туда вставить какие-либо новые записи и при этом не увидеть расщепление страницы. Обычно для баз 1С данный параметр не меняют, а если уж и меняют, то делают это на определенных индексах.

Max Degree of Parallelism – параллельное выполнение плана запроса ядрами процесора. Значение 1 означает, что параллелизм выключен, а значение 0 означает, что оптимизатор сам решит, сколько ядер ему использовать. Есть рекомендация Microsoft, которая говорит, что нельзя ставить значение в данном параметре больше, чем число ядер, находящимся в одном процессорном сокете. Это справедливо для NUMA архитектуры. С данным параметром можно экспериментировать, лучше сразу не ставить большое значение, поставьте, к примеру для начала значение 4. Если вы видите, что нагрузка слишком высокая, попробуйте уменьшить число параллельных потоков, а значение параметра “Cost Threshold for Parallelism” наоборот увеличить, к примеру значение 300. А если вы видите, что процессор, наоборот, не нагруженный и запас производительности высокий, параметр “Cost Threshold for Parallelism” уменьшите, к примеру, до 120, а параметр “Max Degree of Parallelism” увеличить. Если значение параметра равно не единице (1), то оптимизатор строит 2 плана запроса. Один план запроса для однопоточного выполнения, второй план запроса для параллельного выполнения.

Cost Threshold for Parallelism – время в секундах, после которого запрос начнет выполняться в параллельном режиме.

Ниже приведены картинки данных параметров и картинка плана запроса, который выполняется параллельно.

Системные базы данных SQL

Tempdb - хранит временные таблицы, промежуточные планы запросов, промежуточные данные при перестроении индексов. Данная база может становиться “узким местом”. Так как мы часто используем временные таблицы. Данную базу иногда выносят на отдельные диски SSD, иногда даже на RAM диск. За счет этого можно получить выигрыш в производительности. Есть рекомендация, разбивать tempdb на части, обычно на 4 файла. Единственное условие, чтобы данные файлы были одинакового размера. На небольших системах прирост производительности будет не заметной.

Master – база всех настроек MS SQL сервера.

Model – шаблон для создания новых баз данных. Все настройки, заданные в данной базе, при создании новой базы, будут взяты отсюда.

Msdb – служебная база, её использует агент MS SQL сервера для запуска и работы заданий.

Перемещение баз данных

Особенность всех системных баз, что их просто так не переместить. Их необходимо перемещать скриптами. Пример перемещения базы данных tempdb скриптом:

Если мы хотим переместить обычные пользовательские базы данных, мы можем поступить следующим образом:

1. Правой кнопкой мыши на перемещаемую базу> tasks> detach…

1. Поставить галочку напротив базы данных “Drop Connections”

1. После этого нажать “ОК”

Чтобы присоединить базу, необходимо сделать Attach… и выбрать файл базы данных

Флаги трассировки MS SQL

1. Только в режиме отладки! Могут замедлить работу программы.

Т2301 - более глубокая оптимизация

Т8780, Т8788 (аналог) - отключение таймаута оптимизации

Т8671 - отключает отбрасывание неэффективных ветвей для поиска Good Enough Plan

2. ХОТФИКСЫ ДЛЯ ОПТИМИЗАТОРА ЗАПРОСОВ

Т4199 - свободно собраны хотфиксы для оптимизатора запросов для SQL Server 2005 - 2014

Эти улучшения (хотфиксы) по умолчанию выключены,

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

По умолчанию Т4199 включен в SQL Server 2016, если база в режиме совместимости 130 или выше.

Пример команд включения флагов трассировок:

DBCC TRACEON (2301, -1)

DBCC TRACEOFF (2301, -1)

DBCC TRACESTATUS ()

sovetnik1c.ru

Настройка Microsoft SQL Server для 1С Предприятие (Maintenance Plans) : Black Box

Доброго времени, гости и читатели блога k-max.name. Сегодня публикую небольшую мемори-записку о настройке Microsoft SQL 2005 для 1С Предприятия. Думаю для других нужд использования MS SQL данная статья тоже даст некоторую информацию. Итак, начнем...

Шаг 0. Перед установкой и настройкой MS SQL 2005 желательно иметь 3 физических диска. Один - для системы, второй - для файлов баз и третий - для журналов транзакций SQL. При этом, раздел для логов SQL и tempdb желательно чтобы был более производительным (например RAID 1+0).

Шаг 1. Установка сервера MS SQL

При установке SQL-сервера для работы с 1С достаточно следующих включенных компонентов (более подробно о компонентах Microsoft SQL тут, об установке серера тут):

На данном изображении: - Integration services - не обязательный элемент - необходим для управления пакетами SSIS (планами обслуживания (экспорт/импорт)), потом его можно будет отключить - Database Servises - собственно сам сервер СУБД - Client Component - Managment Tools - утилита управления

Остальные настройки при установке по Вашему вкусу. Единственный нюанс - необходимо правильно установить способ сортировки collate. Для автоматической и правильной работы необходимо в "Языке и региональных стандартах" операционной системы выбрать "Русский". В этом случае при установке SQL Server сам предложит правильную сортировку Cyrillic_General_CI_AS. Выбор режима проверки подлинности пользователей укажите смешанный (mixed). Остальные параметры всегда можно скорректировать после установки - 1С:Предприятие будет работать независимо от них.

Более подробно об установке (ахтунг - English):

Желательно обновить сервер MS SQL до актуального релиза (на текущий момент - SP4 для 2005 SQL). Кроме того, на многопроцессорных системах сервер Microsoft SQL 2005 может отказаться устанавливаться с ошибкой 1053 (The error is (1053) The service did not respond to the start or control request in a timely fashion). Решение этой проблемы описано тут.

Шаг 2. Настройка сервера Microsoft SQL 2005

2.1. Настройка протоколов подключения

Для настройки протоколов взаимодействия сервера и клиента Microsoft SQL необходимо запустить "SQL Server Configuration Manager":

...и оставить для работы только протоколы TCP/IP и Shared Memory:

Если устанавливается версия MS SQL Express по-умолчанию выключен протокол TCP/IP, нужный для работы с 1С:Предприятие 8 - его необходимо включить. Протокол именнованных каналов (Named Pipe) выключите совсем (и для "клиента" тоже на сервере приложений).

2.2. Перенос tempdb на быстрый независимый массив/диски

Для переноса tempdb необходимо запустить sql-скрипт примерно следующего содержания:

USE master GO ALTER DATABASE tempdb modify file (NAME=tempdev, FILENAME='E:\Temp\tempdb_data.mdf') GO ALTER DATABASE tempdb modify file (NAME=templog, FILENAME='E:\Temp\tempdb_log.ldf') GO

где, E:\Temp\ - каталог, в котором будут лежать tempdb, а tempdb_data.mdf и tempdb_log.ldf имя файла базы данных и лога соответственно.

2.3. Настройка параметров сервера SQL

Для настройки сервера запускаем "SQL Server Management Studio", подключаемся к установленному серверу Database Engine'ом и открываем свойства (Server Properties). Тут нам нужно настроить 3 пункта:

Память (Memory)

Параметр "Maximum server memory (in MB)" задает максимально отведенное серверу количество памяти из расчета: [Общее количество оперативной памяти сервера] – [3ГБ под систему(1,5ГБ если Win2k3)] – [сколько_нужно ГБ * количество процессов работающих на данном сервере (если SQL еще какой-то важный сервис крутится на этом же сервере)]. Например, если у нас на сервере всего 24 ГБ оперативной памяти, стоит Windows 2003 и запущен сервер 1С Предприятия с 2мя процессами rphost (которым нужна память хотябы по 1,5Гб) то рассчет будет следующим: 24 - 1,5 - 1,5*2 = 19,5 ГБ ставит параметр "Maximum server memory (in MB)". Это необходимо для того, чтобы sql-сервер рассчитывал на заданный объем и освобождал память заблаговременно, т.к. если поставить неограниченный объем, и сервер попробует получить память, которой нет, он начинает лезть в файл подкачки, что сильно замедлит работу.

Процессоры (Processors)

Максимальное количество потоков (Maximum worker threads) стОит регулировать только при большом количестве клиентов (более 255) и для 1С это не актуально, поэтому оставим по-умолчанию. (хотя некоторые утверждают обратное ). Также выставляем галку повышенного приоритета сервера (Boost SQL Server priority).

Database Settings

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

2.4. Дополнительные "приседания"

Желательно просканировать СКЛ утилитой SQL Server 2005 Best Practices Analyzer и избавиться от ошибок и сообщений (как? и еще раз как?).

Шаг 3. Настройка рабочих баз данных Ms SQL

Если база еще не развернута из .dt файла, и вы знаете примерный ее размер, то первичному файлу размер инициализации лучше сразу указать больший или равный размеру базы, но это дело вкуса, он все равно вырастет при развертке до нужных размеров. А вот Автоувеличение (Autogrowth) размера надо обязательно указать примерно по 200 МБ на базу и по 50 МБ на лог (можно увеличить/уменьшить, в зависимости от размера конечной базы и наличия места на диске), т.к. значения по умолчанию – рост по 1МБ и по 10% очень сильно тормозят работу сервера, когда ему при каждой 3й транзакции надо файл увеличивать. В этом же параметре можно ограничить размер файла лога, чтоб сильно не разрастался, хотя это очень спорный параметр...

Остальные параметры можно оставить по умолчанию, за исключением некоторых:

Например, параметр AutoShrink советуют отключить, ибо он приводит к постоянным скачкам размера лога. Лучше его держать в узде с помощью Планов обслуживания (они же Регламентные задания, они же Maintenance Plans).

Шаг 4. Настройка Планов обслуживания (Maintenance Plans, Регламентных заданий)

Для работы регламентных заданий необходимо создать план обслуживания:

Итак, приведу свой пример настроенного Maintenance Plans с комментариями. Мой план состоит из 5 подпланов:

Первый подплан (ежедневное еженочное обслуживание сервера и резервных копий):

Данный подплан состоит из нескольких шагов. Связи зеленого цвета задают переход к следующему заданию при удачном завершении (т.е. без ошибок), связи синего цвета задают переход к следующему заданию при любом результате выполнения текущего. Параметры шагов видны на размещенных в редакторе заданиях. Параметры некоторых заданий нужно описать отдельно. Первым шагом выполняется "Проверка целостности базы данных" (Check Database Integrity Task), которая выполняется для всех баз системы и следующие задания выполняются только при отсутствии ошибок при проверке баз. Следующим шагом выполняется "Перестроение индексов баз данных" (Rebuild Index Task) для всех баз данных сервера. Данная процедура довольно ресурсоемкая, но в последствии ускоряет работу базы, т.к. если фрагментированость индексов > 25%, это резко снижает производительность сервера. Если размер баз не позволяет выполнять данную задачу, т.к. она занимает много времени, то рекомендуется делать данное действие хотябы раз в неделю, при этом, на ночные задания заменить задачу Перестроение индексов баз данных (Rebuild Index Task) на Дефрагментацию индекса (Reorganize Index Task), которая менее ресурсоемка. Далее происходит "Обновление статистики базы данных" (Update Statistics Task) для всех баз данных, опять же для оптимизации. После этого задания рекомендуется выполнить "Очистку процедурного кэша":

При этом, запускается процедура

DBCC FREEPROCCACHE

После оптимизации работы желательно сделать резервную копию журналов транзакций. Этот шаг делать не обязательно, но желательно. Во время выполнения предыдущих шагов (Перестроение индексов баз данных (Rebuild Index Task) и Обновление статистики базы данных (Update Statistics Task)) файлы журналов вырастают примерно до размера базы данных, а то и более. В результате, в следующих подпланах при выполнении первого резервного копирования журнала транзакций, размер копии имеет довольно большой объем. Это может сильно увеличить вермя восстановления базы. Таким образом, делая копию логов до полной копии, мы избавляемся от данной проблемы. (спасибо за идею комментатору Kyoshiro)

Далее можно выполнить полный бэкап заданием Создание резервной копии базы данных (Back Up Database Task):

При выполнении данного задания копии складываются в сетевую папку на файловом сервере с расширением bak, при этом, для каждой базы создается своя папка:

SAMBA ~ # ls -1 /backup/full/ database1 database2 ... SAMBA ~ # ls -1 /backup/full/satabase1/ database1_backup_201111210250.bak database1_backup_201111220251.bak ...

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

Данный подплан у меня запускается каждую ночь в нерабочее время по будням:

Второй, третий, четвертый подплан (обновление статистики 3 раза в день):

Следующие 3 подплана одинаковы по содержимому и различаются лишь временем выполнения. Выполняются 3 раза в день - в 6, 13 и 19 часов:

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

Пятый подплан (резервное копирование журнала транзакций):

Данный план выполняет инкрементальное копирование транзакционного лога Microsoft SQL Server:

Копирование выполняется каждые пол часа в рабочее время и сохраняется в сеть с расширением trn:

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

Более подробно о выборе и планировании плана обслуживания можно посмотреть данный подкаст(временно убран по причине заражения сайта s.rpod.ru):

Шаг 5. Траблешуттинг

Периодически необходимо анализировать фрагментированность индекса для снижения нагрузки. Для больших баз данных нужно уменьшать ненужные операции по дефрагментации тех индексов, для которых это не требуется. То есть дефрагментацию выполнять не для всей базы, а для избранных таблиц, например. Если значение avg_fragmentation_in_percent в этом столбце превышает 25%, то для восстановления исходных параметров производительности рекомендуется выполнить дефрагментацию/реиндексацию этого индекса. Чтобы просмотреть текущую фрагментированность, можно воспользоваться отчетом:

Для настроенного плана регламентных заданий периодически желательно просматривать историю на наличие ошибок и устранять их:

FAQ MS SQL для 1С Предприятие

В связи с частыми одинаковыми вопросами в комментах решил добавить FAQ.

Q: Сегодня настроил SQL и создал план обслуживания. Все в точности по данной статье, но бэкапы не создаются. Почему? (ведь настроено создание бэкапов каждые 30 минут) A: Потому что бэкапы каждые 30 минут создают копии журналов транзакций. Данный вид копий может создаваться только от полной копии. Соответственно, пятый подплан (бэкап лога транзакций) будет выполнен только после выполнения первого подплана и только после удачного выполнения шага полного бэкапа в первом подплане!!! Соответственно, выход из ситуации - дождаться выполнения первого подплана.

Q: Почему файл жарнала транзакций растет? Как от этого избавиться? Что делать? A: Самый правильный выход из ситуации - увеличить раздел жесткого диска, на котором размещен файл журнала. Уменьшать размер файла с помощью операции shrink только вызовет лишние обращения сервера к жесткому диску. Сервер увеличивает размер этого файла до того размера, который ему необходим для выполнения транзакций. Соответственно, обрезав файл журнала мы заставляем SQL сервер лишний раз насиловать жесткий диск - снова увеличивая размер журнала при очередной ресурсоемкой транзакции. Данный нюанс я обсуждал в этой теме - советую перечитать ее. Есть еще выход - "если не очень сыкотно - можно перед ребилдом делать "финальный" бакап (можно только логов), ставить симпл модель, после ребилда - возвращать фулл рекавери и опять делать фулл бакап" отпять же отсюда (с).

Q: Почему не делается shrink для лога? многие советуют... A: см предыдущий вопрос\ответ

Дополнительно почитать:

- обслуживание базы данных MS SQL (регламентные задания и резервное копирование) от мекрософт тут и тут. - установка MS SQL 2005- Лучшие советы по эффективному обслуживанию баз данных - Настройка почтовых уведомлений об ошибках выполнения плана MS SQL серера

Удачных Вам бэкапов

blackbox.freshdesk.com

1С 8.x : Регламентные операции на уровне субд для MS SQL Server, Оптимизация работы » Администрирование » FAQ 1С 8.x : » 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

Оптимизация 1С Предприятие регламентные операции на сервере, проактивные меры для безперебойной работы 1С

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

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

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

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

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

Чтобы быть максимально полезной для оптимизатора запросов, статистика должна быть точной и свежей. Время от времени SQL Server периодически сам обновляет статистику — данное поведение регулируется опциями AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS. Кроме того, при пересоздании индексов, статистика по ним обновляется автоматически с включенным флагом FULLSCAN, гарантирующим наиболее точное распределение данных. При реорганизации индексов же — статистика не обновляется.

Когда данные в таблицах изменяются очень часто, целесообразно выполнять избирательное обновление статистики вручную, с помощью операции UPDATE STATISTICS.

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

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

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

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

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

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

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

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

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

Таким образом, рекомендуется всегда после обновления статистик очищать содержимое процедурного КЭШа.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

sovetnik1c.ru

1С:Предприятие 8.3: Администрирование и оптимизация MS SQL Server для поддержки системы 1С:Предприятие / TeachMePlease

Когда программисты проигрывают битву за производительность, в бой вступают администраторы. На этом уникальном курсе Вы узнаете, как средствами СУБД Microsoft SQL Server повысить эффективность и надежность «1С: Предприятие 8.3».

Курс «1С:Предприятие 8.3: Администрирование и оптимизация MS SQL Server для поддержки системы 1С:Предприятие» является уникальным, поскольку только в Центре «Специалист» сертифицированные преподаватели-эксперты по Microsoft SQL Server и 1С расскажут вам о проблемах производительности баз данных системы 1С:Предприятие и о приёмах её повышения. Вы научитесь управлять базами и обеспечивать их безопасность при помощи SQL Server, овладеете инструментами мониторинга базы данных, а также сможете выполнять резервное копирование и восстановление базы данных системы «1С:Предприятие».

«Специалист» – золотой партнер Microsoft в области обучения и лучший учебный центр Microsoft в России, Восточной и Центральной Европе. Преподаватели по SQL Server имеют множество авторитетных международных сертификаций Microsoft, подтверждающих их блестящие навыки владения SQL Server. Каждый пятый администратор SQL Server в России – выпускник «Специалиста»! Вы изучите 1С:Предприятие 8.3 на стыке технологий 1С и Microsoft SQL Server, что сделает из вас высококлассного универсального специалиста!

Курс предназначен для администраторов и программистов, занимающихся установкой, конфигурированием и поддержкой систем 1С, использующих Microsoft SQL Server.

Программа курса

Модуль 1. Введение

Модуль 2. Архитектура 1С:Предприятия

Клиент-серверная архитектура
Базы данных и СУБД
SQL Server

Модуль 3. Знакомство с SQL-сервером

Службы и инструменты
Management Studio

Модуль 4. Управление хранением данных

Механизм журналирования
Работа с файлами базы данных
Оптимизация механизма хранения

Модуль 5. Резервное копирование и восстановление

Стратегии резервного копирования
Оптимизация резервного копирования
Резервное копирование в облако

Модуль 6. Защита данных

Процесс аутентификации
Учётные записи
Серверные роли
Роли базы данных
Отображение учётной записи в базе данных

Модуль 7. Автоматизация административных задач

Механизм SQL-агента
Задания SQL-агента
Работа с журналом агента
Настройка оповещений

Модуль 8. Регламентные задачи

Резервное копирование
Оптимизация индексов
Обновление статистики
Проверка целостности

Модуль 9. Установка

Процедура установки SQL-сервера
Настройка разрешений для SQL-сервера
Начальная настройка SQL-сервера
Выбор оптимального оборудования для поддержки базы 1С:Предприятия

Модуль 10. Отказоустойчивые конфигурации

Надёжность и отказоустойчивость
Работа базы данных в кластере
Зеркалирование базы данных
Группы доступности AlwaysOn

Модуль 11. Мониторинг

Сбор и анализ данных производительности
Типовые сценарии мониторинга
Типовые проблемы производительности

Аудиторная нагрузка в классе с преподавателем: 24 ак. ч.

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

teachmeplease.ru

Предприятие 8.3: Администрирование и оптимизация MS SQL Server для поддержки системы 1С:Предприятие

Дата Режим обучения Место обучения Преподаватель

19.11.2018 — 21.11.2018	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич
19.11.2018 — 26.11.2018	ежедневно вечер 18:30 — 21:30 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская Группа почти укомплектована. Успейте записаться на свободные места!	Самородов Федор Анатольевич
26.11.2018 — 28.11.2018	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская Группа почти укомплектована. Успейте записаться на свободные места!	Самородов Федор Анатольевич
03.12.2018 — 05.12.2018	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская Группа почти укомплектована. Успейте записаться на свободные места!	Самородов Федор Анатольевич
17.12.2018 — 21.12.2018	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич
24.12.2018 — 26.12.2018	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич
02.01.2019 — 04.01.2019	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич
27.01.2019 — 24.02.2019	воскресенье утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Радио» м.Бауманская, м.Авиамоторная	Самородов Федор Анатольевич
28.01.2019 — 30.01.2019	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Радио» м.Бауманская, м.Авиамоторная	Самородов Федор Анатольевич
04.02.2019 — 15.02.2019	ежедневно вечер 18:30 — 21:30 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич
11.02.2019 — 13.02.2019	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич
11.03.2019 — 13.03.2019	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич
17.03.2019 — 31.03.2019	воскресенье утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич
18.03.2019 — 25.03.2019	ежедневно вечер 18:30 — 21:30 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич
01.04.2019 — 03.04.2019	ежедневно утро-день 10:00 — 17:10 Открытое обучение Индивидуальные очные консультации преподавателей Обучение по видеозаписям реальных занятий Самостоятельный выбор темпа обучения Визуальный контакт с преподавателем и одногруппниками Оптимальное соотношение цены и качества Подробнее	«Белорусско-Савеловский» м.Белорусская или м.Савеловская	Самородов Федор Анатольевич