В этой статье я рассмотрю вопросы связанные с производительностью Microsoft SQL Server в виртуальной среде на примере гипервизора Proxmox и способы её повысить.
С какими проблемами можно столкнуться в плане производительности SQL сервера:
- SQL не раздувает память - не кэширует данные или наблюдается минимальный ее расход при работе с базами данных, сопровождается медленной работой приложений, в том числе и 1С, при этом сервер в плане ресурсов будет простаивать.
- Повышенный расход памяти
- Низкая производительность дисковой подсистемы в виртуальной среде
Повышение производительности достигается изменением следующих параметров и настроек:
1. Первое и самое основное - это проверка Shared Memory, если 1С и MSSQL на одной машине:
- Запускаем диспетчер конфигурации SQL Server
- Заходим в SQL Native Client ---> Клиентские протоколы ---> Общая память ---> Включено
- В консоли сервера 1С в параметрах подключенных баз, сервер должен быть указан: localhost или Имя_Сервера\Имя_Экземпляра
2. Блокировка страниц в памяти
Решается отключением для учетной записи из под которой работает служба SQL параметра Блокировка страниц в памяти.
Просмотр статуса этого парамет, запрос:
SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;
- CONVENTIONAL. Разрешение "Блокировка страниц в памяти" не предоставлено.
- LOCK_PAGES. Разрешение "Блокировка страниц в памяти" предоставлено.
- LARGE_PAGES. Разрешение "Блокировка страниц в памяти" предоставляется в режиме предприятия с включенным флагом трассировки 834. Это своего рода дебаг режим для программистов, которая не рекомендуется для Production сред.
Включение параметра Блокировка страниц в памяти
Групповая политика определяет, какие учетные записи могут использовать процесс для сохранения данных в физической памяти, чтобы система не отправляла страницы данных в виртуальную память на диске.
Для включения этой политики для учетной записи SQL Server, используем средство редактирования групповой политики gpedit.msc:
- Win+R откроется окно Выполнить, вводим в строку gpedit.msc, нажимаем ОК, запустится Редактор локальной групповой политики
- В консоли Редактор локальной групповой политики разверните раздел Конфигурация компьютера ---> Конфигурация Windows ---> Параметры безопасности ---> Локальные политики ---> Назначение прав пользователя
- Выберите из списка политику Блокировка страниц в памяти и двойным щелчком откройте ее.
- В окне Свойства: Блокировка страниц в памяти нажмите Добавить пользователя или группу. Добавьте учетную запись с помощью которой запускается служба SQL Server, подсмотреть можно в свойствах этой службы, после добавления нажимаем ОК и перезагружаем SQL сервер.
Проверяем повторно статус Блокировка страниц в памяти, запрос:
SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;
Должно быть LOCK_PAGES.
3. Максимальная степень параллелизма
Это один из основных параметров для повышения быстродействия баз 1С. Данный параметр определяет, во сколько потоков может выполняться один запрос и по умолчанию с параметром равным тому, что сиквел запишет в конфигурацию по данным пред установочной диагностике, сервер сам якобы подбирает число потоков. Для баз данных 1С рекомендуется значение 1 и положительно скажется на работе запросов:
Порядок действий:
- Запустим Management Studio и откроем свойства сервера, нас там интересует закладка Дополнительно
- Найдем и установим значение параметра равное 1
0 - авто, 1 - отключен, от 2 и выше, количество ядер участвующих в распараллеливании потоков.
Я выполнял замеры с помощью теста Гилева и скажу так, что лучшие показатели наблюдались при значении 1.
Что делать при повышенном расходе памяти:
- Ограничить потребление ресурсов MSSQL
- Увеличить память
- Повысить производительность дисковой подсистемы, как показывает практика чем выше скорость дисков тем меньше SQL кэширует данные.
Ограничиваем максимальный объем памяти сервера MS SQL Server:
Не забываем, что максимальный объем памяти для MS SQL Server рассчитывается по следующей формуле:
Установим максимальный объем памяти для MS SQL Server:
- Запустим Management Studio и откроем свойства сервера, нас там интересует закладка Память.
- Изменим параметр Максимальный размер памяти сервера на ваше значение.
Повышаем производительность дисковой подсистемы в виртуальной среде
1. Отключаем механизм DFSS для дисков, который отвечает за балансировку и распределение аппаратных ресурсов между пользователями:
- Win+R откроется окно Выполнить, вводим в строку regedit, нажимаем ОК, запустится Редактор реестра
- В окне Редактора реестра разверните раздел HKEY_LOCAL_MACHINE ---> SYSTEMSYSTEM ---> CurrentControlSet--->Services--->TSFairShare--->Disk установите параметр EnableFairShare 0
2. Отключаем сжатие данных для каталогов, в которых находятся базы:
При включенном сжатии система будет пытаться сжимать файлы для экономии места, что значительно замедляет процесс записи.
Чтобы отключить сжатие файлов в каталоге, необходимо:
- Открыть свойства каталога
- На закладке Общие нажать кнопку Другие
- Снять флаг Сжимать содержимое для экономии места на диске.
3. Изменение параметра авто увеличения файлов базы данных
Рекомендуется установить значение от 256 МБ до 5 ГБ в зависимости от размера файла.
Для изменения размера авторасширения необходимо:
- Запустим Management Studio и откроем свойства сервера, нас там интересует закладка Память.
- В правой части окна увидим Автоувеличение/Максимальный размер и под ним два параметра для файла базы данных и журнала транзакционных логов. Установим значения исходя из тенденции роста от 250МБ и выше.
Данная настройка будет действовать только для выбранной базы.
4. Разнесите файлы данных mdf и логов ldf на разные физические диски.
В этом случае работа с файлами может идти не последовательно, а практически параллельно, что повышает скорость работы дисковых операций. Лучше всего для этих целей подходят m.2 SSD, pci-express SSD или хотя бы sata ssd диски.
Для переноса файлов необходимо:
- Запустить Management Studio и подключиться к нужному серверу
- Открыть свойства нужной базы и выбрать закладку Файлы
- Запомнить имена и расположение файлов
- Отсоединить базу, выбрав через контекстное меню Задачи – Отсоединить
- Поставить флаг Удалить соединения и нажать Ок
- Открыть Проводник и переместить файл данных и файл журнала на нужные носители
- В Management Studio открыть контекстное меню сервера и выбрать пункт Присоединить
- Нажать кнопку Добавить и указать файл mdf с нового диска
- В нижнем окне сведения о базе данных в строке с файлом лога нужно указать новый путь к файлу журнала транзакций и нажать Ок.
5. Вынести файлы базы TempDB на отдельный диск
Служебная база данных TempDB используется всеми базами сервера для хранения, промежуточных расчетов, временных таблиц, версий строк при использовании RCSI и многих других вещей. Обычно обращений к этой базе очень много, и если она будет лежать на медленных дисках, это может замедлить работу системы.
Рекомендуется хранить базу TempDB на отдельном диске для повышения производительности работы системы
Для переноса базы TempDB на отдельный диск:
- Запускаем Management Studio и нажимаем Создать запрос
- Вставляем скрипт:
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
Нажимаем Выполнить
Перезапускаем службу MS SQL Server
6) Вносим правила на исключение контроля папок и процессов защитника Windows.
Это касается в первую очередь тех, кто использует файловый режим БД, там наблюдается колоссальный прирост производительности, чуть ли не в 2 раза. Для SQL прирост производительности составляет до 30℅ и менее заметен на маленьких базах до 10ГБ.
Если кому-то нужен совет или помощь пишите на почту - она указана в статье список планируемых публикаций.
Всем удачи!