Clarify the description for logical writes in sys.dm_exec_xxx_stats - by Paul White NZ

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 761859 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 9/9/2012 8:15:33 PM
Access Restriction Public

Description

The documentation for sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, and sys.dm_exec_trigger_stats describes the last_logical_writes column as:

Number of logical writes performed the last time the trigger was executed.

It seems the various logical_writes columns count the number of buffer pool pages dirtied, not the number of logical writes performed.  If a page is already dirty (modified) no writes are counted.  It is perhaps a small distinction, but it would be helpful to avoid misconceptions.

Demo script:

USE tempdb;
GO
CREATE TABLE Test 
(
    Name    varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL
);
GO
INSERT dbo.Test
    (Name) 
VALUES
    ('Paul');
GO
CREATE PROCEDURE dbo.UpdateBob
AS
UPDATE dbo.Test 
SET Name = 'Bob';
GO
CREATE PROCEDURE dbo.UpdatePaul
AS
UPDATE dbo.Test 
SET Name = 'Paul';
GO
CREATE PROCEDURE dbo.ShowStats
AS
SELECT
    proc_name = OBJECT_NAME(deps.[object_id]),
    deps.last_logical_writes
FROM sys.dm_exec_procedure_stats AS deps
WHERE
    deps.[object_id] IN
    (
        OBJECT_ID(N'dbo.UpdateBob', N'P'),
        OBJECT_ID(N'dbo.UpdatePaul', N'P')
    );
GO
DBCC FREEPROCCACHE;
CHECKPOINT;
GO
-- 1 logical write for Bob, 0 for Paul
EXECUTE dbo.UpdateBob;
EXECUTE dbo.UpdatePaul;
EXECUTE dbo.ShowStats;
GO
-- 0 logical writes
EXECUTE dbo.UpdateBob;
EXECUTE dbo.UpdatePaul;
EXECUTE dbo.ShowStats;
GO
-- 1 logical write for each after CHECKPOINT flushes dirty pages
CHECKPOINT;
EXECUTE dbo.UpdateBob;
CHECKPOINT;
EXECUTE dbo.UpdatePaul;
EXECUTE dbo.ShowStats;
GO
DROP PROCEDURE 
    dbo.UpdatePaul,
    dbo.UpdateBob,
    dbo.ShowStats;
GO
DROP TABLE
    dbo.Test;
Sign in to post a comment.
Posted by Microsoft on 8/6/2013 at 3:13 PM
I have adjusted those three topics to read "Number of the number of buffer pool pages dirtied the last time the plan was executed. If a page is already dirty (modified) no writes are counted." These will appear in MSDN as the topics are re-published; in a couple weeks. Thank you.
Posted by Microsoft on 9/21/2012 at 7:48 AM
Thank you for submitting this. I'll take a look.