Legacy Cardinality Estimation temporarily stabilized one customer memory-grant problem. A query waited on RESOURCE_SEMAPHORE while the instance became unresponsive.
Query Store helped identify a longer-term correction. Applying it was difficult while the instance was unresponsive. The older estimator changed behavior enough for us to continue. We later applied the correction and restored the previous setting.
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = N'LEGACY_CARDINALITY_ESTIMATION';
-- Historical database-level workaround, after selecting the intended database:
-- ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
-- Restore the verified baseline when appropriate, not blindly in every database:
-- ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;RESOURCE_SEMAPHORE indicates a wait for query execution memory grants. Estimates can affect those grants. This case doesn’t establish a universal remedy. I compare the query’s estimates, plan and grant requirements.
Database-scoped LEGACY_
Reference: Database-scoped estimator control.
Related reading
A different estimator is not a universal memory-grant cure, it is a scoped change that needs plan evidence.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
Hi Pinal, I guess that was SQL Server 2016 or above. Was it recently migrated to 2016 from lower edition ?
We faced a query(Stored Proc) issue on 2014, not completing and going into CXPACKET waits, after the database was migrated from 2008. But it worked after setting Compatibility to 2008. Performance degradation is known issue after migration. The issue was fixed with running Rebuild Index job and Updating Stats with Full Scan.
My question is – how do we know that the solution is not lowering compatibility level but tuning the query ?
Hi kunal,
This is very popular question I receive during my http://blog.sqlauthority.com/comprehensive-database-performance-health-check/ and will be happy to help you with the same.
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
Hi Pinal,
I am also facing such performance issues. We have migrated from SQL server 2012 to 2017. There are some procedures which are taking 15 seconds for execution in compatibility level 110 and >4 mins in compatibility level 140. I got the query which is more time consuming in the procedure. Query in compatibility level 140 is consuming high CPU and using DOP 1 but in compatibility level 110 its using DOP 8.
Then I used the hint of MAXDOP 1 for 110 and its working fine and then used MAXDOP 8 for 140 its still taking high time for execution. Now execution plan seems same for both compatibility level but 140 is performance is very poor.
Could you please suggest, what is the main performance factor here that need to check ?
Thanks
Ashish Jain
Hi Pinal,
You have already compared the following two deserts in SQL Server 2012 and their plans were not different
— Option 1
SELECT p.ProductID
FROM Production.Product p
INNER JOIN Production.ProductListPriceHistory pph ON p.ProductID = pph.ProductID
INNER JOIN Production.WorkOrder wo ON p.ProductID = wo.ProductID
GO
— Option 2
SELECT p.ProductID
FROM Production.Product p
INNER JOIN Production.WorkOrder wo ON p.ProductID = wo.ProductID
INNER JOIN Production.ProductListPriceHistory pph ON p.ProductID = pph.ProductID
But when you compare in SQL 2014 and later, the plans are different.
And it becomes the same by Enabling Older Legacy Cardinality Estimation plans.
why ?
I think this is a bug in the new estimation algorithm