views:

1583

answers:

2

I making stress test on my database which is hosted on sqlserver 2008 64bit running on 64bit machine 10 GB of RAM. I have 400 threads each thread query the database for every second but the query time does not take time as the sql profiler says that, but after 18 hours sql takes 7.2 GB RAM and 7.2 on virtual memroy.

Does is this normal behavior? and how can I adjust sql to clean up not in use memory?

+5  A: 

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.

Greg Beech
+1  A: 

Going off Greg's answer, when you configure the memory try to leave at least 10% of the overall memory free for the OS. If SQL spins out of control on a long query you want that cushion to be able to remote in and still administrate the box.

SQLChicken