Home Dashboard Directory Help
Search

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


Status: 

Active


3
0
Sign in
to vote
Type: Suggestion
ID: 761859
Opened: 9/9/2012 8:15:33 PM
Access Restriction: Public
0
Workaround(s)
view

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;
Details
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.
Sign in to post a workaround.