how to get deletion stats from dm_db_index_operational_stats

• zhen168

  Newbie

  Points: 6

  More actions

  July 25, 2008 at 1:28 pm

  #191536

  SELECT name, (leaf_delete_count+leaf_ghost_count

  FROM master.sys.dm_db_index_operational_stats(db_id('test'),NULL,NULL,NULL) T

  join test.sys.sysobjects S (nolock)

  on S.id = T.object_id

  where T.index_id=1

  and S.xtype='U'

  and database_id =DB_ID(N'test')

  We use the above query to figure out how many deletion happens in our database. But seems the leaf_ghost_count is really tricky. It does not work in one situation.

  Say you are deleting 100,000 rows from a table, you stop the query during its run. When check the data in the table, it still has all the rows there(rolled back for you). But leaf_ghost_count gets increased.

  Wondering:

  1). Is there any better ways to get deletion stats other than triggers(on every table)?

  2). How does dm_db_index_operational_stats work? (when rollback happens)

  Thanks for your input.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 25, 2008 at 6:58 pm

  #848738

  Might this be the source of confusion:

  From the definition of: sys.dm_db_index_operational_stats

  Cumulative count of leaf-level rows that are marked as deleted, but not yet removed. These rows are removed by a cleanup thread at set intervals

  See

  http://msdn.microsoft.com/en-us/library/ms174281.aspx

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• zhen168

  Newbie

  Points: 6

  More actions

  July 30, 2008 at 1:15 pm

  #850818

  Thanks for the reply.

  Of course, we read the BOL. But it does not mention explicitly the rollback scenario. Also looking for any alternatives.