Tom Libby wrote:
> We have 2 instances of MSDE 2000 SP3 on a W2K Advanced server with 1
> Gig of memory. One for an online storefront and the other for live
> chat software from SightMax.com. The live chat instance which is
> really not being used since the users never log into the software
> starts to use over a gig of virtual memory. The online storefront
> gets used all day and does not have this problem. Any suggestions as
> to what to do about it.
> Sorry if this is a basic question but I am not that familiar with
> administration of SQL Server or MSDE.
>

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
news:4cj8c1F15qhf6U1@individual.net...
> hi Tom,
> Tom Libby wrote:
>> We have 2 instances of MSDE 2000 SP3 on a W2K Advanced server with 1
>> Gig of memory. One for an online storefront and the other for live
>> chat software from SightMax.com. The live chat instance which is
>> really not being used since the users never log into the software
>> starts to use over a gig of virtual memory. The online storefront
>> gets used all day and does not have this problem. Any suggestions as
>> to what to do about it.
>> Sorry if this is a basic question but I am not that familiar with
>> administration of SQL Server or MSDE.
>>
>
> are you really sure the "unused" insntance is really unused? when starting
> up on my system with "default" memory configuration, that's to say dynamic
> automatic management, the 1st instance adds about 34mb of ram, and a 2nd
> instance another 12mb of physical memory...
> virtual memory is reserved for both intances but not allocated.. it
> actually becomes allocated when needed..
> Loosely speaking, 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
> and because MSDE does not admit it)..
> 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, linked server drivers, NetLib dlls, 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...
>
> more, each connection, live or sleeping, will eat about 24kb of memory,
> calculated as 12 KB + 3 times the Network Packet Size (default setting
> that
> can be partially customized via sp_configure system stored procedure
> modifying the 'user connections' setting), used to store the data
> structures
> holding the connection 's context, as long as for buffer used to send and
> receive the relative associated network streams (default to 4KB network
> packet setting), that can be stolen from the buffer pool memory region
> and/or the MemToLeave memory area...
>
> not limiting the resources, 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 releasing
> 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 staying 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...
> but on "inactive" instance, this should not be the case...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply
>
>
>