SQL Server Performance Forum – Threads Archive

Proper Memory Config on SQL Cluster

We have a Active/Passive SQL Cluster with two SQL instances and noticed the SQL Logs upon starting is holding on the ‘awe is enabled’ entry for nearly 4 minutes on our larger instance, and about 45 seconds on our smaller instance. Here are the specs,configs, both Servers are identical: Please let me know what you think. OS W2K3 SP1
SQL 2K SP3A
Processors: 4
Memory: 12gb
boot.ini /PAE /3GB
awe enabled yes – Both instances
Memory config Ins1 Dynamic – Both instnaces
Large SQL Instance max: 6144mb
Small SQL Instance max: 3072mb
Task Mgr Memory alloc: ~10gb physical
Task Mgr SQL Mem. ~130mb and ~110mb for each instance We use these SQL Servers (mainly) for OLTP. We have approximately 800 in-house users, about 30 applications, (various/multiple apps for various/multiple users), website application with approximately 100,000 members registered. I’ve never seen over about 150mb being used by SQL and some of our dbs have over 1000 tables and 10000 stored procedures and > 100gb databases. Since the ‘awe enabled’ line in the 6gb instance of the SQL Server Log startup is hanging there for nearly 4 minutes, and I never see either instance use over 150mb, I am proposing the following: 1.) Remove /3gb out of the boot.ini
2.) Allocate only 3072mb (max server emory) to the larger instance.
3.) Should I change SQL memory to ‘Fixed’ with 3072mb on min and max (same) ?
4.) Should I check the ‘Reserver Physical Memory’ box?
5.) Should I set working size Does this seem like logical or correct settings to try? Thanks, Mark Skinner
MarkSkinner

Hello, as the instances start up they have to allocate their memory and ask for the whole lot to be zero-ed out. This can take quite a while depending its memory consumption Task Manager can not be used to gauge memory usage once you enable awe. You need to use the perfmon counters for sqlserver with awe, sql will always grab all the memory it can up to the max configured (or until the OS is down to its last 128MB or so) This is quite a good article, but there are loads more out there too http://www.sql-server-performance.com/awe_memory.asp Cheers
Twan

http://www.sql-server-performance.com/sql_server_performance_audit5.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;319942 Fyi and may do little help. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>