August 21, 2013 at 2:28 pm
How does reducing the logical read increase performance ? Your valuable inputs are welcome.
Thanks in Advance !
August 21, 2013 at 2:32 pm
The lesser amount you need to read from cache, the better the performance. Seems quite logical. (pun intended)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 21, 2013 at 2:40 pm
Sure 🙂 But can you explain this as we are having problem with some developers arguing that physical reads are the one to be looked for and we can safely ignore the logical reads.
August 21, 2013 at 2:43 pm
Sanz (8/21/2013)
Sure 🙂 But can you explain this as we are having problem with some developers arguing that physical reads are the one to be looked for and we can safely ignore the logical reads.
It would be very unwise to ignore the logical reads.
Logical reads are the reads from cache.
Physical reads are the reads from disk.
First time ever a query is executed: high number of physical reads. Nothing you can do about that.
Second time: data is in cache, so low to none physical reads, but still high logical reads. You can bring down the logical reads by indexing or by rewriting the query.
So it's possible for a query to have logical reads (and still have terrible performance, if for example the same pages have to be read multiple times from the cache), but no physical reads.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 21, 2013 at 2:51 pm
Thanks 🙂
August 21, 2013 at 3:14 pm
Sanz (8/21/2013)
Sure 🙂 But can you explain this as we are having problem with some developers arguing that physical reads are the one to be looked for and we can safely ignore the logical reads.
A logical read means that the data has to go through the CPU and be processed, so high logical reads usually result in increased CPU usage. Also, it takes a certain amount of time to process each page, so high logical reads result in longer query run times.
I use a trace to analyze the cpu, reads, and writes of all the stored procedures in our system, and almost always find stored procedures that have high logical reads have high cpu usage. In an one hour long trace I ran this week, the top 4 ranked procedures in logical reads were all in the top 5 in cpu usage.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply