November 15, 2011 at 9:03 am
aurato (11/15/2011)
jared-709193 (11/15/2011)
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
I'm pretty bad with replication. So I'll try to explain as best I can. The distributor, which I take to mean the distribution database, is on the subscribing server. The servers are in the same building and LAN. And your second paragraph is what I wanted to do, trying to clear it with my boss.
Also for anyone reading this who's interested in the extra info, I posted the same question on stackexchange at
http://dba.stackexchange.com/questions/7906/determining-that-indexes-on-a-table-are-unused
Well, that could certainly be an issue as I think the distribution database is best kept on the publisher if you do not have a separate box.
For your indexes, one final thing you can do is search all stored procedures for the columns that are in the indexes. Sure, they will be queried sometimes and not filtered on, but at least if you know which SPs contain column names it is a big help. Then you just have to worry about the ad hoc queries.
Thanks,
Jared
Jared
CE - Microsoft
November 15, 2011 at 9:07 am
Easiest test is to script them, drop 'em all and see how much and if it helped.
I still feel there's a bigger issue here.
November 15, 2011 at 9:09 am
What's the reason why the distributor should be on the publication side? I'm sorry to admit I know next to nothing about the inner workings of replication.
November 15, 2011 at 9:15 am
aurato (11/15/2011)
What's the reason why the distributor should be on the publication side? I'm sorry to admit I know next to nothing about the inner workings of replication.
I will look for an article, but from a practical sense think of it this way... If I insert 10,000 rows from table A to table B on the publisher, this goes into the distributor as 10,000 insert statements because replication actually reads the transaction log, not the SQL statement directly. Which means that the distributor has to run these at the subscriber. If the distributor is doing its work and the subscriber is doing its work, that is a lot of resources. However, the work is done as 1 statement on the publisher, so housing the distributor is not going to take up as much resources. Plus, the distributor has to read from the log of the publisher, so it makes sense to have it on the same machine in the situation that you do not have a dedicated distributor box. This is extremely simplified, but I will search for an article.
Thanks,
Jared
Jared
CE - Microsoft
November 15, 2011 at 9:18 am
Oooops.... Pretty Long thread (was almost lost :-))...
Few recommendations:
•Extra indexes are not always bad. And they are not burden on server also. For Example DW schema, you may have N heavy indexes on huge table, NOT all are used for frequently but NOT causing any problem as well because Data Load happens ones a month.
•Index utilization guess based on query may not be a good idea. It should be justified by Developers / Architects.
•If you have any Data Dictionary for the application, please dig in & try to identify their usage. (It’s time consuming but will give you a firm base for your decision)
November 15, 2011 at 9:27 am
Check out this:
http://msdn.microsoft.com/en-us/library/ms151706.aspx
I couldn't find anything really about placing the distribution database on the subscriber, but that's because it is not really done. You see, when you create a publication, it creates a log reader job on the distributor along with a distribution job. If these are on the subscriber, it still has to go back and read the info from the log on the publisher. This is basically a "remote distributor" without its own dedicated box. If you post this question in another thread, I'm sure you will have many people telling you this is a bad idea and they may have better explanations for it.
Thanks,
Jared
Jared
CE - Microsoft
November 15, 2011 at 9:29 am
Thanks everybody for your comments and insight.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply