MSDE Release A max number of concurrent queries

Hi All. A friend of mine mentioned that Release A actually has the max number
of concurrent queries to be 25 instead of 7 as MSDE. Is this true? Thank you
hi Andrew,
Andrew @. Abaki wrote:
> Hi All. A friend of mine mentioned that Release A actually has the
> max number of concurrent queries to be 25 instead of 7 as MSDE. Is
> this true? Thank you
false.. MSDE (and there's only one of it, only different packages with
different eulas) allows up to 8 concurrent (of the ones included in the
count of the Governor it self) batches before the Governor Workload kicking
in ...
http://msdn.microsoft.com/library/?u...asp?frame=true
25 is a "magic number" guessed by Microsoft,
http://www.microsoft.com/sql/msde/ho.../msdeuse.mspx, at the question
Q. Can I use MSDE as a database for Web applications?
A. Yes, MSDE is an ideal solution for basic Web applications with up to 25
concurrent users.
this number obviously depends on the application code quality, access type,
data nature and design, activities on it, ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||The confusion comes from "concurrent queries" versus "concurrent users".
The workload governor operates off the former while users are typically
looking to configure based on the latter.
The workload governor allows essentially 480 (sub-second) database queries
or other (e.g., update) operations per minute without degradation. You can
ballpark what this means in terms of transactions. Assume a simple
transaction has perhaps 5 queries. A mid-level has perhaps 20, and a
complex transaction has about 40. So you can support between 10 and 100
transactions per minute with MSDE with no degradation. OLTP leans towards
the simple side (volume-wise you might have 70-80% of your transactions in
the simple category) so lets assume 75 transactions per minute.
The real question for MSDE thus is, how many users does it take to generate
75 transactions in a minute. If your users submit one transaction per
minute then you could support 75 users. If your users submit 2-3
transactions per minute then you get to the 25 users that Microsoft targets
as a maximum. If your workload leans towards the more complex transactions
then the numbers you support go down. If users do very infrequent
transactions then the numbers go up.
Microsoft's real target for MSDE was for apps that support 10 users or less.
There is a lot of history there in terms of the business target and the
technical requirements. One part of the requirements analysis dictated that
hard concurrent user limits cause major customer dissatisfaction. So MSDE
was not given a hard concurrent user limit nor was the governor set to make
performance fall off a cliff after 10 users. The result is that for many
applications MSDE supports far larger numbers of users than the technical
specs might indicate.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3sbpn3Fn11hqU1@.individual.net...
> hi Andrew,
> Andrew @. Abaki wrote:
> false.. MSDE (and there's only one of it, only different packages with
> different eulas) allows up to 8 concurrent (of the ones included in the
> count of the Governor it self) batches before the Governor Workload
> kicking in ...
> http://msdn.microsoft.com/library/?u...asp?frame=true
> 25 is a "magic number" guessed by Microsoft,
> http://www.microsoft.com/sql/msde/ho.../msdeuse.mspx, at the question
> Q. Can I use MSDE as a database for Web applications?
> A. Yes, MSDE is an ideal solution for basic Web applications with up to 25
> concurrent users.
> this number obviously depends on the application code quality, access
> type, data nature and design, activities on it, ...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||And, BTW, this is an oversimplification. Most queries take a tiny fraction
of a second. So you could really support quite a bit more than I show. But
my example allows for some decent conservative planning.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:u9qGL4x2FHA.632@.TK2MSFTNGP10.phx.gbl...
> The confusion comes from "concurrent queries" versus "concurrent users".
> The workload governor operates off the former while users are typically
> looking to configure based on the latter.
> The workload governor allows essentially 480 (sub-second) database queries
> or other (e.g., update) operations per minute without degradation. You
> can ballpark what this means in terms of transactions. Assume a simple
> transaction has perhaps 5 queries. A mid-level has perhaps 20, and a
> complex transaction has about 40. So you can support between 10 and 100
> transactions per minute with MSDE with no degradation. OLTP leans towards
> the simple side (volume-wise you might have 70-80% of your transactions in
> the simple category) so lets assume 75 transactions per minute.
> The real question for MSDE thus is, how many users does it take to
> generate 75 transactions in a minute. If your users submit one
> transaction per minute then you could support 75 users. If your users
> submit 2-3 transactions per minute then you get to the 25 users that
> Microsoft targets as a maximum. If your workload leans towards the more
> complex transactions then the numbers you support go down. If users do
> very infrequent transactions then the numbers go up.
> Microsoft's real target for MSDE was for apps that support 10 users or
> less. There is a lot of history there in terms of the business target and
> the technical requirements. One part of the requirements analysis
> dictated that hard concurrent user limits cause major customer
> dissatisfaction. So MSDE was not given a hard concurrent user limit nor
> was the governor set to make performance fall off a cliff after 10 users.
> The result is that for many applications MSDE supports far larger numbers
> of users than the technical specs might indicate.
> Hal Berenson, President
> PredictableIT, LLC
> www.predictableit.com
>
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3sbpn3Fn11hqU1@.individual.net...
>

MSDE Release A

Dear Experts,
I heard that MSDE Release A removes the limitation of 7 concurrent
queries that affect MSDE when too many users connecting to the system at
the same time. Is this correct?
Thank you very muchNo, Release A does not make any changes to the Query Governor. You are
probably confusing this with SQL Server 2005 Express Edition which replaces
MSDE. Express does not have a Query Governor.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
news:OspP3bHVGHA.5332@.TK2MSFTNGP10.phx.gbl...
> Dear Experts,
> I heard that MSDE Release A removes the limitation of 7 concurrent queries
> that affect MSDE when too many users connecting to the system at the same
> time. Is this correct?
> Thank you very much|||Thank you very much. This answers my question

MSDE Performance Against Concurrent Access

Thx Andrea, Thx William,
Since u told me, I've made some test on the SQL model I
have running and the queries use to take just 1/100 sec (I
use to have up to 10 queries in chain per ASP page) due to
the volume of the info related (this intranet wouldnt
reach 100.000 records total in 38 tables, ever I think)
and the easyness of the queries (it has as maximum 2
subqueries) but there is more inner joins, up to 5 in the
same query, there is also 100's of relations between the
tables (38 total tables).
I think this information make sence to migrate the model
to MSDE and use it without translating the cost of a SQL
licence to my client since it wouldnt worth it.
Any other sugest?
Thx in advance...

>--Original Message--
>Hello,
>Can anybody tell me about your own experience using IIS
>with MSDE as repository?
>Would the concurrent access of the anonymous internet
user
>to the MSDE engine affect its performance if the
>concurrent access reach more than 5 at a time?
>Im planning to deploy an Intranet Application for
>consultant office and Im affraid the MSDE to cause a
>dregradation in performance if it gets more than 5
>concurrent access.
>Thx in Advance.
>Mike.
>.
>
1) MSDE is free
2) JET is not intended for use in a web site for several reasons.
3) JET will outperform MSDE for some queries but it does not scale.
4) See 1.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:b13701c47973$8f7843c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Thx Andrea, Thx William,
> Since u told me, I've made some test on the SQL model I
> have running and the queries use to take just 1/100 sec (I
> use to have up to 10 queries in chain per ASP page) due to
> the volume of the info related (this intranet wouldnt
> reach 100.000 records total in 38 tables, ever I think)
> and the easyness of the queries (it has as maximum 2
> subqueries) but there is more inner joins, up to 5 in the
> same query, there is also 100's of relations between the
> tables (38 total tables).
> I think this information make sence to migrate the model
> to MSDE and use it without translating the cost of a SQL
> licence to my client since it wouldnt worth it.
> Any other sugest?
> Thx in advance...
>
> user

MSDE Performance Against Concurrent Access

Hello,
Can anybody tell me about your own experience using IIS
with MSDE as repository?
Would the concurrent access of the anonymous internet user
to the MSDE engine affect its performance if the
concurrent access reach more than 5 at a time?
Im planning to deploy an Intranet Application for
consultant office and Im affraid the MSDE to cause a
dregradation in performance if it gets more than 5
concurrent access.
Thx in Advance.
Mike.
hi Mike,
"Mike" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:7be701c4771f$40cdd7e0$a401280a@.phx.gbl...
> Hello,
> Can anybody tell me about your own experience using IIS
> with MSDE as repository?
> Would the concurrent access of the anonymous internet user
> to the MSDE engine affect its performance if the
> concurrent access reach more than 5 at a time?
> Im planning to deploy an Intranet Application for
> consultant office and Im affraid the MSDE to cause a
> dregradation in performance if it gets more than 5
> concurrent access.
> Thx in Advance.
> Mike.
you simply have to test, becouse no other project will be the same as your,
depending on the base schema, coding conventions, transactions lengths,
objects involved, data involved and so on...
usually it' said that MSDE can afford about 25 concurrent users, but this is
not a magic number, only a guess
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||As Andrea said, there is no fixed number of users. It's like asking how many
orders can a fry cook work at the same time? It's a function of how
efficiently each order is processed. How fast is the CPU? How much RAM is
available to cache the data and procedures? How fast is the hard disk? How
much data is being shipped to and from the server? How efficient is the
query or update? A single application can bring a standard edition SQL
Server to its knees if you knew what to ask for. A thousand clients an hour
might not cause the MSDE engine to kick into governor mode. It all
depends...
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2n21guFsegv1U1@.uni-berlin.de...
> hi Mike,
> "Mike" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
> news:7be701c4771f$40cdd7e0$a401280a@.phx.gbl...
> you simply have to test, becouse no other project will be the same as
your,
> depending on the base schema, coding conventions, transactions lengths,
> objects involved, data involved and so on...
> usually it' said that MSDE can afford about 25 concurrent users, but this
is
> not a magic number, only a guess
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

MSDE newbie question

Hey All -
I need to develop a database that will be approximately 100 MB in size
and will have approx 15 concurrent users. I am attempting to
understand what my Microsoft database options are. Clearly Access is
one. What about MSDE? Do I need to purchase a copy of SQL Server to
utilize the MSDE technology? If so, which edition/CALs do I need?
Thanks for helping this DB rookie out.
Dave
It sounds like MSDE would work fine for your needs, depending on how often
the 15 user hit the server. Here is a link to some information about the
appropriate uses for MSDE:
http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp
For a multi-user network client server application with a low number of
users MSDE is a very good choice. Purchasing a copy of SQL Server is not
necessary, although I would recommend buying the Developers Edition of SQL
Server ($50) for the development tools it provides and the ability to test
your application on SQL Server.
Personally, I wouldn't bother with Access as a backend data store.
Jim
"dave" <thorung@.mailcity.com> wrote in message
news:f9e0449e.0404201837.5eefeade@.posting.google.c om...
> Hey All -
> I need to develop a database that will be approximately 100 MB in size
> and will have approx 15 concurrent users. I am attempting to
> understand what my Microsoft database options are. Clearly Access is
> one. What about MSDE? Do I need to purchase a copy of SQL Server to
> utilize the MSDE technology? If so, which edition/CALs do I need?
> Thanks for helping this DB rookie out.
> Dave

MSDE Limits

I know from experience that when the number of concurrent users on an MSDE
database exceeds the limit, an error is logged and the workload govenor
kicks in. Does something similar occur when the 2 GB limit is exceeded? I
had the experience yesterday where an application stopped responding for no
apparent reason. It's MSDE database was near 2 GB and the only thing we
could figure was that MSDE was trying to extend the database past 2GB.
Unfortunately there was no error logged, either from MSDE or from my
application.
MSDE will not allow you to extend the size of the database beyond 2GB. There
is no limit on the size of the log file, but there will be an inherent limit
on insert transactions due to the limit on the database size. If your
database has reached 2GB you should still be able to query data or perform
deletes or updates (as long as the update don't tigger an extent). If MSDE
failed to create an extent then there should be an log entry created in the
SQL Server log.
Jim
"Andy Walldorff" <andyw@.REMOVEsuperior.net> wrote in message
news:OfI6oIJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I know from experience that when the number of concurrent users on an MSDE
> database exceeds the limit, an error is logged and the workload govenor
> kicks in. Does something similar occur when the 2 GB limit is exceeded?
I
> had the experience yesterday where an application stopped responding for
no
> apparent reason. It's MSDE database was near 2 GB and the only thing we
> could figure was that MSDE was trying to extend the database past 2GB.
> Unfortunately there was no error logged, either from MSDE or from my
> application.
>
|||Thanks
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uxhZEINHEHA.4092@.TK2MSFTNGP11.phx.gbl...
> MSDE will not allow you to extend the size of the database beyond 2GB.
There
> is no limit on the size of the log file, but there will be an inherent
limit
> on insert transactions due to the limit on the database size. If your
> database has reached 2GB you should still be able to query data or perform
> deletes or updates (as long as the update don't tigger an extent). If MSDE
> failed to create an extent then there should be an log entry created in
the
> SQL Server log.
> Jim
> "Andy Walldorff" <andyw@.REMOVEsuperior.net> wrote in message
> news:OfI6oIJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
MSDE
> I
> no
>

MSDE Licensing

The MSDE licence says that it can be used for up to 5 concurrent users. My app is an ASP.NET app, so won't I just be using one user - the ASP.NET account? I'm wondering whether we can get away with just buying a new server and sticking MSDE on it rather than shelling out for another £5000 SQL server licence...I do not believe what you say (5 concurrent users) is correct.

http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp.

From this page:

Q. Can I use MSDE as a database for Web applications?

A. Yes, MSDE is an ideal solution for basic Web applications with up to 25 concurrent users.

There is throttling of performance with 8 concurrent OPERATIONS (but three are normally used internally).

Fromthis page:

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

Processing a request to open an inbound connection and login.

Processing a batch of one or more Transact-SQL statements received over an inbound connection.

Processing a distributed transaction operation, such as a prepare-to-commit or rollback operation.

Processing a request to log off and close an inbound connection.

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.

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.|||Sorry - missed a "2" out there.... Thats answered the question great - thanks a lot!

MSDE for web applications

Hi, I am needing help on which MSDE version support 25 concurrent users. Can anyone help?

Creating an intranet that I would like to run on msde. My problem is that it wont allow more than one person to access the intranet at a time.

MSDE is running on windows 2000 with IIS.How do you know that MSDE is only allowing one person to access your intranet? Is there an error message it is throwing? If so what is it and how are you calling the data (maybe show us some code samples)?