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
  • 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 39

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!
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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 39

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now