SQL Server 2008 takes up a lot of memory?

Question

I am conducting stress tests on my database, which is hosted on SQL Server 2008 64-bit running on a 64-bit machine with 10 GB of RAM.

I have 400 threads. Each thread queries the database every second, but the query time does not take time, as the SQL profiler says that, but after 18 hours SQL Server uses up 7.2 GB of RAM and 7.2 GB of virtual memory.

Is this normal behavior? How can I adjust SQL Server to clean up unused memory?

Answer 1

SQL Server is designed to use as much memory as it can get its hands on, to improve performance by caching loads of stuff in memory. The recommendation is to use dedicated machines for SQL Server, which makes this a perfectly valid approach, as it isn't expecting anybody else to need the memory. So you shouldn't worry about this; it's perfectly normal.

That said, if you're on a development machine rather than a live environment, you may wish to limit the amount of memory to stop your box being taken over. In this case the easiest ways is to open SQL Server Management Studio, right-click on the server and select "Properties", then on the "Memory" tab you can set the maximum server memory.