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