March 25, 2010 at 10:49 am
I am trying to get a broad feel for how other companies/DBA's handle their disk fragmentation so I thought maybe more people would respond to a poll.
Please feel free to post your thoughts if you wish.
Kind regards,
-Z
March 25, 2010 at 4:05 pm
Linchi Shea has a six part blog titled "Performance Impact: file fragmentation and SAN" that will cover may of your questions at http://sqlblog.com/blogs/linchi_shea/archive/2008/12/07/performance-impact-file-fragmentation-and-san.aspx
Database files will fragment whenever the file automatically grows, so insuring that all databases files have sufficient free space and there is no autogrow occurring will prevent disk fragmentation. So when free space does decreases sufficiently, run an "alter database alter file" to increase the free space rather than allowing auto grow. As the rebuild index command copies the index to new pages, the amount of free space required needs to be greater than the index size, so you need to check the largest index that is being rebuilt and insure that that amount of free space is available. If you follow these steps and insure that all file growths are done manually and in large increments, you will only need to do disk fragmentation on an occasional basis, perhaps yearly.
Finally, there also can be transaction log fragmentation, which often has a bigger impact than disk fragmentation. Linchi Shea also has a blog on this topic at http://sqlblog.com/blogs/linchi_shea/archive/2008/12/07/performance-impact-file-fragmentation-and-san.aspx
Kalen Delaney and Kimberly L. Tripp also have blog posts about transaction log fragmentation.
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply