April 7, 2009 at 6:29 am
Hi everyone. I'm in the process of implementing filegroups using a disk array that I will be purchasing shortly. As I script out the ALTERS to move the the base tables and indexes in a virtual test environment, I'm finding out that there are sometimes several indexes for the same table that are based on the same columns. As a background, this is a MS CRM system that was sold to my company by a third party before I started here. The .mdfs and .ldfs were on the same spindle.
Anyway, as I try to alleviate the I/O bottleneck ( I run Quest Spotlight for SQL Server ), I can't help but notice these indexes which are named differently but are defined with the same columns. I have never encountered this before.
Does anyone have any experience with a scenario like this?
Thanks.
April 7, 2009 at 7:49 am
Can you post the index definitions? If they are identical (same columns in the same order) they they're redundant indexes. It could be that they're not quite the same though.
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
April 7, 2009 at 8:11 am
I once started working in a place and also noticed that there are few indexes that had the same structure but different names. I’ve made the mistake of deleting some of them and then I found out that some of the code had index hint on those indexes. Since then if I encounter such a scenario I run a server side script to check if there is any code the uses an index hint that is based on the index that I want to delete. If you are planning on deleting any index, make sure that there isn’t an index hint that will try to use this index.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2009 at 11:19 am
GilaMonster (4/7/2009)
Can you post the index definitions? If they are identical (same columns in the same order) they they're redundant indexes. It could be that they're not quite the same though.
Yes, they are of identical definition but have different names. This is confimred by sp_statistics and by a third party tool that I use to generatethe database schema - Embarcadero ER/Studio.
April 7, 2009 at 11:31 am
Adi Cohn (4/7/2009)
I once started working in a place and also noticed that there are few indexes that had the same structure but different names. I’ve made the mistake of deleting some of them and then I found out that some of the code had index hint on those indexes. Since then if I encounter such a scenario I run a server side script to check if there is any code the uses an index hint that is based on the index that I want to delete. If you are planning on deleting any index, make sure that there isn’t an index hint that will try to use this index.Adi
Hi Adi. THanks for your input. I'm really not planning on deleting any indexes as I do not want the vendor nailing me for changes that were not discussed with them. I understand what you are saying about the hints and that may be very well be true. However, from a purely database performance standpoint, does it truly make a difference? My impression is that this can even bog down the system. Right now this CRM is running on a 32-bit standard edition of SQL 2005. I plan to scale-up to 64-bit Enterprise 2005. At that point, I'm assuming that I would have a fairly larger buffer size not to mention that enhanced I/O offered by placing the existing data in filegroups on separate spindles.
I guess my question is : is there really any measurable read performance difference if the .mdf is on one physical file and one spindle or can it actually be the source of I/O related issues?
April 7, 2009 at 11:38 am
Well, multiple redundant indexes give you no additional read benefit over having just one index, however when the data is changed all of those indexes have to be updated.
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
April 7, 2009 at 1:31 pm
Multiple identical indexes have no advantages. No speed increase on selects, definite speed decrease on insert/update/delete, and it takes up more disk space too.
On the index hint thing, I would definitely look into that.
What I've seen before, in a situation with duplicate indexes, was multiple devs working in the same database, creating indexes without checking first to see if there was an existing one.
I ran into one dev who created a covering index for every query he wrote, and never bothered to check execution plans, etc., to see if it was even needed. (He didn't check them because he'd never heard of them, much less learned to read them.) He's just heard that covering indexes were good, and took it to an extreme. Might be something similar took place in your database.
- 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
April 8, 2009 at 1:12 pm
Thanks to all of your responses. I've worked on mainframe DB2 as well and never encountered a situation where essentially the same index was propagated with a different name. I will be taking this up with the vendor.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply