SQL Database physical file fragmentation

I think this article gives an excellent overview of defragmentation of SAN drives

http://www.las-solanas.com/storage_virtualization/san_volume_defragmentation.php

Basic points is that defragmenting is not recommended on SAN storage because it is difficult to correlate the physical location of blocks on the disk when the location has been virtualized by the SAN when presenting the LUN.

If you were using RAW device mappings or you have direct access to a RAID set that is the LUN you are working with, I could see degfragmentation having a positive effect, but if you're given a "virtual" LUN off a shared RAID-5 set, no.

Multiple parts to this question and answer:

Physical file fragmentation isn't really relevant for Enterprise SAN storage, as Kevin already pointed out - so nothing to add there. It does really come down to the I/O subsystem and how likely you are to be able to make the drives go from more-random I/Os when performing a scan to more sequential I/Os when performing a scan. for DAS, it's more likely you will, for a complex slice-n-dice SAN, probably not.

File-system level defragging - only do it with SQL shut down. I've never experienced problems myself here (as I've never performed an online, open-file defrag of SQL database files) but I've heard plenty of anecdotal evidence from customer and clients of weird corruption problems occuring. General wisdom is not to do it with SQL online.

Index fragmentation is completely orthogonal to file fragmentation. SQL Server has no idea of file-fragmentation - too many virtualizatin layers in between for it to have any hope of working out actual I/O subsystem geometries. Index fragmentation, however, SQL does know everything about. Without repeating myself too much from the answer you already referenced, index fragmentation will prevent SQL doing efficient range-scan readahead, regardless of how fragmented (or not) the files are at the file-system level. So - absolutely you should mitigate index fragmentation if you're seeing degrading query performance.

You do not have to do these in any particular order, although if you take care of file-system fragmentation and then rebuild all your indexes and cause more file-system fragmentation by growing multiple files on a defragged volume, you're probably going to be ticked off. Will it cause any perf issues though? As discussed above, it depends :-D

Hope this helps!

What's the best way to fix physical file fragmentation on a production SQL box?

I run SYSINTERNALS' contig on my database files.

See http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

I would recommend sizing the db appropriately, shutting sql server down, copy the database file to another disk array, and then copy it back to defrag it. Much faster than using windows defrag in my experience.

I tried to defragment the physical disks in a scsi solution once, but got little or no performance boost at all. The lesson i learned is that if you experince slow performance due to the disk system, it doesnt have anything to do with the fragmentation, as far as we talk data file, since it is using random access.

If your indexes are defragmented and statistics are updated (very important) and you still see I/O as bottleneck, then you suffer from other things than physical fragmentation. Have you used more than 80% of the drive? Do you have enough drives? Are your queries optimized enough? Are you doing a lot of Table scan or even worse a lot of index seek followed by clustered index lookup? Look at the query plans and use "set statistics io on" to find out whats really going on with your query. (look for a high number of logical or physical reads)

Please let me know if i am completely wrong.

/Håkan Winther

Maybe the indexes are not optimized enough for your application and you do not have Veritas I3 to optimize your database then you could use a statement like this to find missing indexes:

       SELECT
      mid.statement,
      mid.equality_columns,
      mid.inequality_columns,
      mid.included_columns,
      migs.user_seeks,
      migs.user_scans,
      migs.last_user_seek,
      migs.avg_user_impact,
      user_scans,
      avg_total_user_cost,
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS [weight]--, migs.*--, mid.*
   FROM
      sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig
         ON (migs.group_handle = mig.index_group_handle)
      INNER JOIN sys.dm_db_missing_index_details AS mid
         ON (mig.index_handle = mid.index_handle)
   ORDER BY
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ;

Or a statement like this to find indexes that is not used in select statements and decreases update/insert performance:

    CREATE PROCEDURE [ADMIN].[spIndexCostBenefit]
    @dbname [nvarchar](75)
WITH EXECUTE AS CALLER
AS
--set @dbname='Chess'
declare @dbid nvarchar(5)
declare @sql nvarchar(2000)
select @dbid = convert(nvarchar(5),db_id(@dbname))

set @sql=N'select ''object'' = t.name,i.name
        ,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups
        ,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups
        ,''user writes'' = iu.user_updates
        ,''system writes'' = iu.system_updates
from '+ @dbname + '.sys.dm_db_index_usage_stats iu
,' + @dbname + '.sys.indexes i
,' + @dbname + '.sys.tables t
where 
    iu.database_id = ' + @dbid + '
and iu.index_id=i.index_id
and iu.object_id=i.object_id
and iu.object_id=t.object_id
AND (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates
order by ''user reads'' desc'

exec sp_executesql @sql

set @sql=N'SELECT
   ''object'' = t.name,
   o.index_id,
   ''usage_reads'' = user_seeks + user_scans + user_lookups,
   ''operational_reads'' = range_scan_count + singleton_lookup_count,
   range_scan_count,
   singleton_lookup_count,
   ''usage writes'' = user_updates,
   ''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count,
   leaf_insert_count,
   leaf_update_count,
   leaf_delete_count,
   ''operational_leaf_page_splits'' = leaf_allocation_count,
   ''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count,
   ''operational_nonleaf_page_splits'' = nonleaf_allocation_count
FROM
   ' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o,
   ' + @dbname + '.sys.dm_db_index_usage_stats u,
    ' + @dbname + '.sys.tables t
WHERE
   u.object_id = o.object_id
   AND u.index_id = o.index_id
    and u.object_id=t.object_id
ORDER BY
   operational_reads DESC,
   operational_leaf_writes,
   operational_nonleaf_writes'

exec sp_executesql @sql

GO

I have some other SQL statements that I am using when I analyzing performance issues in the production environment, but these two is a good start I think.

(I know, This post is a little bit of topic, but I thought you might be interested since it has to do with indexing strategy)

/Håkan Winther