SQL Server Parallelism–The Dark Side

SQL Server Parallelism–The Dark Side

Rate This
  • Comments 4

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!

  • 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.

Page 1 of 1 (4 items)
Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post