Post reply

Check Fragmentation on All Indexes on Database

  • July 10, 2008 at 12:30 pm

    #193703

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

  • September 4, 2008 at 5:26 am

    #867036

    it doesn't work

  • September 4, 2008 at 8:53 am

    #867196

    YEs It doesn't work

    problem with object id and SAMPLE from proc !

  • June 4, 2009 at 2:45 pm

    #1004903

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

  • June 4, 2009 at 2:48 pm

    #1004905

    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.

  • June 4, 2009 at 3:11 pm

    #1004922

    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.

  • July 7, 2011 at 2:14 am

    #1349471

    @scott : +1 --

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

    PS.- i have many indexes for this database

  • July 7, 2011 at 9:22 am

    #1349775

    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 %

  • July 27, 2011 at 9:41 am

    #1359410

    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