x

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

avatar image

Vincent Magnotta
11 1 1 3

(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

avatar image

JoeJF
56 2 2

(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

avatar image

Jorge Segarra
419 2 4

(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

avatar image

sp_lock
10.5k 27 37 37

(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

avatar image

Jack Corbett
1.1k 3 4 7

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x2017
x291

asked: Oct 19, 2009 at 02:00 PM

Seen: 6247 times

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

Copyright 2016 Redgate Software. Privacy Policy