Script to find the Fragmentation of indexes

Script to find the Fragmentation in Indexes

In my previous articles, Fragmentation in SQL Server and Rebuild And Reorganization of Indexes , we talk in detail about Index fragmentation and rebuild or reorganize the indexes. Below is the SQL script which we can use to find the Fragmentation in Indexes created on the database.

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'Database name') , 
NULL, NULL, NULL , 'SAMPLED') ORDER BY avg_fragmentation_in_percent DESC

avg_fragmentation_in_percent represents  logical fragmentation.

If this value is higher than 5% and less than 30%, then we should use

ALTER INDEXREORGANIZE

If this value is higher than 30%, then we should use

ALTER INDEX REBUILD WITH (ONLINE = ON)

Please note:- As per MSDN, rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.

Re-commentated Articles On Indexes

Fragmentation in SQL Server

Indexes in SQL Server

Rebuild And Reorganization of Indexes

 

 

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in DBA, SQL Advanced, SQL Indexes, SQL Server and tagged , . Bookmark the permalink.

33 Responses to Script to find the Fragmentation of indexes

  1. Anonymous says:

    OBJECT_NAME() will be scoped to the current database, which is master in your example, despite the OBJECT_ID being from another database, so that could return nothing or name of an object from the master database if one exists with the same OBJECT_ID. Also, online index rebuilds are only available in Enterprise (or Developer) edition, so many people can't use that feature.

  2. admin says:

    Thanks For Correction. I have remove the Use Master command.:-)

  3. Anonymous says:

    script that works (subst function: do yourself).

    What I do NOT understand: there are some tables I reorganize over and over again, but fragmentation stays high, up to 66 %.

    alter procedure _adm_reorg_ix
    as
    /*
    if @@error = 0
    exec _adm_reorg_ix
    */

    declare @c cursor
    set @c = cursor for
    select sql from ( — a
    SELECT
    tab_name= o.name,
    ind_name = i.name,
    avg_fragmentation_in_percent,
    page_count,
    sql = case
    when avg_fragmentation_in_percent < 5 then
    ''
    when avg_fragmentation_in_percent < 30 and page_count > 100 then
    dbo.subst2('ALTER INDEX $1 on $2 reorganize',i.name,o.name) collate database_default
    else
    dbo.subst2('ALTER INDEX $1 on $2 rebuild WITH (ONLINE = OFF)',i.name,o.name) collate database_default
    end,
    s.index_id,
    s.index_type_desc,
    s.index_level,
    avg_page_space_used_in_percent
    FROM
    master.sys.dm_db_index_physical_stats (DB_ID(db_name()) , NULL, NULL, NULL , 'SAMPLED') s
    left join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
    cross apply ( values (OBJECT_NAME(s.OBJECT_ID))) o (name)
    –order by 3 desc
    ) a
    where sql <> ''

    declare @sql varchar(300)
    open @c
    while 0= 0 begin
    fetch next from @c into @sql
    if @@fetch_status <> 0 break
    print @sql
    exec (@sql)
    end
    go

  4. Pingback: official site

  5. Pingback: have a peek at these guys

  6. Pingback: เงินด่วน

  7. Pingback: Customer Service/Call Center

  8. Pingback: google analytics wordpress

  9. Pingback: Global Recession 2020 Prediction Has Globalization Failed?

  10. Pingback: rajabandarq

  11. Pingback: http://3wieku.pl/

  12. Pingback: Car Crashes Compilation Russia/USA/Europe 2019 HD

  13. Pingback: w88th

  14. Pingback: 카지노사이트

  15. Pingback: Intendente

  16. Pingback: dang ky bong88

  17. Pingback: nha cai fun88

  18. Pingback: https://www.olx.pl/

  19. Pingback: website design company

  20. Pingback: bags game

  21. Pingback: Darknet

  22. Pingback: vao m88 nhanh nhat

  23. Pingback: شركة تنظيف بالبخار بجدة

  24. Pingback: parisqq

  25. Pingback: tips dokter

  26. Pingback: beasiswa etos 2020

  27. Pingback: elang qq

  28. Pingback: daftar warnetqq

  29. Pingback: Klik hier

  30. Pingback: tìm kiếm vé máy bay giá rẻ

  31. Pingback: link 12 bet

  32. Pingback: Freelance WordPress Developer London

  33. Pingback: duratrans nyc

Leave a Reply