January 2, 2008 at 3:45 pm
I'm still looking into the exact reasoning behind all this and very interesting it is getting too
It seems that with 64bit you can allocate more memory than you have physical memory - the page file becomes part of memory and can be allocated thus - I guess I could see this type of event helping virtualised environments where you have page file son fast disks. I think what happens is that the 64bit o/s sees physical and virtual memory as a complete package, if you don't set a physical limit to memory for sql server it will eventually use the virtual memory too - if you haven't fixed your page file this means it could expand much like tempdb. There are a number of working sets of memory which are not always obvious, I'm tracking down on how you actually figure out what memory is being used - seems task manger only tells part of the story and availble memory frequently isn't.
You obviously need to load a server hard to prove this and I've no contacts to ask this type of question about the o/s.
I've always believed DBAs should have excellent o/s knowledge and I further believe that o/s builds for sql server don't always fit a standard build which may fit other enterprise servers - but often proving this is tricky! I do know on 32 bit systems the system cache is either 512mb or 1024mb and task manager does not show this - you need other methods to measure it - as I get free time I'm searching it out.
I stand by my statement - alllow adequate memory for o/s, cache and working sets. Fix your sql server max memory.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 3, 2008 at 12:34 am
ye, it is very interesting. also, i wouldnt want to rely on task manager on 32-bit, its a known bug from M$ saying the memory consumption shown there (especially those large ones) are inaccurate.
on the system cache, i'm not sure if you're referring to some of the missing memory not shown in tasks manager especially on a server with 4GB physical memory (ie it could be only showing 3.5GB, etc). this behaviour is just due to the task manager design where it doesnt show the "reserved" address mapping on the server. Ie. If you have a graphic card of 256MB on-board, this memory must be mapped within the first 4GB of address space. so, on a 4GB memory, part of that address space is already consumed by this graphic card memory mapping, hence, explains why you dont see the full 4GB memory on your server because its already been reserved.
back to my understanding, sql only utilises physical memory and it swaps in/out when in pressure. i know under certain circumstances, it can consume memory from mem-to-leave area and then writes an entry in error log like below but its still happening in the physical memory space
Dynamic Memory Manager: Stolen=xx OS Reserve=xxx
Your point is certainly very interesting. Unfortunately, i only have x64/IA64 in prod and cant test out this scenario. do post to this thread if you find x64/ia64 can commit even the paging memory in sql.
Simon Liew
Microsoft Certified Master: SQL Server 2008
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply