Сегодня мы поговорим о настройках самой распространенной СУБД, это Microsoft SQL Server.
Самым первым шагом после установки SQL сервера необходимо настроить максимальный порог потребления памяти, иначе SQL может занять всю свободную память и не оставит памяти для операционной системы. Существует рекомендуемая формула для расчета порога памяти, которую возможно задать для SQL:
(Всего памяти на сервере – 1024 * Всего памяти на сервере / Всего памяти на сервере * 0,5) – оставить для ОС 1 гб на каждые 16 гб общего размера памяти.
Приоритет MS SQL сервера
Если установить данную галочку, то приоритет процесса SQL сервера будет выше, чем у других приложений. Это может понадобиться, когда у нас стоят другие приложения на сервере, за исключением сервера приложений 1С. Если же сервер 1С стоит вместе с SQL сервером, данную настройку производить не нужно.
Если сервер 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 ()
