| Groups | Blog | Home |
sql server clustering : 300MB DB uses 1.4GB RAM!?
month of continuous use from a single application winds up grabbing 1.4GB of RAM. Is that normal or excessive? (No performance degradation at all, jut a lot of RAM allocated.) This is clustered, but the problem has occurred separately on each node. Yes, that took over 2 months to test. -billb
Hi,
Have you monitored the sysprocess table to find out what spid is utilizing most of the memory? [quoted text, click to view] "Kevin3NF" wrote:
> Yeah...but the default mem-to-leave is 256mb, right? > > Data and index pages go into the Buffer pool. > > So, if every page was in memory, there's 300mb. Mem-to-leave would have to > have been set using -g to 900mb... > > Odd. > > -- > Kevin Hill > 3NF Consulting > http://www.3nf-inc.com/NewsGroups.htm > http://kevin3nf.blogspot.com > > > "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message > news:%23%23gm%23gc%23GHA.2340@TK2MSFTNGP05.phx.gbl... > > cache for : > > queries / views / stored procedures / tables / indexes ... and a lot more > > which SQL holds in memory, when it has the RAM available > > > > > > > > "Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message > > news:%23KaCkcc%23GHA.2300@TK2MSFTNGP04.phx.gbl... > >> 300 MB database, not GB :) > >> > >> The question is what is using the other 900MB? > >> > >> My guess (in another thread on a another group - cross posted) is that > >> mem-to-leave is large and full. > >> > >> -- > >> Kevin Hill > >> 3NF Consulting > >> http://www.3nf-inc.com/NewsGroups.htm > >> http://kevin3nf.blogspot.com > >> > >> > >> "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message > >> news:eq9A3%23Z%23GHA.4980@TK2MSFTNGP04.phx.gbl... > >> > Unless you need to limit it (if you are running other processes on the > >> > same > >> > machine), I wouldn't... the more ram SQL takes the better !! > >> > really... you want SQL use all the availabe RAM and cache as much as > >> > possible (in stead of going to disk) > >> > > >> > IMHO > >> > > >> > > >> > "Alex Persky" <alexvi@bigblue.net.au> wrote in message > >> > news:%23V9pJrZ%23GHA.1220@TK2MSFTNGP05.phx.gbl... > >> >> It's going to grab as much as it can and will hardly give it back. > >> >> > >> >> Yes, it's excessive. I would recommend set a limit on max amount of > >> >> memory > >> >> allocation for SQL Server. The default is unlimited (or a very high > >> > number). > >> >> SQL Enterprise Management Console and right-click the server and go to > >> >> properties. > >> >> > >> >> > >> >> "William Bernat" <nospamplease@unavil.com> wrote in message > >> >> news:Ol4UcjG%23GHA.3348@TK2MSFTNGP03.phx.gbl... > >> >> > SQL Server 2000 running nothing but a 300MB database over the course > > of > >> > a > >> >> > month of continuous use from a single application winds up grabbing > >> > 1.4GB > >> >> > of > >> >> > RAM. Is that normal or excessive? (No performance degradation at > >> >> > all, > >> > jut > >> >> > a > >> >> > lot of RAM allocated.) > >> >> > > >> >> > > >> >> > > >> >> > This is clustered, but the problem has occurred separately on each > >> >> > node. > >> >> > Yes, that took over 2 months to test. > >> >> > > >> >> > > >> >> > > >> >> > -billb > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > >> >> > >> > > >> > > >> > >> > > > > > >
300 MB database, not GB :)
The question is what is using the other 900MB? My guess (in another thread on a another group - cross posted) is that mem-to-leave is large and full. -- Kevin Hill 3NF Consulting http://www.3nf-inc.com/NewsGroups.htm http://kevin3nf.blogspot.com [quoted text, click to view] "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message news:eq9A3%23Z%23GHA.4980@TK2MSFTNGP04.phx.gbl... > Unless you need to limit it (if you are running other processes on the > same > machine), I wouldn't... the more ram SQL takes the better !! > really... you want SQL use all the availabe RAM and cache as much as > possible (in stead of going to disk) > > IMHO > > > "Alex Persky" <alexvi@bigblue.net.au> wrote in message > news:%23V9pJrZ%23GHA.1220@TK2MSFTNGP05.phx.gbl... >> It's going to grab as much as it can and will hardly give it back. >> >> Yes, it's excessive. I would recommend set a limit on max amount of >> memory >> allocation for SQL Server. The default is unlimited (or a very high > number). >> SQL Enterprise Management Console and right-click the server and go to >> properties. >> >> >> "William Bernat" <nospamplease@unavil.com> wrote in message >> news:Ol4UcjG%23GHA.3348@TK2MSFTNGP03.phx.gbl... >> > SQL Server 2000 running nothing but a 300MB database over the course of > a >> > month of continuous use from a single application winds up grabbing > 1.4GB >> > of >> > RAM. Is that normal or excessive? (No performance degradation at all, > jut >> > a >> > lot of RAM allocated.) >> > >> > >> > >> > This is clustered, but the problem has occurred separately on each >> > node. >> > Yes, that took over 2 months to test. >> > >> > >> > >> > -billb >> > >> > >> > >> > >> > >> > >> >> > >
Yeah...but the default mem-to-leave is 256mb, right?
Data and index pages go into the Buffer pool. So, if every page was in memory, there's 300mb. Mem-to-leave would have to have been set using -g to 900mb... Odd. -- Kevin Hill 3NF Consulting http://www.3nf-inc.com/NewsGroups.htm http://kevin3nf.blogspot.com [quoted text, click to view] "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message news:%23%23gm%23gc%23GHA.2340@TK2MSFTNGP05.phx.gbl... > cache for : > queries / views / stored procedures / tables / indexes ... and a lot more > which SQL holds in memory, when it has the RAM available > > > > "Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message > news:%23KaCkcc%23GHA.2300@TK2MSFTNGP04.phx.gbl... >> 300 MB database, not GB :) >> >> The question is what is using the other 900MB? >> >> My guess (in another thread on a another group - cross posted) is that >> mem-to-leave is large and full. >> >> -- >> Kevin Hill >> 3NF Consulting >> http://www.3nf-inc.com/NewsGroups.htm >> http://kevin3nf.blogspot.com >> >> >> "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message >> news:eq9A3%23Z%23GHA.4980@TK2MSFTNGP04.phx.gbl... >> > Unless you need to limit it (if you are running other processes on the >> > same >> > machine), I wouldn't... the more ram SQL takes the better !! >> > really... you want SQL use all the availabe RAM and cache as much as >> > possible (in stead of going to disk) >> > >> > IMHO >> > >> > >> > "Alex Persky" <alexvi@bigblue.net.au> wrote in message >> > news:%23V9pJrZ%23GHA.1220@TK2MSFTNGP05.phx.gbl... >> >> It's going to grab as much as it can and will hardly give it back. >> >> >> >> Yes, it's excessive. I would recommend set a limit on max amount of >> >> memory >> >> allocation for SQL Server. The default is unlimited (or a very high >> > number). >> >> SQL Enterprise Management Console and right-click the server and go to >> >> properties. >> >> >> >> >> >> "William Bernat" <nospamplease@unavil.com> wrote in message >> >> news:Ol4UcjG%23GHA.3348@TK2MSFTNGP03.phx.gbl... >> >> > SQL Server 2000 running nothing but a 300MB database over the course > of >> > a >> >> > month of continuous use from a single application winds up grabbing >> > 1.4GB >> >> > of >> >> > RAM. Is that normal or excessive? (No performance degradation at >> >> > all, >> > jut >> >> > a >> >> > lot of RAM allocated.) >> >> > >> >> > >> >> > >> >> > This is clustered, but the problem has occurred separately on each >> >> > node. >> >> > Yes, that took over 2 months to test. >> >> > >> >> > >> >> > >> >> > -billb >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> >> >> >> > >> > >> >> > >
Unless you need to limit it (if you are running other processes on the same
machine), I wouldn't... the more ram SQL takes the better !! really... you want SQL use all the availabe RAM and cache as much as possible (in stead of going to disk) IMHO [quoted text, click to view] "Alex Persky" <alexvi@bigblue.net.au> wrote in message news:%23V9pJrZ%23GHA.1220@TK2MSFTNGP05.phx.gbl... > It's going to grab as much as it can and will hardly give it back. > > Yes, it's excessive. I would recommend set a limit on max amount of memory > allocation for SQL Server. The default is unlimited (or a very high number). > SQL Enterprise Management Console and right-click the server and go to > properties. > > > "William Bernat" <nospamplease@unavil.com> wrote in message > news:Ol4UcjG%23GHA.3348@TK2MSFTNGP03.phx.gbl... > > SQL Server 2000 running nothing but a 300MB database over the course of a > > month of continuous use from a single application winds up grabbing 1.4GB > > of > > RAM. Is that normal or excessive? (No performance degradation at all, jut > > a > > lot of RAM allocated.) > > > > > > > > This is clustered, but the problem has occurred separately on each node. > > Yes, that took over 2 months to test. > > > > > > > > -billb > > > > > > > > > > > > > >
cache for :
queries / views / stored procedures / tables / indexes ... and a lot more which SQL holds in memory, when it has the RAM available [quoted text, click to view] "Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message news:%23KaCkcc%23GHA.2300@TK2MSFTNGP04.phx.gbl... > 300 MB database, not GB :) > > The question is what is using the other 900MB? > > My guess (in another thread on a another group - cross posted) is that > mem-to-leave is large and full. > > -- > Kevin Hill > 3NF Consulting > http://www.3nf-inc.com/NewsGroups.htm > http://kevin3nf.blogspot.com > > > "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message > news:eq9A3%23Z%23GHA.4980@TK2MSFTNGP04.phx.gbl... > > Unless you need to limit it (if you are running other processes on the > > same > > machine), I wouldn't... the more ram SQL takes the better !! > > really... you want SQL use all the availabe RAM and cache as much as > > possible (in stead of going to disk) > > > > IMHO > > > > > > "Alex Persky" <alexvi@bigblue.net.au> wrote in message > > news:%23V9pJrZ%23GHA.1220@TK2MSFTNGP05.phx.gbl... > >> It's going to grab as much as it can and will hardly give it back. > >> > >> Yes, it's excessive. I would recommend set a limit on max amount of > >> memory > >> allocation for SQL Server. The default is unlimited (or a very high > > number). > >> SQL Enterprise Management Console and right-click the server and go to > >> properties. > >> > >> > >> "William Bernat" <nospamplease@unavil.com> wrote in message > >> news:Ol4UcjG%23GHA.3348@TK2MSFTNGP03.phx.gbl... > >> > SQL Server 2000 running nothing but a 300MB database over the course of > > a > >> > month of continuous use from a single application winds up grabbing > > 1.4GB > >> > of > >> > RAM. Is that normal or excessive? (No performance degradation at all, > > jut > >> > a > >> > lot of RAM allocated.) > >> > > >> > > >> > > >> > This is clustered, but the problem has occurred separately on each > >> > node. > >> > Yes, that took over 2 months to test. > >> > > >> > > >> > > >> > -billb > >> > > >> > > >> > > >> > > >> > > >> > > >> > >> > > > > > >
http://support.microsoft.com/kb/271624
"DBCC MEMORYSTATUS" reports back in number of buffers (1 buffer = 8 K) [quoted text, click to view] "Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message news:eJxADxc%23GHA.4376@TK2MSFTNGP03.phx.gbl... > Yeah...but the default mem-to-leave is 256mb, right? > > Data and index pages go into the Buffer pool. > > So, if every page was in memory, there's 300mb. Mem-to-leave would have to > have been set using -g to 900mb... > > Odd. > > -- > Kevin Hill > 3NF Consulting > http://www.3nf-inc.com/NewsGroups.htm > http://kevin3nf.blogspot.com > > > "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message > news:%23%23gm%23gc%23GHA.2340@TK2MSFTNGP05.phx.gbl... > > cache for : > > queries / views / stored procedures / tables / indexes ... and a lot more > > which SQL holds in memory, when it has the RAM available > > > > > > > > "Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message > > news:%23KaCkcc%23GHA.2300@TK2MSFTNGP04.phx.gbl... > >> 300 MB database, not GB :) > >> > >> The question is what is using the other 900MB? > >> > >> My guess (in another thread on a another group - cross posted) is that > >> mem-to-leave is large and full. > >> > >> -- > >> Kevin Hill > >> 3NF Consulting > >> http://www.3nf-inc.com/NewsGroups.htm > >> http://kevin3nf.blogspot.com > >> > >> > >> "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message > >> news:eq9A3%23Z%23GHA.4980@TK2MSFTNGP04.phx.gbl... > >> > Unless you need to limit it (if you are running other processes on the > >> > same > >> > machine), I wouldn't... the more ram SQL takes the better !! > >> > really... you want SQL use all the availabe RAM and cache as much as > >> > possible (in stead of going to disk) > >> > > >> > IMHO > >> > > >> > > >> > "Alex Persky" <alexvi@bigblue.net.au> wrote in message > >> > news:%23V9pJrZ%23GHA.1220@TK2MSFTNGP05.phx.gbl... > >> >> It's going to grab as much as it can and will hardly give it back. > >> >> > >> >> Yes, it's excessive. I would recommend set a limit on max amount of > >> >> memory > >> >> allocation for SQL Server. The default is unlimited (or a very high > >> > number). > >> >> SQL Enterprise Management Console and right-click the server and go to > >> >> properties. > >> >> > >> >> > >> >> "William Bernat" <nospamplease@unavil.com> wrote in message > >> >> news:Ol4UcjG%23GHA.3348@TK2MSFTNGP03.phx.gbl... > >> >> > SQL Server 2000 running nothing but a 300MB database over the course > > of > >> > a > >> >> > month of continuous use from a single application winds up grabbing > >> > 1.4GB > >> >> > of > >> >> > RAM. Is that normal or excessive? (No performance degradation at > >> >> > all, > >> > jut > >> >> > a > >> >> > lot of RAM allocated.) > >> >> > > >> >> > > >> >> > > >> >> > This is clustered, but the problem has occurred separately on each > >> >> > node. > >> >> > Yes, that took over 2 months to test. > >> >> > > >> >> > > >> >> > > >> >> > -billb > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > >> >> > >> > > >> > > >> > >> > > > > > >
It's going to grab as much as it can and will hardly give it back.
Yes, it's excessive. I would recommend set a limit on max amount of memory allocation for SQL Server. The default is unlimited (or a very high number). SQL Enterprise Management Console and right-click the server and go to properties. [quoted text, click to view] "William Bernat" <nospamplease@unavil.com> wrote in message news:Ol4UcjG%23GHA.3348@TK2MSFTNGP03.phx.gbl... > SQL Server 2000 running nothing but a 300MB database over the course of a > month of continuous use from a single application winds up grabbing 1.4GB > of > RAM. Is that normal or excessive? (No performance degradation at all, jut > a > lot of RAM allocated.) > > > > This is clustered, but the problem has occurred separately on each node. > Yes, that took over 2 months to test. > > > > -billb > > > > > >
MEM TO LEAVE is 384 MB by default (-g is 256 MB default). Don't forget
about the other 128 MB set up to manage the possible default 255 worker threads at 0.5 MB each. This is added to the -g parameter. DBCC MEMORYSTATUS will tell you how SQL Server memory manager has partitioned and allocated the BPool. Anthony Thomas -- [quoted text, click to view] "Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message news:eJxADxc%23GHA.4376@TK2MSFTNGP03.phx.gbl... > Yeah...but the default mem-to-leave is 256mb, right? > > Data and index pages go into the Buffer pool. > > So, if every page was in memory, there's 300mb. Mem-to-leave would have to > have been set using -g to 900mb... > > Odd. > > -- > Kevin Hill > 3NF Consulting > http://www.3nf-inc.com/NewsGroups.htm > http://kevin3nf.blogspot.com > > > "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message > news:%23%23gm%23gc%23GHA.2340@TK2MSFTNGP05.phx.gbl... > > cache for : > > queries / views / stored procedures / tables / indexes ... and a lot more > > which SQL holds in memory, when it has the RAM available > > > > > > > > "Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message > > news:%23KaCkcc%23GHA.2300@TK2MSFTNGP04.phx.gbl... > >> 300 MB database, not GB :) > >> > >> The question is what is using the other 900MB? > >> > >> My guess (in another thread on a another group - cross posted) is that > >> mem-to-leave is large and full. > >> > >> -- > >> Kevin Hill > >> 3NF Consulting > >> http://www.3nf-inc.com/NewsGroups.htm > >> http://kevin3nf.blogspot.com > >> > >> > >> "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message > >> news:eq9A3%23Z%23GHA.4980@TK2MSFTNGP04.phx.gbl... > >> > Unless you need to limit it (if you are running other processes on the > >> > same > >> > machine), I wouldn't... the more ram SQL takes the better !! > >> > really... you want SQL use all the availabe RAM and cache as much as > >> > possible (in stead of going to disk) > >> > > >> > IMHO > >> > > >> > > >> > "Alex Persky" <alexvi@bigblue.net.au> wrote in message > >> > news:%23V9pJrZ%23GHA.1220@TK2MSFTNGP05.phx.gbl... > >> >> It's going to grab as much as it can and will hardly give it back. > >> >> > >> >> Yes, it's excessive. I would recommend set a limit on max amount of > >> >> memory > >> >> allocation for SQL Server. The default is unlimited (or a very high > >> > number). > >> >> SQL Enterprise Management Console and right-click the server and go to > >> >> properties. > >> >> > >> >> > >> >> "William Bernat" <nospamplease@unavil.com> wrote in message > >> >> news:Ol4UcjG%23GHA.3348@TK2MSFTNGP03.phx.gbl... > >> >> > SQL Server 2000 running nothing but a 300MB database over the course > > of > >> > a > >> >> > month of continuous use from a single application winds up grabbing > >> > 1.4GB > >> >> > of > >> >> > RAM. Is that normal or excessive? (No performance degradation at > >> >> > all, > >> > jut > >> >> > a > >> >> > lot of RAM allocated.) > >> >> > > >> >> > > >> >> > > >> >> > This is clustered, but the problem has occurred separately on each > >> >> > node. > >> >> > Yes, that took over 2 months to test. > >> >> > > >> >> > > >> >> > > >> >> > -billb > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > >> >> > >> > > >> > > >> > >> > > > > > >
Probably internal results from old queries and their indexes. The logic is
why not keeping them, maybe they'll be useful for next queries. The fact is that SBS, for example, would even rise a warning event that all available memory has been used by running programs, so I would suggest to limit it anyway. Influence on speed is not going to be noticeable, until such time comes when some of the indexes doesn't fit into memory. [quoted text, click to view] "Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message news:%23KaCkcc%23GHA.2300@TK2MSFTNGP04.phx.gbl... > 300 MB database, not GB :) > > The question is what is using the other 900MB? > > My guess (in another thread on a another group - cross posted) is that > mem-to-leave is large and full. > > -- > Kevin Hill > 3NF Consulting > http://www.3nf-inc.com/NewsGroups.htm > http://kevin3nf.blogspot.com > > > "Edwin vMierlo" <EdwinvMierlo@discussions.microsoft.com> wrote in message > news:eq9A3%23Z%23GHA.4980@TK2MSFTNGP04.phx.gbl... >> Unless you need to limit it (if you are running other processes on the >> same >> machine), I wouldn't... the more ram SQL takes the better !! >> really... you want SQL use all the availabe RAM and cache as much as >> possible (in stead of going to disk) >> >> IMHO >> >> >> "Alex Persky" <alexvi@bigblue.net.au> wrote in message >> news:%23V9pJrZ%23GHA.1220@TK2MSFTNGP05.phx.gbl... >>> It's going to grab as much as it can and will hardly give it back. >>> >>> Yes, it's excessive. I would recommend set a limit on max amount of >>> memory >>> allocation for SQL Server. The default is unlimited (or a very high >> number). >>> SQL Enterprise Management Console and right-click the server and go to >>> properties. >>> >>> >>> "William Bernat" <nospamplease@unavil.com> wrote in message >>> news:Ol4UcjG%23GHA.3348@TK2MSFTNGP03.phx.gbl... >>> > SQL Server 2000 running nothing but a 300MB database over the course >>> > of >> a >>> > month of continuous use from a single application winds up grabbing >> 1.4GB >>> > of >>> > RAM. Is that normal or excessive? (No performance degradation at all, >> jut >>> > a >>> > lot of RAM allocated.) >>> > >>> > >>> > >>> > This is clustered, but the problem has occurred separately on each >>> > node. >>> > Yes, that took over 2 months to test. >>> > >>> > >>> > >>> > -billb >>> > >>> > >>> > >>> > >>> > >>> > >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |