May 4, 2009 at 12:06 pm
How do I know if I need to allocate more memory for SQL Server? Thank you
May 4, 2009 at 12:50 pm
There are a few performance metrics to watch for that may signify signs of memory pressure and the need for more memory.
1. Buffer Cache Hit Ratio - by far the most common metric. This should stay pretty close to 99.xx %. Keep in mind that this is cumulative from the last restart of the SQL service.
2. Page Life Expectancy - this indicates how long the pages are staying in cache. Values consistently below 300 indicate possible need for more memory.
3. SQL Server Total and Target Memory - Target represents how much memory SQL has available for it while Total tells you how much SQL Server is currently using. Don't think that because these 2 values are the same that you need more memory. This is just a good indicator that SQL is actually using what is available for it. If the Total is consistently below the Target, you may be OK w/ memory.
4. Lazy Writes / sec - this tells you how often SQL must write dirty pages from memory to disk to free up memory. Consistent values of > 20 indicate possible need for more memory.
Monitoring these metrics should give you a pretty good idea of how you memory is being used and if your system could benefit from more memory.
May 4, 2009 at 1:30 pm
Hi, I checked #1 is 92%
#2 is 175, how do I add memory?
May 4, 2009 at 1:38 pm
How much memory is in your server?
What is your max server memory set at (sp_configure)?
What version of SQL Server are you on?
What Server OS version are you on?
What else are you running on your server?
May 4, 2009 at 1:50 pm
How much memory is in your server?
8163 (MB)
What is your max server memory set at (sp_configure)?6115 in MB
Name min max conf run value
allow updates 0 1 00
Cross DB Ownership Chaining 0 1 00
default language 0 9999 00
max text repl size (B) 0 21474836476553665536
nested triggers 0 1 1 1
remote access 0 1 1 1
remote login timeout (s) 0 21474836472020
remote proc trans0 1 0 0
remote query timeout (s) 0 2147483647600600
show advanced options 0 1 00
user options 0 32767 00
What version of SQL Server are you on?
2000
What Server OS version are you on? Operating system:
Microsoft Windows NT 5.2 (3790)
What else are you running on your server?
No, just monitoring tools
Thank you
May 4, 2009 at 1:59 pm
You'll need to set your advanced options for sp_configure to ON for your max server memory values to display.
Also, what version of SQL Server 2000 are you running?
May 4, 2009 at 2:06 pm
You'll need to set your advanced options for sp_configure to ON for your max server memory values to display. If I sat on is this going damage anything, any performance problems?
Also, what version of SQL Server 2000 are you running?
Microsoft SQL Server Standard Edition
May 4, 2009 at 2:09 pm
Krasavita (5/4/2009)
You'll need to set your advanced options for sp_configure to ON for your max server memory values to display. If I sat on is this going damage anything, any performance problems?
No, it will just let you view the advanced options, nothing more.
May 4, 2009 at 2:20 pm
I don't see that option in advance settings to change sp_configure, do I need to add more space. Thank you
May 4, 2009 at 2:23 pm
May 4, 2009 at 2:29 pm
SQLServer:Memory Manager Target Server Memory(KB) 167464865536
SQLServer:Memory Manager Total Server Memory (KB) 167464865536
May 4, 2009 at 2:32 pm
Ok, I re ran sP_reconfigure
affinity mask-2147483648214748364700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364761156115
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5123276740964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
May 4, 2009 at 2:38 pm
Also, should I change it back to sp_configure to 0. or I can leave it?
May 4, 2009 at 3:14 pm
Krasavita (5/4/2009)
Also, should I change it back to sp_configure to 0. or I can leave it?
It's up to you...it doesn't hurt anything to leave it, but others that run sp_configure may see the advanced options.....personal preference.
So it looks like you've got 8 GB in your server and you've attempted to set your SQL instance to use 6 GB of the 8. You've got awe enabled, but your Target/Total values show just shy of 2 GB. Here's the scoop. You cannot run awe on SQL Server 2000 Standard Edition so you're stuck with 4 GB of memory. By default, SQL Server only has access to 2 of the 4 GB of memory. You can set the /3GB switch in your boot.ini file and get access to 3 GB of the 4 (but I think this only works in Advanced Server OS).
Here's a great reference for SQL Server 2000 memory configuration. http://www.sql-server-performance.com/articles/per/awe_memory_sql2000_p1.aspx
Bottom line....try configuring for the 3GB and see if it helps w/ memory, otherwise, you'll need to upgrade to Enterprise Edition or SQL Server 2005.
May 4, 2009 at 8:06 pm
Thank you so much for your help, how can I get to boot.ini file ?and get access to 3 GB How can I find out if it works only in Advanced Server OS? Thank you again
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply