XML Feeds"I see a ton of parallelism in my execution plan I’m tuning. I read something online and I’m going to alter the value in Maximum Degree of Parallelism (MAXDOP) so they go away."
This is a very common problem and a very common solution that is found on the great wide internet of SQL and Google search results. The first thing is, don’t blanket change MAXDOP simply because you read it somewhere or someone says they did it or always do it. This isn’t a good default thing to set typically. Some specific database servers can take this type of change but it is more of a diagnosed and specific change.
The question in the beginning of this blog is fictitious but let’s pretend it is real and play it out.
Load up some data into a table. Remember, Parallelism may only show on a table larger than normal, so load a good deal of data into the table.
- CREATE TABLE LotsOhColumns
- (
- id int identity(1,1) primary key
- ,fk_id uniqueidentifier
- ,col1 varchar(20)
- ,col2 varchar(20)
- ,col3 varchar(20)
- ,col4 varchar(20)
- ,col5 varchar(20)
- ,col6 varchar(20)
- ,col7 varchar(20)
- ,col8 varchar(20)
- ,col9 varchar(20)
- ,col10 varchar(20)
- ,col11 varchar(20)
- ,col12 varchar(20)
- )
- GO
- INSERT INTO LotsOhColumns
- SELECT NEWID(),'col1','col2','col3','col4','col5','col6','col7','col8','col9','col10','col11','col12'
- GO 3000000
Now let’s run a basic query on the table
- SELECT [fk_id]
- ,[col1]
- ,[col2]
- ,[col3]
- ,[col4]
- ,[col5]
- FROM [XMLContent].[dbo].[LotsOhColumns]
- WHERE fk_id = NEWID()
Looking at this plan, tons of issues come up. The first one that we insist on tuning is the parallelism and run out to change MAXDOP. Not yet everyone!
Looking at the query we need a nonclustered index on fk_id and include on the resulting columns. This should prove to be useful given the results and predicate.
- CREATE INDEX IDX_COVERING_ASC ON LotsOhColumns
- (
- [fk_id]
- )
- INCLUDE
- (
- [col1]
- ,[col2]
- ,[col3]
- ,[col4]
- ,[col5]
- )
Executing the query again shows a much cleaner plan
What did we learn here?
Don't believe everything you read or are told on the internet. Even this blog should be verified by official documentation and highly skilled experts in the field. The results shown here is the MAXDOP setting should not be an easy setting to run to when parallelism is shown in execution plans. Tune the plan and when that is accomplished and if parallelism is truly an issue on your highly active OLTP SQL Server installations, then take a look at MAXDOP.
Two resources that will assist on this topic greatly are the new book Troubleshooting SQL Server: A Guide for the Accidental DBA, specifically the chapter on CPU and SQL Server. Also, highly recommend fellow SQL Server MVP Paul White’s paper, “Understanding and Using Parallelism in SQL Server” to read more on effects and why, how SQL Server uses Parallelism.
Besides, setting the cost threshold for parallelism to something higher than the default value of 5 is a much more useful change. 
Absolutely! Or in some hardcore cases, the MAXDOP hint can be used over a server wide configuration change. Although until last week I had never used the hint in production. :)
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.