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:
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.
