THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Tuning ‘cost threshold for parallelism’ from the Plan Cache

A good while ago I was asked if it was possible to consistently produce a parallel plan by Tom LaRock(blog/twitter), and I recalled from reading Grant Fritchey’s(blog/twitter) book on Execution Plans that it was possible to do this by adjusting the ‘cost threshold for parallelism’ sp_configure option from the default of five to a value of one which will make even simple queries run in parallel if you have multiple logical processors. 

This got me think about this option and wondering if the default is still applicable in today’s servers and database environments.  If you think about when the default values for SQL Server were actually created, it was a long time ago and in today’s large commodity hardware servers some of them aren’t the best for SQL any longer.  For example, most people migrating from an x86 SQL Server to a x64 server learn really quick that you have to set Max Server Memory on a 64 bit server, something that you could get away with not setting on older x86 hardware even though it was still a recommended practice. 

Max Degree of Parallelism is another hot one these days with newer Xeon Quad Core and Nahelem six core processors and reduced server costs, it is easy to slam sixteen or twenty-four cores into a relatively cheap server that has 64-128GB of RAM, a configuration that only a few years ago was specialty hardware that was cost prohibitive for most shops.  These kinds of servers have quickly become the answer to performance problems that aren’t necessarily hardware related but instead poor design.  What you get is a person with no idea how SQL Server works, with a really powerful server that is full of performance problems because it is trying to run with the default settings which aren’t appropriate for this size of hardware.

In Bob Ward’s session on wait stats at PASS, one thing he said a couple of times is that the answer to CXPACKET waits, isn’t to reduce the ‘max degree of parallelism’ sp_configure option for the hole server.  I have to admit that I am guilty of recommending this solution hundreds of times before PASS and in most cases I continue to recommend this solution to people who have ho idea what they are doing  because:

A.  Most seem to be to lazy to read the referenced whitepapers provided to them for troubleshooting.
B.  They have no idea what they are doing.
C.  They want a quick fix that gets their manager off their back.

One thing that Tom’s question lead me to thinking about was whether or not adjusting the ‘cost threshold for parallelism’ up from five to a number that still allows large queries that don’t execute frequently to work with parallelism, while minimizing the number of smaller queries that execute more frequently from running in parallel.  To help with determining where this option might be set, I wrote a query to search the plan cache for existing parallel plans and see the cost associations to current plans that executed parallel.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES  
  
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
SELECT 
       
query_plan AS CompleteQueryPlan,
       
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
       
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
       
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
       
n.query('.') AS ParallelSubTreeXML
       
ecp.usecounts,
       
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE  n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

I look at the high use count plans, and see if there is a missing index associated with those queries that is driving the cost up.  If I can tune the high execution queries to reduce their cost, I have a win either way.  However, if you run this query, you will note that there are some really high cost queries that you may not get below the five value.  If you can fix the high use plans to reduce their cost, and then increase the ‘cost threshold for parallelism’ based on the cost of your larger queries that may benefit from parallelism, having a couple of low use count plans that use parallelism doesn't have as much of an impact to the server overall, at least based on my own personal experiences.

What are your thoughts?

Published Tuesday, January 19, 2010 1:19 AM by Jonathan Kehayias

Comments

 

Paul White said:

Something that can work well in many environments, assuming a primarily OLTP-type workload:

1.  Server-wide setting to MAXDOP = 1

2.  Cost threshold set to zero

3.  Use the MAXDOP = N query hint on code that benefits from parallelism (even if it's just MAXDOP 2 to allow bitmap operators).

I also would dearly love to see an OPTION (USE_PARALLEL_PLAN) hint in SQL Server, but I can quite see why the SQL Server team would be reluctant to do this - sigh.

January 26, 2010 6:02 AM
 

Jonathan Kehayias said:

Paul,

Your comment is exactly what Bob Ward said not to do in his Wait Stats Session at PASS.  In addition 'cost threshold for parallelism' is ignored as is the MAXDOP hint when you set the server wide setting for 'max degree of parallelism' to 1, so your #2 and #3 are overridden all the time by #1.

http://msdn.microsoft.com/en-us/library/ms188603.aspx

January 26, 2010 2:33 PM
 

Paul White said:

Jonathan,

That is absolutely *not* correct.  A query hint MAXDOP overrides the server-wide setting, and the cost threshold for parallelism is still respected when overriding MAXDOP in this way.

Consider the following very simple query using data from the 2008 SR4 AdventureWorksDW sample database:

SELECT COUNT_BIG(*) FROM dbo.FactInternetSales;

On a server configured with Server MAXDOP = 1, cost threshold = 0, you get a simple serial plan as expected.

Adding OPTION (MAXDOP 2) to the query produces a parallel plan with an estimated plan cost of 0.17

Change the cost threshold for parallelism to 1 and we return to a serial plan.

Separately, Bob's point was to not set server-wide MAXDOP to 1 in an attempt to fix CXPACKET waits.  I could not agree more.  My point was, and continues to be, that OLTP environments can benefit from server MAXDOP = 1, with the DBA selectively overriding MAXDOP as shown.  That was why I heavily qualified my previous comment with the statement concerning OLTP workloads.

Paul

January 27, 2010 11:59 PM
 

Paul White said:

In fairness, the Books Online documentation could be improved to reflect this, so I have created a connect item for it:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=527684

January 28, 2010 12:44 AM
 

Jonathan Kehayias said:

Interesting that it overrides like that.  The BOL is certainly not clear, or incorrect on that topic.  I do know that connect isn't the proper mechanism for providing feedback to the BOL.  The BOL has its own feedback system in the Click to Rate and Give Feedback star system for each page.

I see where you are going with being able to essentially determine yourself whether a query gets to run in parallel or not by making the cost zero so any query you override MAXDOP for can run that way, but that only works in a very focused environment.  If you had a true OLTP and you had complete control over the database code base to be able to manage this, I can see where it might be useful.

I'll have to look at it some more when I get time.

January 28, 2010 1:36 AM
 

David Lozano Lucas said:

I add your query to my arsenal, thanks.

March 25, 2010 12:33 PM
 

Paul White said:

The Books Online documentation has now been updated:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=527684 - Resolved as Fixed.

Hopefully this will help avoid confusion in future.

May 3, 2010 9:12 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement