SQL Q+A Running in RAM, Agent Jobs, and More
A SQL Server Agent has a defined maximum number of threads it will run in total for all jobs. This is independent of the number of threads that can be run per subsystem. In SQL Server 2005, if this maximum number of threads is reached because so many jobs are running, then any jobs that are requested to start will be queued, and the agent will log a message that says:
"The job (%s) has been queued because the maximum number of working threads (%ld) are already running. This job will be executed as soon as one of the working thread finishes execution."
"The job (%s) cannot be run because the maximum number of jobs (%ld) are already running. Please try this job again later."
The following documents provide valuable guidance on creating forests and implementing trust:
Figure 1 Perfmon Log
The Disk Queue Length_Total reaching a maximum of 477 (average of 124) % Disk Time_Total reaching a maximum of 2982 (average of 772) Log Flushes/sec reaching a maximum of 486 (average of 39) Locks: Average Wait Time (ms) reaching a maximum of 210,676 (average of 1237) Latches: Total Latch Wait Time (ms) reaching a maximum of 11,298 (average of 6223) Latches: Average Latch Wait Time (ms) reaching a maximum of 144 (average of 57) Latches: Latch Waits/sec reaching a maximum of 1499 (average of 162)
CREATE TABLE [dbo].[TempSystem]( [TempSystemID] [bigint] NOT NULL, ...
CREATE TABLE [dbo].[TempSystem]( [TempSystemID] [bigint] IDENTITY(1000000,1) NOT NULL, ...
Edited by Nancy Michell
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.