Is there a limit to # of databases I can run on MSDE?

Hi,
We want to host several web sites that use MSDE as backend. I have two
questions:
1. Is there a limit to # of databases we can have in one instance of MSDE?
2. The # of simultaneous connections limit applies to an instance of MSDE
not a database in it. Is this correct?
Some web hosters provide MSDE as standard DB option. Do they run one MSDE on
the server and have multiple databases in it or do they run multiple
instances of MSDE?
We will run a full blown SQL Server but in phase 1, we want to start w/ MSDE
for obvious reasons -- cost.
Thanks,
Sam
SQL Server and MSDE have a limit of 32767 databases per server instance.
There is no limit on the number of connections to MSDE, but there is a
governor on the number of simultaneous batch threads. You'll run out of
physical memory resources long before you reach these limits.
Jim
"Sam" <sam@.globalwebcentral.com> wrote in message
news:%23XsTl5vAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We want to host several web sites that use MSDE as backend. I have two
> questions:
> 1. Is there a limit to # of databases we can have in one instance of MSDE?
> 2. The # of simultaneous connections limit applies to an instance of MSDE
> not a database in it. Is this correct?
> Some web hosters provide MSDE as standard DB option. Do they run one MSDE
> on the server and have multiple databases in it or do they run multiple
> instances of MSDE?
> We will run a full blown SQL Server but in phase 1, we want to start w/
> MSDE for obvious reasons -- cost.
> Thanks,
> Sam
>
|||hi Sam,
Sam wrote:
> Hi,
> We want to host several web sites that use MSDE as backend. I have two
> questions:
> 1. Is there a limit to # of databases we can have in one instance of
> MSDE?
MSDE, as long as SQL Server, has a limit 32767 of databases per
instance...

> 2. The # of simultaneous connections limit applies to an instance of
> MSDE not a database in it. Is this correct?
there's no connections limits directly related to MSDE, as it shares the
same max value as SQL Server, 32767 connections (but this value is
symbolic, as each connection requires some memory and, counting the 2gb of
RAM limitation of MSDE, you will get memory troubles long before that; but a
built-in Workloads Governor will kick in at 8 concurrent defined batches
(you can inspect them and have an idea about it at
http://msdn.microsoft.com/library/?u...asp?frame=true )
...
the Workloads Governor is per instance and not per database..

> Some web hosters provide MSDE as standard DB option. Do they run one
> MSDE on the server and have multiple databases in it or do they run
> multiple instances of MSDE?
you have to ask them for that answer..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Jim,
Thanks for your response. I'm a little confused so I'd appreciate further
clarification. You tell me that I'll run out of memory long before I reach
these limits.
If I have one instance of MSDE running on our web application server that
will host 27 ASP.NET applications which will require 27 databases in MSDE,
wouldn't I run into issues if I have moderate traffic on these sites.
Obviously, moderate traffic is a pretty subjective term. But everything
these applications do require a database read and/or write.
So if I understand you correctly, the limitation is not for the number of
connections but number of batch threads. From the other post, I understand
that the number is actually 8 batch threads. In simplistic terms, wouldn't
that mean, 8 simultaneous users who require database read/write? In our
case, I know that everytime the user hits the site, it triggers multiple
database lookups. So that number could even be less than 8 simultaneous
users.
I know about caching and everything else. But I'm trying to keep it at a
basic level which is number of simulatenous database read/writes.
I'd appreciate your feedback. Thanks.
Sam
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:O964HTwAFHA.1188@.tk2msftngp13.phx.gbl...
> SQL Server and MSDE have a limit of 32767 databases per server instance.
> There is no limit on the number of connections to MSDE, but there is a
> governor on the number of simultaneous batch threads. You'll run out of
> physical memory resources long before you reach these limits.
> Jim
> "Sam" <sam@.globalwebcentral.com> wrote in message
> news:%23XsTl5vAFHA.3616@.TK2MSFTNGP11.phx.gbl...
>
|||On Tue, 25 Jan 2005 14:50:57 -0500, Sam wrote:

>Jim,
>Thanks for your response. I'm a little confused so I'd appreciate further
>clarification. You tell me that I'll run out of memory long before I reach
>these limits.
>If I have one instance of MSDE running on our web application server that
>will host 27 ASP.NET applications which will require 27 databases in MSDE,
>wouldn't I run into issues if I have moderate traffic on these sites.
Hi Sam,
That depends. What is the running time of a typical query? What is the
typical running time of a "long" running query and how often will these
queries be carried out? Is access to the various databases spread over the
day, or do you have notable peaks?
As an example: let's say that typical database use consists of simple,
fast running queries, taking 0.2 seconds to execute on average. If each of
the 27 databases gets to execute 1500 of these queries per hour, the total
execution time needed is 1500 * 27 * 0.2 seconds = 8100 seconds. Since
there are 3600 seconds per hour, the average number of workloads (active
connections) will be 2.25. This is within the limits of MSDE - but only if
all queries are spread evenly over the day. If all your customers decide
that right after lunch is the perfect moment to catch up with a whole
day's worth of business, you'll have no workloads from 2PM to 1PM the next
day, and 27 workloads on average from 1PM to 2PM - now, you'll see the
workload governor kicking in.
Let's take it one step further. Suppose each database also has some
reporting procedure that runs 4 minutes, and is executed once per hour.
The total processing time for 27 of these reports is 27 * 4 = 108 minutes,
causing (if spread evenly over the day) an average number of 1.8
workloads. Add those to the 2.25 workloads for regular use and we're at a
total of 4 workloads constantly active - very near the workload governor
threshold. Every time a few customer happen to submit the reporting
procedure at the same time, that activity plus the regular short-running
queries will surpass the limit. The workload governor will kick in,
causing things to slow down. The procedures will start to take longer (6
minutes - 8 minutes - maybe even 12) and since they are active longer,
there's a good chance that yet more customers will sybmit the reporting
procedure before the others are finished, causing the number of workloads
to exceed the limit even more, so that the workload governor will throttle
performance even further. And you'll see the whole thing coming spiralling
down.
In short: try to get good estimates about running times of queries, the
number of times queries are executed and make sure that you know when to
expect peaks and how high these peaks get. Base your calculation on the
workload during peaks. If you're not sure that MSDE can handle the load,
better get the full product. I know it's not cheap - but in the long run,
losing business as a result of workload governor induced delays will cost
you more.

>Obviously, moderate traffic is a pretty subjective term. But everything
>these applications do require a database read and/or write.
>So if I understand you correctly, the limitation is not for the number of
>connections but number of batch threads. From the other post, I understand
>that the number is actually 8 batch threads.
The correct term is "workloads". The maximum number of workloads allowed
before the workload governor interferes is 8, but you'll often see the
number 5 as well. That's because MSDE requires a total of three workloads
for it's housekeeping - these are always active, completely beyond your
control. This reduces the total of 8 workloads to 5 that are actuially
available for you to use.

> In simplistic terms, wouldn't
>that mean, 8 simultaneous users who require database read/write?
No. It's not that simple (as you can see above). The typical user of a
database spends much of his/her time reading information from the screen
or typing information onto the screen. The connection to the database
usually remains active, but the connection is in a sleeping mode - this is
not a workload. It becomes a workload from the moment the user clicks
"Save", "Search" or "Show" - until the moment the found rows are listed,
the changes have been committed or the details are displayed.

> In our
>case, I know that everytime the user hits the site, it triggers multiple
>database lookups. So that number could even be less than 8 simultaneous
>users.
Does your ASP.NET application use asynchronous execution to submit several
queries at the same time? If so, then you are right, that one user's
action can cause more than one simlutaneous workload. On the other hand,
if execution is synchronous, than 1 user will not cause more than 1
workload.

>I know about caching and everything else. But I'm trying to keep it at a
>basic level which is number of simulatenous database read/writes.
Caching is irrelevant here. The only effect of caching is to speed up
queries. And since MSDE automatically manages it's cache, you don't need
to worry about this.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Thank you very much for your explanation. I really appreciate people like
yourself who are thorough in everything they do. That's a wonderful quality.
Again, thank you very much for your help.
Sam
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:nchdv09g2gfu5hiqd9ev8miraqpeacc11f@.4ax.com...
> On Tue, 25 Jan 2005 14:50:57 -0500, Sam wrote:
>
> Hi Sam,
> That depends. What is the running time of a typical query? What is the
> typical running time of a "long" running query and how often will these
> queries be carried out? Is access to the various databases spread over the
> day, or do you have notable peaks?
> As an example: let's say that typical database use consists of simple,
> fast running queries, taking 0.2 seconds to execute on average. If each of
> the 27 databases gets to execute 1500 of these queries per hour, the total
> execution time needed is 1500 * 27 * 0.2 seconds = 8100 seconds. Since
> there are 3600 seconds per hour, the average number of workloads (active
> connections) will be 2.25. This is within the limits of MSDE - but only if
> all queries are spread evenly over the day. If all your customers decide
> that right after lunch is the perfect moment to catch up with a whole
> day's worth of business, you'll have no workloads from 2PM to 1PM the next
> day, and 27 workloads on average from 1PM to 2PM - now, you'll see the
> workload governor kicking in.
> Let's take it one step further. Suppose each database also has some
> reporting procedure that runs 4 minutes, and is executed once per hour.
> The total processing time for 27 of these reports is 27 * 4 = 108 minutes,
> causing (if spread evenly over the day) an average number of 1.8
> workloads. Add those to the 2.25 workloads for regular use and we're at a
> total of 4 workloads constantly active - very near the workload governor
> threshold. Every time a few customer happen to submit the reporting
> procedure at the same time, that activity plus the regular short-running
> queries will surpass the limit. The workload governor will kick in,
> causing things to slow down. The procedures will start to take longer (6
> minutes - 8 minutes - maybe even 12) and since they are active longer,
> there's a good chance that yet more customers will sybmit the reporting
> procedure before the others are finished, causing the number of workloads
> to exceed the limit even more, so that the workload governor will throttle
> performance even further. And you'll see the whole thing coming spiralling
> down.
> In short: try to get good estimates about running times of queries, the
> number of times queries are executed and make sure that you know when to
> expect peaks and how high these peaks get. Base your calculation on the
> workload during peaks. If you're not sure that MSDE can handle the load,
> better get the full product. I know it's not cheap - but in the long run,
> losing business as a result of workload governor induced delays will cost
> you more.
>
> The correct term is "workloads". The maximum number of workloads allowed
> before the workload governor interferes is 8, but you'll often see the
> number 5 as well. That's because MSDE requires a total of three workloads
> for it's housekeeping - these are always active, completely beyond your
> control. This reduces the total of 8 workloads to 5 that are actuially
> available for you to use.
>
> No. It's not that simple (as you can see above). The typical user of a
> database spends much of his/her time reading information from the screen
> or typing information onto the screen. The connection to the database
> usually remains active, but the connection is in a sleeping mode - this is
> not a workload. It becomes a workload from the moment the user clicks
> "Save", "Search" or "Show" - until the moment the found rows are listed,
> the changes have been committed or the details are displayed.
>
> Does your ASP.NET application use asynchronous execution to submit several
> queries at the same time? If so, then you are right, that one user's
> action can cause more than one simlutaneous workload. On the other hand,
> if execution is synchronous, than 1 user will not cause more than 1
> workload.
>
> Caching is irrelevant here. The only effect of caching is to speed up
> queries. And since MSDE automatically manages it's cache, you don't need
> to worry about this.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)