Best practice for changing SQL Server compatibility level from 120 to 140

Question

We have SQL Server 2014 and we moved to 2017, we didn't update compatibility level yet to 140, so we keep it 120, but before doing it, I want to make sure it will not affect anything.

Is there any best practice to follow before changing compatibility level?, should be rebuild indexes, update statistics, change default setting or something like that?

Thank you.

Answer 1

I wrote this script to do some benchmarking between compatibility levels 100 and 150. You can change that easily. Follow the instructions in the comments. I save results to a table in dbo, but you could change that easily as well.

/*
This script will benchmark the differences between an older version of SQL and SQL 2019.  

THIS SCRIPT SHOULD NEVER BE RUN ON A PRODUCTION SERVER AS IT CLEARS THE QUERY CACHE!!!!

To avoid going crazy seeing a million results in the results pane, enable "discard results after query execution" 
and then after the script is run, turn it back off and view the temp table.  (SELECT * FROM Benchmark_performance_stats)
(Query -> Query Options -> Results -> Discard results after query execution)

You can set the schema to check in the next declare statement
*/
DECLARE @schema sysname = 'dbo'

IF (SELECT @@VERSION) NOT LIKE '%2019%'
BEGIN
    PRINT 'This server is not runnning SQL 2019. This script requires SQL 2019'
    PRINT 'Script aborting'
    SET NOEXEC ON
END

SET NOCOUNT ON
IF OBJECT_ID('Benchmark_performance_stats','U') IS NULL
CREATE TABLE Benchmark_performance_stats (
    ID INT IDENTITY(1,1),
    view_name NVARCHAR(255) NULL,
    old_sql_text NVARCHAR(1000),
    old_cpu_time BIGINT,
    old_query_cost FLOAT,
    new_sql_text NVARCHAR(1000),
    new_cpu_time BIGINT,
    new_query_cost float,
    cpu_time_ratio float,
    query_cost_ratio float
    )

DECLARE @counter INT = 1  --this keeps track of the temp table row so that we add both test results to the same row
DECLARE @view_name sysname
DECLARE @sql_command NVARCHAR(1000)

/*
Start process of cycling through all these views
First, close any open cursor
*/

IF CURSOR_STATUS('global', 'views_to_check') >= -1
BEGIN
    CLOSE views_to_check
    DEALLOCATE views_to_check
END

DECLARE views_to_check CURSOR FOR
    SELECT TOP 10 v.name        /* chanbge 10 to the number of views you want to check per each run */
    FROM sys.views v
    INNER JOIN sys.schemas s
    ON s.schema_id = v.schema_id
    WHERE s.name = @schema
        AND v.name NOT IN ( SELECT view_name FROM dbo.Benchmark_performance_stats)  /* do not test views already completed*/
    ORDER BY v.name

OPEN views_to_check
FETCH NEXT FROM views_to_check
INTO @view_name

WHILE @@FETCH_STATUS = 0
BEGIN
    /* Free memory and query cache*/
    DBCC FREEPROCCACHE WITH NO_INFOMSGS
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

    SET @sql_command = 'SELECT * FROM [' + @schema + '].[' + @view_name + '] OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100''))'
    RAISERROR (@sql_command,0,1) WITH NOWAIT  --force output pane to flush

    BEGIN TRY
        EXEC sp_executesql @sql_command

        /* grab the worker time and estimate query cost from the DMVs */
        ;WITH XMLNAMESPACES  
        (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
        INSERT INTO Benchmark_performance_stats (view_name, old_sql_text,old_cpu_time,old_query_cost)
        SELECT 
            @view_name,
            st.text
            ,qs.last_worker_time
            ,n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
        FROM sys.dm_exec_cached_plans AS cp
        CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
        INNER JOIN sys.dm_exec_query_stats qs
        ON qs.plan_handle = cp.plan_handle
        WHERE text = @sql_command
        OPTION(RECOMPILE);
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE()
        INSERT INTO Benchmark_performance_stats (view_name, old_sql_text, old_cpu_time, old_query_cost)
        VALUES (@view_name, @sql_command, NULL, NULL)
    END CATCH 

    SET @counter = (SELECT MAX(ID) FROM Benchmark_performance_stats)  -- we do it this way becuase if there is an error, the counter would not match any longer

    /* now do the same trick for the new compatibility level */
    DBCC FREEPROCCACHE WITH NO_INFOMSGS
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

    SET @sql_command = 'SELECT * FROM [' + @schema + '].[' + @view_name + '] OPTION (USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150''))'
    RAISERROR (@sql_command,0,1) WITH NOWAIT  --force output pane to flush
    BEGIN TRY
        EXEC sp_executesql @sql_command

        ;WITH XMLNAMESPACES  
        (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
        UPDATE Benchmark_performance_stats
        SET new_sql_text = x.text,
            new_cpu_time = x.last_worker_time,
            new_query_cost = x.StatementSubTreeCost
        FROM (
            SELECT st.text
                ,qs.last_worker_time
                ,n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
            FROM sys.dm_exec_cached_plans AS cp
            CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
            CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
            CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
            INNER JOIN sys.dm_exec_query_stats qs
            ON qs.plan_handle = cp.plan_handle
            WHERE text = @sql_command
        ) X
        WHERE ID = @counter
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE()
    END CATCH 

    FETCH NEXT FROM views_to_check
    INTO @view_name
END

CLOSE views_to_check
DEALLOCATE views_to_check

UPDATE Benchmark_performance_stats
SET cpu_time_ratio = new_cpu_time / CAST (old_cpu_time AS FLOAT),
    query_cost_ratio = new_query_cost / CAST(old_query_cost AS FLOAT)

SET NOCOUNT OFF
SET NOEXEC OFF /* if we bailed earlier, turn execution back on now that we have reached end of script */

/*
When finished, turn discard query results after execution back off and run the follwing command to see results.
SELECT *
FROM dbo.Benchmark_performance_stats
*/