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:
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?
58 Comments. Leave new
From a bugginess and general ick of the code perspective, the feature impacting me on a daily basis is the MERGE statement.
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
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.
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.
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!
Yep, I deal with that hanging all the time.
Not sure if it’s the one you’re referring to, but the SSIS problem for me had to do with the lack of backwards compatibility – had to use a specific VS version, with no indication that anything was wrong except your code just disappeared. That was the nail in the coffin for me and SSIS – never again will I use that junk.
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.
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.
Since the checkbox directly above it (Limit size of job history log) is persisted, I would consider this a bug. Makes no sense that one checkbox is persisted and the other is not when they are doing the same things, only different in that one is based on record count and one is based on date.
Whether bug or bad UI, sounds like M$ acknowledged it needed to be fixed in 2009, then decided they wouldn’t fix it in 2011. Well done, M$.
https://feedback.azure.com/forums/908035-sql-server/suggestions/32894395-sql-server-2008-remove-agent-history-ui-misleadi
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…
Oh, look, this editor ate a string, too! That empty double-quote should say “angle-bracket-open Insert Random Object angle-bracket-close”
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?”.
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. 😀
Jeff Moden FTW
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!
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.
I can literally hear Aaron Bertrand screaming MERGE STATEMENT
SSMS, any version, has always been semi-broken, will never be fixed, per MS: Saved passwords occasionally randomly get cleared out.
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.
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”)
“Not the one you like the least, or the one that requires the most work”
I think there’s maybe one buggy feature in that list
Which one do you call the buggy feature in that list, Michael?
Yes, SSMS forgetting passwords is a PITA!
The Biggest bug of all:
https://feedback.azure.com/forums/908035-sql-server/suggestions/35881492-put-debugger-back-into-ssms-18
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!
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.
Using SSMS to export data to Excel was a major PITA, at times, at my former employer (a Fortune 100 aerospace company). Airplane designations are 5-character text. Examples include ZAxxx, where xxx = a number from 001 and counting upwards (I was a member of the ZA005 flight test team–this was the first 787-8 test airplane with GE engines), RC001 through RCxxx (747), etc. Then, one day, a new designation is assigned for the 737-10 program: 1Exxx. Excel would always “helpfully” convert 1E001 to 10, 1E002 to 100, 1E003 to 1000, etc! Somehow the folks at Quest did not have the same problem, when exporting these airplane designations to Excel using TOAD for SQL Server. They exported from the Quest product as text formatted in Excel, exactly as they should have. But SSMS? Nope, nada, ???!
Apparently Brent’s blog site does not allow unicode characters. I tried to get cute by ending with “Nope, nada, and the Russian translation of the word “no” (it’s my wife’s native language, so I hear it a lot!). It shows up as three question marks. Just FYI.
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
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.
Yeah. That’s annoying.
That is a Silly one
I have when I close SSMS and all the connections are gone!
I–H-A-T-E–I-T
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.
I really tried ADS a few times – I love the idea, but once in awhile it would just hang for no reason, or I’d lose a connection with no way to reconnect without closing and reopening the app – or Intellisense would just stop working for no reason.
Maybe I’ll try again in a year or two, but I gave up for now.
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.
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.
need to prove read too. LOL how do I delete my post or edit it.
Bug in this forum, cant dead or edit post.
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. 😉
Just as a point of reference, I have fairly modern computers and no SSMS addons, and I have similar issues.
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
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.
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.
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.
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.”
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)
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.
I know the live query stats stuff is buggy…sometimes works…and has the limitations you mention, but it is still one of the most useful features added to SQL Server in a long time, for me. Use it every day…as long as you don’t jump to errant conclusions based on the misleading stuff (you’re seeing an actual plan in flight overlaying an estimated one, naturally) it’s useful for so many things!! Definitely imperfect but super useful. Side note…I’ve found a lot of times when you kick off a query with LQS displayed, and the session refuses to show you the in flight plan…you can kick open another window, run sp_WhoIsActive, look at the plan it pulls, and you will see the “in flight” plan there…albeit a static version of it. So you can get an idea what it is doing with repeated pulls of the plan even if the animated version doesn’t work.
I have to think that because it is such a useful tool, it will get better over time…compared to say, DB Diagrams that nobody uses so it barely changes…hopefully lots of feedback going to MS to fix issues with LQP. But its a cold dead hands feature for me, love it. For your purposes though…when you’ve got hundreds of people looking over your shoulder…I can totally understand the frustration…you need things to be clear and uncomplicated as you demo them and the inconsistency and goofiness of some of the flaws would be understandably irritating when you’re leading a class!
OpenRowSet …. sounds like just what you’re after for importing that spreadsheet … except it guesses the datatype by looking at first 8 rows and often gets it wrong
But you can tell it to not guess by telling it to look at all rows rather than guess … by changing the registry (why have the setting in the provider settings where it’s easy to change … more sensible in the registry!)
But then you find the value of “0” for the guesses to tell it not to guess is a lie … it actually means the first 16,384 rows ….
.. shakes fist …
SSMS intermittently crashes when clicking on a column header dropdown arrow when viewing processes in Activity Monitor. This has been around for a looong time.
Has this feature improved much from SSMS 18 to SSMS 19?
Feel free to try it to see!
Heh… the question is “What’s the Buggiest Feature in SQL Server”?
My current answer is “SQL Server 2022”.
ZING
Just to mention the reason for my seemingly off the cuff disgust, it has slowed down every process that I’ve compared so far and even the dog has tried twisting the knobs. It’s even on more modern hardware than it was previously. We’ve tried it with the new stuff enabled and disabled to try to make it look like 2016 (which is what we migrated from). We changed to the 2016 compatibility level, which kept our code from bleeding out instantly but is still totally unacceptable. Our overnight jobs now take way longer than the original times they did and now bleed over into the normal work hours.
Even the simple code that I created for a “simple” test, which uses zero reads and virtually zero display time, takes substantially longer and uses substantially more CPU time on 2022 than it does on 2016 or 2017.
Yeah, I hit that earlier this year with SQL Server 2019 with a client, and I couldn’t believe it: https://www.brentozar.com/archive/2023/03/is-sql-server-2019-more-cpu-intensive-than-sql-server-2016/
After extensive testing across multiple cloud providers and CPUs (because I was sure someone was gonna point out an obvious mistake on my part), I went public with it, and the comments chimed in that it was reproducible on their end, too.
I didn’t see that article before. Thanks for the link, Brent.
I actually have some much simpler code that doesn’t have anything with the disks, etc, etc. It’s pure CPU. Between 2017 and 2022 (and, yeah, I’m one of those that do make things identical… even being on the same laptop), 2022 used 30% more CPU and took 18% longer.
Here’s the code… Like I said, CPU and Memory only (hopefully, some of the space indenting will be preserved).
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS ;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ;
CHECKPOINT;
WAITFOR DELAY ’00:00:05′; –Just to let things “cool down” from the Checkpoint, etc.
GO
DECLARE @BitBucket BIGINT
;
SET STATISTICS TIME,IO ON;
WITH
H1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1))H0(N)) –Up to 16 rows
,Tally(N) AS (
SELECT TOP(100000000) –Change this number
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM H1 a,H1 b,H1 c,H1 d,H1 e,H1 f,H1 g,H1 h –1 to 16^8 or 4,294,967,296 rows max
)
SELECT @BitBucket = N FROM Tally
OPTION (MAXDOP 4); –Change the MAXDOP as you see fit. This is the default on my server and laptop.
SET STATISTICS TIME,IO OFF;
GO 5