Understanding When the Workload Governor Is Activated
Topic last updated -- January 2004
Understanding when the workload governor is activated requires a high-level understanding of SQL Server 2000 connections and operations.
An instance of the SQL Server 2000 database engine runs as a process separate from applications. An application works with the instance by opening a connection to the instance, sending a series of commands over the connection, and then closing the connection. The most common type of command the application sends is Transact-SQL batches. (A batch is one or more Transact-SQL statements that are run as one executable unit.)
This series of commands can be illustrated using the SQL Server 2000 osql utility. When you launch osql, it uses the ODBC API to make a connection to an instance of the database engine. As you type in Transact-SQL statements, osql builds them into a string variable that it can pass to an ODBC API function that sends batches over the connection. osql uses a GO command to define batches. Whenever the utility reads a GO command, it sends all of the Transact-SQL statements that have been collected since the last GO command as a batch to the database engine.
This is an example of specifying batches in the input to the osql utility:
/* Start of the first batch, which only has one statement. */
/* End of the first batch; send the USE statement to the database engine. */
/* Start of the second batch, which has three statements. */
DECLARE @ExampleVariable INT
SELECT @ExampleVariable = COUNT(*)
/* End of the second batch; send DECLARE, SELECT, PRINT to the engine. */
When you are done working with the database and type an EXIT command, osql uses the ODBC API to close the connection to the database engine. osql then shuts down.
In an instance of the database engine, the operation to open a connection includes validating the login ID and password and then allocating the internal data structures the instance uses to manage the connection. The operation to close a connection involves rolling back any incomplete transactions and deallocating the data structures associated with the connection.
The operation to process a batch of Transact-SQL statements starts when the batch is received over the connection and continues until the last results have been sent back. Some of the objects stored in SQL Server databases, such as views, Transact-SQL stored procedures, triggers, and user-defined functions, are themselves batches of SQL statements. These objects have no effect on the workload governor. The execution of these types of objects is counted as part of the operation to process the batch sent in by the application. The governor only detects the fact that the instance is processing the batch that came in from the application; it does not detect how many of these objects the application batch executes.
Connections between the database engine and an application most often remain in an inactive state, waiting for the application to build and send a new command. In systems where there are thousands of concurrent connections in an instance of the database engine, only a small percentage of the connections transmit commands at the same time. Most of the users are doing things that do not interact with the database, such as scrolling through a Web page, typing in new data, or answering a phone call from their manager. Only a few of the users have just performed an action, such as clicking an ENTER button, that sent a command to the instance.
Several SQL Server components make connections to an instance of the database engine. The workload governor does not distinguish between connections from applications and those from SQL Server components; operations on connections from SQL Server components are counted against the governor limits. These include connections from SQL Server Agent, Replication agents, and Data Transformation Services (DTS) packages.
The workload governor only counts operations on connections that are coming into an instance of the database engine; it does not count operations on connections going out to other instances. Certain kinds of Transact-SQL statements cause the instance on which they are executed to open a connection to a remote instance of the database engine. If the local instance of the database engine has a workload governor, the only operation counted by the local workload governor is the operation to process the batch containing the Transact-SQL statement. If the remote instance of the database engine has a workload governor, the remote governor will count all operations it receives over the connection. These types of Transact-SQL statements are:
- Distributed queries, which cause the local instance to connect to the remote instance, send the remote instance one or more Transact-SQL batches, and then close the connection to the remote instance. The governor on the local instance counts only the operation of processing the batch containing the distributed query. The governor on the remote instance counts all the operations sent over the connection.
- Remote stored procedure calls, which operate the same as distributed queries.
- Extended stored procedures, which can open a connection to a remote instance of SQL Server. In this case, the extended stored procedure is treated like a distributed query. The governor on the local instance counts only the operation of executing the batch that ran the extended stored procedure. The governor on the remote instance counts all the operations it receives over the connection.
- OLE automation objects, which can be referenced from Transact-SQL statements if the object is registered with the instance of the database engine using the Office Automation system stored procedures, such as sp_OACreate. The workload governor treats connections from these OLE objects the same way it treats connections from extended stored procedures.
Extended stored procedures and OLE objects can also open a connection to the same instance of the database engine on which they are executing. The instance treats this connection as a new inbound connection. It counts as operations all commands sent over the connection, in addition to counting the processing of the batch that executed the extended stored procedure or OLE object. For example, if you execute an extended stored procedure that makes a connection back to that same instance, the governor counts processing the batch you sent as one operation, and it treats as a second operation any command that the extended stored procedure executes on the connection it opened.
In addition to logins, batches, and logoffs, connections involved in distributed transactions can also receive commands synchronizing the distributed transaction across multiple resources. Many of these commands are not executed directly by applications; they are generated internally by instances of SQL Server or the transaction manager (TM) that is controlling the distributed transaction. For example, an application could connect to an instance of SQL Server 2000 Standard Edition and execute an UPDATE statement that references a linked table on an instance of MSDE 2000. This means that the connection opened by the instance of Standard Edition against the instance of MSDE 2000 is in a distributed transaction. If the computer running the application then loses its network connection, the TM instructs both instances to roll back their parts of the distributed transaction. The workload governor counts the connection in the instance of MSDE 2000 as active for as long as it takes the instance to roll back the distributed transaction.