SQL Server Performance Forum – Threads Archive

fn_virtualfilestats – shouldn’t Bytes written be d

I amusing fn_virtualfilestats to gather IO statistic at the file
level. I noticed some results that were unexpected. I was under the
impression that SQL Server always performed IO activity at the 8KB Page
level. I also found the follow resources that support my assumption
1. Books On-Line – SET STATISTICS IO – physical reads is defined as
"Number of pages read from disk."
2.http://www.windowsitpro.com/Articles/ArticleID/44846/44846.html –
You need a subscription to view this but what Brian says is. "Pages in
SQL Server are 8KB, and SQL Server must read an entire extent-a group
of 8 pages, 64KB total-to read a single page. You can’t directly
infer how much data was read or written for any single request." Ok so
the relationship is not one read request = 1 Page..but it does say
reads occur in 8kb pages
3.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIOb…
section on Torn I/o.
"A torn data page for SQL Server occurs when only a portion of the 8 KB
is correctly written to or retrieved from stable media."
I wrote a query that snapshots results of fn_virtualfilestats into a
temp table waits for a period of time and snapshots the data again.
Base don the results I can determine that BytesRead, PAgesRead, Writes,
BytesWritten and PagesWritten.
In my tests I noticed that Pages read was always a non-decimal value
but Pages Written was not. Can anyone explain this. Shouldn’t
BytesWritten divided by 8192 always results in a non-decimal value.
Here is the code I used.
DECLARE @WaitTimeSeconds integer
DECLARE @DBID integer
DECLARE @FileId integer
SELECT @WaitTimeSeconds = 15
SELECT @DBID = -1
SELECT @FileID = -1
SELECT * INTO #FileStats
FROM :: fn_virtualfilestats(-1, -1)
WAITFOR DELAY @WaitTimeSeconds
SELECT * INTO #FileStats2
FROM :: fn_virtualfilestats(-1, -1)
SELECT
fs2.dbid,
fs2.fileid,
fs2.numberreads – fs1.numberreads AS Reads,
fs2.bytesread – fs1.bytesread AS BytesRead,
CAST((CAST((fs2.bytesread – fs1.bytesread) AS DECIMAL (18,2)) / 8192)
AS DECIMAL(18,2)) as PagesRead,
fs2.numberwrites – fs1.numberwrites AS Writes,
fs2.bytesWritten – fs1.bytesWritten AS BytesWritten,
CAST((CAST((fs2.bytesWritten – fs1.bytesWritten) AS DECIMAL (18,2)) /
8192) AS DECIMAL(18,2)) as PagesWritten,
@WaitTimeSeconds * 1000 AS WaitTimeInterval
FROM
#FileStats2 fs2
LEFT JOIN
#FileStats fs1
ON fs2.dbid = fs1.dbid
And fs2.fileid = fs1.fileid
drop table #FileStats
drop table #FileStats2

---

i see SQL Server reading individual 8KB all the time, i don’t why someone thinks it can only read the entire 64K extent,
during SQL Server startup, it will read entire extents even though only 8K was requested for faster buffer cache warmup i think you should find that the majority of writes to the data file to be multiples of 8KB, but there could be meta data write of uneven size.
log writes have no 8KB affinity

---

I agree as I see this also. What metadata writes in non 8kb pages? Thanks it was the Tlog that was getting me.

---

]]>