march 13, 2024
Parallelism settings for query optimization in SQL Server
Introduction
In our practice, users often need to process a large amount of data. At the same time, the time for data processing is most often limited. SQL Server has a large number of options that affect the parameters of query execution. In this article we will consider a way to optimize by simultaneous execution of queries on multiple processors. This feature of SQL Server is called parallel query execution.
Max Deqree of Parallelism
The «Max degree of parallelism» parameter is an additional Microsoft SQL Server DBMS option that defines the maximum number of processors used when executing one query.
By default, the property has the value «0». That is, the server can use all available processors. This can lead to decreased optimization and is not recommended for use. To disable parallelism on the server you should specify the value «1».
Here are the recommendations for setting the parameter:
| Server Configuration | Number of processors | Guideline |
|---|---|---|
|
Server with one NUMA node |
No more than 8 logical processors |
The MAXDOP parameter value must not exceed the number of logical processors |
|
Server with one NUMA node |
More than 8 logical processors |
The value of MAXDOP parameter must be equal to 8 |
|
Server with multiple NUMA nodes |
Maximum 16 logical processors per NUMA node |
The MAXDOP parameter value should not exceed the number of logical processors per NUMA node |
|
Server with multiple NUMA nodes |
More than 16 logical processors per NUMA node |
The MAXDOP value must be equal to half the number of logical processors per NUMA node with a MAX value of 16 |
Changing the «Max degree of parallelism» value is shown in the figures below:
The value of the parameter can also be changed using a script:
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
The «Cost Threshold for Parallelism» parameter — specifies the threshold value at which SQL Server creates and executes parallel plans for queries.
The default value of this property is «5». That is, even small trivial queries will be executed in parallel. This can cause threads to expire quickly for processing. Based on our experience, a value of «50» is appropriate for most projects.
Changing the «Cost Threshold for Parallelism» value is shown in the figures below:
The value of the parameter can also be changed using a script:
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
The Optimize for Ad hoc Workloads option is a database configuration option that helps improve the performance of systems that frequently run ad hoc queries.
An ad hoc query is a standalone query that is not included in a stored procedure and is not parameterized or prepared.
When the property is enabled, the server stores only a small «stub» of the query plan in the plan cache for ad hoc queries. The full query plan will only be stored in the plan cache for reusable requests. This optimization helps reduce memory usage and improves overall performance.
Disabling the option will store the entire query plan in the plans cache for each ad hoc query, regardless of its execution frequency.
To find the number of one-time cached plans, run the following query:
The changes to the «Optimize for Ad hoc Workloads» value are shown in the figures below:
The value of the parameter can also be changed using a script:
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
Enabling this option does not reset the current plans, so you will need to run a command that will reset the entire cache:
DBCC FREEPROCCACHE WITH NO_INFOMSGS; — Reset all cache
DBCC FLUSHPROCINDB(db_id); — Reset the cache of a specific database
