2

The Cost Threshold for Parallelism Setting on one of our servers is set at what is generally considered too low (15) and we are considering increasing to 50 in hope to reduce CPU as it is becoming high.

I'd like to know which queries will be affected so we can do some testing and monitoring on them.

The way I have gone about this is to query the plan cache (and parse the XML). Notwithstanding the problems of using plan cache (plans being aged out / thrown out under memory pressure, server reboots etc) is this the best way to go about this?

My query is

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') AS QueryCost ,
        t.text
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
WHERE   p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') > 15 AND
        p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') <= 50 
1
  • I would use that query. Commented May 5, 2022 at 23:24

2 Answers 2

2

Since you're using SQL Server 2016, I would recommend enabling Query Store on your user databases. You can use the same query above but point to the sys.query_store_plan DMV instead of the plan cache to fetch and interrogate the query plan XML.

Using Query Store ensures your plans are persisted and will likely be kept longer than the cache may retain them. Query Store also allows you to analyse other elements of the queries to help determine potential impacts. It also makes it easier to identify parallel plans, since the DMV has the is_parallel_plan column, which you can use to identify only those queries between those cost thresholds that are parallel.

You also then have the ability to easily identify query regressions after any change to CTFP is made. This means if you identify only a handful of regressions post-change, you can potentially force the old parallel plan to be used while you work to optimise the query for serial execution, and changing the CTFP and rolling it back won't force these plans to be recompiled and potentially flushed from the cache.

Its the same basic principle suggested when raising the Compatibility Level of a database which allows you to easily set a baseline and then identify regressions after the change.

1
  • Hi @handyD yes, we use query store extensively - Good shout - makes much more sense to point at it at that DMV and then easier to join back to runtime stats . Perfect, thanks very much
    – SE1986
    Commented May 6, 2022 at 8:34
0

Plans in the cache contain multiple queries. You can see individual queries from each plan by breaking out the XML using .nodes.

You can also improve the performance of this query slightly by pushing the WHERE into XQuery as follows

DECLARE @newCTFP int = 50;
DECLARE @oldCTFP int = (SELECT CAST(value AS int) FROM sys.configurations c WHERE c.name = 'cost threshold for parallelism');

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  x.stmt.query('.') AS StatementPlan,
        x.stmt.value('@StatementSubTreeCost','float') AS QueryCost,
        t.text
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
        CROSS APPLY p.query_plan.nodes('
            /ShowPlanXML/BatchSequence/Batch/Statements/*[@StatementSubTreeCost]
                [@StatementSubTreeCost > sql:variable("@oldCTFP") and @StatementSubTreeCost <= sql:variable("@oldCTFP")]
          ') x(stmt);

If you want to use Query Store, as recommended by the other answer, you can change it just a little

DECLARE @newCTFP int = 50;
DECLARE @oldCTFP int = (SELECT CAST(value AS int) FROM sys.configurations c WHERE c.name = 'cost threshold for parallelism');

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  x.stmt.query('.') AS StatementPlan,
        x.stmt.value('@StatementSubTreeCost','float') AS QueryCost,
        t.query_sql_text
FROM    sys.query_store_plan p
        JOIN sys.query_store_query q ON q.query_id = p.query_id
        JOIN sys.query_store_query_text t ON t.query_text_id = q.query_text_id
        CROSS APPLY (SELECT CAST(p.query_plan AS xml) ) v(xmlPlan)
        CROSS APPLY v.xmlPlan.nodes('
            /ShowPlanXML/BatchSequence/Batch/Statements/*[@StatementSubTreeCost]
                [@StatementSubTreeCost > sql:variable("@oldCTFP") and @StatementSubTreeCost <= sql:variable("@oldCTFP")]
          ') x(stmt);

You can further join to sys.query_store_runtime_stats and others to get even more information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.