Why leaf_delete_count is not updated?

Question

Why leaf_delete_count is not updated?

seregak76@mail.ru

Right there with Babe

Points: 790
More actions
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

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

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

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.

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 2

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.).

seregak76@mail.ru Right there with Babe Points: 790 More actions · Answer 3

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

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 4

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 5

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!

seregak76@mail.ru Right there with Babe Points: 790 More actions · Answer 6

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

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 7

It might be good a look at it:

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