January 10, 2019 at 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.
January 10, 2019 at 6:17 pm
I would also take the elapsed times of the query.
What is the query you were running?
January 10, 2019 at 7:25 pm
WhiteLotus - Thursday, January 10, 2019 4:53 PMHi 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]
GOFirst 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.
Did you look at the actual execution plan? That might explain it.
January 11, 2019 at 6:34 am
WhiteLotus - Thursday, January 10, 2019 4:53 PMHi 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]
GOFirst 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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2019 at 6:59 am
Grant Fritchey - Friday, January 11, 2019 6:34 AMWhiteLotus - Thursday, January 10, 2019 4:53 PMHi 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]
GOFirst 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.
You are saying there is a change in another table but you are only showing us a single index on one table. What would really help here is the actual query you are running and the actual execution plan, with the execution plan uploaded as a .sqlplan file.
January 11, 2019 at 7:56 am
Lynn Pettis - Friday, January 11, 2019 6:59 AMYou are saying there is a change in another table but you are only showing us a single index on one table. What would really help here is the actual query you are running and the actual execution plan, with the execution plan uploaded as a .sqlplan file.
Or at least show us the two scripts that are being used to modify/create the index. I'll bet there are differences.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2019 at 10:27 am
WhiteLotus - Thursday, January 10, 2019 4:53 PMHi 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]
GOFirst 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.
Still around? Could we get the information we requested?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply