0

In production environment found that the default value of 5 is there for Cost Threshold for parallelism and 0 for Max Degree of Parallelism. From various resources I understand that it is not the optimal value and has to be changed. Now the question I am trying to figure out is what might be the optimal value for my environment.

With the help of sys.dm_os_sys_info found that the environment has:

  • Physical CPU = 1 & Logical CPUs = 4

So does it mean I can set Max Degree of Parallelism as 4? If not please help me understand what to look for and decide the value for it.

Made use of Jonathan Kehayias below script:

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 

and this is the summary I found:

  • StatementSubTree cost: Mean is 36.5, Mode is 12.78 and Median is 9.38
  • Totally ~850 entries out of which ~750 entries subtree cost is less than 50. And ~25 is above 100 & out of which 15 is above 500.

So wondering should I change the cost threshold value as nearest to median or mean. If that is not the way to do please guide me the steps to be followed.

Wait Stats result: enter image description hereenter image description hereenter image description here

  • 1
    Before making any changes -- are you actually facing a performance problem around parallel queries or CPU usage? Have you looked at wait stats? – Erik Darling May 14 '17 at 14:30
  • I checked the wait stats and top most is CXPacket and within the top 10 or 12 items I remember seeing these wait stats as well. Latch_XX, PageIoLatch_xx, SOS_scheduler_yeild. Got complaints from end users that pages are loading slow. Not very sure how to narrow down and say its a performance issues due to parallel queries. – prasanth May 14 '17 at 14:34
  • 1
    If you are seeing parallel queries for an OLTP workload, that suggests query and index tuning is needed. Increasing the cost threshold and/or reducing MAXDOP might mitigate the problem with end user response time won't address the root cause. – Dan Guzman May 14 '17 at 14:49
  • 1
    So far the only symptom mentioned is slow page loads. You should take steps to exclude the possibility that it's your web server. It might have have nothing to do with parallelism. It could be parameter sniffing or lack of indexes. Don't make any changes until you have some baseline and some understanding of what the root cause is. – Nick.McDermaid May 15 '17 at 3:38
  • 2
    Looking at the wait stats, SQL Server is bored. In 1700 hours of uptime you don't have a single prominent wait. I wouldn't change any settings, and I'd follow @Nick.McDermaid 's advice to look elsewhere. – Erik Darling May 15 '17 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.