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

44 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
  • Randy in Marin
    August 20, 2021 8:43 pm

    I’m short sighted, so my perspective is SSIS at this moment. Every few years I need to create an SSIS package. Every few years I’m shocked that I run into the same old annoying issues. Metadata can be a pain to refresh, requiring the recreation of a task or manual mapping of fields. Changing the table name to a table without any of the columns of interest and back again? Really? How is possible that the engineers of a SQL Server product didn’t know how to properly generate a csv file? How is possible they have not yet added an option (e.g., EnableRadicalCsv) to allow proper escaping of quotes? Has anybody else noticed the user-friendly expression builder? It reminds of something a friend says about me…the expression builder is not totally worthless; it can always serve as a bad example.

    Most likely, I’ll be retired by the time I must use SSIS again. I won’t be around to see the new and improved SSIS replacement. (I also support our TFS system. I waited about 10 years for a very popular feature request to allow project migration. The solution is to stop using TFVC and convert to Git.)

    I do like SQL Server. I think I’m getting to old for this and a few other things. I should not be so cynical.

    Reply
    • Randy in Marin
      August 20, 2021 8:54 pm

      I forgot to mention that my VS2019 suggests that I disable the SSIS extension because it stops responding or crashes too much. Also, I noticed that I have to kill a dts debug process once in a while before my SSIS project will load.

      Reply
      • Randy in Marin
        August 20, 2021 8:58 pm

        I will become less cynical as the years go by because I won’t remember what there is to be cynical about. With age comes wisdom.

        Reply
        • Heh… thank goodness that with age, I can forget and actually get away with it. 😉 It’s like hearing… One of my favorite shirts is printed with “Old folks aren’t hard of hearing… We’re just tired of listening.”

          Reply
  • Strange – nobody mentioned DBCC SHRINKFILE / SHRINKDATABASE until now.

    Yes, it works, but it moves always the last page to the first free position which reverse the order of an index and leads to almost 100 % fragmentation of the effected tables, so you would have to do an index rebuild which will blow up your database again (if you do not use the sort_in_tempdb option)

    Reply
    • On that note, let’s talk about the horrors known as REORGANIZE and REBUILD and at that to the horrors of either of the SHRINKs. REORGANIZE perpetuates page splits by removing free space and REBUILD creates a copy of the index before it drops the old one if it’s more than 128 Extents (only 8MB there, folks).

      Both REORGANIZE and REBUILD (and the SHRINKs, of course) are serious “WTFs” !!! Has no one heard of Peter Norton??? Lordy! REORGANIZE is so bad that it’s actually the leading cause of fragmentation, especially in evenly distributed indexes such as those created by Random GUIDs, which are actually the very epitome of how people think that non-ever-increasing indexes should operate as! IMHO, if you’re using REORGANIZE anywhere in your index maintenance plans, there’s a 99+% chance that you’re causing more damage than you’re fixing.

      And, yeah… I have the proof of all that. I demonstrate that you can insert 100 THOUSAND rows per day into a Random GUID Clustered Index for 58 days with less than 1% fragmentation in the following presentation.

      https://www.youtube.com/watch?v=qfQtY17bPQ4

      Another horror is the serious mistake that MS made when they made the MAX and XML datatypes default to “In-Row” instead of defaulting to “Out-of-Row” like the old LOB datatypes did. And, yeah… I have a presentation on that, as well. (As a bit of well-meaning self-promotion, I’m doing that presentation at 2PM EDT on Thursday of the PASS Summit).

      And then there are the performance issues with the FORMAT function and things like scalar functions (especially in Computed Columns, which Brent has a fantastic article on and, yeah… I verified his findings to be painfully true!)

      All of these things are “hidden horrors” but, heh… don’t worry… SQL Server 2022 will pick the best execution plan to make parameter sniffing a thing of the past, RIGHT?

      Lordy. The real issue isn’t that there are one or two “buggiest features”. The real issue is how many such “features” have existed for sometimes decades and nothing has been done about them. Yep… I appreciate the technical complexity of the cardinality estimator and all the AI that goes into making things like execution plans but it seems to me that the features we’re talking about should never have had such “bugs” to begin with and nothing in more than 2 decades has been done to fix them. And, to think that some have actually made the use of some of these a “Best Practice” just absolutely floors me, especially since I was also one of those suckered into drinking the Purple Kool-Aid like most of the rest of the world.

      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