Q. Under SQL Server 2000 Is the memtoleave concept still valid under 64-bit?
A. Yes. If SQL Server’s component requests memory larger than 8 KB block, the block will be allocated outside of Buffer Pool. Remember, inside of SQL Server, Buffer Pool is preferable memory manager for dynamic allocations. However, it can only serve allocations that are equal or below size of 8 KB. In case when request exceeds 8 KB size, a block will be allocated by Multi Page Allocator from the area that some people call memtoleave,i.e memory outside of Buffer Pool. So basically there is no difference between 32 and 64 bit versions. Please keep in mind that I am not a big fan of memtoleave term due to the fact that SQL Server does make use of memtoleave area.
Q. Under 32-bit, my understanding is that proc plans over 8k will go into memtoleave. What about 64-bit?
A. Proc plans have lots of different allocations associated with it. The first allocation will be always below 8 KB and will be allocated form the Buffer Pool. It is possible for some proc plan allocations to be larger than 8 KB and those will be allocated by Multi Page Allocator, so there is no difference between 32 and 64 bit versions. However, please keep in mind that on 64 bit platform some of the data structures are bigger due to widening of the pointer size and alignment changes. For example on 64 bit some of our data structures require 16 byte alignment for memory allocations. The increase of pointer size might force allocation request to spill over 8 KB boundary and will make memory manager to use Multi Page Allocator instead of Buffer Pool.
Q. With SQL Server 2005, is it the same?
Q. In addition, proc plans over 8K go to the multi-page allocation. Is that still sourced from buffer cache or a different area?
A. See the answer to your previous question. Multi page allocations are sourced from area different than Buffer Pool.