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

MS SQL Server – самая распространенная СУБД для клиент-серверного использования 1С. Какие настройки необходимо произвести в ней для обеспечения максимально производительной работы 1С?

Самый лучший вариант — если сервер СУБД расположен на отдельном компьютере, на котором другие серверные роли не установлены (контроллер домена, терминальный сервер и пр.). Но ничто не мешает установить сервер 1С и СУБД на один компьютер, если нагрузка на систему будет небольшой.

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

Использование памяти

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

Обычно для нормальной работы операционной системе достаточно 4 Гб. Для систем с большим объемом памяти рекомендуется для ОС оставлять еще 1 Гб памяти на каж­дые 16 Гб, установленной RAM.

sql1

Чтобы вычислить, какой объем памяти оставить для сер­вера 1С, необходимо посмотреть, сколько памяти занимают процессы кластера серверов 1С в пиковые нагрузки, и до­бавить к этому числу некоторый резерв. К процессам кла­стера серверов 1С относят rphost, ragent, rmngr.

Получаем следующую формулу:

Память для SQL Server = Всего память — Память для ОС -Память для сервера 1С

Максимальный объем памяти, выделяемый SQL Server, устанавливается параметром Max server memory (Макси­мальный размер памяти сервера). Для этого необходи­мо в Management Studio через контекстное меню открыть свойства сервера и на странице «Память» указать параметр «Максимальный размер памяти сервера» (см. рис. 1).

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

Каждая база данных состоит из файла базы данных и жур­нала транзакций.

Файл данных имеет расширение *.mdf или *.ndf и содер­жит непосредственно данные и хранит содержимое таблиц базы данных. Журнал транзакций имеет расширение *.ldf, содержит информацию, необходимую для отката или фик­сации транзакции. У одной базы данных файлов обеих ти­пов может быть несколько.

Исходя из назначения этих файлов для них существуют различные требования:

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

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

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

Чтобы поменять местоположение файла базы данных или журнала транзакции необходимо:

>   Создать бэкап.

>   В Management Studio вызвать правой кнопкой мыши контекстное меню на имени нужной базы, выбрать «За­дачи» (Tasks) — «Отсоединить» (Detach) и нажать «ОК».

>   Переместить файлы базы и журнала транзакций в нуж­ный каталог.

>   В Management Studio вызвать правой кнопкой мыши контекстное меню на узле базы данных (Databases), выбрать «Присоединить» (Attach).

» В поле «Базы данных для присоединения» (Databases to attach) добавить новый путь.

» В поле «Сведения о базе данных» (Database details) изменить путь к файлу журнала транзакций на но­вый.

>  Нажать «ОК».

Расположение базы TempDB

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

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

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

Рекомендация: вынести базу TempDB на отдельный быстрый диск (желательно RAM или SSD).

Листинг 1. Запрос, перемещающий базу TempDB на другой диск.
use master
go
alter database tempdb modify file (NAME = tempdev, ↵
FileName = ‘Новый диск:\Новый каталог\tempdb.mdf’)
go
alter database tempdb modify file (NAME = templog, ↵
FileName = ‘Новый диск:\Новый каталог\templog.ldf’)
go

Настройка авторасширения базы

С увеличением количества данных в базе растет и ее раз­мер, но он растет не постепенно, а дискретно. Объем базы данных при достижении предела увеличивается на конкретно задаваемый размер. По умолчанию это 1 Мб, а это очень незначительный объем, и поэтому на посто­янное увеличение размера базы будут тратиться ресурсы SQL Server.

Рекомендация: задать авторасширение базы 5 Гб (или не менее 500 Мб).

Параметр авторасширения задается в свойствах базы данных, на странице «Файлы» в колонке «Авторасширение» (см. рис. 2)

sql2

Установка полной модели восстановления базы данных

Если мы хотим иметь возможность восстановить состояние базы данных на точно определенный момент времени, то сле­дует использовать FULL (полную) модель восстановления.

Модель восстановления задается в Management Studio. В контекстном меню на имени нужной базы выбираем «Свойства» (Properties) и переходим на страницу «Параме­тры» (Options). Нужное значение задается в поле «Модель восстановления» (Recovery model) (см. рис. 3).

sql3

Настроить резервное копирование и проверить его работу

Старая истина гласит: системные администраторы делятся на тех, кто «делает архивные копии», и тех, кто «будет де­лать архивные копии». Архивные данные для баз 1С особен­но важны, т.к. обычно там хранится вся учетная и финансо­вая информация по организации.

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

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

  • >   Создаем новый план обслуживания в Management Studio: «Управление» (Management) — «Планы обслу­живания» (Maintenance Plans) — «Создать план обслу­живания» (New Maintenance Plan) (см. рис. 4).
  • >   Указываем имя нашего плана.
  • >   Добавляем задачу «Резервное копирование базы дан­ных» из панели задач раздела «Общие».
  • >   Указываем расписание для данной операции, а также для каких баз данных делать копию.

Установка параметра Max dergee of Parallelism

Параметр Max dergee of Parallelism (Максимальная степень параллелизма) указывает, сколько процессоров может быть использовано при выполнении одного запроса. По умолча­нию параметр равен 0, т.е. для выполнения запроса могут использоваться все процессоры. Для OLTP-систем реко­мендуется устанавливать этот параметр в 1. Для его уста­новки в Management Studio открываем свойства сервера и на странице «Дополнительно» указываем параметр «Мак­симальная степень параллелизма» (см. рис. 5).

sql5

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

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

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

Статистика обновляется автоматически, если в свойствах базы данных установлено значение параметра Auto update statistics (Автоматическое обновление статистики) = True.

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

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

Обновить статистику можно с использованием планов обслуживания:

  • >   Создаем новый план обслуживания в Management Studio: «Управление» (Management) — «Планы обслу­живания» (Maintenance Plans) — «Создать план обслу­живания» (New Maintenance Plan).
  • >   Указываем имя нашего плана.
  • >   Добавляем задачу «Обновление статистик» (Update Statistics Task) из панели задач.
  • >   Указываем расписание для данной операции, а также для каких баз и таблиц обновлять статистику.

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

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

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

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

 DBCC FREEPROCCACHE

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

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

Из-за интенсивной работы с таблицами базы данных воз­никает эффект фрагментации индексов, который приводит к снижению эффективности работы запросов. Дефрагмен­тация (реорганизации) индексов — процесс устранения фраг­ментации. Дефрагментацию рекомендуется делать, если фрагментация не более 30%. Выполнение дефрагментации не блокирует работу пользователей, но создает некоторую нагрузку на

оборудование, поэтому не рекомендуется выпол­нять дефрагментацию в рабочее время.

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

Дефрагментация выполняется командой:

 ALTER INDEX ALL ON «ИмяТаблицы» REORGANIZE

Реиндексация индексов

Реиндексация — операция пересоздания индексов. Эту опе­рацию рекомендуется выполнять, когда в индексе большая степень фрагментации (> 30%).

Реиндексация выполняется командой:

 ALTER INDEX ALL ON «ИмяТаблицы» REBUILD

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

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

2 ответы
  1. find-way
    find-way says:

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

    Ответить

Ответить

Хотите присоединиться к дискуссии?
Приглашаем поучаствовать!

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>