March 19, 2009 at 9:42 am
I have a table which has multiple indexes....
Index_Name: PK_My_Table
Type: Non-Clustered
Cols: col1,col2,col3,col4,col5
Index_Name: IX_My_Table
Type: Clustered
Cols: col1,col2,col3,col4,col5
The PK_My_Table index is created as a result of the PK constraint on the table and the IX_My_Table index is added as a clustered index to the table.
The querying against this table is heavliy balanced towards queries against single records .... however the index DMV's show the non-clustered index getting most of the seeks, when i would have expected the clustered index to have been predominetly used. Also the columns 1-5 are not always used i.e. some queries may or may not use col2 & col3
My intial reaction was that this was created by someone who didn't know that a Primary key constarint created an index as default (but then again whoever did this specifically identified the primary key as Non-clustered).
My question is does anyone know why someone would do this? does this provide some kind of performance improvement that i'm not considering...
March 19, 2009 at 9:46 am
Michael O Connor (3/19/2009)
I have a table which has multiple indexes....Index_Name: PK_My_Table
Type: Non-Clustered
Cols: col1,col2,col3,col4,col5
Index_Name: IX_My_Table
Type: Clustered
Cols: col1,col2,col3,col4,col5
The PK_My_Table index is created as a result of the PK constraint on the table and the IX_My_Table index is added as a clustered index to the table.
The querying against this table is heavliy balanced towards queries against single records .... however the index DMV's show the non-clustered index getting most of the seeks, when i would have expected the clustered index to have been predominetly used. Also the columns 1-5 are not always used i.e. some queries may or may not use col2 & col3
My intial reaction was that this was created by someone who didn't know that a Primary key constarint created an index as default (but then again whoever did this specifically identified the primary key as Non-clustered).
My question is does anyone know why someone would do this? does this provide some kind of performance improvement that i'm not considering...
It is redundant. I look at dropping both indexes and recreating the Primary key as the clustered index in this case. There is extra overhead in maintaining both indexes.
March 19, 2009 at 9:50 am
and you sure that the non-clustered index doesn't have INCLUDE columns?
If not then I would go with Lynn on this one, the index is not need, and may end up confusing the optimizer and could end up taking up space on the server depending on the column types and the size of the table etc...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 10:19 am
The column list i submitted with this was only as an example, as it turns out the number of columns in this index is quite large at at the very end there is one extra column which a script we were using to bring back data wasn't showing.....
I'm still planning on scrubing this table clean of indexes and building them up again from scratch...thanks for the help guys
March 19, 2009 at 10:30 am
just a note, be care when deleting a large number of indexes from a table. You might notice a drastic change in performance 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 11:08 am
I'll be running this through a staging envoirnment to make sure we don't kill the performance on the system but I appreciate the concern and i couldn't agree with you more for the record
March 19, 2009 at 11:58 am
There is one other difference based on what you have told us - the primary key will be a unique index. This may be a reason why it is being used more often.
March 19, 2009 at 12:12 pm
The querying against this table is heavliy balanced towards queries against single records .... however the index DMV's show the non-clustered index getting most of the seeks, when i would have expected the clustered index to have been predominetly used. Also the columns 1-5 are not always used i.e. some queries may or may not use col2 & col3
Since the PK index is guaranteed-unique, it will pick that one over the other one in many cases, because it knows that there aren't going to be multiple rows with the same criteria.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 19, 2009 at 12:16 pm
Thks guys that makes perfect sense
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply