how to release memory from sql

Question

I'm writing a program to search tables contain a certain tables in a server. It increase the memory usage of sqlserver dramatically. It seems that it's because the queries of the searching that cause it. How can I release the memory cuz it's just one time deal and i need that memory to do other things?

Answer 1

Are you talking about RAM memory usage? If yes, then that's the expected behavior. Of course, you could optimize your queries, but the idea is that SQL Server should bring data into memory to be able to do its work and if you configure it to use up to X GB then it will use it. Other queries which run after yours will ask for different data and SQL Server will move the other data into memory and move out your query data if they are not being referred - that will happen automatically. Again, that's quite normal - that's the way things work in SQL Server.

Please explain - what's your concern? One potential issue I see is this: you refer to tables which aren't being used by other 'normal' queries so when you run your queries, SQL Server 'unloads' other 'good' data to make some room for your data and after you are done, it has to read it again from disk during the first usage which will impact the performance.

Thanks,

---

Varsham Papikian, New England SQL Server User Group Executive Board, USA: www.nesql.org; http://www.linkedin.com/in/varshampapikian

Answer 2

Run the maintainess plan and shrink log.

if you are asking about physical memory restart your sql server.

you can also shrink you sql server log using DBCC SHRINKDATABASE('dbname',size)

---

Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

Answer 3

Are you talking about releasing the memory used by the SQL Server process back to the OS? If so, you don't really have to do anything. SQL Server adjusts its memory utilization dynamically by default. If there isn't a load running on SQL Server that requires the memory and other apps on the same machine requires more memory, SQL Server will release the memory it's holding. It's not instantaneous but usually reasonably quick.


joe.

---

No great genius has ever existed without some touch of madness. - Aristotle

Answer 4

Chen,

As outlined by Varsham, its the way SQL Server works. It grabs as much memory assigned to it (and available) unless it is limited by using the max. server memory setting like below for SQL Server.

sp_configure 'max server memory', 12288
RECONFIGURE
GO

As Mr.Varsham suggested, add more details about your concerns.

---

| Sankar Reddy | http://sankarreddy.spaces.live.com/ |