I/O vs Logical I/O

Kenneth Fisher, 2018-09-13 (first published: 2018-09-05)

tl;dr; While the difference is very important 90% of the time you won’t care and should just add the two numbers together.

I/O and Logical I/O are two pieces of information you will see all over the place.

 

Just to name a few.

There is an important difference between the two. Physical reads/IO are page reads from the disk. Logical reads/IO are pages read from memory. So why do we care? Reading from the disk is massively slower than reading from memory.

Side note: Simplifying dramatically, data gets stored in memory when it is read from disk to satisfy a query. It remains in memory until it is pushed out as new data needs to be moved to memory. Data that gets referenced more often is less likely to get pushed out of memory.

Now, realistically, pages are always going to have to be read from disk, at least initially and if you have a reasonable amount of memory then once the data is loaded to memory it should stay there for a while. (See page life expectancy.) So I ask again, why do we care? Honestly, most of the time I don’t. I add the two numbers together and that tells me how many pages the query is having to read. Hopefully, the vast majority are from memory (logical) but that will change depending on a ton of factors, not the least of which is Is this the first time in the recent past that the query has been run; or the second; or the twenty-second? We have absolutely no control over factors like that, so when tuning, or whatever, pay attention to the total, not the individual numbers.

That said, there are times when you care about them separately, but honestly, I couldn’t tell you what they are off the top of my head.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads