Understanding the load running on my SQL server
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://docs.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://docs.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=&quot;http://schemas.microsoft.com/sqlserver/2004/07/showplan&quot;;
        max(//p:RelOp/@Parallel)', 'float') &gt; 0 then 1 else 0 end
) as #num_parallel_plans,
SUM(
    case when p.query_plan.value('declare namespace p=&quot;http://schemas.microsoft.com/sqlserver/2004/07/showplan&quot;;
        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)