| Author |
Topic |
|
harshal_in
Aged Yak Warrior
633 Posts |
 Posted - 2003-08-25 : 03:31:02
|
| hello,we have sql 2k /win2k advanced server with all patches applied.suddenly I have noticed the server goes 100 % cpu on simple select queries and all the time the memory (ram) usage for sql server is also high.After monitering the server by using profiler I did not find any long running queries.please help,where do I start.regards,harshal.The Judgement of the Judge is as good as the Judge. |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2003-08-25 : 08:33:09
|
| My understanding is that SQL uses as much memory as available and then releases it when done. I don't think that what you are seeing is abnormal. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-25 : 12:26:47
|
| SQL Server will not release memory unless it no longer needs it AND another process is asking for it. If no other process is asking for it, SQL Server will not release it. Are other programs suffering from the lack of available memory? Is this a dedicated database server?Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-25 : 12:31:05
|
| Here is some good information about memory used by SQL Server:[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;321363[/url]Semi-colons still aren't showing up when you provide links, so just add semi-colons to the above link where you see the spaces.Tara |
|
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-08-25 : 13:21:15
|
quote:
Originally posted by tduggan
SQL Server will not release memory unless it no longer needs it AND another process is asking for it. If no other process is asking for it, SQL Server will not release it. Are other programs suffering from the lack of available memory? Is this a dedicated database server?Tara
no the server just is just a database server.actually since last couple of days we are recieving following errors when trying access the database from the websites database server not available or sql server does not exist ,after looking at it we noticed that at time it goes 99% this happens when some specific jobs are running,I understand ,there is some tunning required there,but the memory is always high,is this abnormal because it does not release it after use and is there any way to release the memory if it is not in use and at the first place should we release the memory?The Judgement of the Judge is as good as the Judge. |
|
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-08-25 : 13:24:55
|
quote:
Originally posted by tduggan
Here is some good information about memory used by SQL Server:[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;321363[/url]Semi-colons still aren't showing up when you provide links, so just add semi-colons to the above link where you see the spaces.Tara
okThe Judgement of the Judge is as good as the Judge. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-25 : 13:26:02
|
| SQL Server will NOT release memory unless it no longer needs it AND another process is asking for it. Why would you want it to release the memory if SQL Server still needs it or why would you want it to release it if no other process needs it? This is not abnormal. On a couple of our database servers, we've got only 10MB of available memory, but that isn't a problem because they are dedicated to SQL Server. The error that you are receiving occassionally is not related to memory. It is most likely related to the CPU being at 99%. What kind of jobs are running? Tara |
|
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-08-25 : 13:31:21
|
| all kind of jobs are running on the server like mailing the customers,generating reports ,some of them give a call to exe's or dll's.which do some processing on according to the data provided on other servers.The Judgement of the Judge is as good as the Judge. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-25 : 19:29:34
|
quote:
no the server just is just a database server.
quote:
all kind of jobs are running on the server like mailing the customers,generating reports ,some of them give a call to exe's or dll's.which do some processing on according to the data provided on other servers.
I'm trying to think of a greater contradiction and I'm pretty sure there isn't one.In any case, you've answered your own question. You almost certainly have one or more memory leaks in the DLLs being used. As long as you continue to use the software you have, on that SQL Server, you'll continue to have these problems. |
|
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-08-26 : 01:40:04
|
| I think you may be right there may be leak in the dll but this job runs hardly for 10 minutes.Remaining all the jobs are stored procedures ,mails go through the procedures,reports are generated through the procs.The basic problem is that the server shows activity when no job is running on it.The Judgement of the Judge is as good as the Judge. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-26 : 12:26:16
|
| Even if the job runs for 10 minutes, the memory will still be held. Have you run Performance Monitor to see what is occurring on the database server?Tara |
|
|
|
|
High memory usage