When Microsoft started to talk about Intelligent Query Processing (IQP) before SQL Server 2017, I wasn't sure what to think. There was a diagram with 20 things on it, and only 5 were highlighted (you can see that in the article linked). That felt like something, but barely something. Over time, as we've gone through SQL Server 2019 and now 2022, the diagram now looks more filled in. In fact, now there are a lot of things filled in.
When I think about new features of SQL Server, I don't often spend a lot of time on the IQP stuff. First, I don't have to live with people yelling at me every day about slow performance. If I did, I might be more anxious to test and evaluate the changes. Second, these are mostly just hidden things that come with the product and (fingers crossed) improve my workload.
There's an article on the 2022 changes, which improve upon some of the older IQP work as well as add some new items. It seems the feedback loops are getting better, though not perfect. I'm sure all the performance tuning experts will write posts about where these things fail, which is good. We want to know where we might have problems. Hopefully, we'll also see lots of demo code that shows where these things work.
One of the more interesting things is the Parameter Sensitive Plan Optimization. This should help with parameter sniffing issues, though it will be interesting to see what percentage of a variable workload is improved by this. I also wonder if loaded systems that struggle here might have other plan issues. I already know some people have an overloaded plan cache now. If they could have even more plans for each type of query, there might be other issues to contend with.
For the most part, SQL Server works well for a wide variety of situations. Many people seem to be quite pleased with how well it performs. I don't know if that means customers look forward to upgrades or are happy where they are, but I am glad that the product doesn't just add developer features or complex technology for administrators to learn. Getting investments in better query performance is important, especially as it can be hard to get developers to write better code on their own.
