0

We have a project this year to move lot of physical SQL Servers, starting with medium critical servers onto Virtual machines.

Generally on physical boxes we have a large count of logical processors, for example 80. Now when moving to VM we get 64 vCores/vCPU.

My understanding is: it's ok to be on low vCores/vCPU in VM till the time their speed is faster. I mean clock speed compared to one on physical.

This bought up an interesting debate with my colleague on how above behaves differently between servers where the majority of load is either single threaded queries vs parallel ones.

Following are a few questions:

  1. How true is above?

  2. Is there a way we can determine even approximately the servers where the queries are running serially vs parallel? I mean a way to say that my SQL Server has 70% queries which just run single threaded or parallel?

0

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="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)
2
  • Maybe I understand your comment wrong, but cost threshold for parallelism is not a value that is calculated in seconds. You wrote: "If sql server expects a duration higher than this parameter (in second) then it will use a parallel plan" CTFP is an arbitrary value calculated by the SQL _OS based on a very old computer. If the estimated cost for a Index scan/seek exceeds the CTFP value, then it can go parallel. It might not. – John K. N. Jan 8 at 9:58
  • You have right, i updated the answer. too rush to write – MBuschi Jan 8 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.