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://darkslategrey-bat-805937.hostingersite.com)
