We are observing the following phenomenon:
One of several SQL server 2005 (64-bit) instances is NOT using almost any memory. The database is not small (600GB) compared to the memory (64GB). This server underperforms compared to any other instance with the same DB. What's really surprising is that NO MATTER WHAT memory configuration we use, the server doesn't seem to ever use more than just under 300 MB of RAM. This seems like an obvious cause of slowness (let's face it, the SAN is good but not as good as cache, right).
We run the same process on dump-and-load copy of the DB on another server and it is happily chugging away (currently using about 24GB of memory) and the overall process runs 2-3 hours.
On the misbehaving server we're seeing 5-6 hours (about double!) and almost no RAM usage.
It sounds like you need to grant the SQL Server service account the "Lock pages in memory" user right. http://support.microsoft.com/kb/918483
Lock pages required SQL Server Enterprise edition until a recent update. SQL Server 2005 SP3 Cumulative Update 4 introduce support for SQL Server Standard editions to use the Lock pages in memory user right.
answered Oct 19 '09 at 04:27 PM
Did you guys recently upgrade this database from 2000 by chance? What do your settings show for Maximum server memory (in MB) [can be found by right clicking instance in SSMS, properties, Memory]
answered Oct 19 '09 at 03:22 PM
This is prob why you are only seeing that SQL Server is using 300Mb. Enabling Locked pages isnt part of the working set of memory (normally seen in Task Man).
There is a DMV you can use to query the lock pages memory usage.
There is a post that descibes in full the above MS Blog
answered Oct 22 '09 at 01:54 AM
Have you tried setting the min memory and max memory to the same setting to try to force SQL Server to use more memory?
answered Nov 12 '09 at 10:28 AM