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
August 26, 2020 at 10:48 am
A couple of things. First, you don't need both. If you set the compatibility mode to 110, you get the legacy cardinality estimation engine. No need to also set that. Now, you can use the legacy engine, but leave the compatibility mode in 140 (150, 160, 130, whatever). Then you get the legacy CE, but all the other enhancements are enabled.
Next, when you compile a batch, all the statements are compiled at once. When you compile that batch and it's running under the new estimation engine, the plan is compiled for that engine. It doesn't matter that you change the engine mid-way, unless you get a statement level recompile. I'd have to test it, but I'm not sure that you would.
However, the key here would be to look at the execution plans. In the properties of the plan itself (accessible through the first operator, unless you capture the plan in Profiler or Extended Events, then it's in the XML), you can see the compile CE. Check that.
As to the problem, most of the time, the queries suffering from this issue were already edge case queries. I'd suggest getting rewrites prioritized.
"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
Also, this document is worth looking at:
https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN
August 26, 2020 at 11:46 pm
Thanks for all your reply!
Putting OPTION (QUERYTRACEON 9481); at the end of the query did the magic.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply