Paul Randal (Blog – @PaulRandal) nailed a blog post to the church wall entitled, “What 5 things should SQL Server get rid of?” His choices included auto-shrink, putting databases in full recovery by default, GUIDs as clustered indexes, and more. He tagged me, and I knew right away what I’d say.
5. IntelliSense restrictions on 2000/2005.
Look, the game is up. We know it worked in the early betas of SQL Server 2008’s Management Studio, and you pulled it at the last minute. We have a sneaking hunch you pulled it because you wanted to force users to upgrade their servers from 2000 and 2005 up to the new hotness, but that’s not how it works.
The execution of this feature managed to instill doubt in the quality of SQL Server. Even when it works right, it only works on some of our servers (since they’re not all 2008), so we keep pausing when we expect IntelliSense to kick in. Do I feel lucky? Maybe I should wait another second? Ah, screw it. We end up asking ourselves, “Why isn’t IntelliSense working here? Is it a bug, or is it – oh, wait, I’m just connected to a crippled server.”
And don’t give me the excuse that you didn’t have time to test it fully with the older versions, Microsoft, because it didn’t get any better in SQL 2008 R2. You just gave up implementing and went on to the next shiny thing, which brings me to #4.
4. Any feature described using the phrase “down payment.”
Where I’m from, if you can’t pay for something, you don’t bring it to the cashier. Layaway is for suckers, because the store makes money off the interest your money makes while they’re holding on to it for you. Credit cards aren’t much better, but at least you can give it to the cashier and take the item home with you right there.
Microsoft has been putting a lot of features on layaway lately. They make a down payment, and when the next payment comes due in vNext, gee golly, they left their wallet in their other pants. I’ll talk about this in more detail in my SQL Server 2008 R2 review coming out this week.
Wanna make a down payment? Go ahead – but don’t tell all your friends you just started paying for something on layaway. Mum’s the word. Nobody needs to know you’re living paycheck to paycheck and that you can’t afford those dub spinners just yet. Make your payments, and when you’ve got all four, get ’em mounted and show ’em off. Otherwise, you look like a moron saying, “My truck is gonna look *awesome* next year when I get that Resource Governor paid off – don’t look at it yet, though.”
3. Database Diagrams.
In SSMS, when you drill into a database, the very top thing is “Database Diagrams.” You might think the list is in alphabetical order, but no – look closer. Synonyms comes above Programmability.
Because, you know, THE ONE THING WITH ABSOLUTELY NO FUNCTIONALITY THAT’S NEVER INSTALLED ON ANY SERVER BY DEFAULT SHOULD BE THE VERY TOP OPTION.
Bonus points for putting it right next to Tables, thereby ensuring that I accidentally click on Database Diagrams at least once a week. But those hard-working Microsofties just couldn’t stop there, no. Clicking into Database Diagrams gives you a big, ugly error because nobody ever installs this craptastic feature.
This has annoyed the holy hell out of me for the last five years. It’s not just a layaway feature problem – it’s a simple basic design error. You don’t put rarely used optional stuff at the top of the list. Period. Design 101. Which leads me to the next thing SQL Server should get rid of…
2. Every SSMS UI designer. Send in Donald Trump.
Your server can have blocked transactions, huge rollbacks going on, databases with autoclose/autoshrink enabled, 99% fragmentation, and you’ll never know. SSMS is the worst server administration tool I’ve ever seen when it comes to telling me if my servers are healthy. I have to drill way, way, way down into submenus to discover – oh my goodness – I’ve had hundreds of database corruption errors in the last 24 hours. That sounds awfully critical – SO WHY IS IT BURIED IN LOGS NOBODY EVER READS?
It’s not called SQL Server Development Studio.
It’s called MANAGEMENT Studio.
And everything about this design is wrong – even just start with the basics, the list of things you see when you connect to a server. There’s Databases, Security, Server Objects, Replication, Management, and SQL Server Agent.
Pop quiz – is the Distributed Transaction Coordinator a Server Object or Management? Nope, I never remember either. How about Operators? Those sound like they could be Security or Management – nope, SQL Server Agent. Throw a stick of dynamite in there and start over – and by “there” I don’t mean the code, because it’s too late for that now. Every DBA has been trained to this ridiculous system, but we need to make sure any future tools work better, so the dynamite goes into the cubicles.
1. The shrink option in maintenance plans.
I totally understand that sometimes you need to shrink stuff. I get it. I’ve been there. But you shouldn’t ever automate this to happen every X days. Ever.
After all that ranting, I’m spent. I’m supposed to tag 5 more people, but I’m gonna do something different this time. I’ve heard from bloggers (not just Kevin Kline, either) that they get frustrated when nobody tags ’em, so if this post resonates with you and you’ve got something to add, tag yourself below in the comments.
I need a cigarette. And maybe some Sex Panther.
Update 5/12: David Ramel, a columnist at Redmond Developer News, says this post is full of “rambling, inchoate gripes.” I found out not because I read his blog (I’d never heard of it) but because I’ve got Google Alerts set up. Anytime anyone says the word “inchoate” I want to know about it right away. I wasn’t quite sure how to react, though, because he said there was “bellicose bombast across the blogosphere,” and I wasn’t entirely sure what any of those words meant. I tried asking him a question on his Twitter account, but it looks like he hasn’t been around since about a year ago when he was looking for some jobs training:
Hmmm. I’d leave a comment on his blog, but it’s only had two comments since April, and he hasn’t responded to either of ’em. Hey, maybe he’s taking tips from my blog! Sounds like he’s reading this, so I’ll just leave him this note here. 😉
Just having a little rambling fun, sir…
47 Comments. Leave new
Brent,
Dude, you have to stop repressing yourself. Let your feelings out. What do you really think of SSMS?
I’ve been trying to be more honest about my feelings, but it’s just so difficult for me to take that first step. Someday…
I look at those menus every day, and I still click on 5 things I don’t want before I find the one I want.
Alphabetical organisation for God’s sake!
Database Diagrams: GO AWAY!
*deep breaths*
I was going to sign myself up, but you nailed it. Especially with 5, 3 & 2. (Maybe if I come up with a couple more, I’ll write something up)
You lost me on #4, what are you referring to here? Maybe I missed something…
I hear ya on the others though, especially Database Diagrams which I wrongfully click at least once a week.
Aaron – I’ll have plenty of examples in this week’s R2 review, but I’ll get you started with just one: SQL Server Notification Services.
Here’s my five. Not DBA activities, though. I’m pretty sure noone will agree with me though:
1) ctrl-tab going to most-recently used tab. Why can’t it just go in order? Especially when four tabs are open, navigating between the third and fourth is dreadfully confusing.
2) Deprecating classic join syntax. The ANSI style joins are ugly, confusing, and wrong. Nonetheless they are popular. So be it. But why deprecate the classic join syntax?
3) Disable F4. F4 open a relatively unused window and is most often hit when trying to hit F5. Not only does F4 not close it again (alt-w h is required), F5 within that window does nothing.
4) alt-F1 displaying SYNONYM as just a SYNONYM. If i use alt-F1 on a SYNONYM do i really want it to tell me “gee, that’s a SYNONYM”. Give me information on the underlying object!
5) When ading some neat, new functionality to implement specific methodologies (PIVOT and the like), make them go faster than doing it the traditional way. I don’t want the new functionality just for looks.
1. Agreed.
2. Oooo, I don’t agree there. I love ANSI join syntax because it keeps the WHERE clause focused on search predicates.
3. To avoid this, use control-E instead of F5. Plus it’s closer to your hand anyway. I agree, I never use F5 anymore.
4. Ah, never tried that!
5. HAHAHA, agreed.
2) But why *deprecate* the classic syntax?
3) Alt-e, sheesh, i ought to try that. Thank for the tip!
It seems like a stretch though. That is, pinky on ctrl and index on e. That’s harder than F5, but maybe i can tweak that.
F5, Ctrl-E, Alt-X take your pick!
F5 works great on a split keyboard, it is the last key before the split so it is very easy to hit.
alt-x every time — it executes / refreshes in ALL windows (code AND tables/views) and it’s the shortest path for your hand to… uh… execute.
Intellisense full stop. Sorry MS, fix it or chuck it. Redgate’s got it mostly right, yours sucks.
I turned intellisense off permanently after I noticed I was spending more time correcting the suggestions than I was actually writing the query.
HAHAHA, wait, yours as in Quest’s in Toad for SQL Server? What doesn’t you like about it?
I don’t use Toad. The ‘yours’ was in agreement with the subject of the previous sentence.
AH, gotcha!
Yes, Yes and YES! Brent…these things need to change. I think the stick of dynamite actually went off prior to shipping, that’s how everything got so mismatched and buried in SSMS.
Re #1 – yeah, I just got burned by someone else doing that on my servers! I had to blog about that one yesterday…
In the import/export wizard last window before you click “Finish”, I think it would help if they could make it more easier to read, maybe add coloring for source tables and target tables. Some way where you just take a glance and you know what you are importing.
Brent, great post and good points – well, at least 4 out of 5. I disagree with the removing of the diagrams and here is a post which explains why: http://feodorgeorgiev.com/blog/2010/05/borrowed-post-5-things-sql-server-should-truncate/
I replied on your blog, and here’s the reply for my readers:
Great question – I don’t use Database Diagrams because they’re built inside the database. Like you said, no one should touch the database until they understand the model – but you don’t create the database until you have that information. How do you build a database diagram in a database that doesn’t exist yet?
You should use a data modeling tool that lets you build these relationships out, talk about them as a group, and only THEN do you build the database.
Plus, how do you design new changes to the database after it’s been in production? With SQL’s database diagrams, you have to make changes to the underlying database. Would you agree that’s a bad way to design new changes? You want someone else to look at your work first before you commit it, but you can’t do that when the database itself IS the diagram.
Thanks, Brent, for the tip about the Data Modeler. I will give it a try.
I don’t agree with you about diagrams, especially when you continue to say “Like you said, no one should touch the database until they understand the model – but you don’t create the database until you have that information. How do you build a database diagram in a database that doesn’t exist yet?”
Diagrams are just fine for people who are inheriting someone else’s DB. Your comment assumes the person managing the DB is the same person as the DB’s developer. And most of the time, of course, that’s not the case.
Maybe I’m the only one using Projects and Solutions, but my personal gripe is the inability to create subfolders. Connections/Queries/Miscellaneous is rather useless when 99% of what I have are “Queries” and there are 200 of them in a flattened view!
Oh, and #3 kind of folds into #4. Diagrams might be useful if they had actually finished developing them.
Thanks for the lunchtime chuckle, Brent. And congrats on your MCM status!!
Thanks! I don’t think database diagrams inside the database are ever a good idea, though – it makes production/QA/dev synchronization that much tougher. I would be happy if Microsoft could synchronize the schema first in SSMS, much less diagrams.
I’m going to do something that I’ve enver done before. I’m going to disagree with Gail.
I tried Redgate’s Intellisense when I first switched to SQL 2005. In short, I hated it. I actually thought they were displaying some real audacity in calling it a real product. heopefully, it is better now. If it’s better than the built-in intellisense in SQL 2008, then it must have improved greatly. Hmmm, maybe SQL isn’t the only one giving us things on layaway.
I agree that database diagrams should be booted but for a different reason. I don’t think people understand them and do not realize that if they change something in the diagram, it changes it in the database. I would be okay with them staying if this functionality was removed.
I’ve seen many requests for help where the person claimed that nothing had changed in the database, but ‘x’ stopped working suddenly and they don’t know why. Turns out, they were trying to create a diagram of their database for documentary purposes and didn’t realize that the changes they made in the diagram were made in the database.
Great list. I agree wholeheartedly with Randy regarding Projects and Solutions. I’ve been trying to make use of them for a couple of years now because the concept makes so much sense (not to mention that SSMS tries to force you to save your scripts in the Management Studio Projects folder), but the implementation is just so half-baked awful it makes me want to tear out what little hair I have left.
But wait, there’s more!
1. Many if not most changes to SSMS configurations (like scripting options, fonts, copy/past options, etc.) won’t take affect in current windows. Is this 1998?
2. Ridiculous backward compatibility issues (you touched on this) like not being able to open an 2005 SSIS instance from SSMS 2008.
It has always been my contention that the underlying issue is that too many of the good people on the SQL Server Development Team have a developer’s mindset, not a (systems) DBA’s mindset.
Thanks for a great post.
Thanks Brent!!!! I needed a good laugh… I was LOL’ing throughout the entire post.
-Adam
MS should stop giving option of renaming object through object explorer in SSMS. Renaming any object should only possible through sys procedures
Now what was the syntax for that rename? I can never remember… I’ll just use the UI and then script to clipboard.
On the database diagrams…
Interesting that SSMS provides a filter, but doesn’t allow you to filter out by object type.
Toad allows you to both filter/disable object types and/or actually rename the objects, so you could at least move the diagrams down by calling them ZDiagrams…
Talking of alphabetical arrangement, when you bring up a list of SSIS packages, for example when creating a job, how about listing them alphabetically? Not much to ask, is it?
Amen, who could argue with scraping that Intellinonsense? Just my 2 cents …
* Activity Monitor, pure eye candy. DMVs & some 3rd party tools do a better job, plus you don’t have to resize the heck out of the results to read them.
* Solution Explorer is like going on an excavation for one lousy query. Why can’t folder contents be alphabetized, something Object Explorer got right?
* Standard reports, while a great idea the print feature needs tweaking to present the charts and tables intact.
* Execute right next to Debug button? If I had a nickel for every time I got a user call because they hit debug & couldn’t figure out what happened I’d own shares in Berkshire Hathaway, minus 2 cents.
Have to agree!! My favorite: What’s with the 3 MB/1 MB default size for the data and log files for a new database? Everything’s more than 3 MB these days. At least there’s an easy workaround by changing the size of the model database. But the poor souls who are unaware end up with tremendously fragmented databases.
Hi Brent,
Hey, didn’t mean to cause a fuss with the “inchoate” thing. I was just referring to the “down payment” item, about which you said:
“I’ll talk about this in more detail in my SQL Server 2008 R2 review coming out this week.”
So I said it was “inchoate,” of which the first definition I find is:
“not yet completed or fully developed”
So I just meant you weren’t done yet; there’s more to come, that’s all.
(and come on, you have to give me the “rambling” part!)
I enjoyed your post. Keep it up!
David – thanks, glad you enjoyed it. It certainly didn’t come off that way in your blog post, though.
Hey thats where my picture has been getting all the traffic. the picture of the rusty chevy with the spinner hubcap is my picture.I got a laugh out of the caption. thanks for linking straight to my Flickr account.
You’re welcome! Very funny picture, and thanks for licensing it under Creative Commons. Nice work!
F4 – I cannot stand this one. As one who has to switch keyboards, between standard and split, too often is this irrational and useless functionality enjoined. I know what I wrote, I parse with [crtl] [F5], I execute with [F5]. It takes me more time to remove my hand from the keyboard to cancel the useless properties window and execute the task at hand than it does to scratch my butt.
Ditto with ‘Intellisense’: I type faster than it prompts. The only time it has been useful is at a new client site where I do not fully know the schema.
@Brian: Tab sequence can be changed via [Tools] [Options].
Thanks for your post. : )
Freakin’ awesome post, Brent!
I can spend 40 hours a week on your blog and you never stop impressing me!
Pavel – thanks, glad you liked it!
I have truncated all the tables in a database which is about 250GB in space. But after truncating I have found not a single MB of size being reclaimed. How to get space in disk after truncating the tables in a database?
Allan – SQL Server assumes that you’ll use that space in the database for other objects. You can “reclaim” the disk space by shrinking the database, but that comes with serious gotchas: https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
Is there an another alternative apart from shrinking the database or files? I don’t want to shrink the database! Also, The management here does NOT want to buy any more disk space.
Allan – yes, there’s a really good alternative. There’s never been a better time to put yourself on the market – it’s a great time to look for a job as a database administrator. Find yourself a company that’s willing to get you the resources you need. Come on in, the water’s great!
13 years later… and “Database Diagrams” is still there!
What else has been added to this list over the past 13 years?
Did it get any better?
Great post where some one can just get ideas for “one more swiss knife” app for SQL Server Management )