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!
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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 I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

801 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