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 (https://blog.sqlauthority.com)
15 Comments. Leave new
Hello sir,
I am new in cube.
Can u give some examples about cube?
Thanks
JSaraboji.
thanks for the script, it has help greatly, now i know which tables have to be defrag… many thanks!!!
so then all the tables in that query should be defragmented correct?
when i ran dbcc showcontig (tablename, indexname)
i got different results, why?
i am using sql server 2005
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.
i agree the stats of this script seem unreliable.
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
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
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..
Hi Pinal
I had a typo in object_name which is why it failed. The script ran fine.
Cheers
Anup..
can anyone plz explain me use of index with example of create and display index querys…
How does fragmentation effects the performance or other things?
Quick respose is much appriciated..
syntax error, why is that?
hi…am create a index in sql 2008 but how can i show index values ? plz help me?
It works if I run it on master schema but if I change it to myDatabase I get the following error: Incorrect syntax near ‘(‘.