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?

more ▼

asked Oct 19, 2009 at 02:00 PM in Default

Vincent Magnotta gravatar image

Vincent Magnotta
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Oct 19, 2009 at 04:27 PM

JoeJF gravatar image

56 1

I could only post one link in my answer. Here is the link for the Standard edition. http://support.microsoft.com/kb/970070/
Oct 19, 2009 at 04:28 PM JoeJF
The server is enterprise edition, the user already has local rights to Lock Pages in Memory on that server. Let's say it is getting all the memory it needs, the performance questions still remain.
Oct 20, 2009 at 12:44 AM Vincent Magnotta
(comments are locked)
10|1200 characters needed characters left

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]

more ▼

answered Oct 19, 2009 at 03:22 PM

Jorge Segarra gravatar image

Jorge Segarra
419 2

We run this in compatibility mode but that's true on all the servers. Max Server Mem set to 2147483647 (the default in 2k5) and minumum set to 40,00o (not that it seems to matter). We only tried setting it since it seems that in pure dynamic it wasn't working either.
Oct 19, 2009 at 03:39 PM Vincent Magnotta
Hmm...have you guys done apples-to-apples comparison of settings betwen the two servers?
Oct 19, 2009 at 04:23 PM Jorge Segarra
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 22, 2009 at 01:54 AM

sp_lock gravatar image

9.3k 26 28 31

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 12, 2009 at 10:28 AM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 19, 2009 at 02:00 PM

Seen: 5799 times

Last Updated: Oct 19, 2009 at 05:07 PM