SQL Server DMV – Understanding ‘Reads’ and ‘Writes’ in dm_exec_query_stats

The sub query in the second query it's what's killing you that requires that the sub query needs to be run once per row that's returned by the record set. If you look at the plan it'll tell you how many times each operator is executed. I'll bet that you'll see a high number of executions on some operators of the second query. (I'm on my phone at the moment so I can't open the plan.)

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.