Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read?

Question

I have to find the total time taken for query to execute in SQL-SERVER 2016 when all the data pages should be in Disk only. To accomplish that , I cleared all buffers in the SQL-SERVER using the following commands :

DBCC FREEPROCCACHE WITH NO_INFOMSGS
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO 
DBCC FLUSHPROCINDB (@dbId)
GO

After this I again found that logical reads were there, then I got to know about Read-Ahead , so I disabled read-ahead by the trace flag

DBCC TRACEON(652,-1)
GO

and also the prefetch flag

DBCC TRACEON(8744,-1)
GO

Now still I have logical reads , but Physical reads are significantly higher than just clearing the buffer. Is there any way to achieve 0 logical reads, or my understanding of logical read is wrong?

Please guide me through, I'am new to SQL-SERVER world

Answer 1

A logical read is counted when a single page is retrieved from buffer cache during query execution. This is counted regardless of whether a physical or read-ahead was used to cache the page, or if the page already existed in the buffer cache. Consequentially, logical reads is a measure of how many times pages were actually touched in memory during query execution.

A physical read is counted when the storage engine reads one or more pages from storage because the requested page is not already in cache. This excludes physical IO done by read-ahead scans.

A read-ahead read is counted when the storage engine reads one or more full extents (each extent being 8 contiguous pages) from storage during sequential scans. Read-ahead reads prefetches data into memory aggressively so that the pages are likely to be already cached when needed by the query. The number of extents read by read-ahead scans varies by SQL Server version and edition as well as fragmentation. I've seen read-ahead scans read as much as 4MB at once using a single scatter-gather IO during scans in Enterprise Edition using the latest SQL Server version.

Note that the SQL Server storage engine behaves differently with a cold cache (e.g. after restart or DBCC DROPCLEANBUFFERS). When the cache is not yet warmed up (Buffer Manager's Target Pages met), the storage engine performs full 64K extent reads instead of single 8K page reads to read pages into cache. This warms the cache much more quickly than would otherwise occur.

I prefer to focus on logical reads rather than physical for the purpose of query and index tuning because that's a better measure of the work performed by the query. It is only by happenstance whether data is cached or not. Testing with a cold cache is more of a measure of storage performance than query performance, IMHO, and isn't representative of what will occur under a production workload with a warm cache.

Answer 2

There will always be logical reads. SQL Server never returns data to you directly from disk. If the page you need is already in the buffer pool, there is a logical read. If it’s not, it will be moved into the buffer pool, and then there is also a logical read. You will always get the read coming from cache.

Also, I don’t understand the point of your test. Is it just doomsday analysis? If you are simply measuring time to return data when starting from an empty cache, why aren’t you just measuring time? Why are you worried about logical/physical reads? If you don’t believe the commands you’re issuing clear the plan cache / buffer pool, you can check those things via DMVs right before issuing your query.

Answer 3

Logical reads can be very misleading when evaluating query performance. An increase in logical reads, doesn't mean a decrease in performance, sometimes the opposite is true.

http://www.sql-server-performance.com/2018/logical-reads/