Grant Fritchey - Friday, January 11, 2019 6:34 AM

WhiteLotus - Thursday, January 10, 2019 4:53 PM

Hi All ,
I have tried 2 different approaches on changing the index structure
Current Index structure :
CREATE NONCLUSTERED INDEX [IX_ABC] ON [Table XX].[ColumnXX]
(
    [ID] ASC
)
INCLUDE ( [ABCID],
[XYZID],
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

First approach :
I modified the current index by adding 2 columns as included column , it shows improvement in logical read (from 50.000 down to 750 ) on the table which contains those 2 columns
But on the other hand it shows the decrease of logical read from other table which participate in the query ( from 18000 to 28000)

Second approach :
I deleted current index and created a new one with those 2 columns added as included column . it shows improvement in logical read (from 50.000 down to 750 )on the table which contains those 2 columns .
And shows the small decrease of logical read from other table which participate in the query ( from 18000 to 21000)

Question :
Why  second approach is better than the first one ?

Thanks and appreciate your feedback.

To alter an index, you have to drop & recreate it. Both processes are the same. If you're seeing differences, something else is happening, a different setting, a change in data, something.