August 2, 2011 at 7:50 am
Hello everyone.
Trying to figure something out here. I have a SQL Server 2005 Standard Edition 64 Bit SP4 Box with 64GB of RAM on it. Windows Servers 2003 Enterprise with the lastest service packs installed there as well. I only have 1 database instance on the Server.
For the installed instance, I have the "Max Server Memory" option set to 59392 mb (or 58GB). Now as I understand this option, it is suppose to limit the amount of memory that SQL Server can use on a server. If that is the case, then why, when I pull up Task Manager on the server, does it show that the database instance is using 62,998,272 k in memory, which is a bit more than the max is set to. Additionally, when I pull up perfmon to validate this, I get a different value. Using the SQLServer:Memory Manager - Total Server Memory (KB), I get a total of 60,705,808 kb (this is slightly lower than 58GB, which would be 60,817,408 kb). So this appears to match with what the setting is.
So what am I missing here? Why do I get two different values between Task Manager and PerfMon? It is possble that SQL Server is using something outside of the Max Server Memory, and if so, how on earth can I figure out what it is? Lastly, and this is more of a rank. PLEASE FOR THE LOVE OF ALL THAT IS GOOD IN THIS WORLD MICROSOFT, MAKE EVERYTHING 1 STANDARD VALUE. Why allow us to enter MB, when you aren't going to show the system in that?
Thanks,
Fraggle
August 2, 2011 at 10:43 am
The 58Gb is just Buffer cache. On top of that you have such things as proc cache, memory for locks, user connections........
---------------------------------------------------------------------
August 2, 2011 at 10:50 am
.... clr cache.
August 2, 2011 at 10:52 am
Alright, how do I figure out what is getting used above the buffer pool? Are they counters or something else?
Thanks,
Fraggle
August 2, 2011 at 11:04 am
DBCC memorystatus but good luck with that. 🙂
---------------------------------------------------------------------
August 2, 2011 at 11:11 am
george sibbald (8/2/2011)
DBCC memorystatus but good luck with that. 🙂
Holly ****.
414 rows affected on my system :sick:.
So when you said good luck, what did you really mean?!? 😉
August 2, 2011 at 1:20 pm
trying to interpret the results and find out what it all means 😉
---------------------------------------------------------------------
August 2, 2011 at 2:19 pm
WTF?!?!?!!?!?!?!?!? SERIOUSLY?!?!?!?!?!?!?!?!!?
This isn't a monitoring function. This is a report. I coud probably give this to a web programmer and tell them to parse it and I would get gibberish there as well. Is there really nothing better?
Fraggle.
August 2, 2011 at 2:24 pm
Not sure how usefull this is, but it gives you a good look into a server.
http://www.red-gate.com/products/dba/sql-monitor/
Even the 14 day trial gives you enough time to tweak a lot of stuff and there's anoter 14 day extend if needed.
As for ram I have nothing else to offer except maybe lowering a little the max memory and see if you still have issues. I'm afraid that any solution will be coming down to some trial / error.
August 2, 2011 at 2:29 pm
whats your objective here? do you want to know exactly how SQL is using the memory and if so to what end, or do you just want to get a better setting for max memory?
do you have a memory problem?
---------------------------------------------------------------------
August 2, 2011 at 2:37 pm
I don't remember.
[/ too easy to ignore]
August 2, 2011 at 2:42 pm
george sibbald (8/2/2011)
whats your objective here? do you want to know exactly how SQL is using the memory and if so to what end, or do you just want to get a better setting for max memory?do you have a memory problem?
In the last couple of weeks we have had the server go down twice. I/O, Disk, Network, and TempDB are all fine. Locking and blocking are not the issue. In the error log, I keep seeing AppDomains being unloaded due to memory presure. Since we have ruled out everything else, I am trying to figure out what is going on with my memory and see if I can track down issues there.
We use CLR's as well as XML and some other fun things. So I need a way to track the memory for these. I realize I can pull CPU, Reads, Writes, duration and a whole bunch of stuff per query. However, I got nothing on memory. So I figured I would start at the top down and see what I can come up with. I need to figure out what my normal usage is in each of the "sections" and figure out if any of them go sky high or something when we start having server issues.
Fraggle
August 2, 2011 at 2:45 pm
Do you have dbs on auto-close. I just saw gail saying this is what happens in that particular case.
How did the server go down? Anything else in the logs (windows logs as well)
August 2, 2011 at 2:48 pm
Ninja's_RGR'us (8/2/2011)
I don't remember.[/ too easy to ignore]
😀
Fraggle, I read a good article on setting max memory taking other factors into account recently, but damned if I can find it. there are dmvs which track perfmom counters and also take a look at sys.dm_os_memory_clerks.
the perfmon counter that comes closest to the total amount of memory being used by a process is PROCESS: Private bytes.
Is the available memory counter low?
---------------------------------------------------------------------
August 2, 2011 at 3:01 pm
not the article I was thinking about, but try this:
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply