Understanding MSDE 2000's Limitations in Web Applications
By Pete Freitag
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:
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
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.
- 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.
Understanding MSDE 2000's Limitations in Web Applications was first published on January 12, 2005.
If you like reading about msde, sqlserver, or databases then you might also like:
The FuseGuard Web Application Firewall for ColdFusion & CFML is a high performance, customizable engine that blocks various attacks against your ColdFusion applications.