Share via

Fragmentation Percentage of all tables in a SQL database

  • Article

"The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly." [TechNet]

Issues which I faced in my organization is my company database contains more than 700 tables identifying fragmentation percentage of all table is time consuming process as for each table we need to used SYS. DM_DB_INDEX_PHYSICAL_STATS to avoid this generally written the below syntax to list all table Fragmentation percentage, This syntax can also be used to generate report periodically to identify the fragmentation percentage.

Once fragmentation percentage is identified then we need to plan for de-fragmentation according to the fragmentation level

 

-- be safe for sys.indexes although BOL says "sys.dm_db_index_physical_stats requires only an Intent-Shared (IS)" 
SET TRANSACTION  ISOLATION LEVEL  READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO 
  
-- if object_id('tempdb.dbo.#IDXFRAG') is not null drop TABLE #IDXFRAG 
if object_id('tempdb.dbo.#IDXFRAG') is  not null
DROP table  tempdb.dbo.#IDXFRAG 
  
    CREATE TABLE  #IDXFRAG 
    (   DbName                          sysname, 
        ObjName                         sysname, 
        IdxName                         sysname     NULL,  
    -- columns below are exactly as generated by SYS.DM_DB_INDEX_PHYSICAL_STATS function 
        database_id                     smallint, 
        [object_id]                     int, 
        index_id                        int, 
        partition_number                int, 
        index_type_desc                 nvarchar(60) COLLATE  Latin1_General_CI_AI NULL, 
        alloc_unit_type_desc            nvarchar(60) COLLATE  Latin1_General_CI_AI NULL, 
        index_depth                     tinyint NULL,  
        index_level                     tinyint NULL,  
        avg_fragmentation_in_percent    float  NULL, 
        fragment_count                  bigint  NULL, 
        avg_fragment_size_in_pages      float  NULL, 
        page_count                      bigint  NULL, 
        avg_page_space_used_in_percent  float  NULL, 
        record_count                    bigint  NULL, 
        ghost_record_count              bigint  NULL, 
        version_ghost_record_count      bigint  NULL, 
        min_record_size_in_bytes        int  NULL, 
        max_record_size_in_bytes        int  NULL, 
        avg_record_size_in_bytes        float  NULL, 
        forwarded_record_count          bigint  NULL
   -- ,   primary key nonclustered (DbName, ObjName,[object_id],index_id,partition_number)   -- IdxName is NULL if HEAP (index_id=0) 
    ) 
GO 
  
IF NOT EXISTS (SELECT * FROM tempdb.sys.indexes WHERE object_id = OBJECT_ID(N'tempdb..#IDXFRAG') AND  name = N'IDXFRAG_CI') 
    create index  IDXFRAG_CI on  #IDXFRAG (DbName,ObjName,IdxName) 
  
-- tip: you can re-run in SSMS for different db's by  
declare tblcur cursor for
    select  object_id, TblName=schema_name(schema_id)+'.'+name 
    from    sys.tables where type = 'U' 
    order by TblName  
  
declare @dbid smallint, @objid int, @TblName nvarchar(257) 
select @dbid=db_id()    --, @objid=OBJECT_ID('SEDOL_PRICE') 
--select @dbid, @objid 
open tblcur 
fetch next from tblcur into @objid, @TblName 
while @@fetch_status=0 
 begin 
 -- print @TblName        -- DEBUG only 
    delete from #IDXFRAG where DbName=db_name() and [object_id]=@objid      -- allow re-runs and multiple db's 
    insert into  #IDXFRAG 
        select  DbName=db_name(), ObjName=@TblName, IdxName=I.name, 
                database_id,s.[object_id],s.index_id, partition_number, 
                index_type_desc,alloc_unit_type_desc, 
                index_depth,index_level, 
                avg_fragmentation_in_percent,fragment_count, 
                avg_fragment_size_in_pages,page_count, 
                avg_page_space_used_in_percent,record_count, 
                ghost_record_count, version_ghost_record_count, 
                min_record_size_in_bytes,max_record_size_in_bytes, 
                avg_record_size_in_bytes,forwarded_record_count  
        from    SYS.DM_DB_INDEX_PHYSICAL_STATS(@dbid,@objid,NULL,NULL,'SAMPLED')    S 
        join    sys.indexes                                                         I on  I.object_id= @objid and  I.index_id=S.index_id  
        --where   S.avg_fragmentation_in_percent > 20 AND S.page_count > 8  
            
    fetch next  from tblcur into @objid, @TblName 
 end
close tblcur 
deallocate tblcur 
go 
  
select  DbName, ObjName,IdxName, index_id, partition_number,index_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count, forwarded_record_count    --, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes 
from    #IDXFRAG 
order by  DbName,ObjName,IdxName    -- IDXFRAG_CI  
go

See below gallery for more
http://gallery.technet.microsoft.com/List-of-all-tables-with-b1586515#content

See Also