What’s the Buggiest Feature in SQL Server?

Not the one you like the least, or the one that requires the most work.

What’s the one that you can’t use because it’s just flat out busted?

For me, it’s SSMS’s Live Query Statistics, aka live query plans. The idea is so awesome – you click Query, Include Live Query Statistics, and then when you run queries, you get this awesome animated execution plan:

Live query plan animation

Click to see full resolution

This is really useful when I’m troubleshooting long-running queries or procs, especially cases when I can’t wait long enough to get the full actual query plan, but I need to know where we’re hung up, which parts are taking the longest.

Except…

Except the live plan only shows the first statement in a batch. In the movie above, notice how I’ve got two queries in my batch. I hit execute, but then only the live query plan for the first query – the Users scan – shows up. Then the live query plan stops updating until the entire batch finishes. If you want to see the live plan for subsequent statements, you have to go into Activity Monitor – which you shouldn’t be using – and then go into active expensive queries. You can briefly see the live plan for whatever one statement happens to be running, but as the next statement starts, you have to jump through more hoops to see the next statement.

Except the percentages are misleading. The live plans show percentages on them, which makes it seem like it’s the percent complete for an operation, but it’s not. It’s the percentage of rows SQL Server found in various stages of the plan, compared to its original estimates for that operator. People see 1%, and they think that means it’s 1% done – but that part of the operation might actually be fully complete, but it just brought back less rows than SQL Server expected. Or they’ll see 100%,  but the operator is still working – that’s just because SQL Server found some data, but it’s still churning away on that operator. I could go on and on.

Except the final results are wildly misleading. In the movie above, notice how the final plan shows three statements, not one: it shows the Users operation twice for some reason.

Except often, it doesn’t even work at all. Even if it’s the perfect use case – a single-statement query that takes forever – when I demo the feature, I have to explain, “Now what I’m about to do may not even work at all, so forgive me if I curse and then stop the query and then start it again, or jump over to Activity Monitor.” It doesn’t feel like I can predict success of it at all.

I really want to love Live Query Statistics, except…

What about you? What feature drives you crazy due to the bugs?

Previous Post
If You Could Only Say One Thing About Writing Good Queries…
Next Post
Free Video: Deadlocks: Let’s Do One, Understand It, and Fix It

38 Comments. Leave new

  • Peter Shore
    May 6, 2021 1:24 pm

    From a bugginess and general ick of the code perspective, the feature impacting me on a daily basis is the MERGE statement.

    Reply
  • Database Diagrams. Probably the most unused feature as well. Every time I make one or try to I get an out bounds exception but restart SSMS and it works.
    I use them for sanity check that I did all my FKs

    Reply
  • Bill Rivers
    May 6, 2021 4:05 pm

    sp_rename

    It just has so many caveats, exceptions etc that you end up not using it and doing lots of boring work to achieve the same affect safely. You even see people leaving column names unchanged in tables and using views to give the appearance that a column was renamed.

    Reply
  • SSMS (18.x) bugs drive me crazy; every day it will crash, hang or give my favorite message about SSMS being busy, we’ll let Microsoft know. If you have a query window open that was connected to a server that is now offline, just closing the window will try and try to reconnect even though I just want to close the tab.

    Reply
    • Simon Yeats
      May 8, 2021 11:06 pm

      Agree. SSMS hangs when I forget to disconnect in server explorer before dropping my VPN connection to that SQL server, uff.
      Also, what’s with the known bug (+4 years at least?) where it causes loss of scripting tasks in SSIS packages? Seriously? Check the latest release notes.. It’s still there!

      Reply
  • camaro322hp
    May 6, 2021 4:23 pm

    SQL Agent Properties – History – Remove agent history older than: [time period]

    Checking this checkbox does not stick. It’s been broken since at least 2005. Sure, there are workarounds and it’s really only an issue when building a new server. But to have a bug like this not fixed for so long is irritating.

    Reply
    • I would call that one bad UI rather than a bug. If you go through that workflow and script it out (instead of hitting “Ok”), it shows you that it’s calling sp_purge_jobhistory with an explicit purge date. To my knowledge, there’s no Agent setting for persisting that and the solution is to set up a job to keep it trimmed. The UI has led you believe that it should work though and so it seems like a bug.

      Reply
  • Anyone using the Query Editor built into the Azure Portal?

    That implementation of “Intellisense” should be called “” or just “Nonsense”

    I don’t know about you, but I type ‘from’ in *every* query, but when I hit TAB after it my ‘from’ becomes some bracketed object name.

    Sure, MS branding says that Query Editor is in “Preview” but that’s a poor excuse. Turn off Intellisense until you get it to the point it doesn’t eat *keywords* and poop object names…

    Reply
    • Oh, look, this editor ate a string, too! That empty double-quote should say “angle-bracket-open Insert Random Object angle-bracket-close”

      Reply
  • Richard Armstrong-Finnerty
    May 6, 2021 4:57 pm

    General clunkiness.

    e.g. the Extended Events interface, the Resource Governor interface, Check for Updates not doing anything more than direct one to a web page, Query Store – good, but lacks that extra dollop of insight (such as “why did this plan appear after I forced another?”.

    Reply
  • Jeff Moden
    May 6, 2021 4:59 pm

    Heh… I’m reading through this and other similar posts and remembering many of my own complaints and I’ve realized that no one has actually hit the nail on the head for what the “Buggiest Feature” in SQL Server is…

    The buggiest feature is actually the Microsoft Design, Development, Test, and Release methodology. There are some great exceptions but I know of few companies that could introduce so many partially complete features and break so much stuff that’s already been released (SSMS has been totally snafued IMHO, especailly since version 17 hit the streets and progressively got worse) and actually get away with it simply because we’re a captive audience. The CU process has become a joke starting with (especially) SQL Server 2012. Heh… and then there’s the crippling of T-SQL in Azure. Yeah… really “cloudy” stuff there. More like Smoke’n’Mirrors. 😀

    Reply
  • SSMS! Why?
    1) Registered Servers has issues, it doesn’t let you specify color if the registered servers are from a server and not local. Want query windows with red status bar for production, not if your team wants to be a team and share these registered servers!
    2) Somewhere between 50 and 200 connections made using registered servers causes SSMS to crash.
    3) That causes me to open a separate SSMS iteration to keep the rest of my stuff safe. This in turn seams to corrupt the SSMS preferences when one of the SSMS iterations crashes.
    4) Just give a developer a few hours to get Object Explorer, Registered Servers, Query Results (grid and text), properties dialog, and Object Explorer submenus DARK THEME using the partly done dark theme commented out in the SSMS settings!

    Reply
    • Agree with that, ran into this recently. Registered Servers using SQLPS is buggy and feels like a half baked implementation, we have also gone down the route of local lists for the custom colours which is far from ideal for a DBA team supporting the same set of servers.

      Reply
  • Shaun Austin
    May 6, 2021 8:28 pm

    I can literally hear Aaron Bertrand screaming MERGE STATEMENT

    Reply
  • SSMS, any version, has always been semi-broken, will never be fixed, per MS: Saved passwords occasionally randomly get cleared out.

    Reply
  • What about when you examine locking and blocking? It does not necessarily show you the query that is doing the blocking. May not technically be a bug, but misleading information provide by SQL Server.

    Reply
  • George Walkey
    May 7, 2021 12:55 pm

    Azure:
    Is ALWAYS more expensive than an on-prem server except for edge-cases
    I/O sucks

    Product in general:
    Introducing new features while leaving daily meat-and-potatoes features buggy

    SSMS:
    18.9.1 STILL forgetting passwords after “its been fixed” 9 times…sad trombone

    SQL:
    In-Memory OLTP not giving much perf boost (see HammerDB)
    Columnstore actually being slower than Rowstore in most cases (Synapse all-in on CStore)

    MSDB:
    SQL Agent Job Step History stored in A SINGLE VARCHAR(MAX) field

    Plus what Jeff Moden Says:
    low quality Code from MS (“but we’re saving money”)

    Reply
  • George Walkey
    May 7, 2021 12:57 pm
    Reply
  • Lori Halsey
    May 7, 2021 4:07 pm

    SSIS and Excel compatibility. They are both MS products, you would think they could play well together. The hoops you have to jump thru, the gods you have to pray to…and the looks of confusion from users when you tell them to just use .csv files. Gah!

    Reply
    • Preach it Lori. I didnt even think about that because I swore off Excel Data import. Every time I get an Excel file to import I just open it and save it as CSV by habit now.

      Reply
  • Randy Pitkin
    May 7, 2021 7:32 pm

    There are tricks for improving I/O in Cloud Paas instances , they are quite different for AWS than Azure.
    IDK about IBM or Google
    I use SQLCMD option a lot and the behavior appears to have changed recently. you need a GO to actually Go and this caused me to kill the wrong bunch of spids.
    :connect Server1
    select @@servername
    — GO
    :connect Server2
    select @@servername
    — GO

    Reply
  • Robin Wilson
    May 9, 2021 9:16 pm

    For me one I hit from time to time and wonder why no-one ever fixes it is where you try to copy the value from the find popup (Ctrl+F) and it says “Unsaved documents cannot be cut or copied to the clipboard from the Miscellaneous Files project. You must save the unsaved document(s) before cutting or copying them.”
    It has been in there for many years and seems to make no sense.

    Reply
  • That is a Silly one

    Reply
  • I have when I close SSMS and all the connections are gone!
    I–H-A-T-E–I-T

    Reply
  • Randy Knight
    May 14, 2021 3:17 am

    Agree with the many comments on SSMS. Bloated, buggy, takes forever to load. And don’t get me started on the removal of the debugger. The above is the #1 argument for Azure Data Studio. I just wish it had more feature parity with SSMS. I’ll be cruising along in ADS and go to do something and realize I can’t and have to fire up SSMS. I mean, start SSMS, go get a coffee, walk the dog, grab some lunch, then when I get back to the desk it might be fully loaded.

    Reply
  • What Addon do you have on your SSMS? Or how old and under CPU powered is your computer?
    Plain SSMS no extra tools does not load slow for me. it is not until I add on those xtra 3rd party add on to get add features that is slows down (this is why I leave it running all the time).
    That would be another bug I guess. MS could work more with the 3rd party tools and get them to work better and faster with SSMS.

    Reply
    • This wa sto Randy Knight about it being slow to load. I not had coffee and I am slow to start in the morning and didn’t reply to that post. I need more CPU and am getting old and need a new model.

      Reply
      • need to prove read too. LOL how do I delete my post or edit it.
        Bug in this forum, cant dead or edit post.

        Reply
        • This is a blog, not a forum, and your inability to edit the things you say here is a feature, not a bug.

          Do yourself a favor: step away from the keyboard, go get some fresh air, and come back later. 😉

          Reply
    • Just as a point of reference, I have fairly modern computers and no SSMS addons, and I have similar issues.

      Reply
      • Maybe I am just so use to how slow it is to start up and since I leave it open all the time it doesn’t bother me. I do know addons make it slower and connecting to a SQL Server on a VPN is oh my frustrating. I notice a difference when I work in Office and don’t have to use the VPN.

        oh and Good Morning Brent

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu