March 31, 2004 at 11:51 am
Friends.
I would like to know if SQL server have some know memory leak situations.
Environment: SQL2K with SP3 on Win2K.
I have a controlled environment where I execute a stored procedure which calls OPENXML and deletes the prepared xml document. This SP is running every 5-10 seconds frequency. During this period SQL server memory goes up slowly slowly and shoots up from 36MB to 1.7GB and stays at that level.
Interesting thing is even if I stop calling this stored procedure SQL server stays at higher limit of Memory usage which is 1.7 GB in this situation. This is typical scenerio of Memory leaks. I wonder why this is happening.
Is it SQL server leaving behind the memory leaks.
Thanks
GOPAL
FYI. For details of SP You may also refer.. http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=109119
March 31, 2004 at 12:07 pm
SQL's normal behavior is to continually request memory as it needs it. Unlike other apps, however, it'll hold that memory unless it gets a request from the operating system to release the memory (and then it's a turtle releasing it). Seeing SQL Server going up and maintaining at 1.7 GB is therefore not all that unusual if your SQL Server gets a fair amount of utilization. One thing you might observer through Performance Monitor is the Target Server Memory versus the Total Server Memory to see if they are close to one another. Target is obviously what SQL Server wants and Total is what it believes it has allocated.
K. Brian Kelley
@kbriankelley
March 31, 2004 at 1:05 pm
To demonstrate what Brian mentions.
On a TEST system, go into Properties --> Memory. Also fire up Perfmon or even task manager if you are local or remote desktop'd in.
Start playing with the sliders, shrink down the max SQL can take, and watch it's memory drop. Slowly yes, but it drops. Then bring it back up and slowly (quicker than it releases, but slowly) it consumes more. As he mentions, you might need something running requesting memory to prod SQL to release it. But the memory footprint is truly dynamic.
I found this very interesting when comparing to a certain other DBMS I support. It made me "BLUE" that it couldn't do the same.
KlK
April 1, 2004 at 11:39 am
I monitored Target server Memory and Total server Memory when the system reached idle state the two are equal and staying at higher limit 1629MB. As per Brian's suggestion I should be able to see atleast the Target server memory to go down as there is no activity.
Though knudsons idea of properties-memory-slider worked, but I didn't have any other application requesting memory so SQL server may not be resetting its counters.
Do you have any idea how to see the SQL Server memory usage per SQL connection. Profiler doesn't give me option to see memory.
Thanks
GOPAL
April 1, 2004 at 11:53 am
No, SQL Server Target Memory will stay the same unless there is non-SQL Server activity on that system demanding memory. In that case, the operating system will ask SQL Server to release memory. SQL Server won't release memory unless asked.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply