August 31, 2004 at 7:19 am
How do I control the virtual memory that SQL server takes whenever a query with large amounts of data is returned? Is it possible for me to have SQL server free up the memory it had allocated, after the query has been executed? Right now, memory once allocated stays allocated, and hence affects the overall performance of my system.
Thanks,
Krishnan
August 31, 2004 at 7:55 am
The memory is released, but the caching might be affected. This will balance out as other queries are called. Right now I do not believe there is anyway to affect the memory used by a particular query.
How are you determining that the memory is not reallocated?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 31, 2004 at 8:54 am
When I execute a query returning >100000 rows, then the virtual memory usage of sqlservr.exe (under Windows NT Task Manager) increases quite a lot (>200MB!!). Even after the querying application had closed the connection, the memory usage shown is still the same. However, the memory allocated earlier is reused for future queries, and hence I do not see any further increase in the virtual memory. However, due to such high virtual memory usage, the performance of other applications running on my machine is affected adversely.
Thanks,
Krishnan
August 31, 2004 at 9:29 am
As SQL needs memory, it will request more memory from the OS. You can use Enterprise Manager | Server | Properties | Memory tab and you can limit the maximum amount of memory the server will use. You can also set a mimimum.
How much memory does the server use before the query? 200MB isn't a lot of memory for the server, especially for a server that is holding 1M row queries. Most likely you will want to limit the memory used, which will slow the query potentially.
The memory can go down if the other apps request it from the OS. SQL is slow to return it, so it isn't immediate.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 1, 2004 at 8:01 am
You may hate this answer, but it will work.
If you are not in a production environment, or can get your data to server that is not crucial, you an stop and restart the SQLserver service.
Yuck. I know.
More memory, and less rows are your answer.
There is a DBCC freeproccache statement, but that is only procedural cache. Sorry.
Michael
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply