This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server uses a Maximum Degree of Parallelism (MAXDOP) for achieving the parallel query processing to increase the query execution performance.
The default setting of MAXDOP is 0 means your query execution plan chooses the maximum degree of parallelism automatically.
Which is OK, but in big transaction system you should limit your query by specifying an explicit value of MAXDOP (Number of CPU Core for Parallel Query Execution). Which requires proper analysis of your system loads, CPU configuration and number of concurrent connections.
Below are some different ways to change the value MAXDOP.
Using SSMS:
In SSMS, right click on server go to Server Properties -> Advanced -> Parallelism -> Max Degree of Parallelism. Default is 0.
Using sp_configure:
|
1 2 3 4 5 6 7 8 |
sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO sp_configure 'max degree of parallelism', 8; GO RECONFIGURE WITH OVERRIDE; GO |
Using MAXDOP hint option at query level:
|
1 2 3 |
SELECT * FROM [Person].[Address] OPTION (MAXDOP 1) |
Leave a Reply