Index Consolidation

Question

Index Consolidation

Bobby Glover

SSCarpal Tunnel

Points: 4143
More actions
February 27, 2013 at 7:51 am

#271009

The scenario.
Missing Index 1
Column A,B
Missing Index 2
Column A,C
Missing Index 3
Column A,D Include E
Can these be consolidate as
Column A,B,C,D include E
Indexes ordered 1-3 in terms of Impact value.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

They can be, but the resultant index will be less efficient for queries that filter on anything other than A, B than the individual indexes would have been.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 2

Thougt as much, but nice to have it confirmed.

Thanks.

I'll ponder which way I'll implement this. Trying not to create too many new indexes. Trying to consoliodate missing indexes.

Agian Thanks

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 3

Bobby Glover

SSCarpal Tunnel

Points: 4143

February 27, 2013 at 8:14 am

#1591575

I just re-read and my typing\spelling sucks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Some details on index column ordering

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 5

Thanks,

On a slightly different note. My DB suffers from serious Lookups. The only way I know to improve this is to see if I can consolidate the included columns from missing indexes into the existing index as long as they share the same indexed column. Too many sprocs and parameters to see which are the offending sproc.

I can have between 30-300 sprocs on a table. Not my design....Just one of the issues I have to live with.

Any other way. To see which indexes clearly needs some tweaking. To prevent so many lookups.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Are the lookups a problem?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 7

The whole db is one massive problem, no it's not but it annoys me and I'm trying to make improvements where I see fit.

Once I tighten up the top 30 or so tables I can move onto other area and then revisit.

ScottPletcher SSC Guru Points: 101101 More actions · Answer 8

Look into clustering the table on column A: that will likely address the issue more effectively and efficiently than a slew of nonclus indexes.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".