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.
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.