My Adventures in Coding

May 27, 2013

SQL Server – Check Index Fragmentation on ALL Indexes in a Database

Filed under: SQL,SQL Server — Brian @ 11:46 am
Tags: , , , ,

Often, when I am using a development or test environment and I run into a situation where a query is behaving slower than normal, first thing I want to rule out is “Do I have any fragmented indexes?”. Here is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation. This query will work on SQL2K5 or newer.

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
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

That should do the trick!

Advertisements

29 Comments »

  1. Thanks a million! It works like a charm!

    Comment by Steve — June 29, 2013 @ 8:11 am | Reply

  2. Some of the index names show up as NULL. Any idea what causes this? (heaps maybe?)

    Great script, I had one that was for a single table but this is extremely helpful for global look.

    Comment by Tim M. Hidalgo — September 3, 2013 @ 4:07 pm | Reply

    • Did some reading in SQL Server 2008 Internals. A heap in the sys.indexes table has an index_id value of 0 and the name column contains NULL

      Great work!

      Cheers

      Comment by Tim M. Hidalgo — September 9, 2013 @ 2:50 pm | Reply

  3. […] found a really cool example for getting all the fragmentation on a database.  The example was so cool that I decided to adapt my previous script to this one and made a couple […]

    Pingback by Check Index Fragmentation…Further | SQL TAO — September 5, 2013 @ 3:22 pm | Reply

  4. I was trying it on SQL 2012 and it does not work 😦 but it does in SQL 2005

    Comment by Kilo — December 3, 2013 @ 12:13 pm | Reply

    • sorry my mistake it does work in SQL 2012, I just found out that in order to run this query you must be positioned in master schema

      Comment by Kilo — December 3, 2013 @ 12:19 pm | Reply

      • Hi, ah yes that is right. You must be in the master schema to run the query. Thanks for confirming it works on SQL Server 2012, right now my company is on SQL Server 2008 so I haven’t had a chance yet to try the query on SQL Server 2012.

        Comment by Brian — December 3, 2013 @ 1:15 pm

  5. Thank you,

    It was very helpful to get the detailed output.

    Comment by Dinesh — February 5, 2014 @ 7:54 am | Reply

  6. Hello, the script is very usefull to find all the fragmented indexes, what I wonder is how can I defragment all those indexes that have avg_fragmentation_in_perecent is more than 10-15? I have at least 100 indexes like that and stragling. Any advise would be much appreciated

    Comment by JD — April 9, 2014 @ 2:05 am | Reply

  7. Thank you – this worked very well. 😉

    Comment by Joshua Burstyn — May 20, 2014 @ 11:44 am | Reply

  8. Thanks! Just what I was looking for!

    Comment by Ada — August 12, 2014 @ 10:30 am | Reply

  9. […] found a really cool example for getting all the fragmentation on a database.  The example was so cool that I decided to adapt my previous script to this one and made a couple […]

    Pingback by Check Index Fragmentation…Further | codepimp — September 3, 2014 @ 4:27 pm | Reply

  10. do I need to paste this only and no need to change the code? like I need to modify
    SELECT dbschemas.[name] as ‘Schema’,
    dbtables.[name] as ‘Table’,
    dbindexes.[name] as ‘Index’,

    Sorry i’m noob,

    Comment by Ally — September 18, 2014 @ 5:36 am | Reply

    • Yes, you can just cut and paste this query and use it as it is. In SQL Server reserved words need to be escaped using square brackets. So for example the “name” column in dbschemas must be escaped using “[name]” in the query to avoid any confusion with the interpreter.

      Comment by Brian — September 18, 2014 @ 8:12 am | Reply

      • Thanks. I just got confused. When I run the query.. it has no error in the code itself but my user has no permission.. hahah weird i’m and administrator myself but the database and me have trust issue. Thanks!

        Comment by Ally — September 18, 2014 @ 11:53 am

  11. hi… first excellent post!!!…thank you,

    In case anyone need to run this code against all databases in the server…..

    — variable for the sql command

    DECLARE @command VARCHAR(5000)

    — Create a temporary table
    create table #Index_Fragmentation (
    database_name varchar (100),
    Eschema varchar(100),
    xTable varchar (200),
    xIndex varchar (max),
    avg_fragmentation_percent int,
    page_count int
    )

    — load the @command variable with the query

    SELECT @command = ‘Use [‘ + ‘?’ + ‘]
    SELECT db_name(database_id) db_Name, dbschemas.[name] as ESchema,
    dbtables.[name] as xTable,
    dbindexes.[name] as xIndex,
    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’

    — insert data into temporary table

    insert into #Index_Fragmentation
    EXEC sp_MSForEachDB @command — executes against all databases in current server\instance

    — here you can change the query for your needs

    select * from #Index_Fragmentation
    where database_name not in (‘msdb’,’master’)
    and avg_fragmentation_percent > 50
    order by avg_fragmentation_percent desc;

    — clean up

    drop table #Index_Fragmentation;

    Comment by Orestes Arniella — November 26, 2014 @ 1:37 pm | Reply

    • I am getting a syntax error at this line SELECT @command = ‘Use [‘ + ‘?’ + ‘]. What do I need to change?
      I want to run this query on all databases on a server at one time.

      Comment by Lashonda Greenup — August 5, 2015 @ 3:01 pm | Reply

      • The issue with the script was just how WordPress formatted the SQL script’s comments and quotes in the post. Once fixing those two issues after copying and pasting the script, it works great!.

        
        DECLARE @command VARCHAR(5000)
        
        -- Create a temporary table
        create table #Index_Fragmentation (
        database_name varchar (100),
        Eschema varchar(100),
        xTable varchar (200),
        xIndex varchar (max),
        avg_fragmentation_percent int,
        page_count int
        )
        
        -- load the @command variable with the query
        
        SELECT @command = 'Use [' + '?' + ']
        SELECT db_name(database_id) db_Name, dbschemas.[name] as ESchema,
        dbtables.[name] as xTable,
        dbindexes.[name] as xIndex,
        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'
        
        -- insert data into temporary table
        
        insert into #Index_Fragmentation
        EXEC sp_MSForEachDB @command -- executes against all databases in current server\instance
        
        -- here you can change the query for your needs
        
        select * from #Index_Fragmentation
        where database_name not in ('msdb','master')
        and avg_fragmentation_percent > 50
        order by avg_fragmentation_percent desc;
        
        -- clean up
        
        drop table #Index_Fragmentation;
        

        Comment by Brian — August 10, 2015 @ 10:30 am

  12. In my case it was giving me an syntax error near indexstats

    Comment by raja — December 5, 2014 @ 12:33 am | Reply

  13. here i found one more very useful article.
    http://www.passionforsql.com/how-to-check-index-fragmentation-in-sql-server/

    Comment by Chandrej — February 21, 2015 @ 9:12 am | Reply

  14. — I have created a view to be able to return this data easily whenever I want to do so.
    — I have also added a couple of extra columns to show what type of index it is etc
    — and I’ve truncated the two decimal fields at 2DP to make them easier to read

    If Object_ID(‘IndexFragmentation’) Is Not Null DROP VIEW IndexFragmentation
    go
    Create View IndexFragmentation as
    SELECT top 100 percent dbschemas.[name] as ‘Schema’, dbtables.[name] as ‘Table’,
    dbindexes.[name] as ‘Index’, dbindexes.index_id as IdxID,
    cast(indexstats.avg_fragmentation_in_percent as decimal(6,2)) as Prcent,
    indexstats.page_count, replace(index_type_desc,’ index’,”) as IndxType,
    fragment_count as Fragments, index_depth as IdxDeep,
    cast(avg_fragment_size_in_pages as decimal(10,2)) as AvgFragSize
    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()
    and cast(indexstats.avg_fragmentation_in_percent as decimal(6,2))>0
    ORDER BY indexstats.avg_fragmentation_in_percent desc
    go
    select * from IndexFragmentation order by prcent desc
    go

    Comment by Glyn Roberts — March 16, 2015 @ 11:43 pm | Reply

    • I have tried out the view and it works great. Thanks for posting!

      Comment by Brian — March 17, 2015 @ 9:41 am | Reply

  15. […] From My Adventures In Coding […]

    Pingback by Show index fragmentation on all tables - Matt — May 20, 2015 @ 4:20 pm | Reply

  16. thank you for sharing.

    Comment by wiwats — June 20, 2015 @ 3:13 am | Reply

  17. Hi. I’m a noob here. What do I need to do if the indexstats.avg_fragmentation_in_percent is really high? e.g. over 80 for multiple indexes??

    Comment by Joe — October 7, 2015 @ 8:03 pm | Reply

  18. IN THE FROM SECTION BELOW I APPARENTLY NEED TO ADD MY DATABASE NAME/SERVER NAME NOT SURE

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

    Comment by DEBI — February 9, 2016 @ 10:47 am | Reply

  19. Thank you, so much this helps me a lot.I found one more article where i got the brief description about Fragmentation In SQL Server Database:
    http://www.sqlserverlogexplorer.com/how-to-find-database-fragmentation/

    Comment by Johnson Weltch — November 25, 2016 @ 5:45 am | Reply

  20. […] Source : My Adventures in Coding […]

    Pingback by SQL : profiler dans Azure | macreiben — February 3, 2017 @ 7:45 am | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: