SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index - included column


Index - included column

Author
Message
WhiteLotus
WhiteLotus
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10814 Visits: 1109
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.
Jonathan AC Roberts
Jonathan AC Roberts
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12010 Visits: 5288
I would also take the elapsed times of the query.
What is the query you were running?
pietlinden
pietlinden
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55478 Visits: 17543
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.


Did you look at the actual execution plan? That might explain it.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365450 Visits: 34533
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.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)

Group: General Forum Members
Points: 405913 Visits: 43733
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.

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.


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365450 Visits: 34533
Lynn Pettis - Friday, January 11, 2019 6:59 AM

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.

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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)

Group: General Forum Members
Points: 405913 Visits: 43733
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.

Still around? Could we get the information we requested?


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search