question

Leo avatar image
Leo asked

Index Management

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.

sql-server-2008indexingfragmentation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image
DaniSQL answered

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!!

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - I use Ola script too.
1 Like 1 ·
thanks for providing a useful script.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

Michelle Ufford has some of the best fragmentation scripts at http://sqlfool.com/

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I would also recommend using that same script.
0 Likes 0 ·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for modifying the script and making it much better.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.