Post reply

Index fragmentation

  • June 5, 2009 at 1:16 pm

    #155791

    I query the avg_fragmentation_in_percent of sys.dm_db_index_physical_stats. One of the index was 85 so I did alter index..REBUILD. The same percent showed up after I did that. Then I dropped the index and re-created it again. The same percent showed up.

    Is something wrong with my query? Or I have to do something else to de-frag the index?

    Here is my query.

    SELECT ps.database_id, ps.OBJECT_ID,

    ps.index_id, b.name,

    ps.avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

    AND ps.index_id = b.index_id

    WHERE ps.database_id = DB_ID()

    ORDER BY b.name

  • June 5, 2009 at 1:26 pm

    #1005505

    what is the output of this.

    select * FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'detailed')

    Thanx.
    Vinay

    http://rdbmsexperts.com/Blogs/
    http://vinay-thakur.spaces.live.com/
    http://twitter.com/ThakurVinay

  • June 5, 2009 at 2:08 pm

    #1005518

    I did the query on one table .

    The result:

    index id index_type_desc Alloc_unit index index avg frag fragcnt avg frag page Avg page space record cnt

    dep level in percent size in page cnt used in percent

    1 CLUSTERED INDEXIN_ROW_DATA2 0 0 1 43 43 91.20365702989871046

    1 CLUSTERED INDEXIN_ROW_DATA2 1 0 1 1 1 9.0066716085989643

    2NONCLUSTERED INDEX IN_ROW_DATA2 066.666666666666731 377.5142080553496 1046

    2NONCLUSTERED INDEXIN_ROW_DATA2 1 0 1 1 10.864838151717321 3

    3NONCLUSTERED INDEXIN_ROW_DATA2 066.666666666666731 386.12965159377321046

    3NONCLUSTERED INDEXIN_ROW_DATA2 1 0 1 1 1 0.9389671361502353

    4NONCLUSTERED INDEXIN_ROW_DATA2 066.666666666666731 3 77.51420805534961046

    4NONCLUSTERED INDEXIN_ROW_DATA2 1 0 1 1 1 0.8648381517173213

    5NONCLUSTERED INDEXIN_ROW_DATA2 066.666666666666731 377.51420805534961046

    5NONCLUSTERED INDEXIN_ROW_DATA2 10 1 1 10.8648381517173213

    6NONCLUSTERED INDEXIN_ROW_DATA2 075 4 1 4 77.51420805534961046

    6NONCLUSTERED INDEXIN_ROW_DATA2 10 1 1 1 1.45787002718063 4

  • June 5, 2009 at 2:08 pm

    #1005519

    BTW I am using SQL Server 2005 Standard version.

  • June 5, 2009 at 2:25 pm

    #1005523

    Those indexes are tiny. Fragmentation only really becomes a noticable problem on indexes that have more than 1000 pages. On very, very small indexes, the way the first few pages are allocated means that they will not defrag completely. Don't worry on very small indexes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 5, 2009 at 2:31 pm

    #1005526

    My I suggest that you rerun that last query and put the data into an Excel spreadsheet and attach that? I can't tell which values belong to header, and it appears you attempted to edit it some as some of the solumn headers appear to be missing.

  • June 5, 2009 at 2:40 pm

    #1005532

    So if the page count is less than 100, even the fragment in percent is high, I don't have to worry about it?

  • June 5, 2009 at 2:49 pm

    #1005542

    could be Yes, see index means physical ordering of the leaf level records on clustered index key, with maintaining fillfactor, that way if you have very few records with small column width(which can fit in some pages/extents) you dont need to worry abt those fragmentation as it will maintiain the fill factor, also its will be ok and will help to reduce the page split if you insert/update the records.

    HTH.

    Vinay

    Thanx.
    Vinay

    http://rdbmsexperts.com/Blogs/
    http://vinay-thakur.spaces.live.com/
    http://twitter.com/ThakurVinay

  • June 5, 2009 at 2:53 pm

    #1005546

    Loner (6/5/2009)

    So if the page count is less than 100, even the fragment in percent is high, I don't have to worry about it?

    1000 pages. Not 100.

    1000 pages is about the point where fragmentation starts to affect scans of the index. Note, only scans. Singleton index seeks are not affected by fragmentation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 5, 2009 at 8:42 pm

    #1005614

    Does the table have a clustered index anywhere on it?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 6, 2009 at 6:22 am

    #1005683

    GilaMonster (6/5/2009)

    Loner (6/5/2009)

    So if the page count is less than 100, even the fragment in percent is high, I don't have to worry about it?

    1000 pages. Not 100.

    1000 pages is about the point where fragmentation starts to affect scans of the index. Note, only scans. Singleton index seeks are not affected by fragmentation.

    I follow this approach also. It was recommended to me by a consultant from Microsoft PSS when he came in for a health check session.

Viewing 11 posts - 1 through 10 (of 10 total)

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