Post reply

Reindexing

  • May 7, 2011 at 12:20 pm

    #389470

    I do reindex for 1 clustered index and 16 nonclusterd in my table using the cmd

    ALTER INDEX ALL ON [TBL name] REBUILD WITH (FILLFACTOR = 90)

    and i got the result as successful

    but when i checked the index using the below dmv

    select database_id,index_type_desc,avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(12,null,null,null,null)

    where object_id=2117373291

    it gives result as

    dbid index type desc avg fragmentation in percent

    12 CLUSTERED INDEX 0

    12 NONCLUSTERED INDEX 50

    12 NONCLUSTERED INDEX 50

    12 NONCLUSTERED INDEX 50

    12 NONCLUSTERED INDEX 50

    12 NONCLUSTERED INDEX 66.6666666666667

    12 NONCLUSTERED INDEX 50

    12 NONCLUSTERED INDEX 50

    12 NONCLUSTERED INDEX 50

    12 NONCLUSTERED INDEX 50

    12 NONCLUSTERED INDEX 66.6666666666667

    12 NONCLUSTERED INDEX 66.6666666666667

    12 NONCLUSTERED INDEX 66.6666666666667

    12 NONCLUSTERED INDEX 66.6666666666667

    12 NONCLUSTERED INDEX 50

    12 NONCLUSTERED INDEX 75

    12 NONCLUSTERED INDEX 50

    why the value exceeds more than 50 even after doin reindexing ...?? or am i doing something wrong

  • May 7, 2011 at 12:22 pm

    #1321408

    Let me guess, the indexes are 2-3 pages in size?

    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

  • May 7, 2011 at 12:43 pm

    #1321414

    How to identify that the indexes is for 2-3 pages ? if it is in that way can't i do reindexing for that ......???

  • May 7, 2011 at 12:47 pm

    #1321417

    Page count in sys.dm_db_index_physical_stats.

    It's not that you can't, it's that it's totally pointless. The point where reindexing has a performance impact is usually somewhere around 1000 pages.

    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

  • May 7, 2011 at 12:52 pm

    #1321419

    yeah i got it

    i had pasted the page_count value in this

    page_count

    204

    2

    4

    2

    2

    3

    2

    2

    2

    2

    3

    3

    3

    3

    4

    4

    2

  • May 8, 2011 at 2:15 pm

    #1321489

    As Gail mentioned, Fragmentation gets affected only if the Index pages are usually > 1000. With such small indexes you mentioned, reindex doesn't have any impact.

    Thank You,

    Best Regards,

    SQLBuddy.

  • May 9, 2011 at 2:43 am

    #1321585

    I usually just reindex when page count > 9999

Viewing 7 posts - 1 through 6 (of 6 total)

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