通过

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!

Comments

  • Anonymous
    November 15, 2011
    keep in mind the former is SERVER level (all db on instance) not database level ;)

  • Anonymous
    January 01, 2012
    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.

  • Anonymous
    July 19, 2012
    Clayton, are you suggesting MAXDOP should be left at the default of 0 for the instance?

  • Anonymous
    January 10, 2013
    Fixed wording per ansi.sql's comment.