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 (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – Few Links About SQLAuthority
Next Post
SQL SERVER – Introduction to sys.dm_exec_query_optimizer_info

Related Posts

28 Comments. Leave new

  • thank you, that’s helpful.

    Reply
  • 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

    Reply
  • sauras pandey
    June 10, 2015 4:49 pm

    what is index id 0 and name NULL in the result of this query????

    Reply
    • sauras pandey
      June 10, 2015 4:53 pm

      I can see for the same object id,it has entry for each index(which is fine) but it also have few entries with 0 index id and name is NULL. what is this???

      Reply
  • Thanks Pinal. Here is another query which returns very quickly.

    SELECT dbschemas.[name] as ‘Schema’,
    dbtables.[name] as ‘TableName’,
    dbindexes.[name] as ‘Index’,
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
    INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
    INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
    WHERE indexstats.database_id = DB_ID()
    ORDER BY indexstats.avg_fragmentation_in_percent desc

    Reply

Leave a Reply

Menu