Defrag It

I mentioned in earlier posts both the joys of indices and some strategies for maintaining them so they don't become too badly fragmented. Although these great high-level guidelines generally collect and resolve the major issues, occasionally you need a deeper inspection to make sure you aren't overlooking any mid-size tables that could be causing a few queries some consternation.  This method is more resource intensive, so I only run it every couple months or so.

You will first need to create a table to receive the results of the fragmentation analysis:

CREATE TABLE [dbo].[sampledIndexStats](
      [TableName] [nvarchar](128) NULL, [IndexName] [nvarchar](128) NULL,
      [Frag] [decimal](5, 2) NULL
) ON [PRIMARY]
GO

Next, run this:

INSERT INTO dbo.sampledIndexStats
SELECT
  OBJECT_NAME(i.[object_id]),
  i.name, s.avg_fragmentation_in_percent
FROM
  sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS s
INNER JOIN sys.indexes AS i
ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id;

This takes a while to run as dm_db_index_physical_stats need to physically sample the index trees to see how out-of-sequence the leaf levels are. Once it's done though you can join the resulting sampledIndexStats table into the query that shows overall index usage to determine if any of the mid-sized heavily used indices suffer from fragmentation.

You're welcome.