THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Five Things To Which SQL Server Should Say "Goodbye and Good Riddance"

I was tagged by master blogger Aaron Bertrand and asked to identify five things that should be removed from SQL Server. Easy enough, or so I thought...

 

1) Tempdb. But I should qualify that a bit. Tempdb is absolutely necessary for SQL Server to properly function, but in its current state is easily the number one bottleneck in the majority of SQL Server instances. Many other DBMS vendors abandoned the "monolithic, instance-scoped temporary data space" years ago, yet SQL Server soldiers on, putting more and more "stuff" into tempdb with every new version. Some form of global tempdb may be required for temp tables and other database-agnostic features, but for features like the version store, index rebuilds, and DBCC, we should have a lot more control in order to build the highest degree of performance and reliability into our database solutions.

Tempdb should be taken out back, shot a few times, and rebirthed as a smaller, leaner global store for only very specific types of data. Everything else should go into specially-marked, database-scoped filegroups so that DBAs can have finely-grained control over how and why the space is used.

 

2) T-SQL Scalar UDFs. Scalar UDFs suck. There. I said it. And I'll say it again. Did you know that scalar UDFs suck? I was just reminded of this fact by a webcast done by Simon Sabin, but I've been saying it for years. Scalar UDFs, at least of the T-SQL variety, are painfully slow. And both varieties inhibit parallelism. Since their SQLCLR counterparts aren't quite as painfully slow I'll leave them out of this rant, but the T-SQL versions should be removed from the product. Sure, they seem to be great during development, but as soon as the application needs to scale they become a nightmare. Such timebombs should not exist. SQL Server team, please defuse this situation and replace the current UDFs with inline scalars.

 

3) User-Defined Types (again, of the T-SQL variation). User-defined "alias" types were never exactly a panacea. Oh, joy. Instead of "VARCHAR(36)" I can refer to "AddressLine". But at least, prior to SQL Server 2005, they could be bound to "rules" in certain cases. This, in and of itself, was wrought with problems--I won't bore you with the details--but then the "rules" were deprecated. So now we're left with these aliases, and it feels much like the proverbial hammer waiting for a nail to show up. Every once in a while I'll run into a database where some well-meaning developer discovered alias types and went crazy. And then--always after it was too late--discovered the numerous problems they bring to the table (pun absolutely intended). SQL Server should toss these out with the rest of the garbage and bring in what should have been implemented to begin with: ANSI domains.

 

4) SQL Server Management Studio. I'm going to have to agree with my friend Buck Woody on this one. SQL Server Management Studio should never have been created. If I told you that I was going to take Microsoft Word, cripple it until it became a weak text-editor lacking even spell check support, and sell it as part of some other product, you would tell me I was insane. Well, that's exactly what Management Studio is. Someone decided that DBAs don't like working in Visual Studio, so the team created a dumbed-down version of the shell, popped in a few extensions for various SQL Server features, and there you had Management Studio. The only problem? No one asked a real DBA what they thought, and since all of the BI features are in Visual Studio every DBA I talk to already has it installed anyway, in the form of BIDS. Management Studio should be ripped out of Management Studio and brought back to where it makes sense: As a Visual Studio plugin. This would instantly simplify the lives of DBAs everywhere by reducing the number of environments they need to work with, while automatically enabling a number of features, such as proper support for plugins. A true win-win.

 

5) Database Diagrams. I actually had trouble finding five things to put onto this list. I'm generally pretty happy with SQL Server's features. So I took a look around and this is the best thing I could find. Honestly, my overall opinion of database diagrams is kind of "meh." The feature has never been great. I don't use it, favoring various third-party products or even Visio when I'm desperate. I don't know why this feature was never enhanced to become a full ER tool, but in its current state it's a joke. Might as well just give it up.

 

And that's that. Enjoy.

Published Wednesday, May 19, 2010 9:16 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

BuckWoody said:

I totally missed the DB Designer. You are SO right - it's so bad I didn't even remember to diss it.

May 19, 2010 8:20 PM
 

Michael K. Campbell said:

Excellent. Your number one mention/choice is the same as mine. DB-scoped tempdb's would make sooo much more sense, would provide greater control, and would help overcome some of the FUD and just plain confusion surrounding the tempdb.

I also think SSMS sucks - from anything other than a developer perspective and don't actually wish that Scalar UDFs would be  destroyed - just fixed.

Great list.  

May 19, 2010 10:12 PM
 

Kent Tegels said:

Heh, Right on Adam! For once we agree on something more or less completely. But I would possibly replace the diagrammer option with the insane velocity of upgrades that MS has exhibited since SQL 2005. Seriously... did we really need 2008 and 2008 R2?

May 20, 2010 6:39 AM
 

Wes W. said:

@Kent, depends on your use, but we're heavy in BI and SSAS, so SQL Server 2008 is *MUCH* better than '05 for our use.  If your just an OLTP shop, I can understand your comment.

As for R2, the PowerPivot and related BI features alone might be worth it for those OLAP-oriented folks.

May 20, 2010 8:43 AM
 

Kent Tegels said:

Yes, I agree... the BI stack in 2008 is years ahead of 2005. But that begs the question: would deferring those until 2008 R2 have been worth it?

May 20, 2010 8:54 AM
 

Wes W. said:

@Kent, there's no way to know for sure.  If they had, I might not be here and would be struggling with an outdated Business Objects platform.

May 20, 2010 9:20 AM
 

James Luetkehoelter said:

Wow, couldn't agree more on all points Adam. Especially the TempDB one - and you really presented that nicely in your presentation of all of the items that get thrown in there (I still run into people that think its just for sorting and explicit temp tables!).

May 20, 2010 9:42 AM
 

Paul White said:

T-SQL scalar UDFs, yes.

T-SQL scalar UDFs that do data access, double yes.

May 21, 2010 9:08 AM
 

Adam Machanic said:

Michael: Interesting that you dislike SSMS for anything other than developer stuff. According to some people I've talked to @MS, the tool was only created to appease DBAs who don't want to use Visual Studio. Interesting how wrong they got it, eh?

Kent: Nice to know that we occasionally think alike! I find your suggestion with regard to the release schedule to be a bit ironic. Perhaps you weren't among them, but I recall a huge number of people whining in 2004 that there hadn't been an upgrade for FOUR WHOLE YEARS. And now in 2010 people are whining because there are TOO MANY UPGRADES! I think from a Microsoft perspective they're damned if they do and damned if they don't on this one. I personally think that ~3 years for major releases is a good balance.

Jams and Paul, thanks for agreeing :-)

May 21, 2010 9:28 AM
 

Dan Waters said:

About SSMS:

Some people uses SQL Server, but don't have Visual Studio, nor do that have the money to buy it - just the professional version is over $500!

You have a platform in this blog - please keep everyone in mind.

May 22, 2010 10:04 AM
 

Adam Machanic said:

Dan, a version of Visual Studio ships with SQL Server. It's called BIDS -- Business Intelligence Developer Studio. That's where SSMS should be, in my opinion. I absolutely agree that it should ship with the product. Just not in its current form.

May 22, 2010 10:17 AM
 

Nitin said:

Vooh.... I love SSMS. BIDS is not installed by IT in our company for all SQL developers my friend, and so SSMS is the only thing we get as a start.

So I completely support SSMS and negate the notion.

May 24, 2010 6:47 AM
 

Adam Machanic said:

Nitin: Might it be safe to assume that were there no SSMS your IT department would be forced to change its policy on what to install by default?

May 24, 2010 10:08 AM
 

Anyony Mouse said:

I agree with you on everthing but SSMS and DB Diagrams. I can imagine that for a professional DBA the tools just aren't as functional as you would like them, but I think the reality is that many, if not most web developers (except .NET) are using SSMS as their "application" for SQL.

Now, has SSMS made it too easy for people to create a db and not care about anything else? Yes, certainly, but the rapid protyping is worth it alone.

DB diagrams: I think your perspective is coming from an expert in dbs who doesn't need much of a visual reference (or other) for understand/creating a database. For others it is essential and often required that they demonstrate this. Using other tools is great, but that means you have to pay for them.

And, of course, there's the MS equation. Unless you're invested into their development tools and .net architecture they generally don't provide perfect tools outside of that as they don't want to upset their partners. They're better off having a symbiotic relationship with third party tool developers.

May 24, 2010 10:51 AM
 

Adam Machanic said:

Anonymous: What difference would it make to you if you're using SSMS inside of a shell called "SSMS" or if you're using SSMS inside of a shell called "Visual Studio"? Assume that the functionality is identical; it's just a slightly different environment in which it's hosted. A more powerful, more flexible, faster environment. I see no downsides to that.

I understand your points regarding the database diagram functionality, but Microsoft already has a better tool for that: Visio. Why not ship a stripped, database-specific version of that product with SQL Server, rather than re-inventing the wheel and including a feature that's really not up to the challenge?

It seems as though there's a lot of Not-Invented-Here attitude in the various Microsoft groups and I hope that at some point the company will learn to become a bit more cohesive. This could result in a lot more greatness and and lot less mediocrity.

May 24, 2010 11:01 AM
 

Anyony Mouse said:

@Adam

We typically don't install any more tools on our workstations that needed. We've got Eclipse and SQL (MS/MySQL/etc), etc.

As for Visio, until recently you had to pay out the nose for the ability to do this (Pro used to have this, then lost it, then gained it again).

It's not an attitude that I think is unfair to suggest, but more of a cost/efficieny issue. We would love to have all the perfect tools and pay for RedGate licenses for everyone but the fact is that it's just too expensive for small businesses to maintain. Let's look at a list of software for typical web developers:

SQL 2005 Developer Edition

SQL 2010 Dev Ed

VS 2010

Photoshop

Illustrator

Cold Fusion

Office Pro

Visio

Misc. tools

etc

Generally, licenses for these are NOT one time. Every year we're paying for the next iteration. That's a LOT of cash going out the door.

Do we NEED Visio? Yes, for wireframes and other purposes. Do we need or want to have another file associated with the development of a site? No. If we can do the db diag in SQL and it is sufficient that's where we will do it because, in the end, the client is paying for the time it takes.

What we've found is not perfect, but is better than good enough.

May 24, 2010 11:35 AM
 

Euan Garden said:

Interesting comment re SSMS as it was the MVPs and a bunch of "Real DBAs" on the beta program that killed SQL Workbench which was a solution/file based VS approach to managing SQL as opposed to the connected SSMS approach.

June 2, 2010 12:52 AM
 

Adam Machanic said:

Hi Euan,

Thanks for the insightful comment. I wasn't an MVP at the time so I don't know what happened there, but I will say that "connected" is good. A second environment to learn is not so good. Why can't (couldn't) SSMS be hosted in the "standard" VS shell, just like the BIDS components? (And how many acronyms can I fit into one sentence?) Talking to the manageability team, I was told that this was because "DBAs don't like VS." Well, the DBAs I work with all have BIDs installed and most of them are relatively proficient SSIS users. So they're already using VS. Why should they need a second environment?

June 2, 2010 10:08 AM
 

Euan Garden said:

I would argue that DBAs that are are comfortable in VS are the minority, they certainly were in 2003 when this decision was being made.

Its VERY hard to provide a connected experience in VS, BIDS is a disconnected experience which is why we put it in VS, it works well with files etc.

Oh and the preferred choice for most DBAs when SSMS was created out of the ashes of SQLWB was to go back to MMC, which I think is what Buck proposes.

Having spent the last 4 years working with the VS field the BIDS approach is pretty unloved as well primarily because it almost always means having to run 2 versions of VS side by side.

Maybe its time to revisit, the reality is the majority of the original tech/ideas/concepts in SSMS are 9+ years old at this point, times have changed pretty dramatically.

June 2, 2010 2:00 PM
 

Adam Machanic said:

Hi Euan,

I think your final sentence sums it up beautifully. Decisions were made for some reason or another and the realities of our industry have changed in the interim, as they tend to do.

I actually like SSMS quite a bit (and I'm not sure I'm in the majority there). I just don't like having to flip back and forth between environments all day long...

June 3, 2010 10:25 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement