Memory

Hi,
I posted this question before and I received some conflicting information,
hopefully being a technet subscriber a Microsoft tech will give me some
definitive answers.
I'm not a SQL guy at all so I don't wanna go hacking away at this and screw
it up,
I've got SQL running on a 2000 server box, really it's the only program
running, besides the program that uses SQL. SQL on a regular bases runs up
to around 700MB of memory, I need to cap that if possible, if some body has a
suggestion if I do it could you give the undo if you will, in case it doesn't
work. I'll be honest I don't know a thing about SQL and if I blow it I'm
gonna have a hec of a time fixing it.
Thanks for your help,
Craig
There is a setting called the MAX Memory setting for the SQL Server memory
pool that you will have to adjust. If you look up "max server memory
option" in BooksOnLine it will give several sections of BOL that are related
to this topic and one explains how to adjust this with EM or via commands.
The one thing to keep in mind is that the Max memory setting is for the
memory pool only but there is another area of memory called the MemToLeave
area that can use some above and beyond the Max setting. On an average
system it may use another 100 to 200MB in addition to the memory pool. So
if you are trying to leave x amount for the OS or another app you need to
account for both. Why do you feel the need to CAP this if SQL Server is the
only app running on the server?
Andrew J. Kelly SQL MVP
"seedogg" <seedogg@.noemail.postalias.com> wrote in message
news:C272B47E-68B7-480B-BD2E-06472A2049C7@.microsoft.com...
> Hi,
> I posted this question before and I received some conflicting information,
> hopefully being a technet subscriber a Microsoft tech will give me some
> definitive answers.
> I'm not a SQL guy at all so I don't wanna go hacking away at this and
> screw
> it up,
> I've got SQL running on a 2000 server box, really it's the only program
> running, besides the program that uses SQL. SQL on a regular bases runs
> up
> to around 700MB of memory, I need to cap that if possible, if some body
> has a
> suggestion if I do it could you give the undo if you will, in case it
> doesn't
> work. I'll be honest I don't know a thing about SQL and if I blow it I'm
> gonna have a hec of a time fixing it.
> --
> Thanks for your help,
> Craig
|||Thanks Andrew for the information,
I do have one other program on the server, it's actually the program that
interfaces or uses SQL (it's SQL 2000 by the way).
I was running way over into Virtual Memory, about 200 - 400mb over and users
started receiving errors from the program that uses SQL.
I rebooted the server, memory
dropped back down temporarily and all is well. So, I assumed the fix would
be to control the memory usage of SQL, I am more than open
to any other suggestions though. I did minimize the other program on that
server, it's actually quite large but not a memory hog, I've got
Trend Micro as well but not much more. So, to answer your question the
users are receiving errors from the program, performance is down while
in that program and I'm running over into vm. I apologize I wasn't clearer
in my description in the first e-mail.
Thanks again Andrew,
Go Bucks
Craig
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OETWcIkzEHA.3408@.tk2msftngp13.phx.gbl...
> There is a setting called the MAX Memory setting for the SQL Server memory
> pool that you will have to adjust. If you look up "max server memory
> option" in BooksOnLine it will give several sections of BOL that are
> related to this topic and one explains how to adjust this with EM or via
> commands. The one thing to keep in mind is that the Max memory setting is
> for the memory pool only but there is another area of memory called the
> MemToLeave area that can use some above and beyond the Max setting. On an
> average system it may use another 100 to 200MB in addition to the memory
> pool. So if you are trying to leave x amount for the OS or another app
> you need to account for both. Why do you feel the need to CAP this if SQL
> Server is the only app running on the server?
>
> --
> Andrew J. Kelly SQL MVP
>
> "seedogg" <seedogg@.noemail.postalias.com> wrote in message
> news:C272B47E-68B7-480B-BD2E-06472A2049C7@.microsoft.com...
>
|||Yes, originally you stated that SQL Server was the only program running but
since that is not the case it does make a difference. When ever I run across
a server that has SQL Server and another app I try to set the Max memory
setting in such a way as to always leave memory for the other app. You
didn't say how much ram is available but it sounds like you could probably
use some more. By setting the Max memory low enough to give the other app
what it needs you may hurt the performance of SQL Server by needing to
access disk more often. It's hard to say until you try it though. Monitor
your cache hit ratio and the average disk queues to see if lowering the Max
memory setting has a negative effect. If you start to starve SQL Server the
cache hit ratio will go down (anything above 95% is ideal) and the disk
queues will climb. I should have included this before as well:
http://msdn.microsoft.com/SQL/sqlarc...v_01262004.asp
Andrew J. Kelly SQL MVP
"Craig Pfaff" <seedogg99@.noemailspostalias.com> wrote in message
news:Oc52zSkzEHA.2316@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew for the information,
> I do have one other program on the server, it's actually the program that
> interfaces or uses SQL (it's SQL 2000 by the way).
> I was running way over into Virtual Memory, about 200 - 400mb over and
> users started receiving errors from the program that uses SQL.
> I rebooted the server, memory
> dropped back down temporarily and all is well. So, I assumed the fix would
> be to control the memory usage of SQL, I am more than open
> to any other suggestions though. I did minimize the other program on that
> server, it's actually quite large but not a memory hog, I've got
> Trend Micro as well but not much more. So, to answer your question the
> users are receiving errors from the program, performance is down while
> in that program and I'm running over into vm. I apologize I wasn't
> clearer in my description in the first e-mail.
> Thanks again Andrew,
> Go Bucks
> Craig
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OETWcIkzEHA.3408@.tk2msftngp13.phx.gbl...
>
|||Thanks again Andrew,
I've got just over a GB of memory. Which seems like a lot to me considering
I only have a couple of programs. Right now I have two
instances of SQL running in the processes, one is using over 500mb and the
other is using 50mb of memory. This isn't so bad but last
week I had my memory upto 1.4GB and that's when the errors where being
thrown out and SQL was almost to a GB in the processes.
I'll look at your suggestions,
Thanks Craig
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23qeDGfkzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> Yes, originally you stated that SQL Server was the only program running
> but since that is not the case it does make a difference. When ever I run
> across a server that has SQL Server and another app I try to set the Max
> memory setting in such a way as to always leave memory for the other app.
> You didn't say how much ram is available but it sounds like you could
> probably use some more. By setting the Max memory low enough to give the
> other app what it needs you may hurt the performance of SQL Server by
> needing to access disk more often. It's hard to say until you try it
> though. Monitor your cache hit ratio and the average disk queues to see
> if lowering the Max memory setting has a negative effect. If you start to
> starve SQL Server the cache hit ratio will go down (anything above 95% is
> ideal) and the disk queues will climb. I should have included this before
> as well:
> http://msdn.microsoft.com/SQL/sqlarc...v_01262004.asp
> --
> Andrew J. Kelly SQL MVP
>
> "Craig Pfaff" <seedogg99@.noemailspostalias.com> wrote in message
> news:Oc52zSkzEHA.2316@.TK2MSFTNGP15.phx.gbl...
>