qew420 (4/21/2014)
Some tables are huge also but still all was good before adding replication...
I can check the missing Indexes but again only counting the # of records .. system is taking so long
If some of the tables are huge, then they probably aren't taking advantage of the indexes that exist. Replication does not necessarily use the same indexes as other queries do. And smaller tables could be blocked by the larger tables not moving well.
I think there is somewhere problem with San too but before telling my Manager I need to make sure there is nothing on my side causing this issue
I/O isn't just about hard drives and disk controllers. It is about reading and writing from the database. Hence the reason I suggest running PerfMon and Profiler together. Profiler now has the ability to hook the two traces together and tell you exactly where the bottleneck is. And if the indexes aren't being used, or there aren't indexes for the tables, then those alone would cause a bottleneck before it even gets to the SAN part. But the traces should tell you that.
There are a lot of moving parts in Replication. Where do you have your Distributor? Is it on the Publisher, the Subscriber, or is it a standalone server / instance?