Quick Tip #2 – Are You Using All Your Memory?

I have a couple of quick questions for you.  Are you running SQL Server 32 bit?  Do you have more than 4 GB of memory in the server?  Do you know if you’re using as much memory as you can?

I often see servers that are experiencing performance problems due to a lack of memory.  A recent case was with a 2005 Standard server with 16 GB of memory, but without AWE enabled, so SQL Server was limited to 1700 MB in its buffer cache.  It was running (amongst others) a single query constantly which scanned a 2.5 GB table, and so the disks were flat out trying to service these requests.  Turning on AWE enabled most of the database to fit in memory, and the large scanning query dropped from 400 seconds to about 7 seconds.  An index was then added, and performance was even better.

To find out how much physical memory is installed, and how much physical memory is still available, have a look in the Performance tab of Task Manager.  From within SQL Server, you can run the query:

SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN (’Target Server Memory (KB)’,’Total Server Memory (KB)’)

This will show you the amount of memory SQL Server is aiming to use (Target Server Memory), and how much it is currently using (Total Server Memory).  If AWE is not enabled, these values won’t go above 1700 MB.

For information on how to configure AWE, see my previous post for 2005, or for SQL Server 2000.