SQL 2014 Replication

Running 2 instances of SQL 2014 on same VM server. Both are replicating tables to other servers. When I create a new subscription to a server, it creates the subscription but never creates the table on the subscription server and does not send data. After looking the error in the SQL  Agent Job for the subscription,  It shows that it is waiting for a worker thread.
I then delete the subscription and redo it and sometimes it works.

I did not have this problem until we increased the CPUs from 2 to 4 and increased the memory.

Do I need to increase the worker threads? If so How much?
LVL 2
itstherideSystems AdministrationAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Yes - it could be that SQL Server is trying to run this query on more than one scheduler/processor. The processor that initiated the thread may be waiting on other processors to finish and return the results (which they may not be able to do in case the application has not been designed for parallel workloads).

Rather than worker threads, my guess is that you are running into parallelism issues.

What I would suggest is to reduce the max. degree of parallelism down to half of the physical processors (or 1). What this means is that any single query will run use the max. degree of parallelism and only spin up that many worker threads (e.g. if MAXDOP = 1, a query will run on a single scheduler/processor). All CPUs will still be used by SQL Server - it's just that they will not be used to run the same query.

sp_configure 'show advanced options',1;
RECONFIGURE
GO
sp_configure 'max degree of parallelism',1; -- setting MAXDOP to 1 due to low number of physical processors
RECONFIGURE;
GO
sp_configure 'show advanced options',0;
RECONFIGURE
GO

Open in new window

0
itstherideSystems AdministrationAuthor Commented:
I am a little confused. I am not trying to run a query, I am just trying to add subscription to a publication. I checked and there are over 200 subscription agent jobs running on each instance of SQL. They are already doing transaction replication and each publication is currently replicating to 2 servers. So I am trying to replicate to a third server and having these issue. In the  agent activity monitor it shows "waiting on worker thread" while trying to initiate.  If I stop the agent job in the activity monitor, then go to "view the  synchronization status" on the subscription and start the agent it completes with no problem. I did not have this issue until added the 2 additional processors and trying to replicate to the  3rd server.  I forgot to mention that it is SQL 2014 Standard version.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not sure if the additional CPUs are the real problem. Did you try to run the Snapshot agent after adding the new subscriber?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

itstherideSystems AdministrationAuthor Commented:
Yes I did run the snapshot agent prior to adding the new subscriber.  One the second instance, I tried to add a subscriber and it sits there and retries to initialized and does not mention waiting on worker thread.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I did run the snapshot agent prior to adding the new subscriber
Not prior. If should be after adding the new subscriber. Doesn't make sense to run it before as the subscriber doesn't exist yet.
0
itstherideSystems AdministrationAuthor Commented:
sorry, I thought you meant when I added the subscriber. Yes I did try to run  the snapshot after adding the subscriber and did not make a difference.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no error entry in the Replication log?
0
itstherideSystems AdministrationAuthor Commented:
no there is no log in the replication log, unless I am not looking the right place??

I am still seeing the initial issue about worker threads , in the agent activity monitor screen.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
no there is no log in the replication log, unless I am not looking the right place??
Open Replication Monitor and in the Agent tab right-click on the error line and choose to View Details. This should present you with the error details.
ReplicationMonitorError.PNG
0
itstherideSystems AdministrationAuthor Commented:
Here is what is in the Replication Log. I also have a screen shot of what I am seeing  the activity monitor screen.
One thing I noticed when I was setting up the subscriptions on  the other instance of the server, that one there was 200 jobs, it was showing the worker thread problem. Prior to the number 200 agent job, had no issues.
Repl-Doc1.docx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why would you have 200 jobs running simultaneously? Is any of those job really necessary? You should review them and check which ones can be disabled/deleted.
Meanwhile, run the following command and let me know the result of it:
exec sp_configure 'max worker threads'

Open in new window

0
itstherideSystems AdministrationAuthor Commented:
the 200+ jobs are created for the SQL Replication subscriptions. I have 1 Publication per table

the setting for max worker threads is 0
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I was expecting you to post the result of the command.
I will also need information about the number of CPUs in the server and if it's a 32b or 64b SQL Server that you've running.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Btw, when having more than 100 subscribers isn't a good idea to have Push Subscriptions as the jobs are staying in the Publisher.
You should think in change it to Pull Subscriptions so each subscriber will run his own job instead of having all the jobs running by the Publisher. This will reduce drastically the usage of your worker threads.

Check the recommendations in this MSDN article.
0
itstherideSystems AdministrationAuthor Commented:
These are all Transaction Publications mostly used for Reporting. Maybe I should combine the number of articles  (tables) to reduce the number of push? Each of these 100+ publications only have one table with 3 subscribers.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe I should combine the number of articles  (tables) to reduce the number of push?
How will you do that when you're saying there's only one table being published?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
itstheride, do you need more help on this question?
0
itstherideSystems AdministrationAuthor Commented:
I will put more that one table in each Publication, which will reduce the number of Agent Replication  jobs running.

I have no more questions.

Thanks for your advice!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't forget to close this question.
Cheers.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Thank you, but I would expect you to choose a more proper comment as solution.
Cheers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Virtualization

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.