x

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.

more ▼

asked Apr 08, 2010 at 12:34 PM in Default

avatar image

Leo
1.6k 55 59 62

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Apr 08, 2010 at 01:25 PM

avatar image

DaniSQL
4.9k 33 39 43

thanks for providing a useful script.

Apr 08, 2010 at 06:46 PM CirqueDeSQLeil
  • I use Ola script too.

Apr 09, 2010 at 03:21 AM sp_lock
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 08, 2010 at 12:49 PM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

I would also recommend using that same script.

Apr 08, 2010 at 01:08 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Apr 08, 2010 at 06:45 PM

avatar image

CirqueDeSQLeil
5.5k 11 13 20

  • for modifying the script and making it much better.

Apr 08, 2010 at 09:58 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x150
x25

asked: Apr 08, 2010 at 12:34 PM

Seen: 1715 times

Last Updated: Apr 08, 2010 at 01:15 PM

Copyright 2017 Redgate Software. Privacy Policy