The SQL Server 2000 Workload Governor
Topic last updated -- January 2004
The version of the Microsoft® SQL Server™ 2000 database engine included in SQL Server 2000 Desktop Engine (MSDE 2000) and SQL Server 2000 Personal Edition contains a workload governor designed to limit performance if the database engine receives more work than is typical of a small number of users.
NOTE: The following description of the workload governor is specific to SQL Server 2000 Desktop Engine (MSDE 2000) and SQL Server 2000 Personal Edition. Future versions of SQL Server will use other mechanisms to differentiate editions.
The Microsoft® SQL Server™ 2000 workload governor is designed to limit the performance of an instance of the database engine any time more than eight operations are active at the same time. An instance of the SQL Server 2000 database engine is one copy of the database software that operates as an operating system service.
The operations counted by the workload governor are:
- Processing a request to open an inbound connection and login.
- Processing a batch of one or more Transact-SQL statements received over an inbound connection.
- Processing a distributed transaction operation, such as a prepare-to-commit or rollback operation.
- Processing a request to log off and close an inbound connection.
- Periodic system-generated operations such as shrinking a database if the database has the AUTO_SHRINK option turned on, completing the deletion of rows from the base level of indexes, or populating the SQL Server performance counters in the System Monitor.
- Instances of SQL Server 2000 Personal Edition will also periodically generate system operations to process any full-text indexes referenced by the databases managed by the instance. SQL Server 2000 Desktop Engine (MSDE 2000) does not support full-text indexes.
Like all versions of SQL Server 2000, SQL Server 2000 Personal Edition and MSDE 2000 allow 32,767 connections to an instance of the database engine. There is no limit for the number of connections that can be executing operations at the same time. The only effect of the workload governor is that it starts slowing down the database engine when more than eight operations are actively running at the same time.
Once it has been activated, the workload governor limits performance by stalling a user connection for a few milliseconds each time the connection requests a logical read or write on any of the pages in the data files of a database. (The governor does not affect log files.) The database engine waits before every data page reference as long as there are more than eight active concurrent operations. When the number of active operations is eight or lower, the database engine does not wait before scheduling any reads or writes. When the workload governor is active, it equally affects all connections; it is not limited to slowing down only the connections that activated the governor. The length of the wait implemented by the governor is constant (it does not vary depending on how many operations are active beyond the limit of eight).
The workload governor operates at the level of an instance of the database engine, not at the level of a database. Each instance can have up to 32,767 databases. The workload governor is activated when there are more than eight active concurrent operations in the instance, even if each operation is working in a different database.
In summary, the workload governor in the database engine for SQL Server 2000 Desktop Engine (MSDE 2000) and SQL Server 2000 Personal Edition works by counting active operations. When there are more than eight active operations at the same time in the same instance of the database engine, the governor implements a slight wait before each logical read or write to a data file. For the amount of work typical in databases used by single users or small workgroups, the cumulative effect of the waits is not noticeable. In systems that are reading and writing large amounts of data, the cumulative affect of all the waits slows the performance of the database engine.
The workload governor counts as operations these requests received on any inbound connection from an application or SQL Server component:
- Processing a login request.
- Processing a batch of Transact-SQL statements.
- Processing a distributed transaction command.
- Processing a logoff request.
The workload governor also counts some system-generated operations as if they are operations on active connections.
You can view the application event log for SQL Server 3629 messages or use the DBCC CONCURRENCYVIOLATION statement to assess how often the workload governor is activated. If the governor is frequently activated in a well-designed and well-tuned system, and the system is generating a lot of logical reads and writes, you should consider upgrading to SQL Server 2000 Standard Edition.