Check Fragmentation on All Indexes on Database

  • augusto.alfonso

    SSC Enthusiast

    Points: 108

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

  • peymaster-660178

    SSC Veteran

    Points: 204

    it doesn't work

  • badot

    Grasshopper

    Points: 16

    YEs It doesn't work

    problem with object id and SAMPLE from proc !

  • Scott Grant

    Old Hand

    Points: 380

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

  • Scott Grant

    Old Hand

    Points: 380

    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.

  • Scott Grant

    Old Hand

    Points: 380

    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.

  • aby_dvd

    Ten Centuries

    Points: 1310

    @scott : +1 --

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

    PS.- i have many indexes for this database

  • augusto.alfonso

    SSC Enthusiast

    Points: 108

    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 %

  • Eric Willemstein

    SSChasing Mays

    Points: 610

    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 9 (of 9 total)

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