Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


sql server: getting fragmentation data

Posted on 2011-09-07
Medium Priority
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: 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?


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 1000 total points
ID: 36499676
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>'
LVL 40

Assisted Solution

lcohan earned 1000 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 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


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!
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.


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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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