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 limitations

I'd like to use MSDE since it's free instead of SQL Server for my database. I will be hosting a portal type site. If all goes well and my site is wildly successful are there any limitations in MSDE that I need to worry about?

For instance, I thought there was a limit to the number of connections. I thought I remember seeing 5 or 50 on the microsoft download site.

If there is a connection limit of say 10, what happens when connection 11 comes in? Does it just wait for a free connection or does it fail?

Is anyone using is as the database on a large portal site? How many users are there in total? How many connected at the same time?

I am under the assumption that it is SQL Server underneath, so I assume the performance and abilities are very good. Is this a fair assumption?

Thanks for the inputSome of the limitations of MSDE:

25 concurrent users
2 GB max Database size
No Enterprsie manager|||::For instance, I thought there was a limit to the number of connections.

Less thinking and more reading helps against this. I haved no clue why everyone thinks there is a 10 connection limit on MSDE, when the documentation provided, the readme and the FAW to msde pretty clearly say what is there and how it is there.

::If there is a connection limit of say 10, what happens when connection 11 comes in?

TRIED reading the documentation?

You know the one you find at:
http://www.microsoft.com/sql/msde/

more concrete the document you find at:
http://msdn.microsoft.com/library/?url=/library/en-us/architec/8_ar_sa2_0ciq.asp?frame=true

::Does it just wait for a free connection or does it fail?

I will quote - I hope you understand it:
::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.

Clearn enough? More info on the document provided.

::Is anyone using is as the database on a large portal site?

Define large.

::How many users are there in total? How many connected at the same time?

Irrelevant. No user connects at the same time. THink how web applications work. MSDE can handle 8 concurrentoperations - this is at least 8 concurrent pages being served. If you manage to hammer out a page in 0.1 seconds, this means per second you can serve 80 requests. If a user requests a page every 10 seconds average, this means you can serve 800 users WITHOUT ANY CACHING. If you are a little smart in your use of output caching, you can run this number up to some thousand (4000-5000) without any sweat. It all depends how dynamic your portal really is - and how smart or dump your usage of various caching levels is. Remmeber: cache as early as possible (output cache), as you get the most gain there.

::I am under the assumption that it is SQL Server underneath,

No. They share the same codebase, but SQL Server is not "underneath" it (whatever this is supposed to mean in IT).|||I'm sorry I failed to remember everything I read on their site. I was looking for information on how useful MSDE is in production environments. I was looking to run a .Text blog or some other such portal that used MSDE as the database. From reading on Microsoft.com I find the following:

"Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is the free, redistributable version of SQL Server that's ideal for client applications that require an embedded database, new developers learning how to build data-driven applications, and Web sites serving up to 25 concurrent users."

This tells me that I should *not* be using MSDE for production type sites. I was just wondering if anyone had any first hand experience using it on a web site serving up to more than "25 concurrent users". I was also wondering how many visitors a day would equal "25 concurrent users" since site visitors are generally spread out over the day. I was wondering how many users *the site* had (not the database) and how many of the *site* users are typically using the site at the same time (not neccessarily the database).

Now that I've gone back to read the documentation on their site, I see that my initial questions are not answered by Microsoft. I thought the number was somewhere between 5 and 50, it's 25. They do not define what "25 concurrent users" are or how it affects scaling of the database. They indicate that one can "migrated to SQL Server 2000 technology if the customer or end user wants to scale the solution up to thousands of users or terabytes of data." but there is a lot of ground between this and the 25 concurrent users quote above with no documentation provided as to what happens to msde when you go over 25 concurrent users.|||Wow, what an arrogant guy. My first time here, and i see this kind of junk right off the bat? I thought i'd help out, but it seems none is needed. I do find it interesting that someone who goes out of their way to peddle their own wares (link under username, MVP banner, and sales-begging tagline) wouldn't be such an ass. I look forward to catching one of your questions in the future. No matter how good at something you think you are, there's always someone better.|||ps.

from: http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs

"What does Microsoft expect of its MVPs?
Because the MVP Award is an award-based program whose criteria are based on past contributions, Microsoft has no expectations of MVPs beyond the expectations of courtesy, professionalism, and adherence to the community rules that we ask of all Microsoft community members."

Sounds like you forgot to read that. See professionalism, courtesy, etc.