"Andrea Montanari" wrote:

> hi,
> Keeper wrote:
> > What memory settings should i configure to tune msde?
> >
> > Sometimes it will be installed on a PC but at other times it will be
> > an app server. On the app server i want to take advantage of the
> > increase in memory.
>
> usually you should not manually "tune" SQL Server as it is tailored to self
> configure it self...
> on the other hand, SQL Server is very resources intensive, and is designed
> to take advantage of all the installed available memory (in sys32 processes
> limit :) )
>
> and it's a very challenging taks to manual configure it for best
> performance..
> remember that memory usage is database and application dependent, but usage
> and workloads dependent too...
>
> 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... on
> a heavy loaded server, if you add web server duty, you will have resources
> contentions for sure...
>
> I strongly advice you for
> http://www.windowsitpro.com/Article/ArticleID/37908/37908.html and
> http://www.windowsitpro.com/Article/ArticleID/37890/37890.html
>
> another interesting readings:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_03252004.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://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
>
>
>