What Do You Wish SQL Server Was Better At?

Posted on by Erik Darling

Two Words

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.

Mercedes Bends

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.

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!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount 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.

Related Posts

16 thoughts on “What Do You Wish SQL Server Was Better At?”

  4. Actually, the trick to sargability is about invertible predicates. For example, if you use: where cast(SomeDateCol as date) = ‘20190303’, then you’ll notice that you can get a Seek because it makes helper predicates, working out the outer range of possible values and then using that as the Seek Predicate (which is a little more complex because of time zones, resulting in a Range Scan that’s a little wider than it needs to be, but still better than not having it be sargable at all).

    Back in 2010 I had a Connect item on this which got few hundred upvotes, but didn’t get implemented.
    https://web.archive.org/web/20100127084021/https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=526431

    And I talked about it at SQLBits that year too – a presentation where the audience included a bunch of SQL royalty, like Buck, JonK, BrentO, Mladen, JohnQM, CLeo, AndreK, MaceijP, and some others I can’t think of. https://sqlbits.com/Sessions/Event7/Understanding_SARGability_to_make_your_queries_run_faster

  5. Selecting and converting dateformats could be way better.
    I think about the Oracle TO_CHAR(”, format) Syntax.

  6. Better type handling! We should have anchored type definitions and row types, like in Oracle. So much easier to NOT break code by changing a table column type or a table column list if you have anchored types. Easier to get a wide result set from a cursor (if you must) with a single row type variable declaration instead of a variable for every column in the result. Easier coding is good because that means fewer errors and bugs.

  7. Automatic increase of MaxDOP as the query gets more complex. Right now it’s nothing (1) or all (MaxDOP). For some queries 2 CPU’s would be enough, but for other I prefer the full 8 cores available

  8. BEGIN ASYNC

    A list on non-dependent queries that SQL executes in parallel, as resources permit

    END ASYNC

    Consuming queries.

    TRY-CATCH would need some help recognizing an ASYNC Sev-16+, but most things are doable.

    It bugs me we have to write our own wrappers to go outside SQL to go back to SQL to get a bunch of async work done that SQL then consumes – just recognize “the world ain’t serial!”

  9. More of a “feature request” than a wish that something was better, but I wish I could take indexes “offline”, disabling them from use in select queries, but maintaining them when updated or inserted. This would allow me to test how the optimizer would perform without the index, without requiring a rebuild to en-enable, which can take a while.

  10. More intelligent index recommendations – order of key columns based on selectivity, key columns vs. included columns (ex. in ORDER BY), filtered indexes, columnstore indexes, etc.

  12. Better pivoting.
    It comes up so frequently. And the current pivoting solution is just annoying.

Comments are closed.