July 22, 2012 at 9:53 pm
Hi,
In ssms when below query runs in given order, i expect Logical_reads is Zero. But its showing some values LR.
Can you please explain this? am i missing anything?
checkpoint
GO
dbcc dropcleanbuffers -- comment: after this dbcc, there is no object in Buffer Pool.
Go
dbcc freeproccache
Go
Select * from db_pc.dbo.Tablename
Go
Select reads, writes, logical_reads from sys.dm_exec_sessions where session_id = @@SPID
July 22, 2012 at 10:42 pm
Neither of those DBCC commands clear the DMV's - the session you are connected with has still done x logical reads and y physical reads, regardless of what has or has not been cleared from the buffer cache.
Further to that, the data used by the DMV's themselves will not get cleared from the buffer so part of the logical reads will be reading the system state data which is in memory and thus a logical read.
The only way to reset the DMV counters is a restart of the SQL Server service - excluding a couple which can be reset with SQLPERF.
Bear in mind that you are looking at the session DMV... a new session will have zero values, so if you want to see what reads/writes are done by a command with an empty buffer then start a new session and run the command and then see what figures it has. I would still expect some logical reads as a query will first read data into the buffer and then the buffer will be read, data is not sent directly from disk to the client.
July 23, 2012 at 1:04 am
Why would you expect 0 logical reads? Unless the table is completely empty, the query is going to have to read something from the data cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2012 at 6:24 am
got your point.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply