Number of logical reads in SQL Server

46 views Asked by At

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 plan is following: Query plan

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?

0

There are 0 answers