MSDE and IIS - Web Hosting

In trying to get around the current Jet hang problem people are seeing under
Windows 2003 and the jet oledb provider, we have ended up having to convert
many existing MS Access web site projects to MSDE.
The clients don't have the budgets to license full sql server, and don't
need the full performance you see there versus the "limiters" put into MSDE,
so we have gone this route until the jet hang problem is resolved.
My question is: what are the memory and/or performance issues when MSDE is
installed on a web server providing db services to multiple sites? I mean,
is it just one instance of MSDE running and providing all db services, or is
it doing anything less efficient other than the limiters it has in place?
How many instances of sqlservr.exe processes should I see - just one right?
What mem usage should it be using? Is the usage based on the number of
databases and activity, or will it try to grab a certain percentage of all
memory available?
Any new info on the infamous jet oledb hang problem? I opened (and paid) a
ticket with MS and they sent something to test but it didn't work and I
haven't heard back since - that was months ago.
Thanks,
Kevin
hi Kevin,
"valkev" <nospam@.4me.com> ha scritto nel messaggio
news:0bCDc.9073$Ey3.2483@.newssvr24.news.prodigy.co m...
> ...
> My question is: what are the memory and/or performance issues when MSDE is
> installed on a web server providing db services to multiple sites? I
mean,
> is it just one instance of MSDE running and providing all db services, or
is
> it doing anything less efficient other than the limiters it has in place?
>
running a database server on the same machine of a web server always hits
some penalty...
the Workload Query Governor will hit when more then 8 concurrent batches are
run on the very same time, slowing down all active workloads, reverting to
normal behaviour when the concurrent batches gets again under 8 batches, but
this is not regarding memory usage...
SQL Server/MSDE is very hungry, regarding memory...
memory usage is database and application dependent, but usage and workloads
dependent too...
as you know, in few words, SQL Server organize it's memory allocation in two
distinct regions, the "buffer pool" (BPool) and the "memory to leave"
(MemToLeave) regions. (I'm excluding use of AWE in order to provide easy
understanding)
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, in process COM objects space and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
but you are not limiting the resource, so SQL Server can reclaim that memory
(up to 1 gb, in our example) for it's uses, and it will perhaps release it
only under pressure by the OS claiming for additional memory, if it's the
case, else it will maintain that memory in order to cache pages and
execution plans...
under OS pressure, SQL Server will try having the Lazy Writer releases
resources, but you are not granted that this will occur, even if SQL Server
will always try to leave some memory for other OS needs, usually between 4
and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
memory usage climbing to the top and stayng there for long time, becouse of
SQL Server tends to keep pages read from disk in memory to increase further
and successive access to those pages, as long as query plans and so on... on
a heavy loaded server, if you add web server duty, you will have resource
contentions for sure...

> How many instances of sqlservr.exe processes should I see - just one
right?
as many as you installed and started...
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
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2k8s2kF17hgbiU1@.uni-berlin.de...[vbcol=seagreen]
> hi Kevin,
> "valkev" <nospam@.4me.com> ha scritto nel messaggio
> news:0bCDc.9073$Ey3.2483@.newssvr24.news.prodigy.co m...
is[vbcol=seagreen]
> mean,
or[vbcol=seagreen]
> is
place?
> running a database server on the same machine of a web server always hits
> some penalty...
> the Workload Query Governor will hit when more then 8 concurrent batches
are
> run on the very same time, slowing down all active workloads, reverting to
> normal behaviour when the concurrent batches gets again under 8 batches,
but
> this is not regarding memory usage...
> SQL Server/MSDE is very hungry, regarding memory...
> memory usage is database and application dependent, but usage and
workloads
> dependent too...
> as you know, in few words, SQL Server organize it's memory allocation in
two
> distinct regions, the "buffer pool" (BPool) and the "memory to leave"
> (MemToLeave) regions. (I'm excluding use of AWE in order to provide easy
> understanding)
> so... the BPool is the primary region SQL Server uses for it's internal
> matter, while MemToLeave consists of the virtual memory space within the
1gb
> user mode address space and the memory not used by the BPool.
> when SQL Server starts, it begins calculating the upper limit the BPool
can
> reach... if no MaxMemory is set, this value will be set to the amount of
the
> physical memory or the size of the user mode address space (1gb) , minus
the
> size of the MemToLeave, whichever is less..
> by default, MemToLeave is set to 384mb, 128mb of them are for worker
thread
> stacks and 256mb for allocation outside the BPool, such as memory for
OLE-DB
> providers, in process COM objects space and memory requirements and so
on..
> when MaxMemory value is explicitally set, this upper limit will only
address
> BPool region needs...
> so only the address space of BPool pages is limited by this configuration
> value, while SQL Server memory requirememts outside BPool allocation are
not
> limited this way...
> but you are not limiting the resource, so SQL Server can reclaim that
memory
> (up to 1 gb, in our example) for it's uses, and it will perhaps release it
> only under pressure by the OS claiming for additional memory, if it's the
> case, else it will maintain that memory in order to cache pages and
> execution plans...
> under OS pressure, SQL Server will try having the Lazy Writer releases
> resources, but you are not granted that this will occur, even if SQL
Server
> will always try to leave some memory for other OS needs, usually between 4
> and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
> memory usage climbing to the top and stayng there for long time, becouse
of
> SQL Server tends to keep pages read from disk in memory to increase
further
> and successive access to those pages, as long as query plans and so on...
on
> a heavy loaded server, if you add web server duty, you will have resource
> contentions for sure...
>
> right?
> as many as you installed and started...
> --
> 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
>
Andrea,
Thanks for the great info. Your explanation is consistent with what I'm
seeing.
So, the only real performance difference seen going from MSDE to SQL Server
is that the Workload Query Governor will not be kicking in and slowing
things down on active installations correct? Isn't this governor on the
order of milliseconds?
Thanks,
Kevin
|||Hi Kevin,
The Workload Governor on MSDE is somewhat more serious than a matter of
milliseconds. Basically it is supposed to give MSDE approximately the same
performance characteristics as Access, i.e. 20 concurrent users is about the
most you will get. Of course how this will work in your specific situation
you have to test yourself.
But as you are moving from Access to MSDE, MSDE should perform sufficiently
ok. If you already had performance problems on Access, MSDE probably won't
solve these though.
Jacco Schalkwijk
SQL Server MVP
"valkev" <nospam@.4me.com> wrote in message
news:euLDc.9211$%l7.3730@.newssvr24.news.prodigy.co m...[vbcol=seagreen]
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:2k8s2kF17hgbiU1@.uni-berlin.de...
MSDE[vbcol=seagreen]
> is
> or
> place?
hits[vbcol=seagreen]
> are
to[vbcol=seagreen]
> but
> workloads
> two
> 1gb
> can
> the
> the
> thread
> OLE-DB
> on..
> address
configuration[vbcol=seagreen]
> not
> memory
it[vbcol=seagreen]
the[vbcol=seagreen]
> Server
4[vbcol=seagreen]
> of
> further
on...[vbcol=seagreen]
> on
resource
> Andrea,
> Thanks for the great info. Your explanation is consistent with what I'm
> seeing.
> So, the only real performance difference seen going from MSDE to SQL
Server
> is that the Workload Query Governor will not be kicking in and slowing
> things down on active installations correct? Isn't this governor on the
> order of milliseconds?
> Thanks,
> Kevin
>
|||Thanks Jacco.
Yeah it's kind of funny. Under Access and IIS4 running on NT, we had some
sites getting over 3M hits per month and MOST of those hits were on
db-driven pages! The Access db itself might get as big as say 10MB in size.
And we had NO problems.
Then, when going to Windows 2003 Server and running very SMALL sites using
Access, we immediately ran into this Jet OLEDB hang problem and basically
nothing runs under Access on a new latest-greatest server. Submitte a
ticket, got a little response and a dll thrown at me which didn't help any,
and haven't heard since.
So, any db needs I have for sites on this particular server we're just
coding for MSDE instead of Access because it's really our only option.
I have PHP and MySQL installed on the server, and really want to jump into
mySQL to see how it compares to Access and MSDE and SQL Server but just
haven't had the time. I suspect it will perform somewhat like MSDE, faster
because of the governors, but not as well as SQL Server who knows. My
experience is Windows, SQL Server, SQL Enterprise, stored procedures, et al
so not sure how much I'll end up doing in mySQL UNLESS I foresee getting so
many new db-driven sites that we can't afford to move to SQL Server over
MSDE and so would need to go the mySQL route.
Decision decisions.
Thanks,
Kevin
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uaN2pGQXEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Hi Kevin,
> The Workload Governor on MSDE is somewhat more serious than a matter of
> milliseconds. Basically it is supposed to give MSDE approximately the same
> performance characteristics as Access, i.e. 20 concurrent users is about
the
> most you will get. Of course how this will work in your specific situation
> you have to test yourself.
> But as you are moving from Access to MSDE, MSDE should perform
sufficiently[vbcol=seagreen]
> ok. If you already had performance problems on Access, MSDE probably won't
> solve these though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "valkev" <nospam@.4me.com> wrote in message
> news:euLDc.9211$%l7.3730@.newssvr24.news.prodigy.co m...
> MSDE
I[vbcol=seagreen]
services,[vbcol=seagreen]
> hits
batches[vbcol=seagreen]
reverting[vbcol=seagreen]
> to
batches,[vbcol=seagreen]
in[vbcol=seagreen]
easy[vbcol=seagreen]
internal[vbcol=seagreen]
the[vbcol=seagreen]
BPool[vbcol=seagreen]
of[vbcol=seagreen]
minus[vbcol=seagreen]
> configuration
are[vbcol=seagreen]
release[vbcol=seagreen]
> it
> the
between[vbcol=seagreen]
> 4
see[vbcol=seagreen]
becouse[vbcol=seagreen]
> on...
> resource
visual
> Server
>
|||Thanks Jacco.
Yeah it's kind of funny. Under Access and IIS4 running on NT, we had some
sites getting over 3M hits per month and MOST of those hits were on
db-driven pages! The Access db itself might get as big as say 10MB in size.
And we had NO problems.
Then, when going to Windows 2003 Server and running very SMALL sites using
Access, we immediately ran into this Jet OLEDB hang problem and basically
nothing runs under Access on a new latest-greatest server. Submitte a
ticket, got a little response and a dll thrown at me which didn't help any,
and haven't heard since.
So, any db needs I have for sites on this particular server we're just
coding for MSDE instead of Access because it's really our only option.
I have PHP and MySQL installed on the server, and really want to jump into
mySQL to see how it compares to Access and MSDE and SQL Server but just
haven't had the time. I suspect it will perform somewhat like MSDE, faster
because of the governors, but not as well as SQL Server who knows. My
experience is Windows, SQL Server, SQL Enterprise, stored procedures, et al
so not sure how much I'll end up doing in mySQL UNLESS I foresee getting so
many new db-driven sites that we can't afford to move to SQL Server over
MSDE and so would need to go the mySQL route.
Decision decisions.
Thanks,
Kevin
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uaN2pGQXEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Hi Kevin,
> The Workload Governor on MSDE is somewhat more serious than a matter of
> milliseconds. Basically it is supposed to give MSDE approximately the same
> performance characteristics as Access, i.e. 20 concurrent users is about
the
> most you will get. Of course how this will work in your specific situation
> you have to test yourself.
> But as you are moving from Access to MSDE, MSDE should perform
sufficiently[vbcol=seagreen]
> ok. If you already had performance problems on Access, MSDE probably won't
> solve these though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "valkev" <nospam@.4me.com> wrote in message
> news:euLDc.9211$%l7.3730@.newssvr24.news.prodigy.co m...
> MSDE
I[vbcol=seagreen]
services,[vbcol=seagreen]
> hits
batches[vbcol=seagreen]
reverting[vbcol=seagreen]
> to
batches,[vbcol=seagreen]
in[vbcol=seagreen]
easy[vbcol=seagreen]
internal[vbcol=seagreen]
the[vbcol=seagreen]
BPool[vbcol=seagreen]
of[vbcol=seagreen]
minus[vbcol=seagreen]
> configuration
are[vbcol=seagreen]
release[vbcol=seagreen]
> it
> the
between[vbcol=seagreen]
> 4
see[vbcol=seagreen]
becouse[vbcol=seagreen]
> on...
> resource
visual
> Server
>
|||Don't assume you can't afford SQL Server over MySQL. MySQL is Open Source,
but it is not free, an entry level tech support contract is ?1500/year, the
most expensive support contract with MySQL is ?48,000 per year. Compare that
with SQL Server licenses.
Jacco Schalkwijk
SQL Server MVP
"valkev" <nospam@.4me.com> wrote in message
news:0V4Ec.9708$9w2.1928@.newssvr24.news.prodigy.co m...
> Thanks Jacco.
> Yeah it's kind of funny. Under Access and IIS4 running on NT, we had some
> sites getting over 3M hits per month and MOST of those hits were on
> db-driven pages! The Access db itself might get as big as say 10MB in
size.
> And we had NO problems.
> Then, when going to Windows 2003 Server and running very SMALL sites using
> Access, we immediately ran into this Jet OLEDB hang problem and basically
> nothing runs under Access on a new latest-greatest server. Submitte a
> ticket, got a little response and a dll thrown at me which didn't help
any,
> and haven't heard since.
> So, any db needs I have for sites on this particular server we're just
> coding for MSDE instead of Access because it's really our only option.
> I have PHP and MySQL installed on the server, and really want to jump into
> mySQL to see how it compares to Access and MSDE and SQL Server but just
> haven't had the time. I suspect it will perform somewhat like MSDE,
faster
> because of the governors, but not as well as SQL Server who knows. My
> experience is Windows, SQL Server, SQL Enterprise, stored procedures, et
al
> so not sure how much I'll end up doing in mySQL UNLESS I foresee getting
so
> many new db-driven sites that we can't afford to move to SQL Server over
> MSDE and so would need to go the mySQL route.
> Decision decisions.
> Thanks,
> Kevin
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote[vbcol=seagreen]
> in message news:uaN2pGQXEHA.1036@.TK2MSFTNGP10.phx.gbl...
same[vbcol=seagreen]
> the
situation[vbcol=seagreen]
> sufficiently
won't[vbcol=seagreen]
> I
> services,
> batches
> reverting
> batches,
allocation[vbcol=seagreen]
> in
leave"[vbcol=seagreen]
> easy
> internal
> the
> BPool
amount[vbcol=seagreen]
> of
> minus
for[vbcol=seagreen]
so[vbcol=seagreen]
> are
that[vbcol=seagreen]
> release
it's[vbcol=seagreen]
releases[vbcol=seagreen]
> between
> see
> becouse
one[vbcol=seagreen]
> visual
I'm[vbcol=seagreen]
the
>
|||Don't assume you can't afford SQL Server over MySQL. MySQL is Open Source,
but it is not free, an entry level tech support contract is ?1500/year, the
most expensive support contract with MySQL is ?48,000 per year. Compare that
with SQL Server licenses.
Jacco Schalkwijk
SQL Server MVP
"valkev" <nospam@.4me.com> wrote in message
news:0V4Ec.9708$9w2.1928@.newssvr24.news.prodigy.co m...
> Thanks Jacco.
> Yeah it's kind of funny. Under Access and IIS4 running on NT, we had some
> sites getting over 3M hits per month and MOST of those hits were on
> db-driven pages! The Access db itself might get as big as say 10MB in
size.
> And we had NO problems.
> Then, when going to Windows 2003 Server and running very SMALL sites using
> Access, we immediately ran into this Jet OLEDB hang problem and basically
> nothing runs under Access on a new latest-greatest server. Submitte a
> ticket, got a little response and a dll thrown at me which didn't help
any,
> and haven't heard since.
> So, any db needs I have for sites on this particular server we're just
> coding for MSDE instead of Access because it's really our only option.
> I have PHP and MySQL installed on the server, and really want to jump into
> mySQL to see how it compares to Access and MSDE and SQL Server but just
> haven't had the time. I suspect it will perform somewhat like MSDE,
faster
> because of the governors, but not as well as SQL Server who knows. My
> experience is Windows, SQL Server, SQL Enterprise, stored procedures, et
al
> so not sure how much I'll end up doing in mySQL UNLESS I foresee getting
so
> many new db-driven sites that we can't afford to move to SQL Server over
> MSDE and so would need to go the mySQL route.
> Decision decisions.
> Thanks,
> Kevin
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote[vbcol=seagreen]
> in message news:uaN2pGQXEHA.1036@.TK2MSFTNGP10.phx.gbl...
same[vbcol=seagreen]
> the
situation[vbcol=seagreen]
> sufficiently
won't[vbcol=seagreen]
> I
> services,
> batches
> reverting
> batches,
allocation[vbcol=seagreen]
> in
leave"[vbcol=seagreen]
> easy
> internal
> the
> BPool
amount[vbcol=seagreen]
> of
> minus
for[vbcol=seagreen]
so[vbcol=seagreen]
> are
that[vbcol=seagreen]
> release
it's[vbcol=seagreen]
releases[vbcol=seagreen]
> between
> see
> becouse
one[vbcol=seagreen]
> visual
I'm[vbcol=seagreen]
the
>
sql