Блог

У нас есть SQL Server 2014, и мы перешли на 2017 год, мы еще не обновили уровень совместимости до 140, поэтому мы сохраняем его 120, но прежде чем делать это, я хочу убедиться, что это ни на что не повлияет.

Есть ли какие-либо рекомендации, которым следует следовать перед изменением уровня совместимости?, должны быть перестроены индексы, обновлена статистика, изменена настройка по умолчанию или что-то в этом роде?

Спасибо.

Я написал этот скрипт, чтобы провести сравнительный анализ между уровнями совместимости 100 и 150. Это можно легко изменить. Следуйте инструкциям в комментариях. Я сохраняю результаты в таблице в dbo, но вы также можете легко изменить это.

 /*
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 ' 19%'
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
*/