When analyzing the performance of a SQL Server query, one important aspect to consider is the number of rows read in the execution plan. This metric provides insights into how efficiently the query is retrieving data from the database.

Let’s take a look at two examples to understand the concept of number of rows read:

Example 1

In the first example, the number of rows read is significantly higher than the actual number of rows returned by the query. This occurs when the SQL Server engine needs to perform a full cluster index scan to find the desired rows. Despite reading a large number of rows, only a small subset of them is actually relevant to the query.

Example 2

In the second example, the number of rows read is equal to the actual number of rows returned. This happens when the SQL Server engine can efficiently seek the index and retrieve the necessary information without scanning the entire cluster index. As a result, the query execution is more efficient.

It’s important to note that the number of rows read does not necessarily indicate that scans are slower and seeks are faster. SQL Server always strives to find the most efficient way to read the data. Therefore, it’s not a matter of one approach being universally better than the other.

When you encounter a significant difference between the number of rows read and the actual number of rows, simply updating statistics may not be sufficient to improve performance. Instead, it’s crucial to inspect the query and consider adjusting or creating indexes to optimize the execution plan.

For further discussion on this topic, feel free to connect with me on LinkedIn. I’m always happy to delve deeper into SQL Server performance optimization.

Related blog posts:

• SQL SERVER – DBCC DBREINDEX and MAXDOP Not Possible
• SQL SERVER – Fill Factor – Instance Level or Index Level
• List All Sessions – SQL in Sixty Seconds #148
• SQL SERVER – Attach an In-Memory Database with T-SQL
• SQL SERVER – Attach a Database with T-SQL