Pete Freitag Pete Freitag

Understanding MSDE 2000's Limitations in Web Applications

Published on January 12, 2005
By Pete Freitag
coldfusiondatabases

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.


msde sqlserver databases

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:

FuseGuard Web App Firewall for ColdFusion

The FuseGuard Web Application Firewall for ColdFusion & CFML is a high performance, customizable engine that blocks various attacks against your ColdFusion applications.

CFBreak
The weekly newsletter for the CFML Community


Comments

Once you have setup your connection limits, you can run DBCC CONCURRENCYVIOLATION(RESET) to reset the counters.
by Pete Freitag on 01/12/2005 at 3:23:21 PM UTC
I thought the 2gb limit was database size... but I'd be glad if it was a memory limit instead!
by Dave Ross on 01/12/2005 at 4:25:30 PM UTC
Dave,

According to the "Using MSDE 2000 in a Web Application" document:

"MSDE 2000 supports up to 2 GB of RAM, the same amount as SQL Server 2000 Standard Edition, but less than SQL Server 2000 Enterprise Edition, which can support up to 64 GB of RAM"
by Pete Freitag on 01/12/2005 at 4:46:21 PM UTC
Also Dave, 2GB is also the db limit size.
by Pete Freitag on 01/12/2005 at 4:47:36 PM UTC
Hi,
drom which site do you get this information:
"Limited to 8 concurrent executing operations per instance"
I haven't anything about this.

Regards

Sven
by Sven Berling on 02/02/2005 at 9:36:40 AM UTC
Where is the Datasource setting "Limit Connections"? I'm in Enterprise Manager looking at Properties of my local SQL Server Desktop Engine (Connections tab) and I see "Maximum concurrent user connections (0=unlimited)" and it's set to 0. Should I change this to 8?
by Richard Oppedisano on 04/22/2005 at 3:43:52 PM UTC
2 GB is NOT the database limit, it's the limit of a data file. So, you can create DB larger than 2 GB by using multiple files (2 data files = 4 Gb DB limit, ...)
by Gilles Duchene on 02/13/2006 at 7:13:09 AM UTC
Gilles, I'm not seeing that in MSDE 2000; if I create a second Data File, I get the following error;

"Error 1827: CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database."

If you know a way to increase the Database size through multiple data files, I'd very much appreciate the info.
by Michael Wells on 05/08/2006 at 4:21:02 PM UTC