Thin Provisioning and SQL Server

22 Jul

Thin provisioning is a broad topic that covers a style of allocating resources. That style is to allocate a small amount of actual resources to start and only reserve more resources as actual growth occurs. It is quite a popular topic in virtualization and SAN storage. One thing that came to mind as I read the VMWare storage guide about thin provisioning is SQL Server’s auto-grow feature.

Thin provisioning is a Ponzi scheme. If at any given moment all of the databases or VMs want all of the space they asked for, there won’t be enough actual resources to go around. But, as long as we have a large pool of “investors”, the growth will be predictable enough that we can accommodate all of our VMs and databases.

I’ve read best practices that strongly advise not to use the auto-grow feature. Pre-size the data and log file to make all the gremlins inside SQL happy. Even Microsoft recommends pre-sizing data and log files and only to use auto-grow in case of emergency.

With all these warnings, one might jump on the thick provisioning bandwagon. I’m not sure how much of these SQL warnings translate to VMs and SANs. If you don’t thick provision with accuracy, you are left with a giant mess of unused resources.

Thick provisioning. I’m not a fan. It requires math and a crystal ball. It’s hard to un-do thick provisioning that was poorly executed. How do you fix a database that is too small? Make it bigger, which can happen in an instant. How do you fix a database that is too big? Attempt some fragmenting shrink command that takes forever or attempt to import and export into a properly provisioned file.

It takes real expertise to translate how much space users really need. It’s not even really expertise, but more experience with the people who ask for space. Thick provisioning is a game that you will loose more than win. That is why I like to thin provision my databases. I suspect as a storage and virtualization admin I will consider thin provisioning my storage at those layers as well.

But maybe thin provisioning at all layers in unnecessary. Consider the following thick provisioned setup.

1 TB SAN LUN (Black)
1 TB VMWare datastore (Yellow)
1 TB Virtual Machine (Green)
39 GB C: Drive (Blue)
25 GB D: Drive (Blue)
750 GB E: Drive (Blue)
200 GB F: Drive (Blue)
Database Files (Red)


Thick provisioned, we use 1 TB of SAN storage on day one until the datastore is deleted. If that estimate of 1TB was just a shot in the dark and your database ended up only 100GB before you needed to upgrade the OS/SQL/Storage etc… you basically just wasted thousands of SAN dollars.

The options we have for thin provisioning are at the SAN level, VM level, at the drive level and at the database level (aka auto-grow). If we thin provision at the san level only we could potentially save zero space. Since the VM is built and the .vmdk files are pre-sized, the SAN may consider this space used. Just like a .mdf file, .vmdk files can be thick provisioned instantly(lazy) which doesn’t zero out the file, or slowly(eager) which does zero out the file. Unless you have some magical feature, the SAN will consider this used up and there will be no chance for savings if we don’t actually use the space.

If we thin provision the VMDK files there is some chance for savings. Almost all of the blue and green will be gone and we can fit a whole bunch more VMs in the yellow datastore.

If we thick provision the VMDK files with a lazy zero and thin provision the SAN LUN it may be able to reclaim the empty space inside of vmdk files. I’m not quite sure how well this works but will have to test it out. I’m guessing a quick format in Windows and instant file initialization in SQL Server could have some bearing on how much space we save.

With a few VMs, thin provisioning isn’t needed because we can spend the time to more accurately predict growth. Also, the pool is too small to predict growth on the fly. With a few thousand VMs, thin provisioning makes a lot of sense. That will make the growth curve steady enough for you to predict when more storage is needed.

Leave a comment

Posted by on July 22, 2013 in Storage


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: