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 ?



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2