The Single Greatest Feature of Microsoft SQL Server

SQL 2014 RTM was just announced! I started working with SQL Server at version 7.0.  It was upgraded to 2000 a few months later so I don’t have tons of experience with 7.0 but some.  I also have a minor amount of experience with the Sybase product SQL Server was ported over from originally.

The product from then until now has changed dramatically – the behaviors, features, improvements – nothing short of extraordinary.  I fully recognize that I am biased but SQL Server to me is the premiere enterprise level database platform of the industry.  There are things that could work better – sure – I’m excited to see these improvements, but to quote Nacho from Nacho Libre.  The modern product, “It’s pretty dang fantastic”.

Making use of some of the modern niceties of SQL Server earlier today I started wondering what the single greatest improvement to SQL Server from its humble introduction until now has been?  A question that would have 100 different answers by 100 different users no doubt.

If I had to choose mine. I know what I’d pick.

Recently I was working on a project.  One of our major databases had bloated considerably over the years.  A ton of one-off tables, temporary work tables, tables made for potential roll-back needs etc.  The problem however is that no one has ever gone back and cleaned up after themselves – this database server used to be the wild wild west pard’.

After the days of cowboys and Indians, a little before my time, this database was declared business critical, made highly available, began actively shipping to DR.  Today it’s expected to meet five nines, so no one wants to go back and do what should have been done years ago.  Drop all the crap.  Who knows what is secretly doing critical work or has a key presence in an unknown trigger or view?  Rough estimates led us to believe that close to a full TB of fluff was sitting out there doing nothing.  Junk we’re mirroring, monitoring, shipping to DR, etc etc etc.  Pointless.

I grew up on a ranch, so I’ll be your huckleberry.  Imma gunna start dropping things that don’t look important and if someone complains we’ll know different.  Well ok, maybe I’m not THAT cowboy.

What I did instead?  I asked sys.dm_db_index_usage_stats what kind of activity the suspect tables had seen recently.  Turns out that from our list of 120 drop candidates, only two had been touched since patching six weeks ago (last time the service was restarted).  The rest?  They just took one step closer to hitchin’ a ride on the long black train.

If you’re curious about my exact approach.  The query I used was a “modified for my needs” version of the one Aaron Bertrand provides on his blog.  And yes – heaps get picked up by this DMV also.

Be careful following in my tracks.  There are some gotcha’s about how accurate this DMV is.  Things like service restarts, reboots, index rebuilds and so on, can all affect the data.  Also – just because a table hasn’t been touched for a few weeks doesn’t automatically mean it’s worthless.  Maybe it’s only critical when calculating quarterly profit or something like that.  You’ve got to consider this kind of stuff when spring cleaning.

Back to my original point.  Dynamic Management Views and Functions to me are the single greatest improvement to SQL Server over the past 15 years.  I’d take that over hekaton, extended events, and intellisense in SSMS.  I’d even take it over them finally breaking date and time into two distinct data types.

What to you is the single greatest enhancement to Microsoft SQL Server since it’s pre Y2K days?  Same, different, depends?  Weigh in.  I’m curious what people think.