Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been looking at some SQL Profiler traces recently, and I see something I don't understand. I'm running simple INSERT commands:

INSERT INTO Foo (ForeignID, FileExtension, MimeType) VALUES (1, 'FOO', 'Application/Foo')

SQL Profiler consistently shows 0 Writes and 2 Reads. How can there not be any writes on an INSERT? Or am I misunderstanding what profiler is saying?

For completeness, here's the table definition I'm using:

CREATE TABLE [Foo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ForeignID] [int] NOT NULL,
[FileExtension] [nvarchar](32) NOT NULL,
[MimeType] [nvarchar](96) NOT NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
share|improve this question
add comment

migrated from stackoverflow.com Jan 19 '13 at 3:34

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 4 down vote accepted

The Profiler writes column is documented as

Number of physical disk writes performed by the server on behalf of the event.

Before the transaction is committed the log must be physically written to disc up to that point but those writes don't get included in the write stats reported Profiler.

For many inserts no physical writes may occur before the statement completes as the page is modified in the buffer cache and not written out to disc until later (e.g. by the checkpoint or lazy writer process).

For bulk inserts there is an eager writer so the pages may be written out to disc before . See Writing Pages in BOL for more.

However the above doesn't seem relevant as even when no pages are written to disc the physical writes can be reported as non zero.

In the test below Profiler reports 5 writes for the insert but monitoring the file writes with process monitor and sys.dm_io_virtual_file_stats shows that none actually occurred. From which I conclude that the physical writes column actually shows the number of pages made dirty (on the grounds that these will need to be written out to disc later)

So based on that assumption SQL Profiler shows 0 Writes in your test because you are inserting to a page that is already dirty.

TRUNCATE TABLE Foo

CHECKPOINT;

DBCC DROPCLEANBUFFERS;

/*Returns 0 rows*/
SELECT *
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = DB_ID()
       AND is_modified = 1

SELECT 'Before Insert', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

INSERT INTO Foo
            (ForeignID,
             FileExtension,
             MimeType)
VALUES      ( 1,
              'FOO',
              'Application/Foo')

/*Nothing Written. Figures same as previous query*/
SELECT 'After Insert', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

/*Returns 5 rows*/
SELECT *
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = DB_ID()
       AND is_modified = 1

CHECKPOINT;

/*Shows physical writes*/
SELECT 'After Checkpoint', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1) 

Profiler Trace

Profiler

Process Monitor Writes after CHECKPOINT

ProcMon

So the CheckPoint actually physically wrote 8 pages (6 single pages and 1 double page) but shows up in Profiler as being responsible for only 4.

From the offsets in the Process Monitor screenshot it is possible to see exactly what pages were written to

+-----+---------------+-----------+
|  57 | syscolpars    | DATA_PAGE |
|  86 | sysrscols     | DATA_PAGE |
|  93 | sysrowsets    | DATA_PAGE |
| 143 | sysallocunits | DATA_PAGE |
| 168 | Foo           | DATA_PAGE |
| 169 | Foo           | IAM_PAGE  |
|   1 |               | PFS_PAGE  |
|   9 |               | BOOT_PAGE |
+-----+---------------+-----------+

The three additional pages not shown in the output from dm_os_buffer_descriptors are the database boot page and sysrscols, sysrowsets.

The write of the database boot page is correlated with the CHECKPOINT itself as the dbi_checkptLSN field gets updated.

It would be expected that the insert would cause the other two pages to be updated (The rcmodified and rcrows columns respectively) but this does not appear to happen immediately and I see no LOP_COUNT_DELTA entries in the log file either so I assume this must happen periodically rather than after every modification.

I'm not sure how to account for 9 writes being reported but only 8 occurring. Perhaps one page was double counted.

share|improve this answer
1  
Excellent test, good answer. Seems like the Write column documentation might be somewhat simplified compared to the real life values. –  Mark S. Rasmussen Jan 19 '13 at 15:02
add comment

The Writes column in SQL Profiler lists the number of physical writes (not counting log writes). Confusingly, the Reads column lists logical reads - which includes reads from pages in memory.

And the reads you're seeing comes from the fact that SQL Server will have to read the existing pages to find the location of the insert, as well as to read the page which is about to be modified.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.