Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2005-12-31 : 21:25:13
|
A couple of older tables have suffered some index creep, and perhaps some overzealous use of the index tuning wizard back in the days before I got a little smart.So, for instance, I have a table like this:create table whatever(i_users int not null primary key clustered,dormant tinyint not null default(0),last_pageview smalldatetime not null default(getdate())) Now, there are aw whopping 7 indexes on various permutations of those 3 columns. In addition to the clustered PK, there are nonclustered index on:*dormant*dormant, last_pageviewdormant, i_users*dormant, i_users, last_pageview*i_users, last_pageview*i_users, last_pageview desclast_pageview, i_users* = created by index tuning wizard at some point.Now, am I correct in thinking that indexes like "dormant, last_pageview" obviate an index just on "dormant"?If I am, am I correct in thinking that, since there's an i_users,last_pageview nonclustered index and an i_users clustered index (PK), I might as well make the PK i_users,last_pageview ?I know that the ideal here is to really dig through the app and some profiler traces to figure out which indexes are important and which are relics. But I'm really looking ot learn theory here -- if I run across stuff like this in the future, am I correct that less detailed indexes are obsoleted by more detailed ones on the same columns and sort orders?Thanks-b |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-12-31 : 22:33:43
|
| To follow up, I've found a bunch of references saying these are duplicates and should go, but looking at execution plans in profiler, some of them are being used (and not just to update them, either). In particular, the index just on "dormant" is being used a few times a second. What gives?Cheers-b |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-01 : 06:57:54
|
"Now, am I correct in thinking that indexes like "dormant, last_pageview" obviate an index just on "dormant"?"Yes, I can't think of a situation where this would NOT be the case"I might as well make the PK i_users,last_pageview ?"More tricky - if its a CLUSTERED index then adding more columns will bulk up all the Secondary Indexes - each will contain the PK in the clustered index. However, if you did NOT have a clustered index at all them ALL the indexes would store a pointer to the data on disk, so have a "wide" PK would not be an issue.If "last_pageview" changes then it will cause the indexes to be updated every time it changes ...One other thing to consider is if the index "covers" the data. (This is not relevant for a Clustered Index as the data is "right there" with the index)If you have:SELECT last_pageviewFROM MyTableWHERE i_users = 'FRED'then SQL can provide the answer from the "i_users, last_pageview" Index without ever having to go near the data record."I know that the ideal here is to really dig through the app and some profiler traces to figure out which indexes are important ..."That's spot on .. see you a bit later then?  Kristen |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-01-01 : 08:34:57
|
| dormant and (dormant, i_users) are certainly duplicates, since the table is clustered on the primary key i_users.I'd wonder whether you're getting much advantage from any of the indexes starting with dormant: the cardinality of dormant can't be more than 256. I'd hazard a guess that it's nearer 2: if so, there would have to be a large skew between the values, and queries looking for fixed unusual values for such indices to be useful.(i_users, last_pageview) and (i_users, last_pageview desc) will contain identical data too as there's only one last_pageview per i_users. If your table really only has those 3 columns, I can't really see the point of either of them. The only place you'd reduce the number of pages read by using those indexes is by seeking on a range of clustered index values and even there they won't be much use. |
|
|
|
|
|
|
|
|
Redundant indexes, or not?