Yesterday, I had a very interesting situation, one of our Comprehensive Database Performance Health Check client immediately called me on Skype with his entire server going continuously stopping due to a very toxic poison wait type of Resource Semaphore. Though, we had a good idea how to fix the wait type the real issue was the server was not staying up long enough to put us a patch in the system. It was extremely frustrating for us and finally, we were able to deploy the patch Enabling Older Legacy Cardinality Estimation.
When we see SQL Server struggling with the performance we checked the recently ran query and realize that there was a very toxic Resource Semaphore for a query. We got the execution plan from Query Store and we also found a fix for it. However, every time whenever we try to put the fix, the server was going unresponsive.
We all were running out of the idea to keep the server up and finally, one of the ideas was to move the database to run older cardinality level. We immediately Enabling Legacy Cardinality Estimation and server got stable. After a while, we implemented our patches and were able to turn off the Legacy Cardinality Estimation.
Here is the script to enable older legacy cardinality estimation for your database.
USE [YourDB] GO ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; GO
The following script turns off legacy cardinality estimation.
USE [YourDB] GO ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF; GO
Now when you think of it, the problem and outcome have no relationship but often thinking out of box helps. This is a true story.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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 https://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