[AccessD] Connections and Performance

I think someone is mixing up SQL 2000 MSDE backends and Access.
This MSDE 2000 speed governor restriction to 5 users before degradation
is removed in SQL Express.

See below, you may have to mouseover and click to see hidden yellow 
lettering

http://betav.com/files/content/whitepapers/msde_files/msde.htm

The SQL Server team came up with new technology to limit the performance 
of their “free” versions of SQL Server so developers would not be 
tempted to use MSDE in place of the unbridled versions. They named this 
technology “Target Benchmark Users” or TBU for short. TBU keeps all MSDE 
versions in check. Of course, the Standard and Enterprise editions do 
not activate TBU governing.

Unlike the SQL Server 6.5 thread-based governing used in the past, TBU 
is not prone to the blocking or other operational artifacts that 
troubled earlier versions. TBU is “delay-based”. That is, if the number 
of concurrent threads is greater than the TBU setting, a variable delay 
is induced to cap performance. The length of time (in milliseconds) to 
delay the current operation is calculated based on the number of 
concurrent worker threads. As more operations (threads) are started, 
more delay occurs. MSDE’s TBU limit is 8. That means that after 8 (plus 
6 to account for system threads) active threads are started, a delay is 
added to each operation—and this delay gets increasingly longer for each 
additional thread that’s started. TBU imposes a deterministic, gradual 
throttle on performance. This provides a more natural, less intrusive 
way to limit performance without unwanted side effects.

TBU does not limit the number of user connections. That is, (almost) any 
number of connections can be established to MSDE—limited only by your 
license and system (RAM) resources. However, each of these connections 
can execute an operation and enable threads—but only when they are 
“active”. In other words, if a connection is dormant (the operator is at 
lunch or simply not doing anything), it’s not running a thread on the 
server and TBU governing is not impacted. You can also execute multiple 
operations from a single “user”. For example, a single application can 
establish multiple connections—each creating its own thread. This means 
a single user can consume all of the TBU threads all by itself. The 
Visual Basic IDE also makes connections on its own. For example, the 
Data View window or the Data Environment Designer both create one or 
more connections at design time. When you use a data source control such 
as the ADODC or one generated by the Data Object Wizard, additional 
connections (perhaps several) can be opened and threads started. 
Clearly, a single application can exhaust the limited number of threads 
made available by MSDE.

If you suspect a performance problem when using MSDE, you can use a 
number of techniques to determine if the TBU governor is causing the 
delay. The simplest way is to count the number of concurrent 
users—SELECT COUNT (*) FROM SYSPROCESSES WHERE STATUS <> ‘sleeping’ will 
do the trick. If this returns a value less than 14 (8 + 6), then the 
delay is likely caused by other factors—not the governor.

I have used the DBCC method to do this

Charlotte Foust wrote:

>I've certainly had more than 5 FEs connected to a single BE.  The back
>end doesn't keep track of connections in Access.
>
>Charlotte Foust
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
>Sent: Friday, February 02, 2007 5:58 AM
>To: 'Access Developers discussion and problem solving'
>Subject: [AccessD] Connections and Performance
>
>I've read several times that maintaining a connection between a FE and a
>BE will increase the performance of the FE because it doesn't need to
>reconnect before transferring data.  The connection here would be a
>bound form connected by a table link to a table in the BE.
>
>But, the connection limit for one BE is 5 FE's.  So, will maintaining
>connections on more than 5 FE's reduce performance?  Seems logical, but
>I was wondering if this is correct or is there more to it?
>
>Thanks!
>
>Dan Waters
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada