Logical read in SQL
A logical read occurs when the query engine requires access to data. Initially, it checks the in-memory storage of SQL Server. If the relevant page is already present in SQL Server's memory, it utilizes that information. In cases where the data page is not found in memory, a physical read is triggered, leading to the retrieval of the data page from the disk. A logical read without a subsequent physical read is referred to as a "cache hit."
The buffer cache, also termed the buffer pool, serves as SQL Server's primary working memory for query resolution. The allocation of memory for SQL Server directly influences the size of the available buffer cache.
Providing specific recommendations without examining the query, understanding the table content, data structure, and indexing is practically unfeasible.
While a high count of logical reads may not inherently be problematic, an excessive number of logical reads is undesirable. For instance, if a query retrieves only three rows of data but necessitates scanning through 200 million rows of the table, the process becomes inefficient and can be improved through query optimization or the addition of an appropriate index.
Example query
select *
from
(
select *
from Employees
where empId = 9
)
where deptId = 1;
To optimize the query and reduce logical reads, you can combine both criteria in a single step. Instead of first selecting all employees with empid 9 and then filtering for deptid 1, you can integrate both conditions into the initial SELECT statement. This approach aims to minimize the intermediate result set and enhance query efficiency.
Here's an example of how you can modify the query:
SELECT *
FROM employees
WHERE empid = 9 AND deptid = 1;
By incorporating both conditions in a single WHERE clause, the query engine is more likely to perform a more efficient execution plan, potentially leading to fewer logical reads compared to the two-step process. However, the effectiveness of this optimization depends on the specific database schema, indexes, and the SQL Server's query optimizer.