question

Vincent Magnotta avatar image
Vincent Magnotta asked

SQL 2005 NOT using the available RAM

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.

Any guesses?

sql-server-2005performance
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JoeJF avatar image
JoeJF answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jorge Segarra avatar image
Jorge Segarra answered

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]

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

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.

Select *
from sys.dm_os_process_memory

There is a post that descibes in full the above MS Blog

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jack Corbett avatar image
Jack Corbett answered

Have you tried setting the min memory and max memory to the same setting to try to force SQL Server to use more memory?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.