SQL Server Performance – IO Statistics for Index Seek

performancequery-performancesql serversql server 2014

Does Statistics IO (Logical/Physical Reads) include index pages that are not leaf-level?

The following example is lifted directly from this old post from Gail Shaw:

https://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/

--Setup
CREATE TABLE TestingSeeks (
    id int identity (1,1) not null,
    SomeStr char(6) default '' -- a filler
)

insert into TestingSeeks (SomeStr)
select top (500000) ''
from sys.columns c1 cross join sys.columns c2

CREATE NONCLUSTERED INDEX idx_Seek ON TestingSeeks (id)


--Example queries
--Query 1
select  id
from    TestingSeeks

--Query 2
select  id
from    TestingSeeks
where   id > 0

–Statistics IO output for Query 1

(500000 row(s) affected) Table 'TestingSeeks'. Scan count 1, logical
reads 1120, –All other columns are zero

–Statistics IO output for Query 2

(500000 row(s) affected) Table 'TestingSeeks'. Scan count 1, logical
reads 1120, –All other columns are zero

As you can see, the Logical Reads are the same for both queries (the Seek and the Scan) but I thought that the Seek would have more pages to read as it would read the Root page and pages from the intermediate level(s) of the index.

If I run the following query:

--Seek for one row
--Query 3
select  id
from    TestingSeeks
where   id = 400

–Statistics IO output for Query 3

(1 row(s) affected) Table 'TestingSeeks'. Scan count 1, logical reads
3, –All other columns are zero

You can see that there are three reads (1. Root page, 2. Intermediate page, 3. Leaf level page), so why did the statistics for [Query 2] above not include two more rows than the Scan (Query 1)?

SQL Server @@Version: Microsoft SQL Server 2014 (SP2-GDR) (KB4057120)
– 12.0.5214.6 (X64) Jan 9 2018 15:03:12 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build
7601: Service Pack 1)

Best Answer

Yes, the logical reads include non-leaf level index pages.

For the index scan in the first query, the first logical page is located by traversing the b-tree to find the first index leaf page, followed by a scan of the entire index in logical order. These stats include the 2 non-leaf pages and all index leaf pages.

For the index seek in the second query, the first logical page satisfying the criteria is located by traversing the b-tree followed by a scan of the leaf pages. These stats include the same 2 non-leaf pages the full scan needed to locate the first page (assuming no rows exist before the key value zero or those are in the first page). The same number of leaf pages are touched because all pages qualify for the seek predicate.

The equality predicate in the third query touches the same number of non-leaf pages of the b-tree hierarchy as the first 2 queries, although maybe different non-root pages, Only a single leaf page is needed to satisfy the predicate for a total of 3 logical IOs.

Related Question