Day 6 – Lost in Space

This is the sixth installment in the 12 day series for SQL tidbits during this holiday season.

Previous articles in this mini-series on quick tidbits:

  1. SQL Sat LV announcement
  2. Burning Time
  3. Reviewing Peers
  4. Broken Broker
  5. Peer Identity

Credit: NASA/JPL

One of the more frequently recurring themes I see in my travel and work is the perpetual lack of space.

For instance, every time I fly there is inevitably a handful of people that have at least three carry-on items and at least one of those items is larger than the person trying to “carry” it on the plane.  Imagine watching these people trying to lift 100+ pound bags over their heads to put them into these small confined overhead storage compartments.  We are talking bags that are easily 2-3 times larger than the accepted dimensions, yet somehow this person made it on the plane with such a huge bag for such a tiny space.

Another favorite of mine is watching what appears to be a college student driving home in a GEO Metro.  A peek inside the vehicle might reveal 5 or 6 baskets of soiled laundry and linens.  A look at the vehicle as a whole might reveal a desert caravan’s worth of supplies packed onto the vehicle.  Watching the vehicle for a while you might notice that it can only lumber along at a top speed of 50 mph going downhill and good luck getting back up the hill.  It is just far too over-weighted and over-packed.  The vehicle obviously does not have enough room internally.

In both of these examples we have a limited amount of storage space.  In both of these examples we see people pushing the boundaries of those limitations.  Pushing those boundaries could lead to some unwanted consequences.  The GEO could break down leaving the college student stranded with dirty laundry.  The air-traveler just may have to check their dog or leave it home.

But what happens when people try to push the boundaries of storage with their databases?  The consequences can be far more dire than either of the examples just shared.  What if pushing those boundaries causes an outage and your database is servicing a hospital full of patient information (everything from diagnostics to current allergies – like being allergic to dogs on planes)?  The doctor needs to give the patient some medication immediately or the patient could die.  The doctor only has two choices and one of those could mean death the other could mean life.  All of this is recorded in the patient records but the doctor can’t access those records because the server is offline due to space issues.

Yeah that would pretty much suck.  But we see it all the time.  Maybe nothing as extreme as that case, but plenty of times I have seen business lose money, revenue, and sales because the database was offline due to space.  The company wants to just keep pushing those boundaries.

In one case, I had a client run themselves completely out of disk space.  They wouldn’t allocate anymore space so it was time to start looking to see what could be done to alleviate the issue and get the server back online.

In digging about, I found that this database had 1Tb of the 1.8TB allocated to a single table.  That table had a clustered index built on 6 columns.  The cool thing about this clustered index is that not a single query ever used that particular combination.  Even better was that the database was seldom queried.  I did a little bit of digging and found that there really was a much better clustered index for the table in question.  Changing to the new clustered index reduced the table size by 300GB.  That is a huge chunk of waste.

Through similar exercises throughout the largest tables in the database, I was able to reduce index space waste by 800GB.  Disk is cheap until you can’t have anymore.  There is nothing wrong with being sensible about how we use the space we have been granted.

Thinking about that waste, I was reminded of a great resource that Paul Randal has shared.  You can find a script he wrote, to explore this kind of waste, from this link.  You can even read a bit of background on the topic from this link.

2 Comments - Leave a comment
  1. […] Lost in Space […]

Leave a comment

Your email address will not be published. Required fields are marked *

December 2013
« Nov   Jan »


  • @SQLSoldier: @jonleelockwood I don't understand the question. Do you mean how long will SQL wait for an IO request before it times out? #sqlhelp
  • @jonleelockwood: #sqlhelp what is the max latency for sql 2012 to think tempdb is missing? Thanks.
  • @sqL_handLe: #sqlhelp Anyone monitor ESXTop migrations/sec, switches/sec, wakeups/sec for #SQLServer on VMware? Seems like lotta guesswork with %Ready.
  • @A_Bansal: No 64 GB. So which 32 GB Mobile workstation do u recommend? #sqlhelp
  • @Lee____Cam: I've managed to drop my AG now and recovered my do but it won't allow the recreation of the AG. Looks like it's failing at the FCM #sqlhelp
  • @Lee____Cam: I have a 2 note always on ag, both showing not synchronizing/recovery pending. I can't get them online or remove the AG. Any ideas #sqlhelp
  • @SQLSoldier: @mvelic Yes, but then I pointed out that they were using nolock and SSIS isn't. The matching records were not committed. #sqlhelp #TrueStory
  • @mvelic: It's just maddening because this lookup has *always* worked in the past. It's just now deciding to not recognize matches. #sqlhelp
  • @mvelic: Has anyone just seen an SSIS Lookup fail to make matches? You know the matches exist, but it doesn't connect them and it fails? #sqlhelp
  • @banerjeeamit: @MattPgh No. Current processing report is not visible. This is visible in RunningJobs table but not the stats breakdown. #sqlhelp

Welcome , today is Saturday, October 10, 2015