The "Number of Rows Read" property is incorrect when the Predicate property contains a PROBE
Scan and Seek operators in newer versions of SQL Server report a property "Number of Rows Read" in the actual execution plan which is incredibly usesful when troubleshooting slow queries. However, it reports a misleading number of the execution plan contains a Bitmap operator and the bitmap is probed as part of the Predicate of the scan operator.
This can be demonstrated in ContosoRetailsDW with the queries below:
SELECT ds.StoreManager,
dp.BrandName,
SUM(fos.TotalCost)
FROM dbo.FactOnlineSales AS fos
INNER JOIN dbo.DimStore AS ds
ON ds.StoreKey = fos.StoreKey
INNER JOIN dbo.DimProduct AS dp
ON dp.ProductKey = fos.ProductKey
WHERE ds.EmployeeCount < 30
AND dp.ColorName = 'Black'
GROUP BY ds.StoreManager,
dp.BrandName;
SELECT ds.StoreManager,
dp.BrandName,
SUM(fos.TotalCost)
FROM dbo.FactOnlineSales AS fos
INNER JOIN dbo.DimStore AS ds
ON ds.StoreKey = fos.StoreKey
INNER JOIN dbo.DimProduct AS dp
ON dp.ProductKey = fos.ProductKey
GROUP BY ds.StoreManager,
dp.BrandName;
The first uses bitmaps to reduce the number of rows returned to the operator. But those rows still have to be read. This is confirmed by looking at SET STATISTICS IO, which shows that all pages in the table are touched by both queries, And yet, the first query reports only 3.1 million rows in the Number of Rows Read property, and the second reports 12.6 million.
When tuning a query I want to see how much data the operator touched. In both queries, the clustered index scan touches all 12.6 million rows in the table. The former reports only 3.1 million, which is misleading.
Hugo Kornelis
shared this idea