SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table

One of my friend involved with large business of medical transcript invited me for SQL Server improvement talk last weekend. I had great time talking with group of DBA and developers. One of the topic which was discussed was how to find out Fragmentation Information for any table in one particular database. For SQL Server 2000 it was easy to find using DBCC SHOWCONTIG command. DBCC SHOWCONTIG has some limitation for SQL Server 2000.

SQL Server 2005 has sys.dm_db_index_physical_stats dynamic view which returns size and fragmentation information for the data and indexes of the specified table or view. You can run following T-SQL for any database to know detailed information of the database.
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('test_contig'), NULL, NULL , 'DETAILED')

Above query returns lots of information, most of the time we only need to know Tablename, IndexName and Percentage of Fragmentation. Following query returns only three most important details mentioned earlier. I have added an extra condition where results are filtered where average fragmentation is greater than 20%.

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20

The results will help DBA to make necessary reports.

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

About these ads

17 thoughts on “SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table

  1. My two main questions regarding this DMV are the following:

    1) Using the DETAILED option includes the non-zero index-levels as well (non-leaf pages). According to many source, this should also be actioned, seeing that this indicates “logical” fragmentation (meaning that the leaf pages are not in the order of the non-leaf keys any more). I cannot find anything conclusive on this. Where as the index level=0 occurrences, indicates extent fragmentation. We are using this this dmv, to check fragmentation-levels first, and based on that, do the necessary re-index/reorg. Currently we are using the LIMITED option (which only shows leaf-level), but we seem to be missing indexes that needs to be re-indexed/re-orged.

    2) Secondly, the stats that this function return, does not seem to be reliable. I ran the function prior to an ALTER…. REBUILD, and then again thereafter. There were hardly any differences in the stats. Is it now a case of the stats being unreliable, or the REBUILD not being effective?

    I do not want to be in the situation where we miss indexes because of unreliable stats. We already suspect that this could be the case.

    Can you please comment on this.

    Like

  2. Hello,
    pl any can tell me what is exact justification report for doing defragmentation in sql 2005
    i m and understanding the result of showcontig commands output . it shows Scan Density, Pages plese tell me what is this and how should i know that fragmentation is increases on what factos

    pl help
    Thanks

    Like

  3. Hello,
    pl any can tell me what is exact justification report for doing defragmentation in sql 2005
    i m not understanding the result of showcontig commands output . it shows Scan Density, Pages plese tell me what is this and how should i know that fragmentation is increases on what factos

    pl help
    Thanks

    Like

  4. Hi Pinal

    I am trying to troubleshoot some fragmentation. I saw the script you posted at the beginning which identifies which tables are fragmented etc.

    This is very useful as when you query against ‘sys.dm_db_index_physical_stats’ it only displays object id’s.

    I ran the script & gave the following error “Msg 195, Level 15, State 10 Line 1
    ‘OBJECTNAME’ is not a recognised built-in function name’.

    I would appreciate if you could help me how this error can be resolved.

    If anyone else is able to run this script, pls let me know.

    Thanks
    Anup..

    Like

  5. Pingback: SQL SERVER – Cleaning Up SQL Server Indexes – Defragmentation, Fillfactor – Video « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

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