Anyone running Thin Provisioned storage?

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.

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.

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

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.

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:

http://www.3par.com/products/inform_software/thin_persistence.html

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

http://www.oracle.com/technology/products/database/asm/pdf/oracle_automatic_storage_management_and_thin_reclamation-1.pdf

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.  

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.

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.