<div>
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). <br />
<br />
Rather than worker threads, my guess is that you are running into parallelism issues.<br />
<br />
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.<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42443294-1"><span>sp_configure 'show advanced options',1;</span>
<span>RECONFIGURE</span>
<span>GO</span>
<span>sp_configure 'max degree of parallelism',1; -- setting MAXDOP to 1 due to low number of physical processors</span>
<span>RECONFIGURE;</span>
<span>GO</span>
<span>sp_configure 'show advanced options',0;</span>
<span>RECONFIGURE</span>
<span>GO</span>
</code></pre>
</div>


<div>
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 &nbsp;agent activity monitor it shows &quot;waiting on worker thread&quot; while trying to initiate. &nbsp;If I stop the agent job in the activity monitor, then go to &quot;view the &nbsp;synchronization status&quot; 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 &nbsp;3rd server. &nbsp;I forgot to mention that it is SQL 2014 Standard version.
</div>


<div>
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?
</div>


<div>
Yes I did run the snapshot agent prior to adding the new subscriber. &nbsp;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.
</div>


<div>
<blockquote>
 I did run the snapshot agent prior to adding the new subscriber
</blockquote>
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.
</div>


<div>
sorry, I thought you meant when I added the subscriber. Yes I did try to run &nbsp;the snapshot after adding the subscriber and did not make a difference.
</div>


<div>
There's no error entry in the Replication log?
</div>


<div>
no there is no log in the replication log, unless I am not looking the right place??<br />
<br />
I am still seeing the initial issue about worker threads , in the agent activity monitor screen.
</div>


<div>
<blockquote>
no there is no log in the replication log, unless I am not looking the right place??
</blockquote>
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.<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/01_w05/1266956/ReplicationMonitorError.PNG" target="_blank" title="ReplicationMonitorError.PNG (22 KB)"><img alt="ReplicationMonitorError.PNG" class="file-block lazyload" style="max-width: 793px;" data-src="https://filedb.experts-exchange.com/incoming/2018/01_w05/1266956/ReplicationMonitorError.PNG" /></a>
</div>


ReplicationMonitorError.PNG

<div>
Here is what is in the Replication Log. I also have a screen shot of what I am seeing &nbsp;the activity monitor screen.<br />
One thing I noticed when I was setting up the subscriptions on &nbsp;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.<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/02_w05/1268805/Repl-Doc1.docx" target="_blank" class="file-inline" title="Screen Shots (47 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Repl-Doc1.docx</a>
</div>


Repl-Doc1.docx

<div>
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.<br />
Meanwhile, run the following command and let me know the result of it:<br />

<pre><code class="code-1 nolinks" id="code-20-42455850-1"><span>exec sp_configure 'max worker threads'</span>
</code></pre>
</div>


<div>
the 200+ jobs are created for the SQL Replication subscriptions. I have 1 Publication per table <br />
<br />
the setting for max worker threads is 0
</div>


<div>
I was expecting you to post the result of the command.<br />
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.
</div>


<div>
Btw, when having more than 100 subscribers isn't a good idea to have Push Subscriptions as the jobs are staying in the Publisher.<br />
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.<br />
<br />
Check the recommendations in this <a href="https://docs.microsoft.com/en-us/sql/relational-databases/replication/subscribe-to-publications" rel="ugc">MSDN article</a>.
</div>


<div>
These are all Transaction Publications mostly used for Reporting. Maybe I should combine the number of articles &nbsp;(tables) to reduce the number of push? Each of these 100+ publications only have one table with 3 subscribers.
</div>


<div>
<blockquote>
Maybe I should combine the number of articles &nbsp;(tables) to reduce the number of push?
</blockquote>
How will you do that when you're saying there's only one table being published?
</div>


<div>
itstheride, do you need more help on this question?
</div>


<div>
I will put more that one table in each Publication, which will reduce the number of Agent Replication &nbsp;jobs running.<br />
<br />
I have no more questions.<br />
<br />
Thanks for your advice!
</div>


<div>
Thank you, but I would expect you to choose a more proper comment as solution.<br />
Cheers.
</div>


<div>
<div class="content wysiwyg-content">
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 &nbsp;Agent Job for the subscription, &nbsp;It shows that it is waiting for a worker thread. <br />
I then delete the subscription and redo it and sometimes it works.<br />
<br />
I did not have this problem until we increased the CPUs from 2 to 4 and increased the memory.<br />
<br />
Do I need to increase the worker threads? If so How much?
</div>
</div>


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?

SQL 2014 Replication

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Virtualization is the act of creating a virtual (rather than actual) version of something, including (but not limited to) a virtual computer hardware platform, operating system (OS), storage device, or computer network resources. Virtualization is usually the creation of a system that executes separate from the underlying hardware resources, or the creation of an entire desktop for systems located elsewhere, similar to thin clients.

Virtualization

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.