SQL Server Agent Architectural Overview

As mentioned earlier, the SQL Server Agent service does not have its own authentication mechanism; therefore, it relies on SQL Server to validate the user. A SQL Server Agent user connects to SQL Server via any TSQL client and executes stored procedure commands. Even if the user uses the SQL Server Management Studio user interface or writes managed programs using SQL Server Management Objects, eventually the user actions will still get translated into a series of stored procedure calls. User actions typically result in metadata changes inside the SQL Server store. If such a change requires an immediate update of SQL Server Agent's internal data cache or a direct response from SQL Server Agent, the extended stored procedure xp_sqlagent_notify is called to place a formatted message in a shared memory object from SQL Server. SQL Server Agent continuously looks at this communication channel and processes all information placed into the shared memory object sequentially. The overall architecture of SQL Server Agent is shown in Figure 8-17.

Warning

The shared memory object has a finite capacity, although a very large one, so it can hold only a certain limited number of messages. As each message gets processed by SQL Server Agent, it is removed from the temporary storage, enabling the system to reclaim this piece of memory. Thus, it is theoretically possible to reach shared memory saturation by executing too many metadata operations in parallel. In this case, a user message will be returned from the stored procedure call that lets the user know to hold off making a request until new memory becomes available.

SQL Server Agent stores all its metadata exclusively in the MSDBdatabase. Most SQL Server Agent stored procedures are also contained within this database. Here is a list of some of the most significant metadata tables SQL Server Agent uses, divided by area:

Warning

Do not attempt to modify any of these tables directly. They are meant to be updated via management stored procedures.

The preferred way of viewing and interpreting table data is through "help" procedures. Take a look at sp_help_job, sp_help_jobstep, sp_help_schedule, sp_help_operator, sp_help_alert, sp_help_proxy, and others. Merging data from related metadata tables using these procedures performs all the necessary security checks, so they may be the only option for a user who is not an administrator on the system and therefore does not have access to all underlying tables.

To pull metadata from a SQL Server Database Engine MSDB database, SQL Server Agent establishes regular SQL Server connections from one of its worker threads. SQL Server Agent has an internal thread pool servicing the job execution engine; it no longer creates a thread for each new job as it did in previous releases. The total number of threads in a thread pool is limited by the Registry value MaxWorkerThreads. Thus at any given time only a certain number of jobs can run concurrently. What happens to the job if a thread is not available when it needs to execute? The job remains in the internal job queue until it has a chance to run. This internal balancing assures the system never starves for resources while attempting to maximize throughput.

In addition, every subsystem in SQL Server Agent has its own limit on the number of threads it is allowed to use concurrently. This limit is stored in the msdb.dbo.syssubsystems table and by default is proportional to the number of processors the computer has. For example, the TSQL subsystem is defined as the number of processors multiplied by 20. This limitation is different for every subsystem and also serves as a limiting factor in determining how many jobs are going to run in parallel. Consider a situation in which 100 jobs are scheduled to run at the same time, each consisting of at least one T-SQL step. Because the system allows at most only 20 concurrently executing T-SQL steps, 80 jobs will be sitting in the queue waiting for the first 20 to complete, even though each job has its own thread available.

As you saw in Figure 8-17, SQL Server Agent establishes separate ad-hoc connections to SQL Server for running T-SQL subsystem job steps. That is actually one of the reasons the number of concurrently running T-SQL job steps is limited.

SQL Server Agent has its own error log file called SQLAGENT.OUT. By default it is stored in the same location as SQL Server logs, but can be redirected through the Registry entry ErrorLogFile and also exposed by the stored procedure sp_set_sqlagent_properties through the @error_log_file parameter. SQL Server Agent logging can also be controlled through the ErrorLoggingLevel Registry entry. This flag is a bit mask that enables you to combine the following basic set of values: log error messages (0x01), log warning messages (0x02), and log informational messages (0x04). Thus if you would like to log all messages, you need to supply a value of 0x07.

Note

0x07 is the only value that can be set through the SQL Server Agent Properties dialog part of SQL Server Management Studio. It corresponds to the Include execution trace messages check box.

Similar to SQL Server Database Engine, SQL Server Agent cycles error logs on each service restart.

Tips and Tricks

You can cycle the internal SQL Server Agent error log without restarting the service by calling the sp_cycle_agent_errorlog stored procedure.

The monitoring and alerting capabilities of SQL Server Agent are covered as part of Chapter 9. As for the principal architecture of the SQL Server Agent monitoring mechanism, there is a dedicated thread that polls three different alert sources (Windows Event Log, SQL Server Performance Monitor Counters, and Windows Messaging Instrumentation Provider) in a round-robin fashion. This thread is responsible for firing alerts in response to some particular value that satisfies a pre-configured condition.

SQL Server Agent Architectural Overview

Figure 8-17. SQL Server Agent architectural diagram.