In this article, we will discuss how the Max Degree of Parallelism works in SQL Server and how does it improve the query performance. SQL Server Degree of Parallelism is the processor conveyance parameter for a SQL Server operation, and it chooses the maximum number of execution distribution with the parallel use of different logical CPUs for the SQL Server request. Microsoft SQL Server allows setting this Degree of Parallelism parameter value at the SQL Server instance level or Query level. If you do not specify the SQL Server Degree of Parallelism value at the SQL Server instance, then each request or operation has to rely on SQL Server default value and random CPU allocations.
SQL Server Degree of Parallelism is especially helpful to the Database Warehouse query engine, Telecommunication database, and partitioned table-based database. The Degree of Parallelism parameter should be configured with small computation and data usage analysis. The default value is 0 at the SQL Server instance level, which relied on the database engine to use several logical CPUs as required by the SQL Server Query.
Configuring SQL Server Max Degree of Parallelism using SSMS
SQL Server Management Studio (SSMS) >> Right-click on the SQL Server Instance > properties >> Advanced >> Max Degree of Parallelism
Here, we can see that the default configuration of the SQL Server Degree of Parallelism value is 0.
The user can change the Max Degree of Parallelism by SSMS in the SQL Server Advanced property.
How does Max Degree of Parallelism work in SQL Server?
When SQL Server gets any request from the client application end, it compiles and prepares the quickest execution plan with the minimal cost of server assets. If query cost is high, then the reason should be either database engine is not able to make a proper execution plan due to worse query logic or complex query structure. With choosing the quickest execution plan, the SQL Server request will utilize the required logical CPUs to finish the operation or query execution.
For instance, we have two situations with a single thread and multi-thread distribution with defining the SQL Server Degree of Parallelism parameter. In the first case, the parameter value is set it to 1, and SQL Server request execution utilizes a single logical CPU and completing operation or returning a query result set.
In the second case, the Max Degree of Parallelism parameter value is set to higher than 1/0. 0 stands for SQL Server choose the max number of CPU usage for the query execution as required by the execution plan. Greater than one value will allow using maximum CPUs as defined with a parameter to execute the request as needed. For example, the SQL Server Max Degree of Parallelism parameter is five, and query execution required three only then the query will be distributed in 3 execution only.
For the SQL Server request execution, a processor will circulate the execution with multiple logical CPUs as defined in the parameter and as CPU costs required by the query. Each dispersed execution will have an equivalent period to finish the task. If any threads completed earlier than the expected time, then that threads will hold up till the last one to complete the undertaking.
Distribution of Logical CPUs to the SQL Server Instance
Logical CPUs can be relegated to the SQL Server instance to access those only by the SQL Server Processor property. Logical CPUs will be listed in the server property as are configured or associated with the machine or virtual machine. A rundown of CPUs will be bifurcated with the numa node (Processor Socket).
Logical CPUs can be allotted to two different entities, process affinity mask, and I/O affinity mask. Use can choose the distribution of logical processor units based on the querying size, database size, nature of data, and I/O operations.
By default, these options will be set to automatic with selected checkboxes to both properties. Automatically set processor affinity mask for all processors and Automatically set an I/O affinity mask for all processors. The automatic option will use any free CPUs to play out the operation as required. A best practice is to set individual logical CPUs to both containers because if you assign the same logical resource to both substances, then perhaps one of it doesn’t get an opportunity to get to it.
On the off chance that database and application are in the same server or machine, at that point, keep some room in SQL Server CPU allocation for permitting the remainder of CPUs to access by application and operating system. Max Degree of Parallelism parameter uses maximum CPUs that are assigned to SQL Server by this screen to perform parallel execution.
How does SQL Server Degree of Parallelism help in better Query Performance?
Day by day size of the database is expanding and turn out to be very basic to oversee common data source with the data warehouse. Data warehouse holds typical data information for the end-user response with the enormous measured in gigabytes or terabytes of data. For client responses, complex queries run in the data warehouse, and ideally, it requires more processing time to finish the execution. The Query execution process should be distributed in multiple threads with allocating several logical CPUs to the query execution with the help of the SQL Server Degree of Parallelism to reduce the query executing time.
The default an incentive for the parameter of SQL Server Degree of Parallelism is 0 (Zero), which means SQL Server query can utilize all CPUs for the parallel execution. On the off chance that this Parallelism isn’t zero, at that point that the single query can use the number of CPUs. Both situations are having various requirements and use cases. If we use Parallelism with zero, then long execution queries will utilize the most extreme CPUs, and the rest of the SQL Server request will be in a queue with CXPACKET wait type and suspended status and those requests will wait till get the free CPU resources. In the subsequent case, if a Parallelism parameter value is smaller (1,2 or 3), then any longer query execution will take additional time than expected time to finish it since it needs more resources.
Parallelism at SQL Statement using MAXDOP option
The MAXDOP option is utilized to powers the maximum level of parallelism value at the individual T-SQL statement. MAXDOP value cannot be the same as the SQL Server Degree of Parallelism parameter value. Actually, it is more helpful when additional CPU resources are required to execute the specific SQL statement.
Users can decide the Parallelism parameter value based on a load of SQL Server request, several logical CPUs, Storage Type (I/O Performance), Query Type (Report, Single execution, Remote Procedure, etc…), Partition and many more.
If no longer execution type of report queries in the database, then go with the 1,2,3, As the availability of the CPU resource and define the Parallelism with the SQL statement using the MAXDOP option for the report queries. MAXDOP can be set to limit the query to execute on a number of logical CPUs. MAXDOP option can be added at the end of the SQL Query statement. For example,
|
1 |
SELECT * FROM Production.Product WHERE ProductID = 319 OPTION(MAXDOP 4) |
Here, the MAXDOP value is four for the above T-SQL statement and Query can use a maximum of four logical CPUs to complete the execution.
For example, the current configuration of the SQL Server Degree of Parallelism can be checked by the T-SQL query as well with the help of SYS.SYSCONFIGURES table.
|
1 2 3 |
SELECT * FROM SYS.SYSCONFIGURES WHERE comment LIKE '%degree%' |
Here, we have an example with the execution plan of the T-SQL statement with several JOINS. The first part of the query statement in the execution plan represents the Degree of Parallelism value, as shown in the below image.
Here, Parallelism value is one that means a single CPU is used to complete the execution of this statement. Now, what happens if it is more than 1? Execution will be distributed in the number of parallel threads as configured the Parallelism and required CPUs by the query. In the below image, we can see the Parallelism task in the execution plan while the query used 40 CPUs. Gather Steams in the execution plan collects task information of each thread or worker.
Conclusion
Database sizes are rapidly expanding, and interest for SQL Server queries are growing with the greater unpredictability in the industry. More CPUs on a server is a more thing can do without a moment’s delay with thread distribution in SQL Server. SQL Server Degree of Parallelism helps to improve performance on most undertakings with parallel threading internally. Acknowledged that each operation can’t be made parallel, but Microsoft SQL Server covers most of it.
You can refer to the article, Different Ways to set the Max Degree of Parallelism in SQL Server to get more knowledge about the Parallelism.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020