Access vs MSDE

[Stevie]

I've been reading about the merits of MSDE against Access, but one thing is not clear.

Can anybody tell me which one would perform better for 15-20 users?

I know MSDE is optimised for 5 users, but will it still be faster than Access for upto 15-20 users?

[G.Kumaraguru]

MSDE

User Limits
Best performance with less than 5 users.

Database Limits
2 GB database


ACCESS
File size 2 gigabytes. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.

Number of concurrent users 255

[Stevie]

You've been reading the same stuff as me. ;)

I can see MSDE is optimised for 5 users, but how many users can it handle before it's performace dips below Access?

[G.Kumaraguru]

When they say 5 they mean it..

The SQL Server 7.0 team came up with new technology to limit the performance of their “free” versions of SQL Server 7.0 and 2000 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 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.

If you suspect a performance problem, you can use a number of techniques to determine if TBU 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. You can also use the DBCC USAGEGOVERNOR utility to display and set parameters related to the governor as follows:

dbcc usagegovernor(0,0): alloc, set the usage violation counters to all 0.

dbcc usagegovernor(0,1): display the current violation counters.

dbcc usagegovernor(0,2): dealloc the usage violation counters.

If counter values are all 0, you don’t have any usage violation. A non-zero value means how many times you have concurrent worker threads exceeding TBU. The violation number means how many more concurrent threads you have exceeding TBU. For example, if you see:

TBU = 8

Usage Violation Counter Value

1 100

2 200



This means you had 9 concurrent worker threads 100 times since the counters were started, and 10 concurrent worker threads 200 times. For each of the violation the thread is delayed by a certain amount. You can use dbcc usagegovernor(0,0) to reset the usage violation counters to 0, and dbcc usagegovernor(0,1) to display the current violation counters. Finally, you use dbcc usagegovernor(0,2) to deallocate the usage violation counters:

The above is an Extract From
MSDE—The missing piece
http://www.betav.com/samples/Whitepapers/msde_files/msde.htm

[Stevie]

Wow, you've been busy.

Cheers for the help. :)