Overview

Στο SQL Server υπάρχουν αρκετά configuration settings, ένα από αυτά είναι το Cost Threshold for Parallelism.

Το documentation είναι σαφέστατο για το συγκεκριμένο setting.

The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and is not a unit of time. The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.

SQL Server documentation

Επίσης σε αυτό στα recommendations ξεκάθαρα αναφέρει:

This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server professional.

SQL Server documentation

Τέλος σε αυτό υπάρχει η εξής πρόταση

While the default value of 5 is adequate for most systems, a different value may be appropriate. Perform application testing with higher and lower values if needed to optimize application performance.

SQL Server documentation

Investigation steps

Με βάση αυτή την πρόταση εγείρεται το ερώτημα πως μπορεί κάποιος να κάνει το test αυτό ή πως από τα ήδη εκτελεσμένα queries να αποκτήσει μια πρώτη εικόνα η οποία να αποτελέσει μια βάση έρευνας;

Την απάντηση την δίνει φυσικά ο SQL Server αρκεί να γνωρίζει αυτός που ψάχνει που θα την βρει. Το σημείο αυτό είναι τα execution plans και συγκεκριμένα πρέπει να δει τη τιμή EstimatedTotalSubtreeCost που υπάρχει σε αυτό για τον πρώτο node που είναι στην ουσία τα τελικό select. Αυτό μπορεί να γίνει διαβάζοντας το XML του execution plan το οποίο μπορεί να διαβαστεί με την χρήση της sys.dm_exec_query_plan. Επειδή αυτή επιστρέφει αποτέλεσμα μόνο για συγκεκριμένο execution plan πρέπει να διαβάσουμε αυτά που είναι στην plan cache και να συνδυάσουμε τις πληροφορίες των δύο όπως κάνω στο παρακάτω query.

Στο τελικό αποτέλεσμα μπορώ να κάνω τα aggregations που θέλω πχ average ώστε να αποκτήσω την εικόνα που επιθυμώ.

;with xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
queries as 
(
    select    
            t.text                        as sql_text
        ,    s. plan_handle                as plan_handle
        ,    sum(s.total_elapsed_time)    as total_elapsed_time
        ,    sum(s.execution_count)        as total_execution_count
    from    
            sys.dm_exec_query_stats as s
    cross apply
            sys.dm_exec_sql_text(s.sql_handle) as t
    group by 
            t.text,
            s.plan_handle
),
plans as 
(
    select 
            q.*
        ,    p.query_plan 
    from 
            queries as q
    cross apply 
            sys.dm_exec_query_plan(q.plan_handle) as p
)
select 
        p.sql_text
    ,    p.query_plan
    ,    qp.n.value(N'@EstimatedTotalSubtreeCost', N'float') as estimated_cost     
    ,    qp.n.value(N'(@EstimateRows)[1]', N'float') as estimated_rows 
    ,    p.total_elapsed_time
    ,    p.total_execution_count
from 
        plans as p
cross apply 
        query_plan.nodes(N'//RelOp') as qp(n)
where 
        qp.n.value(N'@NodeId', N'int') = 0 
order by
        estimated_cost desc;

//Antonios Chatzipavlis