This may seem like a very basic question, and indeed it should be. However, as a fan of the scientific method, I like to create a hypothesis, then test it to see if I'm correct. In this case, I'm trying to better understand the output of sys.dm_exec_sessions
, and more specifically, the single column "reads".
The SQL Server Books Online rather dryly specifies this as:
Number of reads performed, by requests in this session, during this session. Is not nullable.
One might presume this would indicate the number of pages read from disk to satisfy the requests issued by this session since the start of the session. This is the hypothesis I thought I'd test.
The logical_reads
column in that same table is defined as:
Number of logical reads that have been performed on the session. Is not nullable.
From experience using SQL Server, I believe this column reflects the number of pages that have been read both from disk and in memory. In other words, the total number of pages ever read by the session, no matter where those pages reside. The differentiator, or value-proposition, of having two separate columns that offer similar information would seem to be that one can understand the ratio of pages read from disk (reads
) vs those read from the buffer cache (logical_reads
) for a specific session.
On my test rig, I've created a new database, created a single table with a known number of pages of data, then read that table in a new session. Then I looked at sys.dm_exec_sessions
to see what the reads
and logical_reads
columns said about the session. At this point I am confounded by the results. Perhaps someone here can shed some light on this for me.
The test rig:
USE master;
IF EXISTS (SELECT 1
FROM sys.databases d
WHERE d.name = 'TestReads')
BEGIN
ALTER DATABASE TestReads SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestReads;
END
GO
CREATE DATABASE TestReads;
GO
ALTER DATABASE TestReads SET RECOVERY SIMPLE;
BACKUP DATABASE TestReads TO DISK = 'NUL:'; /* ensure we are in
simple recovery model */
GO
USE TestReads;
GO
/*
create a table with 2 rows per page, for easy math!
*/
CREATE TABLE dbo.TestReads
(
ID INT NOT NULL
CONSTRAINT PK_TestReads
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, SomeData CHAR(4000) NOT NULL
);
/*
insert 5000 pages of data
*/
INSERT INTO dbo.TestReads (SomeData)
SELECT TOP(10000) o1.name
FROM sys.objects o1
, sys.objects o2
, sys.objects o3
ORDER BY o1.object_id
, o2.object_id
, o3.object_id;
/*
Verify we have 5,000 pages of data, with 10,000 rows.
*/
SELECT o.name
, p.rows
, au.total_pages
, au.used_pages
, au.data_pages
FROM sys.partitions p
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.allocation_units au
ON p.hobt_id = au.container_id
AND (au.type = 1 or au.type = 0)
WHERE p.index_id = 1
AND o.name = 'TestReads'
AND o.type = 'U';
/*
issue a checkpoint to ensure dirty pages are flushed to disk
*/
CHECKPOINT 30;
DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE ('ALL');
DBCC FREEPROCCACHE;
DBCC FREESESSIONCACHE;
GO
/*
ensure we have no data cached in memory for the TestReads database
*/
USE master;
ALTER DATABASE TestReads SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE TestReads SET ONLINE;
SELECT DatabaseName = d.name
, SchemaName = s.name
, ObjectName = o.name
, AllocatedMB = COUNT(1) * 8192E0 / 1048576
, PagesInMemory = COUNT(1)
FROM sys.dm_os_buffer_descriptors dobd
INNER JOIN sys.allocation_units au
ON dobd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 0)
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.databases d
ON dobd.database_id = d.database_id
WHERE d.name = 'TestReads'
AND o.name = 'TestReads'
AND o.type = 'U'
GROUP BY d.name
, s.name
, o.name;
The first select statement above shows that in fact the table does consist of 10,000 rows, with 5,025 total pages, 5,020 used pages, and 5,000 data pages; precisely as one would expect:
The second select statement confirms we have nothing in memory for the TestReads
table.
In a new session, we do the following query, taking note of the session_id:
USE TestReads;
SET STATISTICS IO ON;
SELECT *
FROM dbo.TestReads;
As one would expect, this reads the entire table from disk into memory, as shown in the output from SET STATISTICS IO ON
:
(10000 row(s) affected)
Table 'TestReads'. Scan count 1, logical reads 5020, physical reads 3,
read-ahead reads 4998, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
In a third session, we inspect sys.dm_exec_sessions
:
SELECT des.session_id
, des.reads
, des.logical_reads
FROM sys.dm_exec_sessions des
WHERE des.session_id = 57; /* session_id from the 2nd (previous) session */
I'd expect to see sys.dm_exec_sessions
show at least 5,000 for both reads
and logical_reads
. Alas, I see reads
shows zero. logical_reads
does show an expected number of reads somewhere north of 5,000 – it shows 5,020 in my test:
I know SQL Server read the entire TestReads
table into memory, by virtue of the sys_dm_os_buffer_descriptors
DMV:
USE TestReads;
GO
SELECT DatabaseName = d.name
, SchemaName = s.name
, ObjectName = o.name
, AllocatedMB = COUNT(1) * 8192E0 / 1048576
, PagesInMemory = COUNT(1)
FROM sys.dm_os_buffer_descriptors dobd
INNER JOIN sys.allocation_units au
ON dobd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 0)
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.databases d
ON dobd.database_id = d.database_id
WHERE d.name = 'TestReads'
AND o.name = 'TestReads'
AND o.type = 'U'
GROUP BY d.name
, s.name
, o.name;
What am I doing wrong?
I'm using SQL Server 2012 11.0.5343 for this test.
Further findings:
If I run the following:
SELECT des.session_id
, des.reads
, des.logical_reads
FROM sys.dm_exec_sessions des
I see reads
of 784 in the session where I'm creating the test rig; however all other sessions show zero in the reads
column.
I've now updated my SQL Server test instance to 11.0.6020; however the result is the same.
Best Answer
My understanding has always been that
reads
is only physical (i.e. from disk) andlogical_reads
is only from the Buffer Pool (i.e. from memory). I did a quick test with a smaller table that only has 2 data pages and 3 pages total, and what I am seeing seems to confirm those two definitions.One thing that is probably giving you bad results is that you are not clearing out the memory. You should run the following between tests to force it to reload from disk:
My test setup was just the following:
I then ran the following:
(Yes, I was testing in the same session that I was running the DMV in, but that didn't skew the results for the
reads
field, and if nothing else, was at least consistent if it did contribute to thelogical_reads
field.)For testing I would run the DBCC command and then the two SELECT queries. Then I would see a jump in both the
reads
andlogical_reads
fields. I would run the SELECT queries again and sometimes I would see an additional jump inreads
.After that, I would run the two SELECT queries many times and the
reads
would remain the same while thelogical_reads
went up by 4 every single time.I would then start over with running the DBCC and see that same pattern. I did this quite a few times and the numbers reported were consistent across all of the test runs.
More info:
I am also testing on SQL Server 2012, SP2 - 64 bit (11.0.5343).
The following DBCC commands we have both tried and seen no effect:
Most of the time
DBCC DROPCLEANBUFFERS
does work, but I occasionally see that it is still in the Buffer Pool. Odd.When I:
DBCC DROPCLEANBUFFERS
: The reads go up by 24 and logical_reads go up by 52.SELECT [Col1] FROM dbo.ReadTest;
again: The reads do not go up, but logical_reads go up by 6.DBCC DROPCLEANBUFFERS
).It would appear that the 52 logical reads accounts for plan generation and the results, which implies that the plan generation caused the additional 46 logical reads. But the physical reads do not go up again and yet it is the same 52 logical reads as it was when it did need to also do the physical reads, hence
logical_reads
does not include the physicalreads
. I am just making this point clear, whether or not it was being stated or implied in the Question.BUT, one behavior I did notice that throws off (at least a little) using the existence of the table's data pages in
sys.dm_os_buffer_descriptors
: it gets reloaded by some other process. If you DROPCLEANBUFFERS and check immediately, then it should be gone. But wait a few minutes and it shows up again, but this time without all of the data pages. In my test, the table has 1 IAM page and 4 data pages. All 5 pages are in the buffer pool after I do theSELECT
. But when it gets reloaded by some other process, it is just the IAM page and 1 data page. I thought it might be SSMS IntelliSense, but I removed all references to that object name in my query tab and it still gets reloaded.