February 26, 2008 at 8:00 am
Working Set memory of our production sqlserver keeps increasing constantly. Our database is pretty straightforward just simple stored procedures to query and update tables. The only possible memory intensive operation I can think of is that one of the stored procedures takes xml as input. We parse the xml document by loading into memory and later release the document.
I do not know whether memory consumed by sqlserver is normal or abnormal. Recently we had an issue where our production server locked up and stopped responding. We were not even able to log on to the server. I suspect it probably had something to do with all of memory being used up. sqlserver is the largest memory consumer on that box. There is also another application which consume large memory but it is always the second largest consumer.
I need to know if everything is normal with my sqlserver and the way my application talks to sqlserver.
I did some basic research and found out that sqlserver usually does not release memory untill it has to eventhough it does not need everything in working set.
If I set a limit on max memory but I want to make sure that the limit I set is enough for my sqlserver needs.
Is there way to profile and see if there are any memory leaks?
thanks for help
February 26, 2008 at 9:32 am
What's the memory size on you server? Let us know your O/S with SQL server version + sp infomation. Also check in server logs if you are having stolen memory errors? Have you checked the CPU usage when you think your SQL Server is not responding?
May be with answers to these questions will get us to take some conclusion.
SQL DBA.
February 26, 2008 at 1:03 pm
The machine has 2 3GHz CPU's with Windows 2003 SE SP2 and 3GB of RAM.
SQL Server version: 8.00.2039 SE
I did not find any stolen memory errors in the logs.
when I said server not responding I meant the whole machine did not respond. We were not even able to logon to the machine even from console. I dont even know if sql server was the culprit. I am just trying to eliminate sql server as the suspect.
On another note running "dbcc memusage" told me that a text column is the biggest consumer of memory (about 37% of working set). I do an insert via a stored procedure into a 'text' column.
Is there any way for me to tell sql server to flush these text col values from memory?
appreciate your help
February 26, 2008 at 1:09 pm
It is completely normal for SQL Server to consume all the memory on the box.
More than likely, the reason for the freeze is the other application running on the box. That is why it is not a good idea to install applications on a box where SQL Server is running.
February 26, 2008 at 1:40 pm
phani_va (2/26/2008)
The machine has 2 3GHz CPU's with Windows 2003 SE SP2 and 3GB of RAM.Is there any way for me to tell sql server to flush these text col values from memory?
appreciate your help
Nope. There is no way to tell SQL Server to flush memory when not in use. Most you can do is dynamically set memory on SQL Server property (EM) so that SQL Server will use it to its limit and you have some memory left for applications.
SQL DBA.
February 26, 2008 at 1:56 pm
Is there any way for me to find out if sql server is considering any of my text col values as still in use?
I just want to make sure that some of these text col values are flushed out whenever sql server reaches tries to release unused memory if I set a limit on dynamic memory size.
thanks
February 26, 2008 at 5:29 pm
Nope.. I beleive there is no way to find out what exactly is grabbed and being computed in SQL Server Memory.
So... per me.. best approach will be to limit SQL Server with a minimum and maximum memory setting. However, it is not suggested if you have any workaround like increasing RAM/move other apps from the same box.
Usually SQL is smart enough to manage memory by itself. 🙂
Regards
Utsab Chattopadhyay
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply