SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

Q. What is Fragmentation? How to detect fragmentation and how to eliminate it?

A. Storing data non-contiguously on disk is known as fragmentation. Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation. We can classify fragmentation into two types:

  • Internal Fragmentation: When records are stored non-contiguously inside the page, then it is called internal fragmentation. In other words, internal fragmentation is said to occur if there is unused space between records in a page. This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and therefore, to the indexes defined on the table. As these modifications are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time. This unused space causes poor cache utilization and more I/O, which ultimately leads to poor query performance.
  • External Fragmentation: When on disk, the physical storage of pages and extents is not contiguous. When the extents of a table are not physically stored contiguously on disk, switching from one extent to another causes higher disk rotations, and this is called Extent Fragmentation.

Index pages also maintain a logical order of pages inside the extent. Every index page is linked with previous and next page in the logical order of column data. However, because of Page Split, the pages turn into out-of-order pages. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page. This is called Logical Fragmentation.

Ideal non-fragmented pages are given below:

SQL SERVER - Fragmentation - Detect Fragmentation and Eliminate Fragmentation frag1

Solarwinds

Statistics for table scan are as follows:

  • Page read requests: 2
  • Extent switches: 0
  • Disk space used by table: 16 KB
  • avg_fragmentation_in_percent: 0
  • avg_page_space_used_in_percent: 100

Following are fragmented pages:

SQL SERVER - Fragmentation - Detect Fragmentation and Eliminate Fragmentation frag2

In this case, the statistics for table scan are as follows:

  • Page read requests: 6
  • Extent switches: 5
  • Disk space used by table: 48 KB
  • avg_fragmentation_in_percent > 80
  • avg_page_space_used_in_percent: 33

How to detect Fragmentation: We can get both types of fragmentation using the DMV: sys.dm_db_index_physical_stats. For the screenshot given below, the query is as follows:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

SQL SERVER - Fragmentation - Detect Fragmentation and Eliminate Fragmentation frag3

Along with other information, there are two important columns that for detecting fragmentation, which are as follows:

  • avg_fragmentation_in_percent: This is a percentage value that represents external fragmentation. For a clustered table and leaf level of index pages, this is Logical fragmentation, while for heap, this is Extent fragmentation. The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.
  • avg_page_space_used_in_percent: This is an average percentage use of pages that represents to internal fragmentation. Higher the value, the better it is. If this value is lower than 75%, some corrective action should be taken.

Reducing fragmentation:

  • Reducing Fragmentation in a Heap: To reduce the fragmentation of a heap, create a clustered index on the table. Creating the clustered index, rearrange the records in an order, and then place the pages contiguously on disk.
  • Reducing Fragmentation in an Index: There are three choices for reducing fragmentation, and we can choose one according to the percentage of fragmentation:
    • If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEX REORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running.
    • If avg_fragmentation_in_percent > 30%, then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. In such case, we can also use the drop and re-create index method.
    • (Update: Please note this option is strongly NOT recommended)Drop and re-create the clustered index: Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – The server network address “TCP://SQLServer:5023” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
Next Post
SQLAuthority News – SQL Server Migration QuickStart

Related Posts

75 Comments. Leave new

  • The query needs a slight change to it. I was running the query and was not getting any information for the Object_Name call.

    It may be the version of management studio I am running SQL 2012, but I had to modify and add the database_id to call.

    From this
    OBJECT_NAME(OBJECT_ID)

    To this

    OBJECT_NAME(OBJECT_ID, Database_ID)

    Reply
  • Brilliant! Thank you!

    Reply
  • we have one sp.It is taking 7 mins to generate the report.Can u solve this

    Reply
  • You are awesome ever
    Easy to get the content
    Thanks a lot

    Reply
  • I am sure you guys can make on a lot better then this, but I wrote a small procedure to schedule the maintenance as explained in this article:

    /*Perform a ‘USE ‘ to select the database in which to run the script.*/

    — Declare variables
    SET NOCOUNT ON
    DECLARE @tablename VARCHAR (128)
    DECLARE @IxName VARCHAR (255)
    DECLARE @Avg_Frag float
    DECLARE @Avg_Page float
    DECLARE @Action NVARCHAR(10)
    DECLARE @Query NVARCHAR(700)

    — Declare cursor
    DECLARE tables CURSOR FOR
    SELECT SO.name, SI.name,avg_fragmentation_in_percent,avg_page_space_used_in_percent,
    (CASE WHEN avg_fragmentation_in_percent > 30 OR avg_page_space_used_in_percent 5.0 –Only indexes with fragmentation
    AND sys.dm_db_index_physical_stats.record_count > 1000 –Only table with more then 1000 records
    AND sys.dm_db_index_physical_stats.index_id > 0

    — Open the cursor
    OPEN tables

    — Loop through all the tables in the database
    FETCH NEXT
    FROM tables
    INTO @tablename,@IxName, @Avg_Frag,@Avg_Page,@Action

    WHILE @@FETCH_STATUS = 0
    BEGIN
    — Do the showcontig of all indexes of the table
    set @Query = null

    IF @Action = ‘Rebuild’
    BEGIN
    set @Query = ‘ALTER INDEX ‘ + rtrim(@IxName) + ‘ ON ‘ + rtrim(@tablename) + ‘ REBUILD’
    print @Query
    EXEC sp_executesql @Query
    END

    IF @Action = ‘Org’
    BEGIN
    set @Query = ‘ALTER INDEX ‘ + rtrim(@IxName) + ‘ ON ‘ + rtrim(@tablename) + ‘ REORGANIZE’
    print @Query
    EXEC sp_executesql @Query
    END

    FETCH NEXT
    FROM tables
    INTO @tablename,@IxName, @Avg_Frag,@Avg_Page,@Action
    END

    — Close and deallocate the cursor
    CLOSE tables
    DEALLOCATE tables

    Reply
    • DECLARE tables CURSOR FOR
      SELECT SO.name, SI.name,avg_fragmentation_in_percent,avg_page_space_used_in_percent,
      (CASE WHEN avg_fragmentation_in_percent > 30 OR avg_page_space_used_in_percent 5.0 –Only indexes with fragmentation
      AND sys.dm_db_index_physical_stats.record_count > 1000 –Only table with more then 1000 records
      AND sys.dm_db_index_physical_stats.index_id > 0 , Getting error here , Can you correct and post the updated one

      Reply
  • backup started at 2 pm and completed at 6.00pm ,in between this time if any transaction as occured will it be retrieved or lost.

    Reply
  • Hi Pinal,

    The article is really helpful.
    I need your guidance for few things.
    I have a SQL database (size is more than 800 GB). The database is a central database, it gathers data from different 200 + source databases (can grow to 1500 databases) through Microsoft sync service.
    The sync service runs every hour to sync data from all sources to this central database.
    There are also few jobs which run every hour to process data(SP is executed in job).

    Now every hour database tables goes under heavy deletes, updates and inserts.
    I tried the same query to detect fragmentation on one table, it took more than hour and did not complete i stopped it.

    Please guide me how to detect fragmentation in this case ? why the query takes long time ? what can be ways to detect fragmentation and maintain the database ?
    do i need to take a downtime (stop every job and service) to execute the script and detect fragmentation ?

    Reply
    • You need to figure out the waits which are happening in the SQL Server. Fragmentation removal may not be a solution for you.

      Reply
  • Hi sir i have read soo many of your articles but i have some small small doubts in many . I know how to perform fragmentation but i don’t know when to perform fragmentation. What causes us to check for fragmentation.

    Reply
  • Dear Pinal,
    We have discussed extensively on fragmentation removal in case a clustered index is already present. But reducing the same in a heap is still unanswered (as creation of an index is not recommended by Paul). Kindly suggest for this as well.

    Regards,
    Mayank

    Reply
  • Jim Johnston
    June 8, 2017 6:45 am

    I am trying to use this sys.dm on a SQL2014 database. Do you have any idea why the view doesnt exist? Is there something I need to invoke to cause it to be available? It works in many other installations I have, but I’ve looked and it is not there in this particular instance.

    Reply
  • Jim Johnston
    June 13, 2017 1:50 am

    It turned out I was looking at a case sensitive server. I currently manage 200+ SQL instances, and for some reason, this is the only instance with that setting (SAP, I now know is case sensitive by default). It was dumb of me not to realize it. I changed all my scripts to be sure they will work, when/if I ever work on another case sensitive server – DOH!!!

    Thank you for getting back to me.

    Reply
  • Hi Pinal

    I have a table with 6.3 mil rows, a unique clustered index on this table with combination of 4 fields. This index always show 100% avg_fragmentation_in_percent, index level 2, pages =8. I have tried reorganize, rebuild and even tried dropping them and creating again. However, each time I check (even immediately after recreating the indexes) the avg_fragmentation_in_percent shows 100%. I have pasted the create syntax below for your reference.

    Can you please suggest if I am missing anything, or any help to resolve this is appreciated.

    Thanks
    Joy

    CREATE UNIQUE CLUSTERED INDEX [CIX_RCP_xxxxxx] ON [dbo].[RCP_xxxxxx]
    (
    [AwId] ASC,
    [KType] ASC,
    [SortNumber] ASC,
    [LabourHours] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    Reply
  • Hi Pinal,
    Do we need to rebuild non-clustered indexes after we rebuilt clustered ones on the same table?

    Reply
  • Awesome. Its pretty good and clear… Thanks a lot!

    Reply
  • Hi Pinal,
    a quick question (I hope): if there is more than one index to rebuilt/reorganize, would you do it to the entire table or just to the single indexes?
    Thanks

    Reply

Leave a Reply

Menu