Evaluating Success of CTFP Value Change

Question

We have recently made a change to the CTFP setting on our test server with a view to rolling this out to live.

Our current CTFP is 15 so we tried setting it to 50 which is often seen as a better value.

MAXDOP for the server is set at 8 and has been for some time, the server has 16 CPU cores.

To evaluate the effectiveness of the change, we decided to use query store. While CTFP is a server wide setting, our main workload is in one database, on which we have query store enabled.

To analyse the effectiveness of the change, we first dumped a list of all the queries that had a cost of 16 - 49 which would no longer be eligible for paralellism and then made the change

USE MyDB
GO

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  q.query_id,
        qt.statement_sql_handle
INTO    SomeDb.dbo.AffectedCTFPQueries
FROM    sys.query_store_plan p
        JOIN sys.query_store_query q
            ON p.query_id = q.query_id
        JOIN sys.query_store_query_text qt
            ON qt.query_text_id = q.query_text_id
WHERE   TRY_CONVERT(XML,p.query_plan).value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') > 15 AND
        TRY_CONVERT(XML,p.query_plan).value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') < 50


/* change CTFP */
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
RECONFIGURE WITH OVERRIDE
GO

/* free proc cache so new queries get a compilation */
DBCC FREEPROCCACHE

We then let our workload run for a few days and then analyzed the results:

First dump all the executions of the affected queries into a temp table

DECLARE @DBCCFREEPROCCACHETime DATETIME = '2022-06-30T15:54:54' 

/* get all of the executions since the change */
SELECT  a.query_id,
        p.plan_id AS PlanIDD,
        r.*
INTO    #Executions
FROM    (SELECT DISTINCT * FROM SomeDb.dbo.AffectedCTFPQueries) a
        JOIN sys.query_store_plan p
                ON a.query_id = p.query_id
        JOIN sys.query_store_runtime_stats r
                ON r.plan_id = p.plan_id
WHERE   r.first_execution_time >= @DBCCFREEPROCCACHETime AND
        count_executions = 1

Then we can see:

How many of the affected queries have run since the change

/* how many affected queries have run since the CTFP change time */
SELECT      'NumAffectedRunSinceChange' AS NumAffectedRunSinceChange,
            COUNT(DISTINCT query_id) AS NumAffectedRunSinceChange
FROM        #Executions

Which of the affected queries that have run have gone parallel despite being eligible for serial execution - the explanation seems to be this

SELECT      *
FROM        #Executions
WHERE       avg_dop > 1

Which of the affected queries that have run are now running in serial - add these to a temp table so we can analyze how running in serial has affected their performance

SELECT      DISTINCT query_id
INTO        #Dop1Queries
FROM        #Executions
WHERE       avg_dop = 1  AND query_id NOT IN(
                                                /* ignore queries that have run in serial AND paralell */
                                                SELECT      COUNT(DISTINCT query_id) AS [NumAffectedRunInDop>1]
                                                FROM        #Executions
                                                WHERE       avg_dop > 1
                                                )

Queries that have improved / regressed in terms of duration. I also did some CPU time analysis but have excluded those queries for brevity

/* workload period before change */
SELECT  a.query_id,
        p.plan_id AS PlanIDD,
        r.*
INTO    #BeforeChange
FROM    #Dop1Queries a
        JOIN sys.query_store_plan p
            ON a.query_id = p.query_id
        JOIN sys.query_store_runtime_stats r
            ON r.plan_id = p.plan_id
WHERE   r.first_execution_time >= '2022-06-28T17:00:00' AND
        r.first_execution_time <= '2022-06-29T08:00:00'

/* workload period after change */
SELECT  a.query_id,
        p.plan_id AS PlanIDD,
        r.*
INTO    #AfterChange
FROM    #Dop1Queries a
        JOIN sys.query_store_plan p
            ON a.query_id = p.query_id
        JOIN sys.query_store_runtime_stats r
            ON r.plan_id = p.plan_id
WHERE   r.first_execution_time >= '2022-06-30T17:00:00' AND
        r.first_execution_time <= '2022-07-01T08:00:00'

/* improved duration queries */
SELECT  'Improved Duration Queries',
        t.query_sql_text,
        a.query_id,
        a.avg_duration / 1000000 AS BeforeDurationSec,
        b.avg_duration / 1000000 AS AfterDurationSec,
        (b.avg_duration - a.avg_duration) / 1000000 AS DurationDifferenceSec,
        SUM(((b.avg_duration - a.avg_duration) /1000000)) OVER () AS TotalDurationDifferenceSec
FROM    #BeforeChange a
        JOIN #AfterChange b
            ON a.query_id = b.query_id
        JOIN sys.query_store_query q
            ON b.query_id = q.query_id
        JOIN sys.query_store_query_text t
            ON t.query_text_id = q.query_text_id
WHERE   a.avg_duration > b.avg_duration
ORDER BY b.avg_duration - a.avg_duration DESC

/* regressed duration queries */
SELECT  'Regressed Duration Queries',
        t.query_sql_text,
        a.query_id,
        a.avg_duration / 1000000 AS BeforeDurationSec,
        b.avg_duration /1000000 AS AfterDurationSec,
        (b.avg_duration / 1000000) -( a.avg_duration / 1000000) AS DurationDifferenceSec,
        SUM(((b.avg_duration - a.avg_duration) / 1000000)) OVER () AS TotalDurationDifferenceSec
FROM    #BeforeChange a
        JOIN #AfterChange b
            ON a.query_id = b.query_id
        JOIN sys.query_store_query q
            ON b.query_id = q.query_id
        JOIN sys.query_store_query_text t
            ON t.query_text_id = q.query_text_id
WHERE   a.avg_duration < b.avg_duration
ORDER BY b.avg_duration - a.avg_duration DESC

The top 5 improved:

The top 5 regressed:

My conclusion from these findings is the effect of this change has been negligible, barring one query which appears to have significantly regressed and therefore there is no merit in changing CTFP to 50.

My questions

• Have I gone the correct way about evaluating the impact of this change
• Is there anything I have missed / overlooked?
• Are my conclusions correct?