Downlevel CE

Question

Post reply

Downlevel CE

Milapi65

SSC Enthusiast

Points: 173
More actions
September 6, 2023 at 7:47 am

#4280455

Good day All,
We have a weird issue where someone might be able to shed a light on it:
We have the same SQL database installed on 2 different servers, where we can see different results with sp_blitzcache. On one server, most of the queries which are executed by a 3rd party application, are listed with "Downlevel CE" (Legacy Cardinality Estimator in use). If we check the execution plan, the properties of the select statements indicate "CardinalityEstimationModelVersion = 120"
Some specs:
- Both servers running SQL Server 2017 with the same patchlevel on the same Windows server OS
- Database is on compatibility level 120
- Legacy cardinality estimator is set to OFF within the database
- The 3rd party application is the same used for connecting to both SQL instances
- The SQL database is restored from a backup to the 2nd server
- No traceflags set either within the queries nor in SQL
The only major difference is the performance of the server itself. This 2nd server has much lower CPU cores, slower disks and lower memory (this is because it is only a test server)
Is it possible that the lower server specs are the reason for Legacy Cardinality Estimator used? Or are there any other settings we need to check?
Thanks for any assistance.
Regards Michael

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Pretty sure that it is saying that you are using an old CE because you are running SQL Server 140 but have database compatibility in 120. So the CE is running at 120 even though the instance is set to 140. I would be willing to bet that if you bumped the compat level to 140, that message would go away.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Milapi65 SSC Enthusiast Points: 173 More actions · Answer 3

Hi Brian,

Thanks for your answer and it looks like you are right. Due to the explanation in link https://www.brentozar.com/blitzcache/legacy-cardinality-estimator/ I was under the impression that only compatibility levels lower 120 are generating these result.

Thanks again for your help.

Regards

Michael

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

Please check the trace flags in the server startup. There is a trace flag that can be set to force the old CE to be used and someone may have set it. I wouldn't mess with the Recovery Model to do this. Use the latest Recovery Model all the time unless there is a seriously compelling reason not to... and this is NOT one of the compelling reasons.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

I should say one thing - before changing compat levels, I would test things out on a sandbox. There ARE valid use cases for using the old CE or older compat levels, so make sure you test things out before changing things in production. Last thing you want to happen is to fix the warning and suddenly have horrid performance. I've read scenarios where people updated and changed to the new CE with the new SQL version and performance on one or more queries tanked so bad that the release was planned to be rolled back. The solution though was to update the few slow queries to use the old CE until they could be re-written to perform better.

All scenarios I've read about performance tanking were related to poorly written queries and short term fixes were to have the few queries use the old CE, not to use an old compat level or turn on old CE across the instance.

Offhand, the ONLY use case I've ever read about for using an old compat level was with one 3rd party tool which claimed to not work with newer SQL Server and they recommended a specific compat level. And even in that scenario, I have a suspicion that things would have worked perfectly fine on the newer compat level, but the software vendor didn't support that configuration, so if you wanted support, you had to meet their requirements.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.