sql server: getting fragmentation data

Posted on 2011-09-07
Last Modified: 2012-05-12

I'm running SQL Server 2008 standard.

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

I've seen this page:

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

Can someone assist?


Question by:skbohler
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2

Accepted Solution

venk_r earned 250 total points
ID: 36499676
The below query should do it.
  db_name() AS DatabaseName
, AS TableName
, 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
LVL 40

Assisted Solution

lcohan earned 250 total points
ID: 36505776
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 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 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 =, @schemaname =
            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],
   AS [Table_Name],
   AS [Index_Name],
   AS [Name],
            ic.is_included_column AS [IsIncluded],
            ic.is_descending_key AS [Descending],
            CAST(COLUMNPROPERTY(ic.object_id,, 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 and SCHEMA_NAME(tbl.schema_id)=N'dbo') and 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


Author Comment

ID: 36520116
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!
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Expert Comment

ID: 36522311
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.

Author Comment

ID: 36522416
LVL 40

Expert Comment

ID: 36538650
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question