8

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:

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.

3
  • @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, 2019 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, 2019 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, 2019 at 4:31

2 Answers 2

9

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

1
  • 2
    Logical writes can be examine using sys.dm_exec_query_stats Jul 3, 2021 at 8:09
1

As a DBA's stand point we don't need to worry about logical read; it's our most desired thing. While physical read is where we can have the most PTO.

Logical read is getting a data from Cache or Buffer cache of the database system, and cache simply means RAM. Whenever we requests a particular block of data first it check Buffer cache and if it there you got a Cache hit else you have a problem called Cache miss.

But don't worry we have another layer of cache that handled by OS. While the buffer cache is exclusively for database , OS cache is not. OS cache may have different blocks of data accessed by different apps. For a dedicated DB system OS cache also probably full of database table data.So if buffer cache is miss it check OS cache. If the block of data not there in OS cache also got the real problem of disk access. Now that's a physical read. Now when you got physical read you got slow query also.

For an optimal DB you need to have a cache hit ratio of 95-99%, for our Postgres DB we have 97%.For SQL server can use this DMV to check sys.dm_os_performance_counters. Most of the time a physical read happen when there is a Sequential Scan in the table. If that's case you need to review table indexes and executions plan. For bigger tables even if you have index but you query almost all still cannot use an index, triggers a sequential scan in disk.

You can find the slowest running query in the procedure just by running the procedure itself and checking in parallel if there is any query taking too much time. Mostly queries taking too much time might be related to disk access because disk is too slow compared to memory right?

For your case maybe can make use of perfmon utility in OS to get disk related stats. Can check here https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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