September 8, 2010 at 9:12 am
I perfmon to look at SQL Server target and total memory, and found that it is only using 1GB of RAM. The server has 16 GB of ram and the boot.in is configured as follows:
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /noexecute=OptIn
AWE is enabled in SQL Server.
I thought that 2003 did not need the /PAE switch to use all the memory, but it looks like it is only using 8GB total (1GB for SQL SErver 4GB for some other application currently running on the server, and 1GB for other various stuff, and 2GB is free)
SQL Server is configured with max memory at 8GB
Why is SQL Server using so little memory and why isnt the server using the other 8GB at all?
September 8, 2010 at 9:15 am
Is the OS x86 or x64? Is SQL x86 or x64?
How are you measuring that 1GB memory usage?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 9:24 am
permon SQL Server Memory Manager: total memory...
September 8, 2010 at 9:27 am
One question answered, two to go.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 9:33 am
ah sorry- misread that... all x86 (OS and SQL Server)
September 8, 2010 at 9:34 am
The x86 v x64 matters a lot here. Also, are you sure this instance of SQL is configured for 8GB? What's shown (Exactly) if you run sp_configure for this instance.
September 8, 2010 at 9:36 am
Also, what version of SQL?
SELECT @@version
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 9:40 am
Thanks for your help gail. here's results from sp_configure:
nameminimummaximumconfig_valuerun_value
Ad Hoc Distributed Queries0100
affinity I/O mask-2147483648214748364700
affinity mask-2147483648214748364700
Agent XPs0111
allow updates0100
awe enabled0111
blocked process threshold08640055
c2 audit mode0100
clr enabled0100
common criteria compliance enabled0100
cost threshold for parallelism03276755
cross db ownership chaining0100
cursor threshold-12147483647-1-1
Database Mail XPs0100
default full-text language0214748364710331033
default language0999900
default trace enabled0111
disallow results from triggers0100
fill factor (%)010000
ft crawl bandwidth (max)032767100100
ft crawl bandwidth (min)03276700
ft notify bandwidth (max)032767100100
ft notify bandwidth (min)03276700
index create memory (KB)704214748364700
in-doubt xact resolution0200
lightweight pooling0100
locks5000214748364700
max degree of parallelism06400
max full-text crawl range025644
max server memory (MB)16214748364779007900
max text repl size (B)021474836476553665536
max worker threads1283276700
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5123276740964096
Ole Automation Procedures0100
open objects0214748364700
PH timeout (s)136006060
precompute rank0100
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote admin connections0100
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
Replication XPs0100
scan for startup procs0100
server trigger recursion0111
set working set size0100
show advanced options0111
SMO and DMO XPs0111
SQL Mail XPs0100
transform noise words0100
two digit year cutoff1753999920492049
user connections03276700
user options03276700
Web Assistant Procedures0100
xp_cmdshell0111
September 8, 2010 at 9:43 am
Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86) Aug 5 2008 01:01:05 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
September 8, 2010 at 9:49 am
The first thing to note is that the max server memory is not what SQL will use. It's the max that it's allowed to use. If it's not busy, it may not get anywhere close to that.
Are there any signs that SQL is under memory pressure?
If the total server memory is 1 GB, what's the target server memory?
I'm not sure in the /PAE is necessary or not on Server 2003. Will leave that to someone else. I'd imaging it's safe to put in even if it's not needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 9:52 am
target and total are both 1GB. Yeah- I have a request to hosting to put in /PAE, but I read from MS, that it shouldnt be needed.
It never goes beyond 1GB- even with DBCC checkdb running against a huge database.
September 8, 2010 at 10:45 am
I think there is memory pressure. Once counter- Memory\Pages Input/sec- is supposed to remain near zero with occasional spike... on this server it is more often a high positive number occasionally falling to zero.
September 8, 2010 at 11:10 am
On 32bit systems \PAE switch is required to use memory that is >4 GB. i.e if the Box has 16 GB, in order to make the windows server to use the additional 12 GB we need to add \PAE switch.
If not, the Server will be using only 4GB of memory with 2GB of memory assigned to the applications.
So if an app is already using 1GB then only 1GB is left for all oher aplications including SQL Server.
If the Max Server mem for SQL Server is set to 1GB it cannot use more memory even if that's available.
Even If you have Max memory set to 1GB there is no gaurantee that SQL Server will be using 1GB if there are memory pressures on the Box.
Since committed= target mem , there is no need to worry abt this.
Thank You,
Best Regards,
SQLBuddy
September 8, 2010 at 11:20 am
This makes sense and is totally in line with my understanding- but I dont understand why it appears to be using 8GB, not the full 16GB of RAM.
According to MS (http://support.microsoft.com/kb/875352) in some cases "windows will automatically enable PAE mode to support DEP. Users do not have to separately enable PAE by using the /PAE boot switch."
The server itself is obviously using more than 4GB w/o /PAE set.... which I don't really understand... why it's not using the full 16 is the greater question. I'm hoping that adding /PAE will just take care of it... we shall see.
September 8, 2010 at 11:27 am
Enable \PAE switch. Having 16GB on the box doesn't mean that all of that memory should be in use. It all depends on the activity on the box.
Also to make better use of mem on the box for SQL Server, enable \3GB switch and AWE.
Thank You,
Best Regards,
SQLBuddy
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply