[AccessD] MSDE

Martin,

That is a great explanation and the first time I have seen such detail as to
how the governor actually functions.  Thanks for that.

John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Martin Reid
Sent: Thursday, December 04, 2003 9:16 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] MSDE


Theres always some confusion about the performance of MSDE. This is an
extraxt from a MS document I came across this morning whcih explains how the
software is restricted. As can be seen the limits refer to the number of
operations not the number of users.

<COPY>

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 include:



  a.. Processing a request to open an inbound connection and login.
  b.. Processing a batch of one or more Transact-SQL statements received
over an inbound connection.
  c.. Processing a distributed transaction operation, such as a
prepare-to-commit or rollback operation.
  d.. Processing a request to log off and close an inbound connection.
  a.. 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.
  b.. 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.

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com