In an attempt to demonstrate the performance degradation that occurs with too many somethings on MSDE, I am running some tests.

So far the only hint of performance degradation is the message I found in the server's log file: "This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 17 queries and performance may be adversely affected."

The test plan is this:

1. create 50 user accounts.
2. create 5 tables, each a parent table of the next. ( pk, fk, nFid )
3. recursively add 10 child records. (that makes 10 at the top, 100,000 at the bottom)
4. create a table to post the start/end times to.
   
5. build a VFP exe
   1. wait X
   2. run a query, log the start/end, repeat run/log for Y
   3. upload the query to the server (don't do it in the loop, that will slow down the server and mess up the test data)
   
   
6. run 50 instances of the app. Y = 1.5 hours; X = Y/app#

The query:

select count(*), avg(TblA.nFid1), avg( TblB.nFid1 ), avg( TblC.nFid1 ), avg( TblD.nFid1 ), avg( TblE.nFid1 )
  from TblA
  join TblB on pk_TblA = fk_TblA
  join TblC on pk_TblB = fk_TblB
  join TblD on pk_TblC = fk_TblC
  join TblE on pk_TblD = fk_TblD

The functions account over 50% of the time - this one runs twice as fast
select count(TblA.nFid1) from....join TblE...

This one takes less than .01 seconds:
select count(TblA.nFid1) from TblA (so the amount of time for overhead is negligible.)


Results:
_ MSDEBench Marks 1 - 2 second query, 1-14 clients, no hit seen.
_ MSDEBench Marks 2 - .5 second query, 1-50 clients, 225% hit at 17, but it goes away at 27??!
_ MSDEBench Marks 3 - .2 second query, 1-50 clients, something happens at 25- see chart.
_ MSDEBench Marks 4 - 2 second query, 1-25 clients, no hit seen.

---

All files used to create database, run queries and crunch results and some of the data in a chart. http://dev.personnelware.com/carl/vfp/msde/prof/

It would be great if someone will run this on a more substantial server, with a more substantial query (more data than will fit in ram.)

---

Environment:

Run tests for 4800 seconds (1.5 hours)

Hardware:
Server: pII-333 96M ram w98
Client: p-300 96M ram nt4 server. (yea, it is sort-a backwards, but it just worked out that way...)

Task manager on client running 50 apps shows cpu usage below 10%.

10Mb ethernet, (minimal usage)

---

Good work, Carl. But what sort of access to the data from VFP were you using? Perhaps there'd be a big difference in server-side recordsets as opposed to client-side recordsets? Can you post some environmental info for the test? Thanks! -- John Koziol

John, I was using SPT, and the result was one number (1 record, 1 field). I was trying to make sure I only tested server load, not network or client bottlenecks. -- CFK

---

Ok, I am done testing for now.
My findings: Over 8 concurrent queries results in uneven distribution of the CPU across the running queries. So one query will run a bit slower, but another will run a bit faster. No big deal. Queries are going to take random amounts of time anyway given that the users will be running them in a random manner. -- CFK

---

Q: MSDE for web apps?

| > "Yes, MSDE is an ideal solution for basic Web applications with up to 25 concurrent users." http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp

| 25 concurent users? Holy cow, that pretty much rocks for small apps :)
| And of course, 25 concurent users, and 5 active batchs at any given time?
| Way more users can do it - i just did some tests with ACT - did 100 user
| workload on MSDE and Standard edition of SQLServer - and you can guess
| results, can you? :-))))) - let me put em this way: "I get often mixed up
| and must check my notes to see which graphs are for which instance".... What
| is weird, workload governor is writing to event log his messages, like
| "there is more than 19 extra connections over number of 8 that database is
| optimised for".... well, i read just now how governor works (thanks Ray B),
| and i must say that, if i take MS-s word that it is REALY slowing down
| things, it looks like its so small slowdown, that overall application engine
| is "swallowing" it, and i cant see no diference between SQL Standard and
| MSDE database related applications with 100 concurent users (web-app). Then
| again, our app might be just soooo poorly architected... :)))
|
| Domagoj dk@perpetuum.hr

---

Category MSDE

---

Contributors Carl Karsten John Koziol