aurato (11/15/2011)
jared-709193 (11/15/2011)
Now we understand your real concern. You don't want indexes there that are unused because you feel that it may affect performance. Let me ask you this... Are you seeing problems with performance on your inserts?Jared
When any large number of inserts or updates happens on the publication server, replication falls hours behind and kills report performance. I don't know if that's due to insert issues, though. I can't say that I've ever gone "Man that insert took way too long."
Well, let's start with the fact that when you insert data into a replicated table in an array of 1000 records it is replicated as 1000 inserts. Not a set of 1000. So replication can get behind fairly quickly. The indexes are maintained on each individual server and should not affect replication. Also, if your servers are in different locations (WAN) you may want to set it up to pull from the subscriber instead of push. Also, where is your distributor? These things can all affect replication.
As far as the indexes... If I was reasonably sure that these are not being used I would drop them and save the script. If at some point someone complains about the performance of a yearly report, you tell them that there is not an index to help performance on that specific report and that you can help them improve performance by building one. I would always protect myself by being ready to say "well, it did not run that slow last time because there is more data in the table this year than last year. I can probably improve performance with this though." Then create the index and document WHY it was created somewhere.
Jared
Jared
CE - Microsoft