Hi, Is that possible to Defrag the SAS Drives on Production Servers where SQL Database files reside? SQL performance will be better by doing this? I have been suggested from SQL Gurus, they said that I should do that but I have to stop SQL server while doing the Disk Defragmentation. Otherwise windows de-fragmentation will ignore the Database files, Is that true? What if DB is need to be online 24/7? Do you suggest any free tools to Defrag the Disk if that is a good to apply.... Thanks.
As far as free tools go I have no answers. We use Perfect Disk, and have done for years. It has an open files agent that works magic and doesn't require the database files to be detached. We set it to run once a night to shuffle any fragments together.
The only way to defrag the database files on the physical disk would be to detach the database, if they are on dedicated disk, or you could shutdown SQL Server also. Defrag.exe (free tool, comes with Windows) will not defrag a opened/locked file. This is a good article to read on it:
http://www.bradmcgehee.com/2011/06/do-you-ever-physically-defragment-your-sql-server-mdf-ldf-files/ Depending on the activity of your database you probably will not see a significant increase in performance by just doing defrag on the physical disk. I'm not sure which "SQL Guru" told you this but there are other things that you can do to improve performance on your database that will last longer and return better results, than defragging the disk. You can defrag the disk, but the fragmentation will return eventually. IMHO.
Several products can defrag live files, such as mdf and ldf files. Diskeeper has this ability, and I've read that Contig by Mark Russinovich can, as well - [
http://technet.microsoft.com/en-us/sysinternals/bb897428]. I'm surprised that the native tool wouldn't provide this too. A "live" defrag will impact DB performance and should not be run during times of heavy load or batch processing. Once your mdf and ldf files are defragged and properly sized, they should not require further defrag maintenance unless you allow auto grow to get out of hand through improper database sizing or inappropriate auto grow increments. It's possible you could see some performance benefits from a defrag, but it's not the first place I would look and I wouldn't expect an order of magnitude performance increase like you can often realize through proper index creation and maintenance. :