Understanding MSDE 2000's Limitations in Web Applications

If your using Microsoft's light weight version of SQL Server MSDE for a web application you should understand the limitations, so you can properly configure your database connection.

The basic limitations of MSDE 2000 are:

• Max database size is 2GB
• Uses up to two processors
• Limited to 8 concurrent executing operations per instance
• 32,767 concurrent connections
• 32,767 databases per instance
• 16 instances per server
• Only uses up to 2GB of RAM
• Does not include Enterprise Manager GUI (but you can still use enterprise manager to manage a MSDE database)

One pitfall when using MSDE in a web application, is that it is possible to excede the 8 concurrently executing operations. When this occurs MSDE 2000 will invoke the Workload Governor which will incur a delay on your query execution. This delay usually creates more concurrent queries, and you end up with very long running queries.

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. Via: msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0ciq.asp -- SQL Server 2000 Workload Governor

Step 1: Admit you have a problem

Open up SQL Query Analyzer or osql.exe and run the following T-SQL statement:

DBCC CONCURRENCYVIOLATION

If you see something like this:

Concurrency violations since 2005-01-12 14:13:13.757
     1     2     3     4     5     6     7     8     9  10-100  >100
     0     0     0     0     0     0     0     0     0       0     0
Concurrency violations will be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Then you have not had any concurrency violations. If the third line has numbers other than zero, then you are experiencing concurrency violations. The numbers on the second line indicate how many concurrent operations over the limit each violation was, so if you have the number 500 under number 1, you had 500 violations where there was 9 running queries.

You might also see some of the following errors showing up:

Error Executing Database Query. coldfusion.sql.DatabaseConnectionException: Timed out trying to establish connection

Or

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]No more data available to read.

Note that these errors can also be thrown due to other things.

Step 2: Fix the problem

One way to reduce these violations is to make sure the Maximum number of simultaneous requests setting in ColdFusion Administrator is set to 8 or lower. Setting this value too low however may also effect performance

Another way to remedy this problem is to set your Datasource setting Limit Connections to no more than 8 connections. Keep in mind that if you have more than one database you will need to limit connections to a number lower than 8. If you have a large number of databases, you may be better off limiting simultaneous requests, or creating multiple MSDE instances.

Keep in mind that if you have multiple servers (not just one CFML server) you need to limit connections further.

Additional Links

• msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde2kwrk/html/msde2000webapp.asp -- Using MSDE 2000 in a Web Application
• www.microsoft.com/sql/msde/productinfo/features.asp -- MSDE 2000 Features (note this states that the max concurrent requests is 5 not 8, more sources point to 8 so thats what I'm going with.)
• lab.msdn.microsoft.com/express/sql/ -- SQL Server 2005 Express this is the successor to MSDE, in beta as of this writing.