> ....
> 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

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
news:2k8s2kF17hgbiU1@uni-berlin.de...
> hi Kevin,
> "valkev" <nospam@4me.com> ha scritto nel messaggio
> news:0bCDc.9073$Ey3.2483@newssvr24.news.prodigy.com...
> > ....
> > 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.shtm http://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
>

"valkev" <nospam@4me.com> wrote in message
news:euLDc.9211$%l7.3730@newssvr24.news.prodigy.com...
> "Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
> news:2k8s2kF17hgbiU1@uni-berlin.de...
> > hi Kevin,
> > "valkev" <nospam@4me.com> ha scritto nel messaggio
> > news:0bCDc.9073$Ey3.2483@newssvr24.news.prodigy.com...
> > > ....
> > > 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.shtm http://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
>
>

"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
> 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.com...
> > "Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
> > news:2k8s2kF17hgbiU1@uni-berlin.de...
> > > hi Kevin,
> > > "valkev" <nospam@4me.com> ha scritto nel messaggio
> > > news:0bCDc.9073$Ey3.2483@newssvr24.news.prodigy.com...
> > > > ....
> > > > 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.shtm http://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
> >
> >
>
>

"valkev" <nospam@4me.com> wrote in message
news:0V4Ec.9708$9w2.1928@newssvr24.news.prodigy.com...
> 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
> > 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.com...
> > > "Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
> > > news:2k8s2kF17hgbiU1@uni-berlin.de...
> > > > hi Kevin,
> > > > "valkev" <nospam@4me.com> ha scritto nel messaggio
> > > > news:0bCDc.9073$Ey3.2483@newssvr24.news.prodigy.com...
> > > > > ....
> > > > > 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.shtm http://italy.mvps.org