SQL Server 2005 on Windows 2008 – Memory Configuration Guide

memoryperformancesql-server-2005

Working on a project we need to first stabilize and then upgrade.

We have a 32bit Enterprise edition SQL Server 2005 installed on a 64 bit Windows 2008 standard Server.

The server has 64GB RAM installed, however in taskmgr there are only 32GB available. I believe this is a Standard Windows Server limitation, just want to confirm.

enter image description here

On other side, we have checked the SQL Server memory counters on perfmon, and the value we get for target and total memory is about 3.500.000, so I guess SQL server is using about 3,5GB??

enter image description here

I want to ask about the configurations we need to make in order to make SQL Server use as much memory as possible, hopefully 28 GB, leaving 4GB for OS, as we have no other services in this machine.

This is what I'm planning so far, but as it's a production environment, I would ideally try to nail it on one attempt, coz don't want to stop the services many times:

  • The service account is Local System, but I cant see the "Using locked pages for buffer pool" in the SQL Server logs. Do I need to swtich to another user account to take advantage of the "lock pages in memory" policy? What I can see in the SQL Server log is this: "Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required."

  • The Max server memory property is set to 10240; I think after enabled lock pages in memory, I will need to increse this to 28000

  • As this is 64 bit OS, no need to enable AWE. However, in the SQL server log, I can see this message: "Set AWE Enabled to 1 in the configuration parameters to allow use of more memory."

Anything else I need to take into consideration in order to enable more memory for SQL Server??

Best Answer

The server has 64GB RAM installed, however in taskmgr there are only 32GB available. I believe this is a Standard Windows Server limitation, just want to confirm.

You are correct max memory limitation for Windows Server 2008 Standard is 32 GB. This is mentioned in Memory Limits of Windows Server Machines.

On other side, we have checked the SQL Server memory counters on perfmon, and the value we get for target and total memory is about 3.500.000,

When 32 bit SQL Server is installed on 64 bit machine it is called WOW System. For this the Virtual Address Space limit is 4GB. And in normal and default configuration SQL Server cannot see/utilize more memory than the VAS limit so you are seeing 3.5 GB in Perfmon counters.

The service account is Local System, but I cant see the "Using locked pages for buffer pool" in the SQL Server logs

Since you are on 32 bit system you should not look for this message instead look for "Address Windowing Extensions is enabled", if you see this then it is enabled. If you have 64 bit system then the message "Using Locked pages for buffer pool" makes sense. Actually this is how AWE works in 32 and 64 bit respectively.

As this is 64 bit OS, no need to enable AWE.

No, this is true for 64 bit SQL Server not if you have 32 bit SQL Server.

Now for SQL Server to see more memory, you have to enabled AWE so that SQL Server data and index pages can see more memory than 4 GB. Since your windows can see more than 4 GB( In your case 32 GB) AWE would help SQl Server data and index pages accessing larger part of memory. Please note I mentioned data and index pages, apart from this no other memory consumer can see those extra memory.

PS: At the time of writing the answer most of the blogs.msdn links are not opening as they are being migrated to new platform, I will add the links in answer as soon as they start working so that you have some reference to read

Related Question