The very last part of each key value is a number that specifies the max_work_thread setting for that specific subsystem. In the following example, the LogReader subsystem has a maximum of 25:
Note The Transact-SQL subsystem key is not created by default. To modify the Transact-SQL subsystem key, add a value of Transact-SQL with the datatype REG_SZ. In the string, add only a number such as 40 or 100. This number represents the new max_worker_thread setting for the Transact-SQL subsystem. For example:
|ActiveScripting||Active Scripting Subsystem||D:\Mssql7\Binn\Sqlatxss.dll||NULL||ActiveScriptStart||ActiveScriptEvent||ActiveScriptStop||10|
|Distribution||Replication Distribution Subsystem||D:\Mssql7\Binn\Sqlrepss.dll||D:\Mssql7\Binn\Distrib.exe||ReplStart||ReplEvent||ReplStop||100|
|LogReader||Replication Transaction-Log Reader Subsystem||D:\Mssql7\Binn\Sqlrepss.dll||D:\Mssql7\Binn\Logread.exe||ReplStart||ReplEvent||ReplStop||25|
|Merge||Replication Merge Subsystem||D:\Mssql7\Binn\Sqlrepss.dll||D:\Mssql7\Binn\Replmerg.exe||ReplStart||ReplEvent||ReplStop||100|
|Snapshot||Replication Snapshot Subsystem||D:\Mssql7\Binn\Sqlrepss.dll||D:\Mssql7\Binn\Snapshot.exe||ReplStart||ReplEvent||ReplStop||100|
Because the Transact-SQL subsystem default is 20, the maximum number of concurrent Transact-SQL jobs is reached quickly if several Transact-SQL jobs are set to run. Add the Transact-SQL subsystem value as a string with a value that is higher than 20 if you notice that the Transact-SQL jobs are being queued for this reason.
A design limitation imposes a one second delay between jobs. This limitation was set so that the job scheduler does not monopolize the resources on the hosting server. Because of this limitation, up to 60 jobs can be started in the same one-minute interval. If jobs run and finish in less than one minute and are started at the same time based on a one-minute schedule, some jobs may never run. The jobs may never run because jobs that were already executed are retriggered every minute. Other jobs are left in a starvation situation.
Important This information applies to starting news jobs and does not impose a limitation on jobs that are running at the same time.
We recommend that you group jobs into batches of less than 60 simultaneous jobs and stage schedules so that there is enough bandwidth for SQL Server Agent to run these jobs.
ID do Artigo: 306457 - Última Revisão: 16 de jun de 2008 - Revisão: 1