How much RAM is SQL Server actually using?

Question

I am debugging one of my apps and noticed that the RAM on my SQL Server 2005 x64 box (running on a Windows 2003 R2 x64 ) is pegged and even going into the paging file.

I understand that SQL Server 2005 x64 just grabs everything it can, so this might be normal (???). How can I check how much memory it is actually using?

Answer 1

1. Start -> Run -> perfmon
2. Look at the zillions of counters that SQL Server installs

Answer 2

You should explore SQL Server\Memory Manager performance counters.

Answer 3

Related to your question, you may want to consider limiting the amount of RAM SQL Server has access to if you are using it in a shared environment, i.e., on a server that hosts more than just SQL Server:

1. Start > All Programs > Microsoft SQL Server 2005: SQL Server Management Studio.
2. Connect using whatever account has admin rights.
3. Right click on the database > Properties.
4. Select "Memory" from the left pane and then change the "Server memory options" to whatever you feel should be allocated to SQL Server.

This will help alleviate SQL Server from consuming all the server's RAM.

Answer 4

You need to run Perfmon (start->control panel->administrative tools->performance). The counters you want, I believe, are in SQL Server:Memory Manager. Target Server Memory is how much it's allowed to take if needed, Total Server Memory is how much it's currently using. If you click on Explain it'll pop open a box with the details.

By default it's uncapped, and should use all but a couple gb of memory (it leaves that for the OS). However, there are various bugs and memory leaks that can cause it to go into paging.

Answer 5

Go to mangement studio and typle sp_help db then it will give all details of available memory and usage memory