Check Fragmentation on All Indexes on Database

  • Comments posted to this topic are about the item Check Fragmentation on All Indexes on Database

  • it doesn't work

  • YEs It doesn't work

    problem with object id and SAMPLE from proc !

  • so what updates did you make to get the script to work then?

  • so what updates did you make to get the script to work then? I can get the inidividual pieces to run, but cannot get the full script to produce resutls. I keep getting and invalid ' ' error message.

  • I have found that there are invisible characters from the original copy and paste from the original posting. I copied the script into Notepad and removed the "Box Characters" representing the invisible characters and then pasted the cleaned up version into query analyzer and it runs.

  • @scott : +1 --

    But, i am not seeing any results though commands have been completed sucessfully .

    PS.- i have many indexes for this database

  • Hi, It's proc works fine, because I sill use in production.

    SELECT sysobj.name object_name,

    sysobj.xtype object_type,

    indexes.name index_name,

    index_data.database_id,

    index_data.object_id,

    index_data.index_id,

    index_data.avg_fragmentation_in_percent,

    index_data.avg_fragment_size_in_pages,

    index_data.avg_page_space_used_in_percent,

    index_data.record_count

    FROM sys.dm_db_index_physical_stats ('db_id', NULL,NULL, NULL,'SAMPLED') index_data

    -- replace the table name by NULL

    inner join sys.sysobjects sysobj

    on index_data.object_id = sysobj.id

    left outer join sys.sysindexes indexes

    on index_data.index_id = indexes.indid

    and index_data.object_id = indexes.id

    WHERE (avg_fragmentation_in_percent > 10

    OR avg_page_space_used_in_percent < 90)

    -- avg_fragmentation_in_percent / logical scan fragmentation < 10%

    -- avg_fragment_size_in_page/ Extent Scan Fragmentation

    -- avg_page_space_used_in_percent / Avg. Page Density > 90 %

  • This works, remember to get your database id first. Good tool to help identify fragmented indexes.

    see http://msdn.microsoft.com/en-us/library/ms186274.aspx

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply