SQL Server Parallelism–The Dark Side

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:

image

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!