Monday, March 12, 2012

Excessive memory usage

My sql server apparently makes awful use of the system memory. I have to
restart the server once a day, because use of memory gradually increases to
500-600 mb in one day. What am I doing wrong, can you help me?
Max MullerMax Muller wrote:
> My sql server apparently makes awful use of the system memory. I have
> to restart the server once a day, because use of memory gradually
> increases to 500-600 mb in one day. What am I doing wrong, can you
> help me?
>
> Max Muller
Nothing to do. That's how SQL works. It uses memory as it needs it and
doesn't give it back unless the OS specifically requests the service
release some memory (which rarely happens).
The fact that SQL Server uses memory shouldn't be a problem for most
servers, since it performs better the more memory it has.
If you're running into a situation where SQL Server is running on a box
with other services and applications, you can set the maximum memory SQL
Server can use in SQL Enterprise Manager.
Can you explain why the memory usage is a concern?
David Gugick
Imceda Software
www.imceda.com|||Hi All,
I'm experiencing the same memory issue with SQL Server 2000 Standard as Max.
I now understand that it's normal for SQL to hog memory as needed. You
mentioned that if SQL server is running on a box with other services and
apps, we could set the maximum memory usage option. I know you said we
"COULD" but I was wondering if we SHOULD. I have other services and apps on
the box and I was concern about the low memory available. After reading your
comments and http://support.microsoft.com/defaul...b;en-us;q321363
I now know that SQL server will release memory as needed by OS, but I'm
still wondering, is setting the max mem limit a standard practice when
running other services on same box as SQL?
Thanks.
E. Ortega
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ejkaIx8DFHA.3976@.tk2msftngp13.phx.gbl...
> Max Muller wrote:
> Nothing to do. That's how SQL works. It uses memory as it needs it and
> doesn't give it back unless the OS specifically requests the service
> release some memory (which rarely happens).
> The fact that SQL Server uses memory shouldn't be a problem for most
> servers, since it performs better the more memory it has.
> If you're running into a situation where SQL Server is running on a box
> with other services and applications, you can set the maximum memory SQL
> Server can use in SQL Enterprise Manager.
> Can you explain why the memory usage is a concern?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||E Ortega wrote:
> Hi All,
> I'm experiencing the same memory issue with SQL Server 2000 Standard
> as Max. I now understand that it's normal for SQL to hog memory as
> needed. You mentioned that if SQL server is running on a box with
> other services and apps, we could set the maximum memory usage
> option. I know you said we "COULD" but I was wondering if we SHOULD. I
> have other services and apps on the box and I was concern about the
> low memory available. After reading your comments and
> http://support.microsoft.com/defaul...b;en-us;q321363 I now
> know that SQL server will release memory as needed by OS, but I'm
> still wondering, is setting the max mem limit a standard practice
> when running other services on same box as SQL?
> Thanks.
> E. Ortega
>
Yes, it is. The best thing to do is add as much memory as you can to the
server. Memory is so cheap that it can have a dramatic improvement in
performance for all services. But if you have IIS or other services or
applications running on the server, you should set a maximum limit to
make sure SQL Server doesn't leave the other services starved for memory
as writing to disk is very slow and will hurt overall server performance
for all services.
Tuning your SQL is a great way to limit SQL Servers memory and CPU
footprints as well.
David Gugick
Imceda Software
www.imceda.com|||Thanks for your time and info very valuable to me and I'm sure to others out
there. If is not too much trouble, could you please briefly consider my
configuration and give me a suggestion as to what my max mem usage should
be?
SQL 2000 (SP3) Standard
Dual Xeon 3.0 GHz
Memory 2 Gig
Current Task Manager info:
Physical Memory
Total 2096636
Available 155724
System Cache 363272
Totals:
Handles 15422
Threads 731
Processes 56
CPU Usage varies 1% - 7%
sqlservr.exe mem usage is 1,175,992 mem
available mem is 155724, the rest of the mem is used by other
services(docsfusion, veritas storage replicator, etc.).
I have one DB with 50 users.
Thanks in advance for your time and info.
E. Ortega
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uR6mDWFEFHA.3972@.TK2MSFTNGP15.phx.gbl...
>E Ortega wrote:
> Yes, it is. The best thing to do is add as much memory as you can to the
> server. Memory is so cheap that it can have a dramatic improvement in
> performance for all services. But if you have IIS or other services or
> applications running on the server, you should set a maximum limit to make
> sure SQL Server doesn't leave the other services starved for memory as
> writing to disk is very slow and will hurt overall server performance for
> all services.
> Tuning your SQL is a great way to limit SQL Servers memory and CPU
> footprints as well.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||It really depends on just how much memory these other apps need on a regular
basis. If you determine for instance that they need 300MB to operate
properly you might want to drop the max memory down 300MB from the value you
see Sql Server using in task manager. That would be roughly 1.4GB. That
would leave room for the memtoleave portion of sql server and ~300MB for the
other apps. When you run other apps on the same server as Sql Server and
only have 2GB you always have a trade off of who can best use the memory.
Even though Sql Server will dynamically adjust memory if the other apps
require xxMB's all the time it is usually best to limit how much Sql Server
can use.
Andrew J. Kelly SQL MVP
"E Ortega" <elburu@.hotmail.com> wrote in message
news:eGUritFEFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Thanks for your time and info very valuable to me and I'm sure to others
> out there. If is not too much trouble, could you please briefly consider
> my configuration and give me a suggestion as to what my max mem usage
> should be?
> SQL 2000 (SP3) Standard
> Dual Xeon 3.0 GHz
> Memory 2 Gig
> Current Task Manager info:
> Physical Memory
> Total 2096636
> Available 155724
> System Cache 363272
> Totals:
> Handles 15422
> Threads 731
> Processes 56
> CPU Usage varies 1% - 7%
> sqlservr.exe mem usage is 1,175,992 mem
> available mem is 155724, the rest of the mem is used by other
> services(docsfusion, veritas storage replicator, etc.).
> I have one DB with 50 users.
> Thanks in advance for your time and info.
> E. Ortega
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uR6mDWFEFHA.3972@.TK2MSFTNGP15.phx.gbl...
>|||As always, thanks very much for your time and info.
E. Ortega
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:urxDmcGEFHA.2572@.tk2msftngp13.phx.gbl...
> It really depends on just how much memory these other apps need on a
> regular basis. If you determine for instance that they need 300MB to
> operate properly you might want to drop the max memory down 300MB from the
> value you see Sql Server using in task manager. That would be roughly
> 1.4GB. That would leave room for the memtoleave portion of sql server and
> ~300MB for the other apps. When you run other apps on the same server as
> Sql Server and only have 2GB you always have a trade off of who can best
> use the memory. Even though Sql Server will dynamically adjust memory if
> the other apps require xxMB's all the time it is usually best to limit how
> much Sql Server can use.
> --
> Andrew J. Kelly SQL MVP
>
> "E Ortega" <elburu@.hotmail.com> wrote in message
> news:eGUritFEFHA.3908@.TK2MSFTNGP12.phx.gbl...
>|||Thanks a lot David, you helped me understand what I'm dealing with.
Max
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ejkaIx8DFHA.3976@.tk2msftngp13.phx.gbl...
> Max Muller wrote:
> Nothing to do. That's how SQL works. It uses memory as it needs it and
> doesn't give it back unless the OS specifically requests the service
> release some memory (which rarely happens).
> The fact that SQL Server uses memory shouldn't be a problem for most
> servers, since it performs better the more memory it has.
> If you're running into a situation where SQL Server is running on a box
> with other services and applications, you can set the maximum memory SQL
> Server can use in SQL Enterprise Manager.
> Can you explain why the memory usage is a concern?
> --
> David Gugick
> Imceda Software
> www.imceda.com

No comments:

Post a Comment