August 3, 2011 at 12:31 pm
Let me create an example to illustrate my issue.
table = Orders[ RID - Unique autogenerate BigInt, DeliveryCartID - FK BigInt]
indexOrders1 = [RID], indexOrders2 = [RID,DeliveryCartID],IndexOrders3 = [DeliveryCartID]
My understanding of Indexes leads me to believe that Index2 is totally unnecessary, since RID is unique and is the leading key, you do not need further columns included in the index to take you directly to the leaf. The node basically should not need both rid and anything else since RID all by its lonesome should take you to the unique row.
So my issue is that I need to find all these types of indexes worthy of inspection.
What I need is a script that would go through the entire database, and return all the indexes where one of the columns in the key is unique to the table where the index exists, where the count of columns in the index is more than one.
August 3, 2011 at 1:56 pm
If you were talking about the clustered index, I'd agree with you completely. But, if you're looking at wider tables, and you want to avoid bookmark lookups, the extra columns in the indexes can be useful.
It's possible they should be Includes instead of in the key. I've seen a lot of covering indexes that were built in SQL 2k, before that feature was available, that still have columns in the key that really should be Includes. Also, devs/DBAs/tuners who don't know about Include in index creation will create indexes that way. So, make sure the columns aren't in there to avoid bookmark lookups.
- 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
August 3, 2011 at 1:59 pm
In your case, unless Index1 is clustered, I'd do the following:
Drop Index1
Change index2 to UNIQUE INDEX (RID) INCLUDE (DeliveryCartID)
Sure, key lookups on a single row isn't expensive, but there's always the chance of partial scans that need both columns, multiple seeks (like IN or the inner table of a nested loop) and the multiple key lookups would add up.
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
August 3, 2011 at 2:28 pm
Thanks Champ and GM.
All these indexes look like they were supposed to have included columns, or had them removed from the index later due to space but preserving the combo key index.
But anyway, things look so bad that the way forward would probably be to drop the 19 indexes and start with cluster on rid, add non clustered on the most unique queried columns first, and rinse and repeat. Should not be more than 5.
Then move on to the next high trans table.
But I was hoping that some theory would help guide the decision in deciding which to keep on the entire db, without having to do small changes per table while testing each incremental change.
August 3, 2011 at 2:54 pm
Well, as sorta a general rule-of-thumb to apply to most of your tables, I'd say you could start by dropping any index on the RID column, and replacing it with a PRIMARY KEY against that column instead. That would automatically make it a clustered index.
Once that's done, you could probably write an automated script to adjust any index that has the RID column in it, to just remove the RID column, since all indexes by default have the clustered index column(s) as a part of them.
beyond that, any other automation would be kinda tricky, since you'd need to know which indexes are actually being used. I remember seeing a couple of scripts out there, that monitor index usage based on the execution plans stored, and let you know which indexes are not being accessed frequently. I can't recall the names, but if you search the SQL Server Central forums, you can probably find them.
August 4, 2011 at 12:46 am
kramaswamy (8/3/2011)
Well, as sorta a general rule-of-thumb to apply to most of your tables, I'd say you could start by dropping any index on the RID column, and replacing it with a PRIMARY KEY against that column instead. That would automatically make it a clustered index.Once that's done, you could probably write an automated script to adjust any index that has the RID column in it, to just remove the RID column, since all indexes by default have the clustered index column(s) as a part of them.
beyond that, any other automation would be kinda tricky, since you'd need to know which indexes are actually being used. I remember seeing a couple of scripts out there, that monitor index usage based on the execution plans stored, and let you know which indexes are not being accessed frequently. I can't recall the names, but if you search the SQL Server Central forums, you can probably find them.
The bold statement is actually not true. This is only the way SSMS deals with it.
Straight from BOL:
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]
...
<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
Of course you can assign the PRIMARY KEY constraint to a column without the CLUSTERED constraint and vice versa.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply