July 24, 2008 at 12:14 pm
I am seeing my SQL Server 2005-64 bit using all 18 GB of memory allocated to it. Additionally i see 17.2 GB of paging file used on the server as well. Is this a normal situation?
The environment details are as follows:
Server: 4-quad core
Server memory - 24 GB
Allocated memory to SQL server: 18 GB
Paging file used: 17.2 GB
SQL/NT counters used:
Total server memory: 18 GB Used
Target Server memory: 18 GB
Paging file: 17.2 GB
Active user connections on the server: 140
Buffer cache hit ratio: Above 95%
CPU: under 15% used
The fact that - 18 GB is used by SQL is something doesn’t seem right to me. I was wondering, is there a way - I find out which process-connection-sql statement using the most memory on sql side? Is this normal behaviour of SQL Server to use this much memory? Anything un-usual going on?
Appreciated if someone please share some thoughts here.
Thanks,
Cali
July 24, 2008 at 12:39 pm
While I dont have a 64bit server, I do know if you allocated 18GB to SQL, it will take it. How do you know it's actively using it all? What does perfmon show? Do you have enough memory allocated to the OS? Are you experiencing any issues?
-- You can't be late until you show up.
July 24, 2008 at 12:56 pm
How do i know its actively using all?
Well the counter Total Server memory tells - how much memory the instance is using. Also from the windows task manager - I could see SQL using 18 GB and the paging file is like 17.2 GB.
Performacne on server is kind of slow. The over all memory on the server is 24 GB. i have allocated 18 to SQL. so 24-18=6 GB for OS. The remaining memory is like 2.5 GB for OS.
My understanding is - what ever is allocated to SQL - it wont grab it until it's required. Atleast on SQL 2005 - thats what i have seen.
Is there a way - we see on SQL side as to what process are chewing this memory?
July 24, 2008 at 6:24 pm
Appreciated if someone please take a moment to answer this?
July 28, 2008 at 6:34 am
are we talking 1 instance of sql server??
what are min and max memory set to on instance properties?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 28, 2008 at 6:39 am
California (7/24/2008)
How do i know its actively using all?My understanding is - what ever is allocated to SQL - it wont grab it until it's required. Atleast on SQL 2005 - thats what i have seen.
SQL will allocate the memory and not release it to the OS or any other process, even if it doesn't need it. That's why it's critical not to starve the OS and/or balance any other processes on the server with repect to memory.
-- You can't be late until you show up.
July 28, 2008 at 6:46 am
what Perry and Terry (great name for a double act) are saying is with 64 bit SQL you should set max memory of sql server to about 2Gb less than physical memory to stop SQL starving the OS of memory. If OS is starved probably why its paging.
---------------------------------------------------------------------
July 28, 2008 at 7:02 am
george sibbald (7/28/2008)
what Perry and Terry (great name for a double act)
not a s good as Kevin and Perry,, yes mrs patterson 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 28, 2008 at 11:06 am
SQL will take what it needs up to the max you specified (18GB). I have watched when I restart my SQL servers and noticed it takes about a day before it consumes the total amount I specified for Max. I had a server with 16GB RAM and had set the max for SQL to 14GB.
Another note - your page file **generally** should be 1.5 times the size of the memory in the box. With 24GB of memory, your page file should be 36GB. I would recommend splitting this across more than one drive but don't put any parts of this on the drive with your database or log files.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply