{"id":571,"date":"2010-01-19T10:19:00","date_gmt":"2010-01-19T10:19:00","guid":{"rendered":"\/blogs\/jonathan\/post\/Tuning-e28098cost-threshold-for-parallelisme28099-from-the-Plan-Cache.aspx"},"modified":"2017-04-13T13:06:04","modified_gmt":"2017-04-13T17:06:04","slug":"tuning-cost-threshold-for-parallelism-from-the-plan-cache","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tuning-cost-threshold-for-parallelism-from-the-plan-cache\/","title":{"rendered":"Tuning \u2018cost threshold for parallelism\u2019 from the Plan Cache"},"content":{"rendered":"

A good while ago I was asked if it was possible to consistently produce a parallel plan by Tom LaRock(blog<\/a>\/twitter<\/a>), and I recalled from reading Grant Fritchey\u2019s(blog<\/a>\/twitter<\/a>) book on Execution Plans<\/a> that it was possible to do this by adjusting the \u2018cost threshold for parallelism\u2019 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.  <\/p>\n

This got me think about this option and wondering if the default is still applicable in today\u2019s 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\u2019s large commodity hardware servers some of them aren\u2019t 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.  <\/p>\n

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\u2019t 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\u2019t appropriate for this size of hardware.<\/p>\n

In Bob Ward\u2019s session on wait stats at PASS, one thing he said a couple of times is that the answer to CXPACKET waits, isn\u2019t to reduce the \u2018max degree of parallelism\u2019 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: <\/p>\n

\n

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.<\/p>\n<\/blockquote>\n

One thing that Tom\u2019s question lead me to thinking about was whether or not adjusting the \u2018cost threshold for parallelism\u2019 up from five to a number that still allows large queries that don\u2019t 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.<\/p>\n

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