When to Upgrade from the Governor

  Topic last updated -- January 2004

You should consider upgrading from Microsoft® SQL Server™ 2000 Desktop Engine (MSDE 2000) or SQL Server 2000 Personal Edition to SQL Server 2000 Standard Edition when performance is constrained by the workload governor. The only factor that activates the workload governor is having more than eight active operations at the same time. Once the governor has been activated, one factor determines how much the database engine will slow down: the cumulative number of logical reads and writes of data pages by all the active connections.

There are two ways to find out how often the workload governor is activated:

• Review the application event log for SQL Server informational message 3629. This message indicates how many concurrent operations were active at the time the message was written. When the governor is active, it will periodically put a 3629 message in the event log.
  
  
• Use the DBCC CONCURRENCYVIOLATION statement to monitor how often the workload governor is activated. This will also give you an idea of how many active operations are being processed by the instance at the times the governor is active.

Consider upgrading to SQL Server 2000 Standard Edition under these two conditions:

• DBCC CONCURRENCYVIOLATION or informational message 3629 shows that the workload governor is frequently active.
  
  
• The SQL Server 2000 performance counters show a lot of logical database reads and writes.

Before upgrading to SQL Server 2000 Standard Edition for performance reasons, however, you should first use the standard SQL Server 2000 performance monitoring facilities to ensure that the amounts of reads and writes are not due to application or database design factors. Look for situations such as the following:

• A missing or poorly designed index on a table that means many queries referencing that table generate table scans instead of index retrievals.
  
  
• Poorly coded queries that retrieve unnecessarily large result sets or force unnecessary joins, scans over many index pages, or table scans.
  
  
• A poor database design that forces unnecessary joins.

See Also