Настройки параллелизма для оптимизации запроса в SQL Server
блог о bi, №1 в рунете
В нашей практике часто пользователям необходимо обрабатывать большое количество данных. При этом время на обработку данных чаще всего ограничено. SQL Server имеет большое количество опций, которые влияют на параметры выполнения запросов. В данной статье мы рассмотрим способ оптимизации за счет одновременного выполнения запросов на нескольких процессорах. Такую возможность SQL Server принято называть параллельным исполнением запроса.
Max Deqree of Parallelism
Параметр «Max degree of parallelism» – дополнительная опция СУБД Microsoft SQL Server, которая определяет максимальное число процессоров, применяемых при выполнении одного запроса.
По умолчанию свойство имеет значение «0». То есть, сервер может использовать все доступные процессоры. Это может привести к снижению оптимизации и не рекомендуется к использованию. Для отключения параллелизма на сервере необходимо указать значение «1».
Приводим рекомендации к настройке параметра:
По умолчанию свойство имеет значение «0». То есть, сервер может использовать все доступные процессоры. Это может привести к снижению оптимизации и не рекомендуется к использованию. Для отключения параллелизма на сервере необходимо указать значение «1».
Приводим рекомендации к настройке параметра:
Конфигурация сервера
Количество процессоров
Руководство
Сервер с одним узлом NUMA
Не более 8 логических процессоров
Значение параметра MAXDOP не должно превышать количество логических процессоров
Сервер с одним узлом NUMA
Более 8 логических процессоров
Значение параметра MAXDOP должно быть равно 8
Сервер с несколькими узлами NUMA
Не более 16 логических процессоров на узел NUMA
Значение параметра MAXDOP не должно превышать количество логических процессоров на каждый узел NUMA
Сервер с несколькими узлами NUMA
Больше 16 логических процессоров на каждый узел NUMA
Значение MAXDOP должно быть равно половине количества логических процессоров на узел NUMA со значением MAX, равным 16
Изменение значения «Max degree of parallelism» показано на рисунках ниже:
Значение параметра также можно изменить с помощью скрипта:
USE master;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO
USE master;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO
Cost Threshold for Parallelism
Параметр «Cost Threshold for Parallelism» – указывает пороговое значение, при котором SQL Server создает и выполняет параллельные планы для запросов.
Значение данного свойства по умолчанию – «5». То есть, даже небольшие тривиальные запросы будут выполняться параллельно. Это может привести к быстрому истеканию потоков для обработки. Исходя из нашего опыта, значения «50» подходит для большинства проектов.
Изменение значение «Cost Threshold for Parallelism» показано на рисунках ниже:
Значение данного свойства по умолчанию – «5». То есть, даже небольшие тривиальные запросы будут выполняться параллельно. Это может привести к быстрому истеканию потоков для обработки. Исходя из нашего опыта, значения «50» подходит для большинства проектов.
Изменение значение «Cost Threshold for Parallelism» показано на рисунках ниже:
Значение параметра также можно изменить с помощью скрипта:
USE master;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
EXEC sp_configure 'cost threshold for parallelism', 50;
GO
RECONFIGURE
GO
USE master;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
EXEC sp_configure 'cost threshold for parallelism', 50;
GO
RECONFIGURE
GO
Optimize for Ad hoc Workloads
Параметр «Optimize for Ad hoc Workloads» – опция конфигурации базы данных, которая помогает улучшить производительность систем, часто выполняющую ad hoc запросы.
Ad hoc запрос – это отдельный запрос, не включенный в хранимую процедуру и не параметризованный или подготовленный.
Когда свойство включено, сервер сохраняет только небольшую «заглушку» плана запроса в кэше планов для ad hoc запросов. Полный план запроса будет сохранен в кэше планов только в случае многократного использования. Эта оптимизация помогает уменьшить использование памяти и улучшает общую производительность.
При отключении параметра будет сохранять весь план запроса в кэше планов для каждого ad hoc запроса, независимо от его частоты выполнения.
Чтобы найти количество одноразовых кэшированных планов, выполните следующий запрос:
Ad hoc запрос – это отдельный запрос, не включенный в хранимую процедуру и не параметризованный или подготовленный.
Когда свойство включено, сервер сохраняет только небольшую «заглушку» плана запроса в кэше планов для ad hoc запросов. Полный план запроса будет сохранен в кэше планов только в случае многократного использования. Эта оптимизация помогает уменьшить использование памяти и улучшает общую производительность.
При отключении параметра будет сохранять весь план запроса в кэше планов для каждого ad hoc запроса, независимо от его частоты выполнения.
Чтобы найти количество одноразовых кэшированных планов, выполните следующий запрос:
Изменения значение «Optimize for Ad hoc Workloads» указано на рисунках ниже:
Значение параметра также можно изменить с помощью скрипта:
USE master
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE WITH OVERRIDE
GO
Включение данной опции не сбрасывает текущие планы, поэтому необходимо будет выполнить команду, которая сбросит весь кэш:
DBCC FREEPROCCACHE WITH NO_INFOMSGS; - Сбросить весь кэш
DBCC FLUSHPROCINDB(db_id); - Сбросить кэш конкретной БД
USE master
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE WITH OVERRIDE
GO
Включение данной опции не сбрасывает текущие планы, поэтому необходимо будет выполнить команду, которая сбросит весь кэш:
DBCC FREEPROCCACHE WITH NO_INFOMSGS; - Сбросить весь кэш
DBCC FLUSHPROCINDB(db_id); - Сбросить кэш конкретной БД