Before few days i suffered from the performenance issue in database server, I have optimized so many stored procedures and also optimized indexes. But one option i really forgot which will affect on the performance in SQL Server, that is Max Degree of Parallelism.
This option is for the SQL server instance level and decide how SQL Server will utilize the CPUs of the server, means the value configured will use those number of the CPUs for the parallism. The default value of this option is 0 (will use all CPUs).
Another option MAXDOP , which will work functionally same as Max Degree of Parallelism. We can specify this option at query level.
These above two configuration option are depend on the value of Cost Threshold for Parallelism. The default configured value is 5 in SQL Server.
Let's see how to configure these values by scripts.
-- Changing the value of Max Degree of Parallelism to 1
-- will use 2 CPUs for the parallel plan
EXEC sp_configure 'max degree of parallelism' , 2
GO
RECONFIGURE
GO
-- We can also specify MAXDOP option at query level
-- this is specific to this query only
SELECT
*
FROM EmpDB.dbo.Employee OPTION (MAXDOP 2)
-- Changing the value of Cost Threshold for Parallelism fom 5 to 10
EXEC sp_configure 'cost threshold for parallelism' , 10
GO
RECONFIGURE
GO
When the configure values set to above , whenever queries esimated cost to run the serial plan is more than 10 or whatever specified in Cost Threshold for Parallelism , SQL Server optimizer will use 2 CPUs for the queries parallel plans. If the query's estimated cose below 10 then it will not going to use parallel plan. SQL Server will also use serial plan if the value are configured for the Max Degree of Parallelism to 1. The cost referres to an estimated elapsed time in seconds require to run serial plan on sepcific hardware configuration.
Please whenever you change the values of all above SQL Server parameters, then review the performance of the database server and then decide the best value for the same.