NTFS Fragmentation may be more harmful than you think

I’ve posted previously about the adverse affect of ntfs fragmentation and I’ve also mentioned that it is sometimes suggested that fragmentation is not a concern on a SAN.

I’ve yet to finish testing this on a SAN, after all you don’t often get a free play with a SAN for this type of testing, but I did find that extreme fragmentation of the mdf files on a SAN did degrade performance .. sadly I didn’t have the time or resource available to complete my tests.

However, I see that  CSS SQL Server Engineers ( Microsoft Customer Service and Support (CSS) SQL Support ) have put out quite an extensive post which covers the subject of ntfs fragmentation and SQL Server.

http://blogs.msdn.com/psssql/archive/2009/03/04/sparse-file-errors-1450-or-665-due-to-file-fragmentation-fixes-and-workarounds.aspx

What is interesting is that a KB which only lists vista has been marked as applying to W2K3 and Windows 2008  http://support.microsoft.com/kb/967351

I suggest having a good read of this if you don’t normally pick up posts from this blog.

Published 04 March 2009 22:35 by GrumpyOldDBA
Filed under: , ,

Comments

# re: NTFS Fragmentation may be more harmful than you think

05 March 2009 13:25 by owen983

I can confirm that NTFS fragmentation, even on a SAN, can indeed have a negative impact on performance.

I recently started a new job as DBA for a SQL Server 2005 shop; 100 databases in production with another 100 as training databases on the same server, and no one with real DBA responsbilities until now.  One of the first things I did was to go through and start checking the obvious properties (compatibility mode, recovery mode) to make sure everything was correct; in the course of this I saw that the data files were all set to auto-grow 1MB at a time.  I used the Contig utility from Sysinternals, and found that many of the MDFs were split pretty badly; one particular one was in 78 different fragments.  To test the impact, I backed up and restored that database on the same server as a new database, updated the statistics, and picked a stored procedure to test in both databases.  The stored procedure in the fragmented DB ran for about 14 minutes; in the optimized db, less than a minute.