November 30, 2009 at 3:50 pm
Revisiting this. What if the file system is not directly attached storage? E.g. Netapp device. Is there any advantage to turning this on in that case?
November 30, 2009 at 4:11 pm
It's been a few years, but I have used this on NetApp SANs without problems.
The feature relates to what Windows/NTFS does with the modified file; the type of storage has no bearing on it.
If you are using thin provisioning/thin reservation/whatever your SAN vendor calls it, where the SAN storage is not allocated until the file system requests it, then you get the additional benefit of newly-grown files consuming no additional storage space on your SAN, until the data pages are allocated for actual data storage through inserted data or index builds/rebuilds. Then you can blow all the data files on all databases out with 50% or more free space and not worry about growth as much - just keep an eye on the space consumed on the SAN.
-Eddie
Eddie Wuerch
MCM: SQL
December 1, 2009 at 7:16 am
I can't say that I completely understand thin provisioning, but if the storage device presents disk space "on demand", and then the server OS and sql 2005 insist on writing zeroes to all of the new space, don't you still take a hit ( i.e. instant file initialization is still useful ) ??
December 1, 2009 at 12:00 pm
The key point with IFI is that it doesn't write those zeroes. Using the NTFS Sparse File mechanism, the file is merely marked as being the new size and the space is reserved in file allocation metadata, but the pages are not initialized (zeroed). That's why it's so fast.
Eddie Wuerch
MCM: SQL
December 1, 2009 at 12:17 pm
Yes. I've heard IFI is of no value with SANs ( Netapp in particular ), so I'm looking to see if there is documented evidence to the contrary.
December 1, 2009 at 12:43 pm
I don't know about documentation, we simply ran our own tests on our 3PAR sans:
1. Record SAN allocation
2. Create thin-provisioned LUN, and mount it
3. Record SAN allocation
4. Format mount point
5. Record SAN allocation
6. Grow mount point through concatenation of new thin-provisioned SAN volume
7. Record SAN allocation (note: limited additional space consumed)
8. Create small data file on mount point
9. Record SAN allocation (slight space consumption)
10. Add some data to the file.
11. Grow SQL file significantly
12. Record SAN allocation (note: no new SAN consumption, despite the large file)
13. Start pushing data into the file
14. Record SAN allocation (SAN consumption grows relative to space consumed in the file)
15. Repeat 13 and 14 until you have enough data to agree or disagree with IFI and thin provisioning/fractional reservation/etc.
When we ran the tests, everything appeared to work as advertised.
-Eddie
Eddie Wuerch
MCM: SQL
December 1, 2009 at 12:50 pm
Thanks Eddie.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply