Why new nonclustered index cause more IO reads on table?
We are in process of creating new non-clustered index for specific views. One of the view looks like this:
SELECT * FROM [dbo].[table_backup] UNION ALL ( SELECT * FROM [dbo].[tableA] a LEFT JOIN [dbo].[tableB] b on a.column1PK = b.column1PK and a.column2PK = b.column2PK and a.column3PK = b.column3PK and a.column4PK = b.column4PK WHERE a.column_5= 'D' AND a.column_6 <> '' AND a.column3PK = b.column3PK AND a.column_7 <> 'D' AND a.column_7 <> 'M' )
I already prepared non-cluster filtered index for tableA:
CREATE NONCLUSTERED INDEX [IX_column_6_INCLUDE] ON [sap].[BSEG] ( column_6 ASC, ) INCLUDE ( column_5 column_7 ) WHERE column_6 <> '' AND a.column_5 in ('D','K') AND column_7 <> 'D' AND column_7 <> 'M';
I add also value 'K' for column_5 because in filter because we have similar second view which contains same table but with filter on column_5 equals to 'K'.
Below are execution plans:
Without index: https://www.brentozar.com/pastetheplan/?id=rJwAlxDAF
Sum of logical reads on all tables = 49 189 CPU time = 971 ms Elapsed time = 489 ms
With index: https://www.brentozar.com/pastetheplan/?id=SJzSB7dAF
Sum of logical reads on all tables = 127 932 CPU time = 2109 ms Elapsed time = 1010 ms
Query which I run for test performance:
SELECT TOP (10000) * INTO #temp FROM [dbo].[view]
Do you know why adding index cause performance decrease ?
Demir last edited by
Without knowing the full definition of both tables, here’s what I can see.
Your faster plan is doing a single clustered index scan of Object6 and returning 160k rows.
Your slower plan is doing a nonclustered index scan on Object6, reading 399k rows and only retuning 57k of them. Then it’s following that up with 24,570 key lookups.
Adding column 5 & 7 as keys instead of includes on the nonclustered index may help make it a seek instead of a scan.
After that, consider making it a traditional nonclustered index, without the filter, and let the key columns do their work. Or try both and see which one performs better.
Likewise, try to avoid SELECT * and instead only select the columns you really need.