What does the "reads" column in sys.dm_exec_sessions actually indicate?

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

My understanding has always been that reads is only physical (i.e. from disk) and logical_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:

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;


My test setup was just the following:

CREATE TABLE dbo.ReadTest (Col1 CHAR(7500) DEFAULT (' '));
INSERT INTO dbo.ReadTest (Col1) VALUES (DEFAULT), (DEFAULT);


I then ran the following:

SELECT reads, logical_reads FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
SELECT * FROM dbo.ReadTest;


(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 the logical_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 and logical_reads fields. I would run the SELECT queries again and sometimes I would see an additional jump in reads.

After that, I would run the two SELECT queries many times and the reads would remain the same while the logical_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:

DBCC FREESYSTEMCACHE('ALL');
DBCC FREEPROCCACHE;
DBCC FREESESSIONCACHE;

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.

• Run SELECT [Col1] FROM dbo.ReadTest; again: The reads do not go up, but logical_reads go up by 6.

• Add a space to the query text and re-run: The reads do not go up, but the logical_reads go up by 52 (just like right after the 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 physical reads. 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 the SELECT. 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.

StackExchange Database Administrators Q#123465, answer score: 3