Post reply

Rebuilding Indexes Results in Double Entries in dm_db_index_physical_stats

  • September 22, 2009 at 2:17 pm

    #385409

    I rebuilt all 3 indexes on a table b/c of high fragmentation. Before rebuilding, the DMV returned 3 rows on this table, 1 per index.

    When I requery the stats for this table after rebuilding, I now have 6 rows returned: 2 per index, 1 each for the pre-rebuild and post-rebuild data. (1 row per index has a very high fragmentation percentage, the other row for that same index is 0).

    I figured I needed to UPDATE STATISTICS, but that didn't change the results.

    I'm sure there's something basic I'm missing here, but I cannot find any joy in BOL, SQLServerCentral, or Google.

    Here's what I did:

    1. Rebuild indexes with ALTER INDEX [indexname] on dbo.Grades REBUILD

    2. Then:

    select * from

    sys.dm_db_index_physical_stats(@dbid,135879751,NULL,NULL,'DETAILED')

    where @dbid is the value returned by DB_ID(), and 135879751 is the ID of the table [Grades].

    This returns 6 rows.

    Thanks,

    Rich

  • September 22, 2009 at 3:07 pm

    #1057158

    the old stats will age off after a while

    The probability of survival is inversely proportional to the angle of arrival.

  • September 22, 2009 at 3:21 pm

    #1057164

    If you are still seeing this then just refer to the exact index numbers which in your case is likely 1,2, and 3 when you call the sys.dm_db_index_physical_stats DMV.

  • September 23, 2009 at 5:53 am

    #1057424

    Thanks, Toby, but that won't work: the DMV has 2 rows per index. Two rows with index_id=1, two for index_id=2, and two for index_id=3.

  • September 23, 2009 at 5:55 am

    #1057425

    Thanks sturner. Two questions:

    How long is "a while" and how do I force a recalculation of those stats? It's a real problem if I'm trying to verify a database-wide index rebuild that, in fact, the fragmentation has been eliminated for very fragmented indexes.

    Yours,

    Rich

  • September 23, 2009 at 6:25 am

    #1057437

    Follow-up:

    The 2 rows in the DMV per index differ as follows:

    The row in the DMV with the original fragmentation % has an index_level=0, and the new row (with avg_fragmentation_in_percent=0) has index_level=1.

  • September 23, 2009 at 6:46 am

    #1057447

    rmechaber (9/23/2009)

    Thanks sturner. Two questions:

    How long is "a while" and how do I force a recalculation of those stats? It's a real problem if I'm trying to verify a database-wide index rebuild that, in fact, the fragmentation has been eliminated for very fragmented indexes.

    Yours,

    Rich

    That I can't answer but I would just use DROP STATISTICS on the ones you want to get rid of (or perhaps all of them) then recalculate statistics to make sure everything is cleaned up. Run sp_autostats on your table and see what it is reporting.

    Something else could have been done to the table that is causing this anomoly..,

    The probability of survival is inversely proportional to the angle of arrival.

  • September 23, 2009 at 6:52 am

    #1057457

    My apologies! I discovered that I was rebuilding indexes on the non-production version of the database (which has a nearly identical name as the production DB) and checking stats on the production DB.

    Also, I did some digging on the index_level and learned that the DMV reports fragmentation on each node in each index. I was expecting that the DMV reported once per index, so I incorrectly concluded that I was seeing before/after stats on each index, rather than the fragmentation per node.

    Live and learn.

    Thank you to everyone who replied. I'm still curious about the aging of the statistics in the DB, but at least now I can look at before/after stats correctly.

    Rich

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

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