Here's a query that should find all the plans in the cache with parallel scans. I've modified the original query from here: https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/

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="Table Scan" or @PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan") and @Parallel="1"]') = 1

Be warned that running this query on a production server can take quite a while, and uses quite a bit of CPU.

Now, as for tuning cost threshold for parallelism, doing it correctly will require understanding what queries are running in parallel, how important those queries are to the business, and whether or not parallelism is even helping (it often doesn't, especially with queries that have lower costs).

The default setting of 5 is almost certainly far too low on modern systems, and you will see general recommendations in the range of 25-50 as a starting point on new machines.

The thing you have to be aware of is that if you have an important query that runs 60 times an hour and actually benefits greatly from running in parallel, and has a cost of 24, then even the otherwise conservative setting of 25 might be too high for your workload.

It's ultimately a matter of figuring out which queries will be affected, how they will be affected (test, test, and test some more), and making a judgment about the optimal setting based on that information. It's not a one-size-fits-all answer, but the right ones rarely are. 🙂

Cheers!