Difference between logical and physical reads/writes

I would like to understand the difference between physical and logical reads/writes in a database.

• When (which threshold) should I be worried about them?

• Is this post an accurate description? (Even in that case, I am still a bit unclear as to what can be considered high reads or writes).

I am doing level 2 support and the drive where our production database resides is experiencing severe performance problems:

I ran 2 of Glenn Berry's diagnostic information queries to view the top 10 stored procedures in terms of total logical writes and average I/O and got the following back:

My task is to identify the stored procedures that may be causing the low %idle time so our product team can review the code.

In short, I would like to understand the difference between physical and logical reads/writes in a database

A very terse explanation of the difference between logical and physical reads can be seen in the Microsoft documentation for the diagnostic command SET STATISTICS IO here:

• logical reads - Number of pages read from the data cache.

• physical reads - Number of pages read from disk.

Note that when it says "data cache" there it means RAM.

I've never heard of anyone talk about logical writes so I can't speak to that one. If that just means writes, then it's talking about inserts, updates, and deletes.

Regarding this:


My task is to identify the stored procedures that may be causing the low %idle time so our product team can review the code.

Looking at the graph in your question, I do see the period of high disk utilization ("low idle time"). If you can identify what procedures are running during that time (for instance, by logging sp_whoisactive to a table), then you can try to tune those specific queries (or post questions on this site with sufficient detail to get some help).

StackExchange Database Administrators Q#227534, answer score: 9