1

In short, 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 at this moment and the drive where our production database resides is experiencing severe performance problems:

enter image description here

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:

enter image description here

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

  • @mustaccio It's disk idle time what they care about, which indicates high disk utilization. When that happens, they usually report issues with their main MES platform with end users not being able to use it at all. – gacanepa Jan 25 at 23:35
  • Obviously, I have no idea what "main MES platform" is. It sounds like they configured their storage such that the database shares physical volumes and/or I/O paths with that "main MES platform" and now suffer from it. This isn't something that can be solved by random people on the internet, I'm afraid. – mustaccio Jan 26 at 1:28
  • @mustaccio MES stands for Manufacturing Execution System. Databases are on dedicated partitions, as does the actual MES application. When the disk activity is unusually high on the partitions where the databases reside, the application becomes unresponsive (or very slow at best) because it can't read from the database properly. – gacanepa Jan 26 at 4:31
1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.