Find Which Queries will be affected by Proposed Change to CTFP

All we need is an easy explanation of the problem, so here it is.

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 

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply