Hi,
Anybody know the script for viewing the fragmentation level of indexes by each table in database. I saw the script in SQLservercentral.com last couple of months before but I can't find it now. Anybody can tell me where can I get that?
Thanks.
asked
Index Management
I like scripts by Michelle regarding indexes which Kev points u above. I personally use Ola Hallengren awesome maintenance solution. It includes stored procedures for backup, Integrity check and index optimization. You can also download only the sp for index optimization and modify it for your needs. By default it will re-organize the index if fragmentation is below 30% and rebuild if it is above 30%. (you can modify that)
You can also modify and use this below script which will be enough for your purpose(if you just want the fragmentation level and are not going to reorganize or rebuild based on fragmentation level):
USE AdventureWorks
GO
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO
(**I am not the author and I found it on the net and also it might be slow for large DBs)
Happy Indexing!!
Slight Mod to the script provided by DaniSQL. I prefer to spell out the columns in the order by clause, and I also eliminated the DBName from the db_id() in the index_physical_stats function.
SELECT db_name(db_id()) as DBName,object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL , 'DETAILED') IPS
Inner Join sys.tables ST
ON IPS.object_id = ST.object_id
Inner Join sys.indexes SI
ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY IPS.avg_fragmentation_in_percent desc,TableName
GO
