Infrastructure at your Service

Stéphane Haby

SQL Server: Using Client Statistics to validate a query regression after a change in the database compatibility level

Few days ago, I have very poor performance on a database that I migrate from SQL 2012 to SQL 2019.
The developer Team asks me to upgrade the number of CPU and the Memory to have better performance.
First, I ask the Team to give me some queries sample to test the performance between the old environment in SQL server 2012 and the new one in SQL server 2019.
To do the test, I use the option in SQL Server Management Studio: Client Statistics

Before I begin all tests and between all changes that I perform, I will reset the Client Statistics.
To reset it, go to Query> Reset Client Statistics:

For all test, I run 5 times the query.
First, I test with the default value when I migrate:

USE TestDB
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 150
GO

To have the Client Statistics, go to the menu, at the right from “Include Live Query Statistics” , click to enable it

I run 5 times the query and have this result on the Tab Client Statistics:

As reference, I have now the three last lines in the Average Column:

  • Client processing time : 1336,6
  • Total execution time : 2242,6
  • Wait time on server replies : 906

Now I do a reset and change only the compatibility level to the old one:

ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 110
GO

I run 5 times the query and have this result on the Tab Client Statistics:

I have with the old Compatibility level this result:

  • Client processing time : 389
  • Total execution time : 1082,2
  • Wait time on server replies : 693,2

At this Point, no real performance gain with the compatibility level changes
Now, I change the Cardinality Estimation to the old one and the Compatibility level to 150

USE TestDB
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO
ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 150
GO

I reset the Client Statistics and run 5 times the query:

I have with the old Cardinality Estimation and the Compatibility level this result:

  • Client processing time : 1083,2
  • Total execution time : 1905,6
  • Wait time on server replies : 882,4

Now I do a reset and change only the compatibility level to the old one:

ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 110
GO

I reset the Client Statistics and run 5 times the query for the last time:


The last result is:

  • Client processing time : 401,2
  • Total execution time : 1140,4
  • Wait time on server replies : 739,2

Finally, in my case, the Cardinatity Estimation don’t change the performance after the migration but the compatibility level change the performance (~2 times better).
I will keep the compatibility level at SQL 2012 and not upgrate to SQL 2019…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant