adrienne.lore (3/22/2012)
OK... so I guess my concern is more with index fragmentation then, not disk. I was also told that in a thin-provisioned sql environment, this also meant that you don't necessarily need to defragment indexes within sql server. Is this the same thing? They are talking about disk, and I am talking inside of SQL? It seems incredibly backwards to me to see very fragmented indexes, and then be told not to defragment them because of a thin-provisioned virtual environment. Help?
1) You SHOULD be concerned with OS file fragmentation unless you proactively manage your database file sizes. If I had a nickel for every client I have come across that left default 1MB data file growths I would be living on an island I bought - say Hawaii!! 😀 I have found over a half-million OS file fragments before, resulting in unbelievably bad IO performance!
2) Thin provisioning is ABSOLUTELY TO BE AVOIDED in a SQL SERVER ENVIRONMENT. You are guaranteed to introduce head-thrashing-causing file fragments, as well as internal SQL Server fragmentation as well. I believe some SANs can also 'stall' the IO while additional space is being added too, leading to unpredictable and very difficult to diagnose random performance problems.
3) I STRONGLY encourage you to bring in a performance tuning professional to give your systems and databases a review. I predict there will be LOTS of low-hanging fruit to be found!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service