kennethigiri (4/20/2012)
I know one can adjust the fill factor to deal with internal fragmentation, what can one do about external fragmentation?
Defragment or even rebuild the index to reduce external fragmentation.
kennethigiri (4/20/2012)
In addition, we use a shared storage area (Compellant) which is supposed to be self tuning. Do we need to re-align? How can this be done in an already existing setup?
The SAN may be self-tuning, it will not affect offset and allocation unit issues however. Now, there are more factors to take into consideration as well, most important is the block (aka stripe) size of your SAN. For large blocks, as for instance 4MB, the performance issues related to partition offset are barely noticeable, but for a small block size as for instance 128kB is way worse. But to the point, what can you do about it? To adjust the allocation unit size you'll have to reformat the partition, to adjust partition alignment you'll have to delete and recreate the partition. Thus, My general recommendation in a SAN environment would be to present a new LUN to the server, partition and format it correctly, then move the data files. Yes, it will include downtime, but at least you have to move the data only once. By the way: Partitions CREATED by Windows Server 2008 and later has correct partition alignment (1MB), whereas Windows Server 2003 and earlier creates them with 31.5kB alignment, which is not optimal.