compatibility_level and LEGACY_CARDINALITY_ESTIMATION

Question

Hi everyone,

we migrated from sql 2012 to sql 2016 and since then we have been facing some performance issue. We decided to change the compatibility level to 110 (sql 2012) and set the LEGACY_CARDINALITY_ESTIMATION= ON. It helped but then we were still having performance issues and we switched back to the original configuration.
Now, we lately noticed that one of the sproc which took 4 hrs finished in 35 minutes with the

SET COMPATIBILITY_LEVEL = 110 and LEGACY_CARDINALITY_ESTIMATION = ON
settings.

I decided to turn the feature on inside the sproc and once sproc is done turn it off. But to my surprise if I do it inside sproc and then run sproc, performance is s till bad. But if i change the setting in the same session, I see a quick performance boost. So sproc is not picking up the latest DB changes.

Here is my code in sproc:

declare @sqlcompatiblity nvarchar(200)
SET @sqlcompatiblity = N'ALTER DATABASE xm_data3 SET COMPATIBILITY_LEVEL = 110';

declare @cardinality nvarchar(200)
SET @cardinality = N'USE xm_data3; ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;';

EXEC sp_executesql @sqlcompatiblity;
EXEC sp_executesql @cardinality;

WAITFOR DELAY '00:01:00';

then setting back to original setting

SET @sqlcompatiblity = N'ALTER DATABASE xm_data3 SET COMPATIBILITY_LEVEL = 130';

SET @cardinality = N'USE xm_data3;ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;';

EXEC sp_executesql @sqlcompatiblity;

EXEC sp_executesql @cardinality;

still amazed why is it working if i alter the DB in the same sql session I run the sproc. After I run the sproc, I go and check the DB, and I see settng have been changed.
Any idea?

Thanks,
Ro

Answer 1

Changing the database option in a proc seems like a very weird thing to do, IMO. And I'm not surprised that it isn't picked up by the proc. One aspect is how the plan is cached, but even with recompile in the queries, I'm not surprised if that is the case. And I doubt you'll find this documented anywhere.

So work with what MS wants us to use instead. In your case, since you only want it for the queries in this proc, use optimizer hints. Exactly whine one(s) depends on whether you need optimizer fixes, the cardinality estimator or optimizer enhancements. Or do trial an error. Anyhow, here are hints you can play with. Just search and you'll find out what each do and then you can use one or more accordingly: QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_xxx, ENABLE_QUERY_OPTIMIZER_HOTFIXES, FORCE_LEGACY_CARDINALTY_ESTIMATION, 9481, 2312.

The numbers are trace flags, but they can be turned on at query level using the QUERYTRACEFLAG hint.

Answer 2

Hi @Rohit Kochar ,

Please have a try with below solutions and check whether they are helpful to you:

1. OPTION(USE HINT)
2. Cardinality Estimation
3. DBCC FREEPROCCACHE

You could also refer more tips in below link:
Avoiding SQL Server Upgrade Performance Issues

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer 3

I would suggest that rather than fiddling with compat-level and CE settings, you should tackle the real problem.

The whole idea with the new CE and other optimizer changes is that seen over the overall population of SQL Servers out there, performance should improve. But there will always be cases where these changes backfires. My experience is that in many cases where this happens, this is due to some "problem". It could be that indexing is not the best. It could be that the query is not written in the best way. But the query was left as it was on the old version, because by some chance of luck it performed decently. Or at least it seemed to.

It seems in this case, that the query (or queries) can perform poorly with the old compat level and the old CE as well. This could be because you have moved to a new hardware with more RAM - this can affect the plan choice.

Unless you have not already identified the troublesome query, I suggest that you do that first. Then you need to analyse that query in more details, both with how it is written and what indexes that are available.