Post reply

Why leaf_delete_count is not updated?

  • August 24, 2011 at 3:44 am

    #390261

    The leaf_delete_count is not updated in `sys.dm_db_index_operational_stats`. Anybody knows why? Please see outcome of the following batch.

    /*------------------------

    SET NOCOUNT ON

    IF OBJECT_ID('t1', 'U') IS NOT NULL DROP TABLE t1

    CREATE TABLE t1 (a INT IDENTITY PRIMARY KEY CLUSTERED, b INT NULL)

    INSERT t1 (b) SELECT TOP 10 1 FROM master..spt_values AS c

    WHILE @@ROWCOUNT>0 UPDATE TOP (1) t1 SET b=0 WHERE b<>0

    WHILE @@ROWCOUNT>0 DELETE TOP (1) t1

    SELECT leaf_insert_count

    ,leaf_delete_count

    ,leaf_update_count

    FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('t1'), 1, NULL)

    ------------------------*/

    leaf_insert_count;leaf_delete_count;leaf_update_count

    10;0;10

  • August 24, 2011 at 4:22 am

    #1372448

    seregak76 (8/24/2011)

    The leaf_delete_count is not updated in `sys.dm_db_index_operational_stats`. Anybody knows why?

    The reason is 0 leaf pages are deleted.

    To see a non-zeo value you need to delete enough rows.

  • August 24, 2011 at 4:33 am

    #1372450

    You may have to check leaf_ghost_count for those deleted.

    SELECT leaf_insert_count

    ,leaf_delete_count

    ,leaf_update_count,leaf_ghost_count

    FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('t1'), 1, NULL)

    No delete will really delete the records. (This is for better performance. it will just mark the record as deleted.).

  • August 24, 2011 at 5:04 am

    #1372464

    Thanks!

    I increased the amount of rows in my test case and still no luck. Any other ideas?

    SET NOCOUNT ON

    IF OBJECT_ID('t1', 'U') IS NOT NULL DROP TABLE t1

    CREATE TABLE t1 (a INT IDENTITY PRIMARY KEY CLUSTERED, b INT NULL)

    INSERT t1 (b) SELECT TOP 100000 1 FROM master..spt_values, master..spt_values a

    WHILE @@ROWCOUNT>0 UPDATE TOP (10000) t1 SET b=0 WHERE b<>0

    WHILE @@ROWCOUNT>0 DELETE TOP (10000) t1

    SELECT leaf_insert_count

    ,leaf_delete_count

    ,leaf_update_count

    ,leaf_ghost_count

    FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('t1'), 1, NULL)

    ------- Result---------------

    leaf_insert_count;leaf_delete_count;leaf_update_count;leaf_ghost_count

    100000;0;100000;0

  • August 24, 2011 at 5:19 am

    #1372469

    Try executing one by one statement. Its working for me.

    Your condition for delete is not really deleting the table. Please check.

  • August 24, 2011 at 7:07 am

    #1372527

    could it be something simple like this?

    EVERY command changes the @@ROWCOUNT.

    you need to capture the value immeditely after the updates or deletes, and compare the saved value right?

    also, i don't think the second/delete loop would ever get performed.

    SET NOCOUNT ON

    Declare @Rows int

    IF OBJECT_ID('t1', 'U') IS NOT NULL

    DROP TABLE t1

    CREATE TABLE t1 (a INT IDENTITY PRIMARY KEY CLUSTERED, b INT NULL)

    INSERT t1 (b) SELECT TOP 100000 1 FROM master..spt_values, master..spt_values a

    SELECT @Rows=@@ROWCOUNT

    WHILE @Rows>0

    BEGIN

    UPDATE TOP (10000) t1 SET b=0 WHERE b<>0

    SELECT @Rows=@@ROWCOUNT

    END

    --initialize the second loop...

    SELECT @Rows=COUNT(*) FROM t1

    WHILE @Rows>0

    BEGIN

    DELETE TOP (10000) t1

    SELECT @Rows=@@ROWCOUNT

    END

    SELECT leaf_insert_count

    ,leaf_delete_count

    ,leaf_update_count

    ,leaf_ghost_count

    FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('t1'), 1, NULL)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 24, 2011 at 7:40 am

    #1372565

    Thanks everybody!

    I got it sorted out. Some times the ' leaf_delete_count' does not match amount of deletes. The rest of deletes are reflected in 'leaf_ghost_count' so I can get actual delete count by adding ' leaf_delete_count' to 'leaf_ghost_count'.

    See the test code below.

    SET NOCOUNT ON

    Declare @Rows int

    IF OBJECT_ID('t1', 'U') IS NOT NULL

    DROP TABLE t1

    CREATE TABLE t1 (a INT IDENTITY PRIMARY KEY CLUSTERED, b INT NULL)

    INSERT t1 (b) SELECT TOP 100000 1 FROM master..spt_values, master..spt_values a

    SELECT @Rows=@@ROWCOUNT

    WHILE @Rows>0

    BEGIN

    UPDATE TOP (10000) t1 SET b=0 WHERE b<>0

    SELECT @Rows=@@ROWCOUNT

    END

    --initialize the second loop...

    SELECT @Rows=COUNT(*) FROM t1

    WHILE @Rows>0

    BEGIN

    DELETE TOP (10000) t1

    SELECT @Rows=@@ROWCOUNT

    END

    SELECT leaf_insert_count

    ,leaf_delete_count

    ,leaf_update_count

    ,leaf_ghost_count

    , leaf_delete_count + leaf_ghost_count AS total_deleted_count

    FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('t1'), 1, NULL)

    ----------- Result ----------------

    leaf_insert_count leaf_delete_count leaf_update_count leaf_ghost_count total_deleted_count

    -------------------- -------------------- -------------------- -------------------- --------------------

    100000 37579 100000 62421 100000

  • August 25, 2011 at 11:09 pm

    #1373578

    It might be good a look at it:

    http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx

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

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