Index Seek Results in More Logical Reads in SQL Server

Question

I have the following table and data in it. I have two indexes on it: One that contains only a few columns from the where clause and the other one contains all the columns from the where clause.

I'm experiencing confusion regarding the observed behavior when utilizing different indexes in my query. Specifically, when I employ the first index, which only contains a few columns from the where clause, I notice a low number of logical reads but a high number of rows read. Conversely, when I use the index containing all the columns from the where clause, the number of logical reads increases while the number of rows read decreases. In the first scenario, there are residual predicates, causing the engine to scan all the rows retrieved from the seek operation. However, in the second scenario, the engine directly seeks to the necessary values. Given this, I would anticipate a lower number of logical reads in the second case. I would appreciate any insights into why this is happening. Thank you!

CREATE TABLE myTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Column1 VARCHAR(50),
    Column2 VARCHAR(50),
    Column3 VARCHAR(50),
    Column4 VARCHAR(50),
    Column5 VARCHAR(50),
    Column6 VARCHAR(50),
    Column7 VARCHAR(50),
    Column8 VARCHAR(50),
    Column9 VARCHAR(50),
    Column10 VARCHAR(50)
)

DECLARE @i INT = 1
DECLARE @j INT = 1
DECLARE @distinct_value_count INT = 20
DECLARE @distinct_value_count_with_more_rows INT = 3
DECLARE @rows_per_distinct_value INT = (20000 - (@distinct_value_count_with_more_rows * 2000)) / (@distinct_value_count - @distinct_value_count_with_more_rows)

WHILE @i <= @distinct_value_count
BEGIN
    DECLARE @current_rows_per_value INT = @rows_per_distinct_value
    IF @i <= @distinct_value_count_with_more_rows
    BEGIN
        SET @current_rows_per_value = @rows_per_distinct_value + 2000
    END
    
    SET @j = 1
    WHILE @j <= @current_rows_per_value
    BEGIN
        INSERT INTO myTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10)
        VALUES ('Value' + CAST(@i AS VARCHAR(2)),
                'Value' + CAST(@j AS VARCHAR(5)),
                'Value' + CAST(@j + 1 AS VARCHAR(5)),
                'Value' + CAST(@j + 2 AS VARCHAR(5)),
                'Value' + CAST(@j + 3 AS VARCHAR(5)),
                'Value' + CAST(@j + 4 AS VARCHAR(5)),
                'Value' + CAST(@j + 5 AS VARCHAR(5)),
                'Value' + CAST(@j + 6 AS VARCHAR(5)),
                'Value' + CAST(@j + 7 AS VARCHAR(5)),
                'Value' + CAST(@j + 8 AS VARCHAR(5)))
        SET @j = @j + 1
    END
    
    SET @i = @i + 1
END

Alter Table dbo.myTable
Add Column11 varchar(50), Column12 varchar(50)

Alter Table dbo.myTable
Add dateModified datetime

Update dbo.myTable
  set Column11 = Column1
     ,Column12 = Column1

Update Top (10) dbo.myTable
   Set Column11 = 'Value7'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column12 = 'Value7'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column11 = 'Value6'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column12 = 'Value6'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column11 = 'Value5'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column12 = 'Value5'
  Where Column1 = 'Value1'

Update dbo.myTable
  set dateModified = getdate() + ID

CASE 1: LOGICAL READS LOW BUT NUMBER OF ROWS ARE HIGH

Create NonClustered Index Idx_col1 
On myTable(Column1, Column11)
Include (Column5, Column6,Column12, dateModified)

set Statistics io on
SELECT Column1
     , Column11
     , Column5
     , Column6
     , Column12
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())

Execution Plan: https://www.brentozar.com/pastetheplan/?id=Sywz516Hh

IO Statistics:

(2803 rows affected)
Table 'myTable'. Scan count 4, logical reads 34, physical reads 1, page server reads 0, read-ahead reads 23, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

CASE 2: LOGICAL READS HIGH BUT NUMBER OF ROWS READ ARE LOW

Create NonClustered Index Idx_col2 on myTable(Column1, Column11,Column12, dateModified)
Include (Column5, Column6)

set Statistics io on
SELECT Column1
     , Column11
     , Column5
     , Column6
     , Column12
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())

Execution Plan: https://www.brentozar.com/pastetheplan/?id=HyWbi1aSh

IO Statistics:

(2803 rows affected)
Table 'myTable'. Scan count 8, logical reads 47, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Answer 1

You can see the reason why if you rewrite the queries with UNION ALL and then get the actual execution plan and look at the properties of the index seeks.

For me the Idx_col2 one has one branch that returns 2813 rows and with 26 logical reads and 7 other seeks that all return 0 rows each have 4 logical reads - 26 + 7*4 = 54 (which is exactly the same as I get with the or query hinting Idx_col2). You'll need to see where things differ for you to end up with 47.

The Idx_col1 plan has fewer seeks (four) and the index it is seeking has one less level for me. So this adds up to 25 + 3*3 = 34.

The 8 seeks vs 4 seeks distinction can be seen by examining the seek predicates in the execution plan...

... and also shows up as the Scan count in your STATISTICS IO results.

(See also "When is a Seek not a Seek?")

(UNION ALL rewrite)

set Statistics io on
SELECT Column1      , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col2)
WHERE Column1= 'Value1' AND Column11 = 'Value1' and Column12 = 'Value1' And dateModified > dateAdd(day,-5, getdate())
union all
SELECT Column1      , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col2)
WHERE Column1= 'Value1' AND Column11 = 'Value1' and Column12 = 'Value6' And dateModified > dateAdd(day,-5, getdate())
union all
SELECT Column1      , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col2)
WHERE Column1= 'Value1' AND Column11 = 'Value5' and Column12 = 'Value1' And dateModified > dateAdd(day,-5, getdate())
union all
SELECT Column1      , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col2)
WHERE Column1= 'Value1' AND Column11 = 'Value5' and Column12 = 'Value6' And dateModified > dateAdd(day,-5, getdate())
union all
SELECT Column1      , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col2)
WHERE Column1= 'Value1' AND Column11 = 'Value6' and Column12 = 'Value1' And dateModified > dateAdd(day,-5, getdate())
union all
SELECT Column1      , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col2)
WHERE Column1= 'Value1' AND Column11 = 'Value6' and Column12 = 'Value6' And dateModified > dateAdd(day,-5, getdate())
union all
SELECT Column1      , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col2)
WHERE Column1= 'Value1' AND Column11 = 'Value7' and Column12 = 'Value1' And dateModified > dateAdd(day,-5, getdate())
union all
SELECT Column1  , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col2)
WHERE Column1= 'Value1' AND Column11 = 'Value7' and Column12 = 'Value6' And dateModified > dateAdd(day,-5, getdate())



SELECT Column1  , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col1)
WHERE Column1 = 'Value1' AND Column11 = 'Value1' --Seek
     And Column12 In ('Value1','Value6') And dateModified > dateAdd(day,-5, getdate()) --Residual
UNION ALL
SELECT Column1  , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col1)
WHERE Column1 = 'Value1' AND Column11 = 'Value5' --Seek
     And Column12 In ('Value1','Value6') And dateModified > dateAdd(day,-5, getdate()) --Residual
UNION ALL
SELECT Column1  , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col1)
WHERE Column1 = 'Value1' AND Column11 = 'Value6' --Seek
     And Column12 In ('Value1','Value6') And dateModified > dateAdd(day,-5, getdate()) --Residual
UNION ALL
SELECT Column1  , Column11     , Column5     , Column6     , Column12
FROM myTable WITH (INDEX = Idx_col1)
WHERE Column1 = 'Value1' AND Column11 = 'Value7' --Seek
     And Column12 In ('Value1','Value6') And dateModified > dateAdd(day,-5, getdate()) --Residual