The argument that reducing logical reads isn’t crucial because they occur in memory and are fast compared to physical reads is indeed valid on the surface. However, it’s essential to provide context and not leave the generalization of logical reads unchecked. Logical reads serve as a valuable metric for discerning the performance disparities between execution plans utilizing index scans versus index seeks.
When encountering a high number of logical reads, it’s imperative to ask two critical questions to ascertain if there’s a potential issue:
Index Scan Operation: If the elevated logical reads stem from an index scan operation, it’s probable that there’s a high scan count, often indicative of parallelism. This scenario warrants scrutiny as it could indicate inefficiencies in query execution that might benefit from optimization through query rewrites or improved indexing strategies.
Nested Loop Join: Another consideration is the presence of a nested loop join conducting numerous iterations, repeatedly accessing the same set of pages in memory. This repetitive pattern of reads may signal an opportunity for query optimization or restructuring to minimize resource consumption.
Furthermore, a significant concern related to excessive logical reads is memory allocation. Although modern database systems like SQL Server incorporate mechanisms for dynamically allocating memory based on historical execution feedback, it’s crucial to assess whether the excessive logical reads and consequent high page reads might exacerbate performance issues, particularly in environments experiencing memory pressure. Operational store databases, characterized by intensive read and write operations, are particularly susceptible to such performance bottlenecks.
In summary, while the swift nature of logical reads in memory might mitigate the performance impact compared to physical reads, it’s essential to delve deeper into the underlying causes of elevated logical reads. By examining factors such as index utilization, query execution plans, and memory allocation dynamics, database administrators can identify opportunities for optimization and ensure efficient system performance.
References:
Microsoft Docs: Understanding and Analyzing Logical Reads: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15#understanding-and-analyzing-logical-reads