SQL Server Memory Management

acherman

I have been working on getting our NPM and NTA to run a little quicker, and one thing I have seen note of is getting more usage out of available memory.  I have seen Network Guru here mention having entire BD running in memory to increase I/O performance.  My SQL machine is running Server 2003 Ent, SQL Server 2005 Standard, with 32 GB of RAM.  MY NPM DB is 13.2 GB, my NTA DB is 4.6 GB.  Looking in Task Manager I see sqlservr.exe itself is using 212 MB right now (running for a couple weeks).  After a reboot it started around 89 MB and, of course, is slowly climbing.

Does anyone have any ideas how I can permit SQL to use more of the available memory?

seigniory

Firstly, you don't mention it, but you need to be sure you're running a 64-bit OS.  :-)

Secondly, task manager is not the place to see the actual size of the data taken up by SQL server.  A SQL server actively using 10GB of RAM won't show 10GB in Task Manager - you'll need to look at SQL's dedicated performance counters.

Lastly, trying to force your entire DB into RAM won't work just by allocating more RAM to the instance; By default, SQL Server won't load anything from a table into RAM at startup unless a DB user makes a request for it - this works much like an RAID controller's read cache.  There may be ways to pre-cache table data in SQL server, but I'm not an MSSQL DBA, so I can't give you specifics.

Don't base your performance reasoning solely on the amount of RAM that SQL's using.  If you leave the SQL settings at default, and you have a ton of RAM, MSSQL will do a very good job of taking care if itself.  It's likely that your performance issues are located elsewhere.