THE SQL Server Blog Spot on the Web

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

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Anyone running Thin Provisioned storage?

A friend recently asked me for some advice about implementing SAN storage for SQL Server, and I was able to explain how we'd implemented our storage, but ours is a more traditional setup with static LUNs tied to specific physical disks. Anyone out there have real-world experience running production SQL Server on thin provisioned storage instead? Success? Tips? Failure?

I get the idea in the abstract, but have never really had my hands on a system like that.

Published Tuesday, June 29, 2010 2:51 PM by merrillaldrich

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



Andrew Kelly said:

I have a client using it on a 3PAR SAN and it's working quite well for them so far. You just have to use it wisely and not get too agressive with trying to get the utmost out of every GB.

June 29, 2010 5:46 PM

peter fictum said:

well said andrew!

i am certainly not an expert on thin provisioning by any means; however, a couple things to note.

1. one thing with thin provisioned luns is that if you create a large file on the drive, and then remove or delete that file, the thin provisioned volume on the storage array will hold onto that space. say for example you have a one terabyte lun and your database is taking up 500 GB. in windows and on the storage array, it will show as 500 GB used and 500 GB free capacity. then, say you add a file that is 300 GB to the drive. both the storage array volume and the windows lun will show 800 GB used and 200 GB free. next, say you go ahead and delete the 300 GB file in windows. the drive on the windows server will show 500 GB used and 500 GB free; however, the storage array will still record the volume as 800 GB used and 200 hundred GB free. in this particular case, the san displays the volume as 80% full and the windows lun shows it as 50% full. to reclaim the free space for the thin provisioned volume on the storage array, you may be asked to run a command like sdelete on the windows host and drive.

2. it works similar when setting up, creating, and formatting brand new drives in windows for a new server build.  when using a full format the storage array will show the volume at near capacity; however, the lun on the windows host is displaying close to 100% free capacity.  performing a quick format will show equal space on both the windows host lun and the storage array volume.  this is an interesting one if you are use to utilizing full formats on your newly created windows drives.

thin provisioning has a lot of pros and some cons so your best bet is to talk with your storage vendor and ask a lot of questions so you fully understand the technology, what it offers, and how it can benefit the company.  just a few things to keep in mind when working with thin provisioned luns.

June 29, 2010 9:19 PM

James Luetkehoelter said:

I too have a client doing this, and there's far too much work spent trying to get the most out of each GB. To be fair, their SAN vendor advised them to set things up this way....sigh..

June 29, 2010 11:12 PM

GrumpyOldDBA said:

I did a storage project for some hefty databases recently and one of the points that was raised was Thin provisioning, even though I personally couldn't see any advantage for SQL Server. What was admitted was that in certain circumstances it doesn't work for NTFS, so in effect it doesn't work correctly. On a different tack I attended  Disaster Recovery training, one scenario discussed was thin provisioning - which allows you to effectively allocate more disk space than you actually have - there was a failover to the DR site so provisioned and it all fell over as there wasn't enough physical space.

June 30, 2010 7:36 AM

marc farley said:

I'm Marc Farley and I work for 3PAR.  There are differences in thin provisioning implementations.  The concerns raised here about deleted files consuming disk space and working with NTFS are mostly correct, but there are important exceptions.  FWIW, 3PAR storage supports some of the largest SQL Server implementations in the world - not all of them are using thin provisioning - but some are.

There is a relatively new storage function called space reclamation that returns the space consumed by deleted files to the free space pool of the array, so it can be used by new tables, files, etc that are created. Space reclamation reduces the storage utilization on an array, which significantly helps performance. 3PAR has a reclamation tools called Thin Persistence that works for NTFS file systems and the SQL Server databases residing on them. A URL on our website that describes it on a high level is here:

FWIW, we recently completed work with Oracle on a thin reclamation development project. You can read Oracle's white paper on it here:

June 30, 2010 9:09 AM

Richard Siemers said:

Hello, We are using 3PARs for our Tier1 and Tier2 storage. In our environment we have aprox 80 AIX servers, 10  ESX, and aprox 300 physical Windows machines attached, everything across the board is thin provisioned.   We have some that have been 100% allocated, and we have many more that have not... the net result is we're saving about 35% space.  

At this point, we don't fuss over a thin volume that a host has fully allocated, however the 3PAR does make it easy to identify these LUNs so when the day comes I need to reclaim unused but allocated capacity I have a list to work on.   RIght now we are still not oversubscribed, once we get close to 80% subscribed, meaning the sum of what we are virtually providing to hosts equals 80% capacity of the system, we will start investing time in utilizing the thin-reclemation features of the hardware.

Performance wise, the boost in performance we gained by switching to 3par far exceeded whatever minimal delay there might be, if even noticeable,  by using thin provisioning on a rapidly growing databases.  

June 30, 2010 11:06 AM

GrumpyOldDBA said:

I'm well aware of 3PAR, I was lucky enough to be able to spend some considerable time working with them on behalf of my client. Essentially thin provisioning allows you to pretend to have more storage than you have , I believe the banking sector used similar techniques - you just need to be careful that's all. In my case I have a dedicated SAN for my SQL Server and I could see no practical use for the feature. It works perfectly on Unix ( Linuux ) I understand.

July 1, 2010 8:35 AM

merrillaldrich said:

Thanks everyone for the input. It sounds like:

1. It can be made to work well, with the right design

2. Aside from capacity management, the major risk is probably one workload "stomping" another (for example, an unimportant workload monopolizing the array and compromising performance of an important workload). Are there resource-governer type controls in any of these systems? My company at one point tried a design with a large, shared pool of disks (not technically thin provisioned, just wide luns that shared spindles) for Oracle data and this was a huge problem.

3. Advantages seem to be up in the air - I hear a resounding "meh" from most people I have talked to, then more enthusiasm from a few others.

July 1, 2010 5:57 PM

Richard Siemers said:

If your looking at in from the perspective of a DBA "How does thin provisioning benefit SQL" the answer is none.  It's designed for capacity management in a shared SAN environment, it's benefit is to the people managing and paying for storage.

Performance management was a pre-puchase concern of mine as well, as it does not appear that 3PAR has QOS type restrictions available that one could manage.  However, this can be accomplishing for 3PAR, as well as any fibre based storage, by reducing the queue depth within the HBA drivers on the non-essential hosts.   Leave all your Tier 1 apps set to the default of 32 queued IOs but drop the setting to 8 or even 1 for unimportant hosts caught monopolizing resources.   We have not had to do this yet in our shared environment, but it is the plan in case we encounter performance contention.  We haven't needed to deploy queue throttling yet... but we keep throwing more and more workload on top of the existing storage... sooner or later we should find that proverbial 'last straw'.

#3 consider the source.   DBAs, OS admins, and the like probably should be skeptical and hesitant... the feature is not designed to give them anything (its virtually giving them storage they are accustomed to already having).  Similar to convincing an APP owner to move from physical hardware to VMware.    The opinions from dedicated storage admins, and persons responsible for paying for storage, will be more positive toward TP.  

If I were a DBA only, and storage was someone else's problem, I would want fully provisioned dedicated SAN storage for my stuff, like GrumpyOldDba has.   However, if your storage admin in addition to being the DBA, or concerned with the overall cost of storage in general...   Thin Provisioning works with everything... as far as the APP or host is concerned, there is no difference between a Thin and normal volume.

As far as "just being careful" applies to everything but most importantly to over subscribing resources like storage.   We're only in year 1.5 of a 5 year storage lifecycle so we have not hit the point of over subscription yet.  If all goes well, we will hit year 5 at 80% storage utilization and aprox 118% over subscription, if current trends remain stable.  If my math is wrong... we can always add more capacity non-disruptively... so I sleep pretty well at night.  

July 3, 2010 5:20 PM

merrillaldrich said:

Well put :-)

July 15, 2010 1:52 AM

BastianTheWhite said:

I have recently come into a thin provisioned environment as pure DBA. And my question is not so much of how does it benefit me? But any DBA only wants to know: "How does it impact my SQL Server?"

Regardless of what we admins should do, SQL Server can still only rely on OS counters. When a disk runs full, SQL server can somewhat cope with it, DBA's can measure the disk's free space and predict a point of failure in advance.

But with thin provisioning, as far as I understand it and have seen it work, suddenly a disk can run out of disk space while the OS still 'thinks' there's 100GB available. A thing I have seen happen just today while trying to resize one of my databases for a large import I ran into an 'out of disk space' error.

My question as DBA is, how can I accurately get a counter of what my SQL Server has available in real disk space? Because most databases are managed externally we cannot predict growth so we must use autogrowth settings to ensure business continuity.

I have read several links and articles now on thin provisioned SQL servers, but all are from the perspective of System & Network Architects.

I'm really happy you guys can save disk space! :)

But how can we prevent the disk running out of space at unpredictable points in time?

December 9, 2014 3:27 PM

Leave a Comment


This Blog


Privacy Statement