1

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.
1
  • I get an identical number of reads -- 54 total -- from both of the indexes. Also, in both of the plans you pasted, they're using an index called idx_col. I get the sense that there are local factors at play, like fill factor, modifications to the index(es) after creation, or something along those lines. May 25, 2023 at 19:24

1 Answer 1

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... enter image description here

... 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
3
  • Thank you so much! That makes it so clear. Since Paul's article had a range filter, it was relatively easy to rewrite it. How can I re-write my query for better performance since I have to filter on specific values? Do I just have to live with it? May 26, 2023 at 13:47
  • 1
    I'd still go for the query/index that seeks exactly the right rows and where rows read are exactly what you need. The extra logical reads from needing to travel through the upper levels of the index multiple times are unlikely to make much difference to anything, I'm sure you have much bigger performance fish to fry May 26, 2023 at 14:01
  • 1
    Thank you so much, Martin! I really appreciate your advice! May 26, 2023 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.