how to set memory for SQL Server 2005

[holdahl]

I have an SQL cluster with 2 nodes.
The SQL instance is installed on node1.
Both nodes have 8gb of RAM.
I have tried to set both a min and max memory on node1 for SQL Server to use.

on both nodes:
- enables PAE
- configured the Lock Pages in Memory

question:
do I have to restart the server (the physical machine) for it to notice the change in boot.ini?

on node 1:

sp_configure 'show advanced options',1
reconfigure
go

sp_configure 'awe enabled', 1
reconfigure
go

sp_configure 'min server memory', 2048
reconfigure
go

sp_configure 'max server memory', 6144
reconfigure
go

now when I run:

SELECT * FROM sys.dm_os_performance_counters
WHERE object_name like 'SQLServer:Mem%'
GO

it shows:
Total Server Memory (KB) 1618880

and when I run:

sp_configure

it shows:
maximum config_val run_val
max server memory (MB) 2147483647 6144 6144

min server memory (MB) 2147483647 2048 2048

any suggestions what might cause this?
is it because I have not re-booted the server (physical machine)?

sH

 

[Catadmin]

You do, I believe, have to reboot the server to use the updated boot.ini because the server as it is currently has booted off the old .ini.

Do the reboot and let us know if your values don't change.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

Changes to boot.ini don't take effect until the host machine is rebooted. As its a cluster don't forget to make the change to both nodes.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[holdahl]

I did a reboot of both nodes after doing the changes to the boot.ini file.

Looks better now.

Could someone give a short explanation of these settings/values and what they should be (for SQL Server 2005):

from the Performance monitor:

- Process
* Working Set
- SQLServer:Buffer Manager
* Buffer cache hit ratio
* Page life expectancy
- SQLServer:Latches
* Latch Waits/sec
- SQLServer:Memory Manager
* Target Server Memory (KB)
* Total Server Memory (KB) (is this what the server is currently using?)

sH

 

[holdahl]

currently some of my values are:

- Process
* Working set: 343953408

-SQLServer:Buffer Manager
* Buffer cache hit ratio: 99,850
* Page life expectancy: 17394

sH

 

[Catadmin]

Okay, I found a couple of things.

First -

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=196&rl=1

SQL Server:Memory Manager
Target Server Memory
How much RAM SQL wants

SQL Server:Memory Manager
Total Server Memory
How much RAM SQL is using

SQLServer:Buffer Manager
Buffer Cache Hit Ratio
Shows how much data is found in the Buffer
Should be 99% or greater

Process
% Processor Time
Pick Specific Object
Will explain how much that object is taking on the processor

and from Microsoft:

Latched. The latched buffers record the number of buffers where a connection is reading or is modifying a row from the page. A latch is used to ensure physical consistency of the data in the page while it is read or modified, while a lock is used to ensure logical and transactional consistency.

Then I found the following SQL Server Central article:

http://www.sqlservercentral.com/columnists/abressi/2634.asp

Below are the MS values along with some comments. When comparing your values to these you should always ask yourself if the value that you have collected was sustained over a period of time or if was just a spike - sustained values are obviously much more appropriate for comparison.

Memory: Pages/sec: If counter value is consistently > 5 you may likely have a memory issue.
Available Bytes: Values < 10 MB should raise a serious red flag for you.
Network Interface: Bytes Total/sec: Microsoft simply advises that if you notice this value dropping it may indicate network problems. By rule of thumb you can use a value of half of the available network interface bandwidth as being acceptable. So for a 100 MBS network adaptor, the value of the Bytes Total/sec performance counter can be 50 MBS or greater.
Physical Disk: Avg Disk Queue Length: You will need to calculate this value based on the number of physical drives. It is simply the monitor value / # of disks. A value greater than 2 might indicate an I/O bottleneck. The number of waiting I/O requests should be sustained at no more than 1.5 to 2 times the number of spindles making up the physical disk.
Physical Disk: % Disk time: The recommended numbers for this seem to vary, if the value is greater than 50% you should be concerned and investigate more. If the value is sustained above 80% it is a serious problem and you may have a memory leak or I/O issue at hand.
Processor: % Processor Time: Values in excess of 80% processor time per CPU are generally deemed to be a bottleneck.
System: Processor Queue Length: A sustained queue length > 2 (per processor), generally indicates a processor bottleneck. For instance, if you have 2 processors then a value of 4 or less is usually acceptable.
SQL Server Buffer: Buffer Cache Hit Ratio: A rate of 90 percent or higher is OK. The closer to 100% the better. Less than 85% indicates a problem.
SQL Server General: User Connections: This one varies of course. It should be tracked though so that you can determine what is "normal" for your environment. With this you can spot trends that may signal growing demand, or you may use it to explain spikes in other counters.
Additional Threshold and Monitoring Resources

There are many good articles that deal with monitoring activity and thresholds. Listed below are some worthwhile resources:

"Performance Monitoring - Basic Counters" - Steve Jones

http://www.sqlservercentral.com/columnists/sjones/performancemonitoringbasiccounters.asp

Monitoring Disk Activity - Microsoft

http://www.microsoft.com/resources/...proddocs/en-us/sag_mpmonperf_19.mspx?mfr=true

Monitoring Memory - Microsoft

http://www.microsoft.com/technet/pr...serv/reskit/prork/prec_evl_bzcl.mspx?mfr=true

Troubleshooting Performance in SQL Server 2005 - Microsoft

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

Performance Tuning Checklist 4.5 (NT Based Systems) - Microsoft

http://www.microsoft.com/technet/prodtechnol/bosi/maintain/optimize/bostune.mspx

Hope this helps.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[holdahl]

The memory management got better after using the AWE option and rebooting the nodes.
However, the Target Server Memory and the Total Server Memory now are the same and have been for a couple of days.

Does this mean that sqlserver has too little memory and needs more?

The system seems like running very fine at this moment.


sH

 

[Catadmin]

I would say if they are the same, it should be fine, but if you want to add more memory, feel free.

Don't forget, you've set a min & max on your memory, so SQL cannot be dynamic about its memory allocation now. Hence the reason (I believe) your totals are the same.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[holdahl]

Ok, I see.

I will let it be like this for a couple of weeks to see how it works. If I don't get any more problems with workers finding the system slow, I will probably don't bother with getting more RAM. The activity on the server is not that high anyway.

sH