Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Covering Index Issue Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2014 9:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 23, Visits: 24
I Create an Index Like

Create Nonclustered Index In_product_pdid
ON Product(pd_id)
INCLUDE pd_desp; <- Include product description column with Index
GO

My Question is in 60% query i need pd_desp column in my Sql query and by using this i remove additional Key Lookup operator and nested loop join Operator from Query execution plan.
But in 40% query i need not pd_desp column in my Sql query. so on that time this index may degrade some query performance or not.

is there any other way kindly suggest.
Post #1579875
Posted Thursday, June 12, 2014 12:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 1,575, Visits: 4,320
itsgaurav (6/11/2014)
I Create an Index Like

Create Nonclustered Index In_product_pdid
ON Product(pd_id)
INCLUDE pd_desp; <- Include product description column with Index
GO

My Question is in 60% query i need pd_desp column in my Sql query and by using this i remove additional Key Lookup operator and nested loop join Operator from Query execution plan.
But in 40% query i need not pd_desp column in my Sql query. so on that time this index may degrade some query performance or not.

is there any other way kindly suggest.


One way would be moving the description column into a table of its own, has some additional benefits in terms of flexibility, description reuse, multiple languages and versions. I would normally advice against large free-text type columns to be included in an index but then again, it depends.
Post #1579888
Posted Thursday, June 12, 2014 3:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870
What data type is pd_desp?

To be honest, unless the description tends to be thousands of characters long or a LOB data type, it's probably fine as an include. You definitely don't want a second index without the include, that's a waste of space as it's entirely redundant, plus an extra index which needs to be kept up to date.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1579915
Posted Saturday, June 14, 2014 1:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 23, Visits: 24
At first thanks for reply

pd_desp datatype is varchar(500).

Then if i not take this column in my sql .
And I include this in my index as mentioned above question.
Then it will reduce performance of my sql or not.
Kindly suggest. Please
Post #1580799
Posted Saturday, June 14, 2014 11:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:54 PM
Points: 23,240, Visits: 31,936
itsgaurav (6/14/2014)
At first thanks for reply

pd_desp datatype is varchar(500).

Then if i not take this column in my sql .
And I include this in my index as mentioned above question.
Then it will reduce performance of my sql or not.
Kindly suggest. Please


I doubt if it will significantly impact performance if the only value used from the index is pd_id.



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)
Post #1580852
Posted Saturday, June 14, 2014 11:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870
itsgaurav (6/14/2014)

And I include this in my index as mentioned above question.
Then it will reduce performance of my sql or not.


...
To be honest, unless the description tends to be thousands of characters long or a LOB data type, it's probably fine as an include. You definitely don't want a second index without the include, that's a waste of space as it's entirely redundant, plus an extra index which needs to be kept up to date.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1580859
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse