Re: MSDE and IIS - Web Hosting

From: valkev (nospam_at_4me.com)
Date: 06/29/04 

Date: Tue, 29 Jun 2004 02:58:04 GMT

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

Relevant Pages

  • Re: MSDE help
    ... there is no limit to the number of connections on an MSDE server. ... I'm working with SQL Server Magazine to put together a weekly newsletter ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: version & instances
    ... Microsoft MVP ... Hitchhiker’s Guide to Visual Studio and SQL Server ... MSDE has a limited database size ...
    (microsoft.public.sqlserver.msde)
  • Re: version & instances
    ... Hitchhiker’s Guide to Visual Studio and SQL Server ... SQL 2000 SP3a for Antivirus app ... Consider that MSDE as a product is somewhat dated ...
    (microsoft.public.sqlserver.msde)
  • Re: Unable to connect to SQL 2000 engine on other machine from SSM
    ... I posted a bug report on Microsoft SQL Server Feedback. ... Upgrade MSDE to SP4: not ok. ... port information to your client. ...
    (microsoft.public.sqlserver.connect)
  • Re: Determine Licenses currently in use.
    ... by the SQL Server? ... hits the SQL server again until the next user logs on. ... Do we need/require a CAL for each workstation? ...
    (microsoft.public.sqlserver.server)