Dec 172008

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.

  One Response to “Quick Tip #2 – Are You Using All Your Memory?”

  1. Hi Jim

    My SQL Server is quite slow. I tried your script and I got Target Server Memory 6291456 (6.144 GB which is my max. server memory) and as Total Server Memory exactly the same value! Does this mean I run out of memory? I’m using a 64-bit Win 2003 Standard OS.

 Leave a Reply



You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>