Return to Revisions

usually query leave to the sql server enegine to manage how a query will be executed and this bring to focus on the parallelism settings of the instances (or databases in the newer versions of sql server).

It's a matter of concurrency. Here you can find the sql server ingine parameters that govern the parallelism in query execution:

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15

MAXDOP instance parameter determines the max number of cpu that a single query can use to run.

There is another parameter that govern the threshold for using a parallel plan or not.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=sql-server-ver15

If sql server expects a cost higher than this parameter then it will use a parallel plan (i.e. more than one logical cpu)

with this query you can count the number of parallel plans in cache against single thread plans:

SELECT
SUM(
    case when p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        max(//p:RelOp/@Parallel)', 'float') > 0 then 1 else 0 end
) as #num_parallel_plans,
SUM(
    case when p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        max(//p:RelOp/@Parallel)', 'float') = 0 then 1 else 0 end
) as #num_nonparallel_plans
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
WHERE
cp.cacheobjtype = 'Compiled Plan'
OPTION (MAXDOP 1)