This below post is written and authored by my dear friend Chris Wood. I am very happy to post his writings here on my blog. Credits of this post all goes to Chris Wood. I am just publishing it here.
Thanks to Deepthi Goguri for the idea behind this edition of T-SQL Tuesday. The official title is “What’s your new favorite feature”.
When I saw the topic, I just knew I needed to tell others about a situation I had experienced. I don’t blog and I’m now retired but I would like others to understand my experience.
In my last gig I experienced several situations that used nested views. This approach may make some situations easier as you get to call one piece of already written code rather possibly copy in bad code. On the downside is performance. We were running SQL2019 at the CU16 security fix build with the databases at 2016 DB compatibility level and the Legacy CE set to ON. When a query was executed that went down 3 or 4 levels of nested views it would take a long time to actually create the execution plan and start returning rows. If I changed to the newest CE rows get returned much quicker but the estimated number of rows to be returned is higher with a small performance improvement.
So my most looked for feature would actually be 2 new features. SQL 2022 brings both DOP and CE feedback options that can eventually add hints to the execution plan for later executions. I had seen Grant Fritchey mentioned Cardinality Feedback recently Monitor Cardinality Feedback in SQL Server 2022 – Grant Fritchey (scarydba.com)
Both of these are controlled by running at the compat level of 160 and by using ALTER DATABASE SCOPED CONFIGURATION options. The options are SET DOP_FEEDBACK = ON and SET CE_FEEDBACK = ON. The CE feedback can also be affected by the query having a coded hint or a query store hint or the execution plan is forced.
I am reading this from Grant’s Sixth Edition of his SQL Server 2022 Query Performance Tuning and checking against Databases – SQL Server | Microsoft Learn
As I mentioned earlier I am now retired, this happened just after SQL 2022 RTM was released so I have no idea what could happen with the nested views.
Chris
Deepthi Goguri's SQL Server Blog 