Рекомендации по настройке Microsoft SQL Server
Рекомендации по компонентам сервера:
На SQL сервере должен быть установлен последний актуальный Service Pack.
Нужно удостовериться, что не стоит искусственного ограничения на максимальный объем используемой памяти (Properties -> Memory -> Maximum (MB))
В случае 32-х битной архитектуры Windows необходимо включить опции /3GB и /PAE в файл boot.ini
Рекомендации к программной части:
Отключение эскалации блокировок
Эскалация вызывает дополнительные блокировки пользователей, что затрудняет параллельную работу пользователей. С целью увеличения параллельности работы пользователей и снижения количества блокировок необходимо отключить эскалацию блокировок. Для отключения эскалации блокировок необходимо открыть свойства сервера в Enterprise Manager и на закладке «General» добавить в «Startup Parameters» параметр «-T1211». Опция вступает в силу после рестарта службы SQL-сервера.
Дефрагментация индексов базы данных
Наилучший вариант здесь может принести полная перестройка индексов с определенной периодичностью (неделя), либо их дефрагментация (менее эффективно). В дополнение, при перестройке индексов снижается вероятность ошибок в базе, так как индексы иногда могут нарушаться.
Чтобы произвести переиндексацию необходимо выполнить такой запрос (например в QA):
USE base_1c
DECLARE @MyTable varchar(32)
DECLARE @MyIndex varchar(32)
DECLARE MyCursor CURSOR FOR
SELECT o.name, i.name
FROM sysobjects o INNER JOIN sysindexes i ON o.id = i.id
WHERE (o.xtype = 'U') AND (INDEXPROPERTY(i.id, i.name, 'isStatistics') = 0) AND (i.dpages > 0)
ORDER BY o.name, i.indid
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'Дефрагментация индекса '+@MyIndex+' из таблицы '+@MyTable
DBCC INDEXDEFRAG (0,@MyTable,@MyIndex)
FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex
END
CLOSE MyCursor
DEALLOCATE MyCursor
Перераспределение данных на страницах базы данных
В результате работы базы данных заполнение страниц базы данных происходит в зависимости от работы пользователей, при этом распределение данных по страницам, заданное при создании базы данных нарушается. Изменение распределения данных по страницам ведет к неэффективному использованию памяти SQL сервера. Рекомендуется настроить периодическое сжатие базы данных с перераспределением данных на страницах. Это поможет более эффективно ее использовать.
Данный запрос выполняет перераспределение:
DBCCshrinkdatabase(N'ТестТорговли2')
Модель восстановления базы данных и журнал транзакций
Для работы с журналом транзакций рекомендуется полная модель восстановления данных.
Это даёт возможность при необходимости в любой момент откатиться на нужное число транзакций назад.
DB Properties -> Options -> Recovery model
В случае использования полной модели восстановления данных, для недопущения чрезмерного разрастания объёма базы, необходимо делать периодический бекап транзакций (один раз в неделю), при бекапе происходит (при установленной опции "Remove inactive entries from transaction log" или "Truncate the transaction log") удаление всех завершенных транзакций.
Также необходимо раз в неделю осуществлять shrink database.
Автоматическое обновление статистики
При работе с базой данных с включенной опцией автоматического обновления статистики при каждом добавлении изменении или удалении данных происходит соответствующая перестройка статистики. Тем не менее, зачастую статистика не соответствует актуальной, ее требуется время от времени пересчитывать (обновлять) статистику. Работы по пересчету статистики должны быть соответственным образом настроены на проведение в автоматическом режиме в нерабочее время. Достаточно создать регламентное задание (JOB) с текстом: