August 4, 2009 at 11:21 am
I changed the configuration of my SQL Server 2005 server, increasing the maximum memory allocations to 25GB.
Despite of having 32GB of memory, Perfmon counter shows 1,620,224 KB is being used. Why SQL server does not use all the memory?
AWE enabled - with the /PAE switch in the boot.ini . Granted "Lock Pages in Memory" to the Admin account that runs the SQL Server process
Restarted server after all of this.
Windoows 2003 Sp2 Ent Edition
SQL 2005 Sp2 32 bit
32GB RAM
CPU utilization is 2-4%
Only one instance installed on the server
Perfmon counter shows
Target Server memory is 1,620,224
and Total Server memory is also 1,620,224
Thans
August 4, 2009 at 12:20 pm
If you are on SP3 there was a hot fix for it only using 1/2 the memory. Also, SQL is only going to use the memory it needs. Just because it is at 25 gig it won't use it until needed. Depending on usage this memory should keep going up
August 4, 2009 at 12:55 pm
How can I determine and make sure that 25GB is available for the SQL server?
I was expecting that Target Server memory would be 25GB (1024*1024*25=26214400)
not 1.55 GB (1,620,224KB).
How did you determine that the server is using only half of the memory?
The page life expectancy goes below 300 once a while, I just want to make sure all the memory is utilized.
Thanks for your help
August 4, 2009 at 1:01 pm
What does buffer and proc cache look like?
August 4, 2009 at 1:31 pm
run this
SELECT 'Memory through AWE'
SELECT
SUM(awe_allocated_kb) / 1024 AS 'Memory through AWE'
FROM
sys.dm_os_memory_clerks
August 4, 2009 at 1:35 pm
This is what I get
Memory through AWE
--------------------
0
August 4, 2009 at 1:46 pm
Buffer cache is 99.8%
Procedure cache is 89%
August 4, 2009 at 1:52 pm
Do you have AWE enabled in SQL Server?
What does this show when you run it from a query window?
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'awe enabled'
August 4, 2009 at 1:57 pm
Here is correct it has to enabled on the sql server side or the AWE does nothing for sql
August 4, 2009 at 2:01 pm
Yes I have done this before manually from SSMS and then from query window.
I just did it again now.
AWE is checked in SSMS
Here is the result after running your query
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
awe enabled 0 1 1 0
August 4, 2009 at 2:05 pm
Maybe John can chime in, have you rebooted the server (inclduing SQl Server0
August 4, 2009 at 2:06 pm
Here is a screen shot attached
August 4, 2009 at 2:12 pm
uciltas (8/4/2009)
Yes I have done this before manually from SSMS and then from query window.I just did it again now.
AWE is checked in SSMS
Here is the result after running your query
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
awe enabled 0 1 1 0
The run_value is zero, so AWE is not active.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
awe enabled 0 1 1 0
August 4, 2009 at 2:16 pm
Your run value should be 1 not zero, try running the reconfigure
August 4, 2009 at 2:22 pm
How can I change the run value?
It is checked in SSMS. I have boother the machine before.
Is it because of the rights issue?
I Granted "Lock Pages in Memory" to the Admin account that runs the SQL Server process.
Or should I re boot once more?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply