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

Question

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.

Answer 1

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.