February 19, 2016 at 9:19 am
I have a fairly complicated issue and looking for any advice.
There are two database servers with transaction replication between them. They are healthy otherwise.
The issue has been ongoing for a while. What happens is that search queries via a stored procedure which hits against a replicated database will get issued and won't clear out from the servers (have seen this on both servers). They will spin up one after another and won't time out or clear out, they will have to be killed in one batch. This will consume 100% of CPU. This will happen intermittently with no apparent cause every 1-2 months.
My best guess is that this has something to do with replication and statistics. We have seen the issue cleared up with "exec sp_updatestats" but also on the subscriber server it seems to cause the issue when we put that command in our nightly indexing job as recommended by microsoft. This doesn't occur on the publisher server. Our index job only updated stats on indexes previously.
February 22, 2016 at 2:04 pm
Any thoughts?
February 23, 2016 at 11:07 am
I would start updating the statistics first on subscribers.
February 29, 2016 at 9:35 am
Updating stats using exec sp_updatestats on the subscriber seems to cause the issue, it doesn't happen when I run that command on the publisher.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply