Find Which Queries will be affected by Proposed Change to CTFP
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)','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)','FLOAT') > 15 AND p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)','FLOAT')
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 https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-plan-transact-sql?view=sql-server-ver15 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.