I'm trying to understand how sql server works access data.
For very simple queries I'm able to exactly calculate number of logical reads that are needed, but I've problem with following rather simple query:
SELECT a.*
FROM TT_TMP_3 AS a
INNER JOIN TT_TMP_4 AS b
ON b.id = a.id
This is code that produces tables:
CREATE TABLE TT_TMP_3(
id int,
x int)
DECLARE @i int
SET @i = 0
WHILE @i <= 100000
BEGIN
INSERT INTO TT_TMP_3
VALUES(@i, 1)
SET @i = @i + 1
END
SELECT *
INTO TT_TMP_4
FROM TT_TMP_3
WHERE id <= 770
CREATE INDEX IDX ON TT_TMP_3(ID)
Query statistics are following:
(771 row(s) affected)
Table 'TT_TMP_3'. Scan count 771, logical reads 2429, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TT_TMP_4'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
There are 771 rows in TT_TMP_4 and that is why 771 Index seeks are needed. Each seek needs 2 logical reads. Then for each row that has been found RID_lookup needs to be done to find value of column x. That gives us another 771 logical reads. In total it is 2313 reads, still 116 missing.
Question: What for are those 116 logical reads?