Index design on a table where the primary key is not clustered

Question

Index design on a table where the primary key is not clustered

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

If that was my table, I'd start by finding all the queries that refer to that table (profiler can help here), then in a dev environment, get a copy of that table, drop all the indexes and run the queries one by one with the exec plan on to see what they do, what columns they filter on/group on/sort on.

Based on the results from that, I'd design new indexes.

Is a bit extreme, but is sometimes necessary, especially if the original developer didn't understand indexeing properly.

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

noeld SSC Guru Points: 96590 More actions · Answer 2

You don't have to be an SQL Server Ranger to know that there is something terribly wrong with 34 indexes in one table.

By the way a quick way to determine index usability is by knowing you data. If the index "selectivity" is > 10% chances are is not going to help ...

Cheers,

* Noel

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

noeld (12/4/2007)
You don't have to be an SQL Server Ranger to know that there is something terribly wrong with 34 indexes in one table.

I've seen worse. 38 indexes on one table, none clustered, all with the same leading column, a column that had 0% selectivity and was not used in any query.

I love vender code so much. </sarcasm>

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

noeld (12/4/2007)
By the way a quick way to determine index usability is by knowing you data. If the index "selectivity" is > 10% chances are is not going to help ...

I'm thinking you want to reverse that >, don't you? The higher the selectivity, the higher the usefulness (in most cases).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

GilaMonster (12/4/2007)
noeld (12/4/2007)
You don't have to be an SQL Server Ranger to know that there is something terribly wrong with 34 indexes in one table.
I've seen worse. 38 indexes on one table, none clustered, all with the same leading column, a column that had 0% selectivity and was not used in any query.
I love vender code so much. </sarcasm>

I personally LOVE such situations (and countless others out there)!!! It is one of the main reasons my services are in such high demand. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

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

TheSQLGuru (12/5/2007)
I personally LOVE such situations (and countless others out there)!!! It is one of the main reasons my services are in such high demand. 😎

If I was contracting I'd agree with you. Currently I'm full time. That mess caused me several late nights.

Vendor kept on telling my boss that I was useless and didn't know what I was talking about, then when the revised indexes worked better, they took the changes and incorporated them into the application they sell and told everyone the changes were their own. :angry:

Did I mention I love vendors?

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 7

I have a series of posts about how indexes work - this one ( I had to post in three parts so if you want the original doc drop me a private message and and I'll mail you the originals )

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/09/27/analysing-indexes-part-4-size-does-matter.aspx

the relationship between the clustered index and others is interesting, I'd almost always define a NC index to duplicate a clsutered index - I tend to see the clustered index more for physical ordering and removing fragmentation and I sometimes wonder if there's any real advantage to having clustered indexes - anyway read my post and feel free to mail me direct with any questions.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/