April 28, 2009 at 1:57 pm
32BIT
Server Type : PowerEdge 2950
CPU: 2 x 3.0 GHz Quad Core
Memory: 32GB
Drives:
Local
2 x 73GB RAID1 – C: 20GB OS
4 x 300GB RAID 5 – J: 336GB Backups
Array MD1000
2 x 300GB RAID1 – E: 278GB SQL
1 x 300GB RAID0 – F: 278GB TempDB
6 x 300GB RAID10 – G: 836GB DB
4 x 300GB RAID10 – I: 557GB Logs
AWE is enabled
Lock pages in memory is set for system account
Memory on the Server will not go over 2.9 G and the system comes to a halt until it can process through the workload it has. The server was rebuilt 7 days ago and has not gone over 2.9 Gig. Before the server was rebuilt SQL would use up to 24G.
There are many posts on this but there does not seem to be answer regarding how to fix the issue if all the steps are followed.
1.) Do I need the /PAE switch?
2.) What else do I need to do to troubleshoot?
DBCC memorystatus
VM Reserved1660656
VM Committed1659656
AWE Allocated0
Reserved Memory1024
Reserved Memory In Use0
VM Reserved1656496
VM Committed1655648
AWE Allocated 0
MultiPage Allocator 31504
SinglePage Allocator 644112
VM Reserved1615160
VM Committed1615160
AWE Allocated0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 392
SELECT
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
FROM sys.dm_os_memory_clerks
The result of this is ZERO
April 28, 2009 at 2:41 pm
32GB of memory? Have you configured AWE to use about 29GB ( leaving some for the operating system)?
First, Add the “Lock Pages in Memory” option to the account that has privileges to run SQLServer.exe.
Then:
Exec SP_CONFIGURE 'show advanced options', 1
RECONFIGURE
go
exec SP_CONFIGURE 'awe enabled', 1
RECONFIGURE
Go
-- ConfigureMINIMUM to 2 GB
exec SP_CONFIGURE 'min server memory', 2048
GO
RECONFIGURE
go
-- Configure MAXIMUM to 29 GB select 29 * 1024
exec SP_CONFIGURE 'max server memory', 29696
go
RECONFIGURE
Go
By the way, I've seen sql server take weeks to use up this memory, it depends on load. If there is a heavy load it should start using it fairly quickly.
April 28, 2009 at 2:47 pm
Yes, Thanks, I did run the script as you have laid out.
I did verify that Lock Pages in Memory has been granted to the service account running SQL SERVER
We have the benefit of having another server just like the one we are having problems with. when running a work load script on each server the "working" server memory increases to 15G and the "incorrectly working" server never gets higher than 2.9G and takes 33x more time to complete.
EXECUTE sp_configure 'awe enabled'
GO
Returns
awe enabled0110
SELECT
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
FROM sys.dm_os_memory_clerks
returns
0
April 28, 2009 at 3:19 pm
Has sql server been restarted since the last memory configuration change?
April 28, 2009 at 3:56 pm
1.) Do I need the /PAE switch?
Yes, for a 32-bit system you will need to add the /PAE switch to the boot.ini file to utilize more than 4GB of memory.
April 28, 2009 at 5:34 pm
Server Admin says it did have the /PAE switch already
He says he rebooted after setting the AWE, Locked pages, and max memory 7 days ago. I don't see the reboot in the server log so I am going to go ahead and reboot and see if that makes a difference. I will post results after reboot.
April 28, 2009 at 6:37 pm
The reboot did it. Obviously, the reboot was not done in the correct order this seems to be a hard fast rule. We missed step three and that seems to have made all the difference.
1. Enabled PAE (boot.ini)
2. Provide Lock pages in memory privilege via secpol.msc
3. Reboot
4. Configure AWE using sp_configure
5. Restart SQL Services
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply