When I think about things that I see causing people performance pains in SQL Server, Microsoft has been doing a good job of starting to go after them.
If you asked me in 2015 what Microsoft should do, I’d have said…
Well, I posted about it a couple times.
Some of these have snuck into the product, and I think some others might start to make surprise appearances.
If you look at what a lot of people have been blogging, presenting, and getting rich off of over the years, it’s not hard to track down common issues.
- Scalar Functions
- Multi-Statement Table Valued Functions
- Table Variables
- Non-SARGable predicates
- Parameter Sniffing
- Optional parameters
A whole bunch of stuff on that list has been addressed. Scalar functions have been froided, and table variables deferred for 2019, MSTVFs were interleaved in 2017, and to some extent parameter sniffing got a stern talking to via adaptive joins and memory grant feedback. There may not be a good way to address SARGability, but hey, consultants have to make money fixing something.
What’s next? Well, a couple white papers from Microsoft have come out sort of recently that point to parameter sniffing getting some more attention.
- Leveraging Re-costing for Online Optimization of Parameterized Queries with Guarantees
- Plan Stitch: Harnessing the Best of Many Plans
If you don’t feel like reading all that, just imagine SQL Server starting to cache more than one version of a plan, and using different parts from different plans depending on what parameters are passed in. Sort of like a choose your own adventure game for query plans. But hopefully with fewer Discipline Crabs.
Yeah. That’s hard as hell.
I don’t expect to see it in SQL Server next year, but when I see multiple research papers about the same subject, I start to get hopeful. And even when it shows up, you can bet your best drawers it’s gonna be a rocky road to getting it right.
You Can’t Forget
There might be people out there who depend on current behavior. Every fix might be a breaking change for someone. That’s why nearly every feature has a trace flag to turn it off.
There can be cases where the wrong plan is the fastest plan. Heck, plan costing is bizarre. I can’t imagine how hard it would be to tinker with optimizer inner workings. Like, at all. But the way some costs are estimated these days doesn’t make a ton of sense. It’s like if you still estimated how long it would take you to get somewhere based on the average ground speed of a horse. No offense to the equestrians among us, but no one’s riding a horse from NYC to Seattle.
But What About You?
What do you wish SQL Server did better? It doesn’t have to be performance related. Just anything that you run into that makes your day harder.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.