SQL SERVER – 2005 – Find Index Fragmentation Details – Slow Index Performance

Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I wrote to find fragmentation of the database for all the indexes.

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO

You can REBUILD or REORGANIZE Index and improve performance. Here is article SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script for how to do it.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

25 thoughts on “SQL SERVER – 2005 – Find Index Fragmentation Details – Slow Index Performance

  1. Hi there – any chance of amending the above script to show the actual table name where the index resides…..

    Thanks

    Like

  2. select tbl.[name] TableName,
    ind.[name] IndexName,
    mn.index_type_desc IndexType,
    mn.avg_fragmentation_in_percent [FRAG_%]
    from sys.dm_db_index_physical_stats (null, null, null, null, null )as mn
    inner join sys.tables tbl on tbl.[object_id] = mn.[object_id]
    inner join sys.indexes ind on ind.[object_id] = mn.[object_id]
    where [database_id] = db_id(‘dbSynthetics’)
    order by mn.avg_fragmentation_in_percent desc

    Like

  3. This modification of the original will return the Parent table name.
    USE [your DB]
    GO
    SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
    (SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
    INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
    AND ps.index_id = si.index_id
    WHERE ps.database_id = DB_ID() AND si.name is not null AND
    ps.avg_fragmentation_in_percent > 30 — min % to return
    ORDER BY ps.avg_fragmentation_in_percent desc
    GO

    Like

  4. I tried teh above script but it give sme this error-
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near ‘(‘.

    Do I have to replace the exact Database name etc somewhere in this script.

    Like

    • You need to run it against the master database, other databases doesn’t contain the DB_ID() function select DB_ID(N’MyDataBaseName’) will return the database id.

      Like

  5. You can replace DB_ID() with the database ID:

    You can do this to find out the ID:

    USE yourdatabase
    SELECT DB_ID() AS [Database ID];
    GO

    Like

  6. I have tried to defrag the tables in my databases but have discovered that some indexes will not improve better than 85% total fragmentation. How can I overcome this problem? Or is this something that I should not be concerned with?

    Like

  7. Pinal,

    Why the script would not return any indexes and their statistics e.g % fragmented? Doe this mean ther are none indexes in this datbase thar are fragmented even a small percent. DO i need to run something else before i run this script.

    Like

    • Once you rebuild a script ti will clear the statistics on the related table – hence the above query will then not return any results. You will need to wait for the table statistics to build up again before getting a meaningful result set.

      Like

  8. Warning: On a large table, this command can take a very very long time to run… I have some indexes that are over 1GB in size (2.6M records) and looking at fragmentation on some individual indexes with properties can take more than 30 minutes and require over 3M physical I/O according to activity monitor.

    Like

  9. Hi,

    I am using the following query to get my index details to reorgnaize or rebuild them

    SELECT
    OBJECT_NAME(B.OBJECT_ID) as TableName, b.name as Index_Name,
    CASE
    WHEN ps.avg_fragmentation_in_percent > 30 THEN ‘REBUILD WITH (,PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=OFF,ALLOW_PAGE_LOCKS=OFF,ONLINE=ON,SORT_IN_TEMPDB = ON,MAXDOP=2)’ –FOR ONLINE ADD USE THIS ‘REBUILD WITH(ONLINE = ON)’
    ELSE ‘REORGANIZE’
    END AS INDEX_OPTION,avg_fragmentation_in_percent as Fragmentation_Size,
    ps.Page_count as TotalPage
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
    AND ps.index_id = b.index_id
    WHERE ps.database_id = DB_ID() AND b.name IS NOT NULL AND ps.avg_fragmentation_in_percent > 10
    ORDER BY TotalPage desc

    But its making my machine hang, I am unable to do anything after executing this. Could we do some improvement in this query.

    Like

  10. Is there a way to quantify how much query performance can/will suffer once an index becomes heavily fragmented? I have a client with a 365x24x7 system that suffers from locking/blocking during index optimizations, so they are considering reducing the frequency or doing away with index optimizations altogether for specific indexes. I don’t think this is a good idea, so I’d like to be able to quantify the performance impct that would result.

    Like

  11. this will loop thru all tables that have a frag% > 40 and also puts the schema in front of the table name.

    create table #frag(
    tabName varchar(200)
    )
    insert into #frag
    select S.name + ‘.’ + tbl.[name] TableName
    from sys.dm_db_index_physical_stats (null, null, null, null, null )as mn
    inner join sys.tables tbl on tbl.[object_id] = mn.[object_id]
    inner join sys.indexes ind on ind.[object_id] = mn.[object_id]
    inner join sys.schemas S on tbl.schema_id = S.schema_id
    where [database_id] = db_id() and mn.avg_fragmentation_in_percent > 40
    order by mn.avg_fragmentation_in_percent desc

    while exists(select top 1 tabName from #frag)
    begin
    declare @name as varchar(200), @sql varchar(1000)
    select top 1 @name = tabName from #frag
    set @sql = ‘ALTER INDEX ALL ON ‘ + @name + ‘ REBUILD’
    select @sql
    delete from #frag where tabName = @name
    exec(@sql)
    end

    drop table #frag

    Like

    • Hello prumery. I really like the script you have but can you tell me how i’d be able to return the output for all dbs on a given db server? Say if i join against the sys.databases catalog. I tried to tweak the script you had and it somewhat works, but only if i exec against a specific db. I have to pass in the the “use ” syntax and it works fine. But i was hoping to return the same frag info for all dbs on a given server.

      Here’s my updated script:

      –passing in for a specific db works fine
      use
      go

      –when i pass in to use master, i get a 0 result set
      –use master
      –go

      insert into #fragindex
      SELECT b.name,
      ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
      (SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
      FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
      INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
      AND ps.index_id = si.index_id
      inner join master.sys.databases b ON ps.database_id = b.database_id
      WHERE ps.database_id = DB_ID() AND si.name is not null
      AND ps.avg_fragmentation_in_percent >= 70
      and si.name not like ‘PK%’ –disregards primary key based indexes
      and b.database_id > 4
      –and b.name in (‘IBC’,’UBS’,’Petro’) –tried to use this to bring back frag info for specific set of dbs; doesnt work for me
      ORDER BY ps.avg_fragmentation_in_percent desc
      GO

      Like

  12. Bhagesh, Apple

    There a way to quantify how much query performance will suffer once an index becomes heavily fragmented? I have a client with a 365x24x7 system that suffers from locking/blocking during index optimizations, so they are considering reducing the frequency or doing away with index optimizations altogether for specific indexes. I knew that this is a good idea, so I’d like to be able to quantify the performance impct that would result.

    Like

  13. Hello all. How can i return the frag output for all dbs on a given db server? Say if i join against the sys.databases catalog.

    Here’s my current script:

    –passing in for a specific db works fine
    use [dbname]
    go

    –when i pass in to use master, i get a 0 result set
    –use master
    –go

    insert into #fragindex
    SELECT b.name,
    ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
    (SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
    INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
    AND ps.index_id = si.index_id
    inner join master.sys.databases b ON ps.database_id = b.database_id
    WHERE ps.database_id = DB_ID() AND si.name is not null
    AND ps.avg_fragmentation_in_percent >= 70
    and si.name not like ‘PK%’ –disregards primary key based indexes
    and b.database_id > 4
    –and b.name in (‘IBC’,’UBS’,’Petro’) –tried to use this to bring back frag info for specific set of dbs; doesnt work for me
    ORDER BY ps.avg_fragmentation_in_percent desc
    GO

    Like

  14. i got a script that automates the index maintenance (if 10% fragmented use reorganize, and if 30% or more use rebuild).. however after performing the reindex, the database queries somehow take longer to executed (lots of users got timeouts)… if i stop the job (no reindex is performed), the queries go back to normal time… do you know what happened? is it due to the statistics? thx

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s