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.
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/...908/37908.html and
http://www.windowsitpro.com/Article/...890/37890.html
another interesting readings:
http://msdn.microsoft.com/library/de...v_03252004.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://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
|||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/...908/37908.html and
http://www.windowsitpro.com/Article/...890/37890.html
another interesting readings:
http://msdn.microsoft.com/library/de...v_03252004.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://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
|||i appreciate the information
thanx
"Andrea Montanari" wrote:
> hi,
> Keeper wrote:
> 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/...908/37908.html and
> http://www.windowsitpro.com/Article/...890/37890.html
> another interesting readings:
> http://msdn.microsoft.com/library/de...v_03252004.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://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
>
>
No comments:
Post a Comment