1

A dev team announced performance degradation after migrating their database from SQLServer 2016 to SQLServer 2019. So I started tracing the database using SQL profiler.

It turns out duration, cpu time and the number of reads to complete the SQL statement and the RPC call is much lower on SQL2016 SQL2016

These are metrics for the same statement on SQL2019 SQL2019

I guess the SP/StmtCompleted just shows execution time of the query, while RPC:completed includes resource consumption of compilation.

I pasted the traced statement in ssms to investigate the problem.

-- select @dbid
DBCC FLUSHPROCINDB (@dbId)
GO
set statistics io on
set statistics time on
BEGIN TRAN x
/*
UPDATE myTable
    SET 
        r_modify_date=N'2023-04-18 14:47:33.000'
    ,   r_modifier=N'xxx'
    ,   r_lock_owner=N' '
    ,   r_lock_date=N'1753-01-01 00:00:00.000'
    ,   r_lock_machine=N' '
    ,   i_vstamp=100
    WHERE 
        (r_object_id=N'OID'
        AND i_vstamp=90)
*/
exec sp_executesql N' UPDATE myTable SET r_modify_date=N''2023-04-18 14:47:33.000'',r_modifier=N''xxx'',r_lock_owner=N'' '',r_lock_date=N''1753-01-01 00:00:00.000'',r_lock_machine=N'' '',i_vstamp=100 WHERE (r_object_id=@P1           AND i_vstamp=@P2           )',N'@P1 nvarchar(36),@P2 int',N'OID',90
ROLLBACK TRAN x
set statistics io off
set statistics time off
GO

While searching for causes and solutions I found this post.

Slow query compilation, lots of reads during compilation phase

I'am gratefull the poster added a workaround which helped me to dive into further testing.

I tested the query 3 times on SQL2016 and 5 times on SQL2019 while changing some things in between ... It seems schemabinding has an impact on compile team on both versions, while the absolute impact on lead time is much higher on SQL2019.

DB on SQL Server 2016 (SP1)

  1. Query not in procedure cache
  2. Query in procedure cache
  3. Query not in procedure cache. Results after altering views (commenting with schemabinding).

Restore DB on SQL Server 2019 (RTM)

  • 4 = test with compatibility level 130
  • 5 = test after altering compatibility level to 150
  • 6 = test after rebuild index and update statistics (17 indexes highest fragmenation 9,67%)
  • 7 = after dropping 18 views with invalid column names
  • 8 = after commenting WITH SCHEMABINDING on 1974 of 1992 views using ALTER VIEW

While executing the 8 steps above I noted the results from Query Store and IO Stats ...

Test Results

In addition I checked dependencies on the table ... This indicates there are 724 views that reference the table and more than 52000 columns.

SELECT * from sys.sql_expression_dependencies WHERE referenced_id = OBJECT_ID(N'dbo.table')

This brings me to some assumptions and questions.

Does SQL2019 check all dependencies (views and columns) during compilation, while SQL2016 only checks depending views?

If so, what might be the reason for checking all those dependencies during compilation?

Is there a way to manipulate compilation behaviour without the need to remove schemabinding?

Might this be an optimization that works well in 99% of the cases, but has contra-productive impact on this specific database?

queryplans on github

Plan Before Removing SchemaBinding

Plan After Removing SchemaBinding

Execution Plan On SQL 2016

Before commenting with schemabinding in the views the following query returns 52760 rows. After commenting with schemabinding the same query returns 724 (only the views, not the columns) But I have no idea whether that has impact on the compilation binding phase.

SELECT * from [db].sys.sql_expression_dependencies WHERE referenced_id = OBJECT_ID(N'dbo.dm_sysobject_s') 
7
  • 3
    Can you post the actual execution plans for these? They'll contain compilation duration, CPU, and memory, along with some wait stats. It would be good to compare the plans as well. Commented May 1, 2023 at 21:45
  • RPC:completed also includes time taken to return results Commented May 1, 2023 at 23:29
  • Good to know RPC:completed includes network time. So I guess the application server sends timestamp to sqlserver to tell results have been delivered succesfully. I added a link that contains the sqlplan xml files. Commented May 2, 2023 at 10:32
  • I just added the plan on sqlserver 2016 as well. The other 2 plans made on sqlserver 2019. Commented May 2, 2023 at 10:55
  • 2
    Please don't test with SQL Server 2019 RTM. SQL Server doesn't have Service Packs any more, and doesn't slipstream updates into the installer, so you should just apply the latest CU after installing. You can get the latest CU here: microsoft.com/en-us/download/details.aspx?id=100809 Commented May 2, 2023 at 12:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.