February 17, 2023 at 5:03 pm
I have two databases in SQL server hosted on 2016 enterprise edition. One has compat level 110 (DatabaseA) and another one has compat level 130(Databae B)
DatabaseA and DatabaseB are loaded from the same soure, they have similar structure with some differences.
DatabaseA has more data (historical) than DatabaseB
Similar query runs really faster on DatabaseA but very slow on DatabaseB. (like 1 min 30 secs on DatabaseA vs 7-8 mins on DatabaseB)
One of the table that is used in the query is partitioned and clustered columnstored index in Database B (530145838 rows). But just a clustered index in DatabaseA (1810451555 rows).
Any help/suggestions to improve DatabaseB will be highly appreciated as we are in the process of shutting down DatabaseA.
February 17, 2023 at 5:27 pm
So, performance comes down to identical behaviors. A similar query running on a similar structure with similar data sets may, yeah, perform wildly different. This is because every little thing adds up. Different code means different choices by the optimizer, even if the code is close. Different structures means the optimizer MUST make different choices (partition elimination on a partitioned index for example). And different data sets results in different statistics which leads to different row estimates ultimately resulting in changes to the execution plan choices by the optimizer. Different compatibility levels also means different cardinality estimates.
So, comparing similar to similar, frequently just leaves you frustrated.
However, start with the execution plan for each. Understand the estimated values and how they may be different. Understand why they're different. It may be down to the different structures, but it could also be due to the cardinality estimation. That can lead you towards what you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2023 at 6:29 pm
Based on the Compatibility Levels you've posted, there's a really good chance that this is because of a fairly major change they made to the "Cardinality Estimator" (CE for short) in SQL Server 2014 (Compatibility Level 120). That change killed us and, thank goodness, there was a way to override the change. The CE can be changed at the instance level, database level, or query level. Please see the following article for more information.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply