Downlevel CE for a stored proc

Question

Downlevel CE for a stored proc

Anoop Agarwal

SSC Enthusiast

Points: 141
More actions
July 19, 2023 at 12:22 pm
Go to Answer
#4239133

Hello,
I am running a sp on sql server 2019. sp_blitzcache reports it using a downlevel cardinality estimator. My database is on compatibility lever 150 and there are no trace flags in the query.
What else I could check to find out why it is using a downlevel cardinality estimator and how can I resolve the issue.
Thanks
frederico_fonseca

SSCoach

Points: 16158
More actions
July 19, 2023 at 1:18 pm
Answer
#4239188

it could be the "LEGACY_CARDINALITY_ESTIMATION " being set to ON.
see https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver16

Viewing 8 posts - 1 through 7 (of 7 total)

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

Anoop Agarwal SSC Enthusiast Points: 141 More actions · Answer 1

Hello, Thanks for your reply. Looks like someone set the database scoped option for the legacy cardinality estimator to 1. will change that to 0 and see how performance improves.

Thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

Before you do that enable query store (if you haven't already), run with the option on for the moment to collect data, then disable the option to see what regresses etc.

Then you can make a more informed decision on what queries where faster/slower/indifferent with what the plans are saying.

Anoop Agarwal SSC Enthusiast Points: 141 More actions · Answer 3

I am thinking of disabling this option on UAT database we have and monitor it for a week or after which I will move it to production.

I do not have query store enabled on my production database as even with a size of 20 GB, it goes into read_only mode with in few hours. Any ideas on how I could size query store correctly would be appreciated.

Thanks everyone.

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 4

capture mode and cleanup mode should both be set to auto. That is the default now, but previously it wasn't and relatively idle databases would fill it up quickly.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

In addition to changing the capture mode to auto, look to custom to get a little more control, possibly.

"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

Anoop Agarwal SSC Enthusiast Points: 141 More actions · Answer 6

Thanks All for your input, will try to enable the query store and let you know how it goes.