December 10, 2019 at 11:23 am
Few reports are run which uses source db which was upgraded to SQL 2017 this weekend. After this change those SP's are running very slowly and performance degradation is observed.
After using cardinality estimator the query just run fine. How should this be resolved.
Its not possible to change 100+ queries to use legacy cardinality. We can put source db back to sql2012 mode.
December 10, 2019 at 2:31 pm
I assume you jumped from below SQL Server 2014 to SQL Server 2017?
Your best bet would be to change the compatibility mode back to the old compatibility so that SQL Server uses the old cardinality estimation engine. Then, turn on Query Store. Capture performance metrics for a period of time (you figure out that period, day, month, I don't know, your system). Then, switch the compatibility mode to the new version. Any queries that regress, you can use plan forcing to put the older plan in place. No code changes necessary. You can even turn off data collection on Query Store after that, the plan forcing will continue.
"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
December 10, 2019 at 3:09 pm
Yea we went from SQL 2012 to SQL2017.
We cannot put our systems back to 110 since other product would be impacted.
Morever, forcing old query plan would be bad idea. With newer version I suppose we should atleast not see very high degradation ( in our case from seconds to hrs)
What else can be done? Modify query? or?
December 10, 2019 at 3:14 pm
If you can't reset to capture the older plans, you're stuck. Code modification is the best way to go. You could also look to plan guides to supply the hint.
Most of the time, queries are either neutral or faster after an upgrade. I've only seen consistent performance hits on queries that were already edge cases for performance. I'd start evaluating them for tuning rather than just force hints on them. If you have to modify the code anyway, modify it to just make the query faster.
"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
December 10, 2019 at 7:51 pm
New CE can do quite bad stuff.
One case I had, huge CRM database, had one simple query -- select 6 columns from tbl where field = 'abc'
table with 200 Million row
Field in question was always null - New CE would always pick up a table scan and run for ages, old CE would take miliseconds.
Running stats didn't help 🙂
At the OP - do the other products use the same database? or different databases on the same server?
If different databases you can change the CA mode just on the problematic database to solve the issue
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
will change just the DB where you run it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply