July 21, 2009 at 11:55 am
My production server is a 64 bit, 8 processor 2.66GHz 16GB of RAM running 2005 SP2. Is shows that my memory usage is 95 to 98%.
Memory settings are:
Min:0
Max:2147483647
PerfmonCounters:
Page Life Expectancy :Ranges from 10 to 1000(During peak production hours)
Pages/sec:0
Buffer cache hit ratio : 96 to 99 %
Avg Disk Queue Length:Seen it go till 12(most of the time its 0 to 2)
Available Bytes:224862208
Working Set:1.6164e+010
CPU Usage: 20 to 40%
Top Memory ConsumingComponets are:
cachestore_sqlcp 3286592 KB
Why is the memory usage so high or SQL Server is using all that's available.
Thanks
July 21, 2009 at 12:55 pm
Your SQL Server is only set up to use 2 GB so I'm sure it is not using 95-98%. Check the Total/Target SQL Server Memory counters to verify the available versus consumed SQL Server memory. Given your Max Memory setting of 2GB, I'd expect to see your Target memory counter around 1.7 GB and your Total counter the same or less.
July 21, 2009 at 1:14 pm
These are my counters for SQl Server memory: both look the same
Target Server Memory:14377680
Total Server Memory:14377680
Should I be changing the max threshold?
July 21, 2009 at 1:22 pm
reddyk (7/21/2009)
These are my counters for SQl Server memory: both look the sameTarget Server Memory:14377680
Total Server Memory:14377680
Should I be changing the max threshold?
your sql server is not set to use 2gb its set to over 2000 TB! so it will take as much of the 16gb as it wants until the OS asks for it back. with 16gb i would cap it around 12/14gb assuming the primary/only use of the server is for sql.
July 21, 2009 at 1:53 pm
On x64 systems, it is highly recommended that you set a max memory setting. If you don't, then it is likely that SQL Server will take all of the memory at some point and starve the O/S.
The number you see defined for the max memory is in megabytes (MB). To give you an idea of how large that number really is, think about this:
1024 MB = 1GB
10240 MB = 10GB
102400 MB = 100GB
2147483647 MB = ??????????
On a system with 16GB of memory, you would not want to set it higher than 13GB - leaving at least 3GB for the OS. Depending upon your system utilization you may need to go lower - or possibly up to 14GB if your system is not heavily used.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 21, 2009 at 1:59 pm
Thanks all. I will reduce the max to around 13 gig. This server is only used for SQL. Nothing else runs on it.
July 21, 2009 at 2:09 pm
Guys, 2147483647 is not 2 TB. The default value for Max Server Memory(MB) is 2147483647, which is 2 GB. I know, I know the counter should be MB, but by default, it is Bytes. 2147483647 Bytes = 2 GB = the SQL Server default value for Max Server Memory.
July 21, 2009 at 2:36 pm
John Rowan (7/21/2009)
Guys, 2147483647 is not 2 TB. The default value for Max Server Memory(MB) is 2147483647, which is 2 GB. I know, I know the counter should be MB, but by default, it is Bytes. 2147483647 Bytes = 2 GB = the SQL Server default value for Max Server Memory.
Where are you getting that the number there is 2GB? That number is the largest value an integer can be in SQL Server. The max memory definition is in MB - not KB, so using that number it is the largest possible value you can set using an integer.
Not only is that number a lot larger than 2TB - it is extemely larger than 2GB and is meaningless other than to say that SQL Server can have all the memory that is available on the server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 21, 2009 at 2:51 pm
Jeffrey Williams (7/21/2009)
John Rowan (7/21/2009)
Guys, 2147483647 is not 2 TB. The default value for Max Server Memory(MB) is 2147483647, which is 2 GB. I know, I know the counter should be MB, but by default, it is Bytes. 2147483647 Bytes = 2 GB = the SQL Server default value for Max Server Memory.Where are you getting that the number there is 2GB? That number is the largest value an integer can be in SQL Server. The max memory definition is in MB - not KB, so using that number it is the largest possible value you can set using an integer.
Not only is that number a lot larger than 2TB - it is extemely larger than 2GB and is meaningless other than to say that SQL Server can have all the memory that is available on the server.
I was thinking back to the 2 GB default memory limitation of SQL Server 2000. For some reason, that's what jumped out at me when I saw the max setting as 2147483647 bytes = 2 GB.
You are correct, a max server memory of 2147483647 tells SQL Server to use all available memory. My bad.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply