Pages

Thursday, 19 April 2012

SQL Server: Measuring The Index Fragmentation

In my previous post , I have explained about the different types of fragmentation and how it will affect the query performance. In this post we will discuss about measuring the index fragmentation.

Measuring Internal Fragmentation 

Internal fragmentation  is all about the fullness of the page and it is measured using the         sys.dm_db_index_physical_stats function with DETAILED mode. The avg_page_space_used_in_percent column in the output gives the internal fragmentation of the index. Below query list all the indexes which have more than 10 pages and page fullness is less than 85 percent.

EXEC sp_configure 'show advanced options',1
GO

RECONFIGURE WITH OVERRIDE
GO

DECLARE @DefaultFillFactor INT
DECLARE
@Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)

INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)'     
SELECT @DefaultFillFactor  = CASE WHEN run_value=0 THEN 100 ELSE  run_value  END  FROM @Fillfactor 

SELECT
DB_NAME() AS DBname,
QUOTENAME(s.name) AS CchemaName,
QUOTENAME(o.name) AS TableName,
i.name AS IndexName,
stats.Index_type_desc AS IndexType,
stats.page_count AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END  AS [Fill Factor],
  
stats.avg_page_space_used_in_percent,
  
CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevel
FROM
        
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'DETAILED') AS stats,
        
sys.objects AS o,
        
sys.schemas AS s,
        
sys.indexes AS iWHERE
        
o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id       AND     i.OBJECT_ID = stats.OBJECT_ID AND i.index_id = stats.index_idAND     stats.avg_page_space_used_in_percent<= 85 AND stats.page_count >= 10 AND        stats.index_id > 0 ORDER BY  stats.avg_page_space_used_in_percent ASC, stats.page_count DESC


I have used the where condition to fetch  only the indexes which  have more than 10 pages  and page fullness is less than 85 percentage. This is  based on   my environment and some best practices documentations.Low values for avg_page_space_used_in_percent and higher  value for PageCount together will affect the performance of the system. The value of avg_page_space_used_in_percent will be  low due to various reasons

  • Due to Page split and deleting records: In this scenario we have to REBUILD or REORGANIZE  the indexes. If the fragmentation is reported in the non leaf level , REBUILD is required to reduce the fragmentation.
  • Due to fill factor setting : A wrong setting  of fill factor value of the index might cause the internal fragmentation.If the internal fragmentation is due the fill factor setting, we have to REBUILD the index with new fill factor value.
  • Due to record size : Some time size of the record might account for internal fragmentation. For example let us assume that size of one record is 3000 bytes and page can hold only two record. The third record can not be fitted into a page as the remaining free space in the page is less than 3000 bytes. In this scenario each page will have empty space of 2060 bytes. To get rid of the fragmentation due to the size of the record , we might need to redesign the table or has to do a vertical partitioning of the table.

Measuring External Fragmentation 

External fragmentation also measured using the sys.dm_db_index_physical_stats function with LIMITED mode ,but we will be using the avg_fragmentation_in_percent from the result to measure the external fragmentation. With LIMITED mode it will give the fragmentation of the leaf level. To get the fragmentation of non leaf level, it  should be executed  with DETAILED or SAMPLE mode. A fragment is a continuous allocation of pages.For example if an index has 150 pages and pages are allocated from 1 to 50, 55 to 60 ,65 to 120 and 140 to 180.Each of these sequences are called as fragment and we can say that this index has four fragment.

EXEC sp_configure 'show advanced options',1
GO

RECONFIGURE WITH OVERRIDE
G

ODECLARE @DefaultFillFactor INT
DECLARE
@Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)

INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)'     
SELECT @DefaultFillFactor  = CASE WHEN run_value=0 THEN 100 ELSE  run_value END  FROM @Fillfactor 

SELECT
DB_NAME() AS DBname,
QUOTENAME(s.name) AS CchemaName,
QUOTENAME(o.name) AS TableName,
i.name AS IndexName,
stats.Index_type_desc AS IndexType,
stats.page_count AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END  AS [Fill Factor],
  
stats.avg_fragmentation_in_percent,stats.fragment_count,
  
CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevel
FROM
        
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED') AS stats,
        
sys.objects AS o,
        
sys.schemas AS s,
        
sys.indexes AS iWHERE
        
o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id       AND     i.OBJECT_ID = stats.OBJECT_ID AND i.index_id = stats.index_idAND     stats.avg_fragmentation_in_percent>= 20 AND stats.page_count >= 1000ORDER BY  stats.avg_fragmentation_in_percent DESC,stats.page_count DESC



In this query ,  I have used a where condition to fetch indexes which have fragmentation  greater than 20 percent and have minimum of 1000 pages.  

avg_fragmentation_in_percent can have higher value due to various reasons :

  • SQL server storage engine allocates pages from mixed extent to a table or index till the page count reaches eight.Once the page count reaches to eight SQL server storage engine starts assigning full uniform extents to the index. So there is a possibility of having higher fragmentation for small table and rebuilding indexes might increase the fragmentation.For example, let us assume that an index has 7 pages and these pages are allocated from two mixed extent, while rebuilding the index there is possibility of allocating pages from more than 2 extents and maximum of seven extents which in turn increase the fragmentation. 
  • Even the pages are allocated from uniform extent , there is possibility of fragmentation. When the size of index grow , it need more pages in the non leaf level also.If last page allocated to leaf level is 250 and to accommodate more row in the leaf lever index structure might need a page in index level 1, then SQL server storage engine allocate page 251 to the index level 1 which create fragment in the leaf level.
  • Other common reason  is the page split  due to the DML operations . This I have explained well in my previous post.Rebuild/Reorganize  index may not be effective to fix fragmentation happened due to the fist two reason, but it can reduce the fragmentation caused by the page split or delete operation. 
    • In our environment we follow the index maintenance  as given below:
      • 20 to 40 percentage of fragmentation is handled with reorganizing the index.
      • All index which has more 40 percentage fragmentation will considered for rebuild
      • Index which has less than 1000 pages will be ignored by the index maintenance logic.
      • Index which has more than 50K pages and fragmentation between 10 and 20 will also be considered for Reorganize.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba







15 comments:

  1. Very nice and well explained post ..

    ReplyDelete
  2. The second of two very good posts. Very clear explanations in both. Thank you.

    ReplyDelete
  3. Hi
    you may want to change this
    SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 END FROM @Fillfactor

    to this
    SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 ELSE run_value END FROM @Fillfactor
    regards
    Perry

    ReplyDelete
    Replies
    1. In my environment run value is 0 ...Thank you for pointing it out. I am going correct it

      Delete
  4. John, thanks for these articles. We've been using Ola Hallengren's IndexOptimize scripts for years. It only looks at indexes where index_level=0 and uses only the LIMITED switch when querying the DMV. What are we missing by only using the LIMITED switch and not looking at index_level>0 It seems that the fragmented indexes where index_level>0 have fairly small page counts. Our DB is 1.7 TB with 60 million primary records -- some tables have nearly 200 million records. Many hundreds of indexes.

    ReplyDelete
    Replies
    1. Alexander Suprun20 May 2012 at 13:36

      http://msdn.microsoft.com/en-us/library/ms188917(v=sql.100).aspx

      Delete
  5. Hello Nelson,

    Nice blog! I am cursing myself now for not noticing this one the moment it was published!

    But, in my understanding, having a fill factor of 0 is the same as having a fill factor of 100 as both will not let any free space in the page and use up all the space to insert the records. I am not seeing a point why we should assign a 0 or a 100 the variable @DefaultFillFactor. Shouldn't it be something like

    SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 ELSE 80 END FROM @Fillfactor

    Considering 80 is an allowable FillFactor.
    Please let me know what you think about this.

    Thanks in advance,
    Ananthram

    ReplyDelete
    Replies
    1. Thank you for reading the article. In the first section ,measuring the internal fragmentation , I have put the statement correctly but in the other part the 'else' part was missing which caused the confusion.
      SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 END FROM @Fillfactor . Now I have corrected it as
      SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 else run_value END FROM @Fillfactor.
      This will help us to display the actual fill factor of the index. Yes fill factor 0 and 100 are same .I prefer to display it as 100 to avoid any confusion. Thank you for pointing it out.

      Delete
  6. Thanks for the scripts. I found that these run fine on SQL Server 2008R2 but a slight mod was needed using SSMS on 2012 for both queries

    change: sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'DETAILED')
    to: sys.dm_db_index_physical_stats(@DB_ID, NULL, NULL , NULL, 'DETAILED')

    and of course add
    DECLARE @DB_ID INT

    ReplyDelete
    Replies
    1. Hmmm... this carried over okay to my SQL 2012 SP1 CU2 environment... Maybe an RTM issue??

      SELECT
      object_id AS objectid,
      index_id AS indexid,
      partition_number AS partitionnum,
      avg_fragmentation_in_percent AS frag
      FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
      WHERE avg_fragmentation_in_percent > 15.0


      Delete
    2. Hello Sir ,

      I have one table and frgamentation two entry for same index\table
      I am allready drop and re-ceate but problem not solve and also change fillfactor .

      IndexName PercentFragment avg_page_space_used_in_percent NumPages
      DPCCalcOther46 0.542635658914729 89.8397949098097 1290
      DPCCalcOther46 87.5 65.7184334074623 8

      Please give me advise

      Delete
  7. Hello ,

    yes,

    Second thing why not change runtime use <@dbname>
    e.g.
    set @sql1 = 'USE ' + @Dbname + ';'

    EXEC sp_sqlexec @sql1
    print @sql1

    print perfect but database not change , How to change ?

    ReplyDelete
    Replies
    1. Alexander Suprun30 August 2013 at 08:05

      Because it changes context only within sp_sqlexec, but when it gets to print the current database changes back.
      You have to put the whole query in @sql1 in order to do what you want.

      Delete
  8. hello ,

    I want run time change when pass dbname
    when pass @dbid then dynamical change records ,
    ---
    SELECT object_name(IPS.object_id) AS [TableName],
    SI.name AS [IndexName],
    IPS.Index_type_desc,
    IPS.avg_fragmentation_in_percent,
    IPS.avg_page_space_used_in_percent,
    IPS.page_count
    FROM sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL , 'DETAILED') IPS
    JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
    JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
    WHERE IPS.avg_fragmentation_in_percent > 30

    --

    when first type manually write

    use

    SELECT object_name(IPS.object_id) AS [TableName],
    SI.name AS [IndexName],
    IPS.Index_type_desc,
    IPS.avg_fragmentation_in_percent,
    IPS.avg_page_space_used_in_percent,
    IPS.record_count,
    IPS.page_count,
    ips.index_level
    FROM sys.dm_db_index_physical_stats(db_id(N'dbname'), NULL, NULL, NULL , 'DETAILED') IPS
    JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
    JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
    WHERE IPS.avg_fragmentation_in_percent > 30


    this is work


    Regards ,

    ReplyDelete