I just upgraded my dev box from four cores to six cores with a bunch of memory. I was excited to see how fast my multithreaded, data intensive program would run. I launched it and my previously exception free program started throwing SQL Server query timeout exceptions. I was not impressed!
Rebuilding indexes and updating statistics did not fix the problem. It was easy enough to see which stored procedure was timing out so I decided to compare the query execution plans of the stored procedure before and after the hardware upgrade. They were basically the same plan but some of the icons had a couple parallel arrows on the new hardware:
Some quick research uncovered details about SQL Server’s parallel query processing. You can control it at the server level:
EXEC sp_configure 'show advanced option', '1' RECONFIGURE GO EXEC sp_configure "max degree of parallelism", <integer value> RECONFIGURE GO EXEC sp_configure "cost threshold for parallelism", <integer value> RECONFIGURE GO
or at the query level:
SELECT * FROM Sales.SalesOrderDetails OPTION (MAXDOP 1)
I did the latter but I’m thinking of doing the former for the whole database because my query went from taking over 60 seconds down to 18 seconds. Clearly parallelism and default settings are not helping that query!
keep in mind the former is SERVER level (all db on instance) not database level ;)
Parallism is provided by SQL Server to improve performance. By and large the optimizer knows bet how to use it. MAXDOP should never be set at the database level (and certainly not for an entire instance).It should be set only as needed on individual statements.
Clayton, are you suggesting MAXDOP should be left at the default of 0 for the instance?
Fixed wording per ansi.sql's comment.