the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
out of 2.5 gigs. is this normal? is there a way to prevent this? should it
be controlled somehow?From BOL:
--snip
To acquire as much memory as possible without generating excess paging I/O,
each instance of SQL Server sets a target of acquiring memory until free
physical memory on the computer is in the range of 4 MB to 10 MB. This range
was chosen because testing has shown that Windows NT and Windows 2000 have
minimal memory swapping until the memory allocations equal the available
physical memory minus 4 MB. An instance of SQL Server that is processing a
heavy workload keeps the free physical memory at the lower end (4 MB) of the
range; an instance that is processing a light workload keeps the free memory
at the higher end of the range (10 MB).
--snip
This behavior is expected and is designed for maximum performance on a
dedicated SQL server.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"harun kazi" <kazi[RemoveThis]@.wideopenwest.com> wrote in message
news:OqgYjHOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
> the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
> out of 2.5 gigs. is this normal? is there a way to prevent this? should it
> be controlled somehow?
>|||thank you both.
"harvinder" <hs@.metratech.com> wrote in message
news:001b01c360e4$282db540$a301280a@.phx.gbl...
> sql server will like to take most of the memory if
> required and no other process need memory... generally it
> is good idea to leave it this way(dynamic)...but if u
> want to have control over memory usage u can change the
> parameter max memory from EM or QA.
> Thanks
> --Harvinder
> >--Original Message--
> >the memory usage on our SQL 2000 server keeps creeping up
> to near 1.5 gig
> >out of 2.5 gigs. is this normal? is there a way to
> prevent this? should it
> >be controlled somehow?
> >
> >
> >.
> >|||Additionally SQL will not release memory ( even if no one is logged in)
unless there is < 5 ( between 5 and 10 ) MB of free memory... Why work to
reduce memory utilization if there is no need?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and its
community of SQL Server professionals.
www.sqlpass.org
"harun kazi" <kazi[RemoveThis]@.wideopenwest.com> wrote in message
news:OqgYjHOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
> the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
> out of 2.5 gigs. is this normal? is there a way to prevent this? should it
> be controlled somehow?
>|||And just to hammer it home :-)
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"harun kazi" <kazi[RemoveThis]@.wideopenwest.com> wrote in message
news:OqgYjHOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
the memory usage on our SQL 2000 server keeps creeping up to near 1.5 gig
out of 2.5 gigs. is this normal? is there a way to prevent this? should it
be controlled somehow?
Showing posts with label gig. Show all posts
Showing posts with label gig. Show all posts
Monday, March 19, 2012
Wednesday, March 7, 2012
memory question
hi experts,
I am running MSSQL Standard edition on a dedicated Win2003 server with 3 GIG
RAM. SqlServer now uses 1,83 GB. I am not sure if and how the /3G and /AWE
switches can help using more RAM.
Thanks for every info!
BerndCheck on these KB Articles :
Using /3GB or /PAE and AWE with SQL Server:
http://support.microsoft.com/default.aspx?scid=kb;en-us;555068&Product=sql2k
HOW TO: Configure memory for more than 2 GB in SQL Server:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&Product=sql2k
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Bernd Maierhofer (dato)" <bernd.maierhofer@.dato.at> wrote in message
news:%23IWOxSlNEHA.1608@.TK2MSFTNGP12.phx.gbl...
> hi experts,
> I am running MSSQL Standard edition on a dedicated Win2003 server with 3
GIG
> RAM. SqlServer now uses 1,83 GB. I am not sure if and how the /3G and
/AWE
> switches can help using more RAM.
> Thanks for every info!
> Bernd
>
I am running MSSQL Standard edition on a dedicated Win2003 server with 3 GIG
RAM. SqlServer now uses 1,83 GB. I am not sure if and how the /3G and /AWE
switches can help using more RAM.
Thanks for every info!
BerndCheck on these KB Articles :
Using /3GB or /PAE and AWE with SQL Server:
http://support.microsoft.com/default.aspx?scid=kb;en-us;555068&Product=sql2k
HOW TO: Configure memory for more than 2 GB in SQL Server:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&Product=sql2k
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Bernd Maierhofer (dato)" <bernd.maierhofer@.dato.at> wrote in message
news:%23IWOxSlNEHA.1608@.TK2MSFTNGP12.phx.gbl...
> hi experts,
> I am running MSSQL Standard edition on a dedicated Win2003 server with 3
GIG
> RAM. SqlServer now uses 1,83 GB. I am not sure if and how the /3G and
/AWE
> switches can help using more RAM.
> Thanks for every info!
> Bernd
>
Memory problem
I have an accounting program that is loaded on a server
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
Hi,
I guess that the SQL Server is configured to use Dynamic Memory
Allocations.
SQL Server memory will continue to grow as long as there is no RAM pressure
in
the machine. As long as SQL Server feels some pressure on memory pool, it
will
start to release memory for other applications use or to avoid swapping.
Try
setting the Min Server and Max Server memory to a know value then run the
same
applications. You should see the server memory grow to that limit and then
maintain on that level.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
Hi,
I guess that the SQL Server is configured to use Dynamic Memory
Allocations.
SQL Server memory will continue to grow as long as there is no RAM pressure
in
the machine. As long as SQL Server feels some pressure on memory pool, it
will
start to release memory for other applications use or to avoid swapping.
Try
setting the Min Server and Max Server memory to a know value then run the
same
applications. You should see the server memory grow to that limit and then
maintain on that level.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
Memory problem
I have an accounting program that is loaded on a server
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
hi Daniel,
"Daniel Barnaby" <anonymous@.discussions.microsoft.com> ha scritto nel
messaggio news:57e701c42d34$9e0bcfa0$a301280a@.phx.gbl...
> I have an accounting program that is loaded on a server
> and have 7 users. The program seem to have a memory leak.
> The has server 1 gig of memory after the program runs for
> a few hours the memory usage climes from 150meg used to
> 870meg, used all of witch is used by the sql instance. I
> then have to have eveyone get out of the program stop the
> sql server then restart it. Is there any thing out there
> that can help me(beside buying a new accounting program
> that is writen well),some type of auto resource recycling,
> or auto restarting the sql instance with out causing the
> users any to be down.
you must first understand memory usage by SQL Server...
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...
it's quite a normal behaviour...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
hi Daniel,
"Daniel Barnaby" <anonymous@.discussions.microsoft.com> ha scritto nel
messaggio news:57e701c42d34$9e0bcfa0$a301280a@.phx.gbl...
> I have an accounting program that is loaded on a server
> and have 7 users. The program seem to have a memory leak.
> The has server 1 gig of memory after the program runs for
> a few hours the memory usage climes from 150meg used to
> 870meg, used all of witch is used by the sql instance. I
> then have to have eveyone get out of the program stop the
> sql server then restart it. Is there any thing out there
> that can help me(beside buying a new accounting program
> that is writen well),some type of auto resource recycling,
> or auto restarting the sql instance with out causing the
> users any to be down.
you must first understand memory usage by SQL Server...
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...
it's quite a normal behaviour...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Monday, February 20, 2012
Memory Management: SQL Enterprise, 2000 Adv. Srv.
Questions
A) If I have the /3GB /PAE variables in the Win 2k Server boot.ini and only have 2 gig of memory will this cause issues with memory readings I am seeing in Performance Monitor? (I know it doesn't make sense to have but happened out of circumstance
B) From what I am reading if "SQLServer: Memory Manager -- Target Server Memory (KB)" provides the same value as "SQLServer: Memory Manager -- Total Server Memory (KB)" then SQL is content and has as much memory as it needs. What I am seeing on my SQL server is that the two numbers match but are using all the memory on the server which is 2 GB. I assume that this means increase the amount of memory on the box. Am I correct in this assumption
C) Again from reading my understanding is that when using AWE (under 16 GB), SQL at startup will grab the specified chunk of RAM you have alotted to it. I was wondering if there are any counters within Performance Monitor that will tell me exactly how much of that RAM (AWE) SQL Server is using
Thanks...Anyone?
A) If I have the /3GB /PAE variables in the Win 2k Server boot.ini and only have 2 gig of memory will this cause issues with memory readings I am seeing in Performance Monitor? (I know it doesn't make sense to have but happened out of circumstance
B) From what I am reading if "SQLServer: Memory Manager -- Target Server Memory (KB)" provides the same value as "SQLServer: Memory Manager -- Total Server Memory (KB)" then SQL is content and has as much memory as it needs. What I am seeing on my SQL server is that the two numbers match but are using all the memory on the server which is 2 GB. I assume that this means increase the amount of memory on the box. Am I correct in this assumption
C) Again from reading my understanding is that when using AWE (under 16 GB), SQL at startup will grab the specified chunk of RAM you have alotted to it. I was wondering if there are any counters within Performance Monitor that will tell me exactly how much of that RAM (AWE) SQL Server is using
Thanks...Anyone?
Subscribe to:
Posts (Atom)