December 16, 2008 at 3:05 am
Hi ,
We are running SQL 2000 server database for our websites.
When we look sqlservr.exe inTask Manger lot of memory ( upto 1.7 GB ) and also cpu usage ( from 40% to 80% )
Because of that sometimes our website is down.
The server is only sql server. No any other application is running on it.
When we restart the sql service it will solve our problem temporarily.
But again same problem arises agin after some time. and sqlservr.exe keep on increasing memory usages.
Please can you help to solve this issue.
December 16, 2008 at 5:02 am
Please specify the sql server edition and memory configuration.
MJ
December 16, 2008 at 5:14 am
ananda.murugesan (12/16/2008)
Hi ,We are running SQL 2000 server database for our websites.
When we look sqlservr.exe inTask Manger lot of memory ( upto 1.7 GB ) and also cpu usage ( from 40% to 80% )
Because of that sometimes our website is down.
The server is only sql server. No any other application is running on it.
When we restart the sql service it will solve our problem temporarily.
But again same problem arises agin after some time. and sqlservr.exe keep on increasing memory usages.
Please can you help to solve this issue.
The fact that the server is using 1.7 GB doesn't neccerly means that there is a problem. It does mean that it wac configured in a way that let it use 1.7 GB. You can decide to limit the memory consumption and not let the server get 1.7 GB, but it might do more damage. Instead you should try to find out why the web site is down. It could be something completely different. For example you could have blocking problem that will be solved if you restart the server. Of course when the server is starting it might take less memory and since the blocking is over, you will not have a problem with your web site. For you it appears that the problem was due to memory usage, but in this example it had nothing to do with it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 16, 2008 at 5:21 am
i run sp_confiure, for the following result.
If there any changes required.
affinity mask-2147483648214748364700
allow updates0100
awe enabled0100
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364721474836472147483647
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0111
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
December 16, 2008 at 5:36 am
By the looks of things you have not set a cap on the amount of memory that SQL Server can take. Given this you could run severely short on resources, essentially choking the OS.
How much memory is in the server?
As a general rule of thumb I would recommend leaving 500MB to 1GB of memory for the OS and then allocate the rest to SQL.
December 16, 2008 at 9:16 pm
Hi thanks for reply.
server has using 3 GB RAM
processor - Intel xeon(R) CPU 5152 @ 2.66GHs.
OS - Windows server 2003 Enterprise Edtion (sp2)
DB- Sql server 2000 (sp3)
so, How can i set the cap on the amount of memory that SQL Server 2000?
still sqlservr.exe using 1.7 GB in Task manager, how do minimize the memory on sqlservr.exe?
Thanks
December 16, 2008 at 11:12 pm
You can limit the memory by running sp_configure with the 'max server memory (MB)' option. You can also do it from the GUI - right click on the server's name, then click on properties. From that point it is self explenery.
Although you can change it and limit it, I still think that it wouldn't solve your problem. You did not explain why you think that SQL Server uses too much memory. From what you posted here it seems that your OS has more then 1.3 GB of memory left after SQL Server got his chunk of memory. If there is nothing else running on the server, it is more then enough for the OS. As I wrote before your problem might be related to something completely different. Did you look for other problems?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 17, 2008 at 6:58 am
Seeing as you are only on Standard SQL 2000 (by the looks of your spec) and have 3GB of RAM, there's no issue on the memory side that stands out as it can only use 2GB max on Std Edition, leaving 1GB free at least for the OS. Also, SQL will take what it can when it needs it and does not always release it back unless a specific request is made. E.g. it might have spiked at 1.7GB but may currently only be using 0.5GB of that - it is just nothing else has requested it back so it is keeping hold of it.
I would look into the CPU usage and maybe a few profiler traces on the system to see what is using so much CPU time.
HTH,
M.
February 10, 2010 at 4:57 am
Hi,
I have same problem with memory usage of sql server.
Did you find the solution for it .
Please do let me know , if you have the solution .
Thanks ,
Yukthi
February 10, 2010 at 10:04 am
I don't think dynamic AWE is support in SQL 2000, Why don't you upgrade to 2005 or 2008?
Here is the steps enable and set AWE memory
To enable AWE
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
To set Max and Min Memory level 1.7GB and 1.3GB
sp_configure 'min server memory', 1332
RECONFIGURE
GO
sp_configure 'max server memory', 1740
RECONFIGURE
Check out BOL for more info on this.
February 10, 2010 at 10:15 am
As mentioned, SQL Server uses the memory is needs. If that's too much for your server, then you might run slower, in essence you have poorly performing code or not enough memory. SQL Server has no issue in using "too much memory".
It's only in a situation where the hardware is too small, or code poorly written, that there is an issue with memory.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply