• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

sql server: getting fragmentation data


I'm running SQL Server 2008 standard.

I want to get fragmentation data on a table and/or index.

I've seen this page: http://msdn.microsoft.com/en-us/library/ms188917.aspx

...but I don't understand where to enter the table name or the database name.

Can someone assist?


  • 2
  • 2
  • 2
2 Solutions
The below query should do it.
  db_name() AS DatabaseName
, B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent, A.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B ON A.object_id = B.object_id
INNER JOIN sys.indexes C ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D ON B.object_id = D.object_id AND A.index_id = D.index_id
and B.name='<tablename>'
lcohanDatabase AnalystCommented:
Or you can run it at the DB level and see if any tables need index rebuild due to fragmentation:

I commented out all the EXEC statements so the query below will just list in the result set which table indexes from the DB where executed need to be reindexed

use MC


DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
DECLARE @TableName sysname;
DECLARE @updstats sysname;
DECLARE @num_recs bigint;

create table #TableUsed (
      tabName varchar(100),
      rowcnt int,
      reserved varchar(50),
      data varchar(50),
      index_size varchar(50),
      unused varchar(50))

--insert into #TableUsed exec ('sp_spaceused '''+@tab+'''')
insert into #TableUsed exec ('sp_MSforeachtable @command1="print ''?''", @command2="sp_spaceused ''?''" ')
--select * from #TableUsed

--Create outer cursor to get all TableName for all objects that have an index
select distinct so.name as tablename
      from sys.indexes si with (nolock), sys.objects so with (nolock)
            where si.object_id = so.object_id and si.index_id > 1 and index_id < 255 -- Excluded CLUSTERED INDEX
            and so.name not like 'sys%'
            order by tablename;

-- Open the cursor.
OPEN TableList;
-- Loop through the ReindLoop.
FETCH NEXT FROM TableList INTO @TableName;
BEGIN; -- outer TableList cursor
      --reset update stats flag
      set @updstats = ''
      --get and loop through ALL NONCLUSTERED indexes for the object
      SELECT index_id FROM sys.indexes WITH (NOLOCK) WHERE object_id=object_id(@tablename) and index_id > 1 -- exclude CLUSTERED indexes
                                                                                    --and is_unique = 0 -- exclude unique indexes as they can't be rebuilt WITH ONLINE = ON
      -- Open the cursor.
      OPEN ReindLoop;
      -- Loop through the ReindLoop.
      FETCH NEXT FROM ReindLoop INTO @indexid;

      --reset the command string and fragmentation counter
      SET @command = ''
      SET @frag = 0
      --get the fragmentation info from sys.dm_db_index_physical_stats
      SELECT @objectid = object_id,
            @indexid = index_id,
            @partitionnum = partition_number,
            @frag = avg_fragmentation_in_percent
            FROM sys.dm_db_index_physical_stats (DB_ID(), object_id(@TableName), @indexid , NULL, 'LIMITED')
            WHERE (avg_fragmentation_in_percent > 5.0 OR avg_page_space_used_in_percent < 75) AND index_id > 1 -- excluded CLUSTERED index
                        --added max density check to capture indexes that don't fill the pages properly
                        --changed SAMPLED to LIMITED

      -- get index info
      SELECT @objectname = o.name, @schemaname = s.name
            FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id
            WHERE o.object_id = @objectid;

      SELECT @indexname = name
            FROM sys.indexes
            WHERE  object_id = @objectid AND index_id = @indexid;

      SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;

--TEST if the INDEX has any of columns IN ('text', 'ntext', 'image', 'varchar', 'nvarchar', 'varbinary', 'xml')
--If yes just log it and don't rebuild because it can't be done with ON-LINE ON.

      SELECT  SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
            tbl.name AS [Table_Name],
            i.name AS [Index_Name],
            clmns.name AS [Name],
            ic.is_included_column AS [IsIncluded],
            ic.is_descending_key AS [Descending],
            CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N'IsComputed') AS bit) AS [IsComputed],
      FROM sys.tables AS tbl
            INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
            INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
            INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
            INNER JOIN sys.systypes AS st ON st.xusertype = clmns.user_type_id
      WHERE (i.index_id = @indexid and tbl.name=@TableName and SCHEMA_NAME(tbl.schema_id)=N'dbo') and st.name in ('text', 'ntext', 'image', 'varchar', 'nvarchar', 'varbinary', 'xml')
                  and clmns.max_length in (-1, 16)


set @num_recs = (select rowcnt from #tableused where tabname = @objectname)

      IF @num_recs > 1000000 and @frag > 5.0 and @indexid > 1 -- exclude clustered index
            SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD WITH(FILLFACTOR = 80, ONLINE=ON)';
            --EXEC (@command);
            PRINT (@command);
            set @updstats = @objectname

      IF @num_recs < 1000000 and @frag > 10.0 and @indexid > 1 -- exclude clustered index
            SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD WITH(FILLFACTOR = 80, ONLINE=ON)';
            --EXEC (@command);
            PRINT (@command);
            set @updstats = @objectname

      --you could log the info below into a table
      if @frag > 5.0
            --INSERT INTO index_defragmentation (tablename, indexname, partitionnum, rowcnt, frag , dateentered)
                  SELECT @tablename,(select name from sys.indexes where object_id=object_id(@tablename) and index_id = @indexid),@partitionnum,@num_recs,@frag,getdate()

--log index that can't be rebuilt!!! by inserting partitionnum = 9999 and  frag = 99.99
--select top 1 * from index_defragmentation where partitionnum = 9999 and frag = 99.99
--INSERT INTO index_defragmentation (tablename, indexname, partitionnum, rowcnt, frag , dateentered)
            SELECT @tablename,(select name from sys.indexes where object_id=object_id(@tablename) and index_id = @indexid), 9999, 0, 99.99, getdate()

      FETCH NEXT FROM ReindLoop INTO @indexid;
      -- Close and deallocate the cursor.
      CLOSE ReindLoop;
      DEALLOCATE ReindLoop;

--refresh stats on table IF index rebuild
IF @updstats != ''
            BEGIN -- update statistics
                  SET @command = 'UPDATE STATISTICS ' + @updstats + ';';
                  --EXEC (@command)
                  PRINT (@command)
                  SET @command = '-- sp_recompile ' + @updstats;
                  --EXEC (@command)
                  PRINT (@command)
            END -- update statistics
FETCH NEXT FROM TableList INTO @TableName;
END -- outer TableList cursor

CLOSE TableList;

drop table #TableUsed

skbohlerAuthor Commented:
Thanks for these scripts.

venk_r, using yours, at what average fragmentation percentage should one update the index?

lcohan, how do I interpret the data that appears when your script is run? I'm not sure what the columns 3,4,5 and 6 mean. Can you explain that a touch?

Thanks again!
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I would say if the avg fragmentation goes beyond 30 % perform reindex and if its between 10-30% perform the reorganize.
Below is the link which will help you use it.

skbohlerAuthor Commented:
lcohanDatabase AnalystCommented:
Sorry I missed that...I guess you are talking about the columns with no name shwon by the select statements and they should be in order:

tablename, indexname, partitionnum, rowcount, fragmentation%, dateexecuted
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now