April 19, 2011 at 11:43 am
This article talks about the -g parameter forcing SQL Server to set aside additional memory for non-buffer pool activities within the SQL Server memory space. Does it have any effect on 64-bit systems?
http://msdn.microsoft.com/en-us/library/ms190737.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2011 at 11:50 am
Bob Beauchemin said no:
http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/edf53293-d8f5-4a3c-b395-d9d53d255dd6
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2011 at 12:24 pm
In 32-bit, the mem-to-leave is a piece of the virtual address space (2GB) left for non-paged memory. SQL reserves it before it allocates the buffer pool, then frees it to ensure that there is some virtual address space left over.
In 64 bit, the virtual address space is 8TB in size, hence reserving any of that is pointless, there is no way that it can be filled
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2011 at 12:31 pm
I guess was thinking about it all wrong...if a 64-bit instance has 10GB memory allocated to it, I figured you may want to tell SQL Server to start out with 2GB for non-buffer pool items if you knew you had processes that would immediately need that much. I am still unclear as to why you would not want to be able to claim that memory ahead of time, always ensuring it is segregated from the buffer pool, but I guess it's not something we can control in 64-bit.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2011 at 12:56 pm
It's not something you need to control in 64-bit.
It has nothing to do with the amount of physical memory, it's a non-reserved portion of the virtual address space of the SQL process. In 32-bit SQL, the virtual address space is 4GB, half user mode, half kernel mode, hence the 2GB I mentioned.
In 64-bit, the theoretical size of the virtual address space (the total amount of memory that can be addressed by a process) is 16 exabytes (16*1024 petabytes, 16*1024*1024 terabytes)
Windows limits that (for various reasons) to 8TB (or 16, I can't recall offhand). Again, half user, half kernel. Hence the user-mode virtual address space size mentioned earlier. Since we don't have physical machines with that much memory yet, there's little risk in assuming that there will always be some free virtual memory (not physical memory) for the non-paged items.
The mem-to-leave has always been unallocated, unreserved memory. The buffer pool is reserved (and the max memory setting controls the size of the buffer pool only). In 32-bit if you say -g384, what you are saying to SQL is that it must leave 384MB of it's virtual address space unallocated for future use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2011 at 1:25 pm
If that was greek, may I suggest downloading and watching this: http://channel9.msdn.com/Events/PDC/PDC10/CD01 and this http://channel9.msdn.com/Events/PDC/PDC10/CD02
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2011 at 2:48 pm
Thanks Gail, that helped. The relationship between virtual address space and physical memory, what relationship there is, is becoming more clear...I just stumbled into a really big topic.
You said "The mem-to-leave has always been unallocated, unreserved memory" which makes sense to me, and I can see that the -g flag simply reserved virtual address space, not physical memory, for eventual mapping to non-buffer pool physical memory. And that this effectively limited the address space that could be used for mapping physical memory for the buffer pool, but that this is only relevant on 32-bit instances where virtual address space is limited relative to the number of physical memory addresses (catches breath). All of that said, the -g concept is no longer in play in a 64-bit environment given the abundance of virtual address space when compared to the number of physical memory addresses.
PS Thanks for the links.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2011 at 3:52 pm
opc.three (4/19/2011)
I just stumbled into a really big topic.
Welcome to memory management. Please check your sanity in at the door.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2011 at 4:16 pm
:blink:
I just watched about half of the first presentation. I think my brain popped my ear open, snuck out, and dropped down to Mexico for a few days for some Tequila. Once it's properly smashed out of its mind, it mentioned coming back and trying this again.
After the first four or five slides I'm relatively sure I have no damned idea what he's talking about. I know I *think* I understood it... but no, no, I'm sure I didn't. Cripes. This is going to take a few tries, I can see this now.
Don't feel bad, OPC. I didn't realize this was Oceanic Shelf level of depth either. Gyeaaahhh... I just wanted to take a fast dip in the kiddy pool.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 19, 2011 at 4:24 pm
Phew, glad I am not the only one. It took me 30 minutes reading various articles online, and re-reading Gail's posts on this thread, just to get to to a place where I could formulate a response. I am "in" though, good stuff. And I agree, I don't think the pres in the video was built out for a single serving, I need a primer...sanity, checked :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2011 at 4:25 pm
Craig Farrell (4/19/2011)
I just watched about half of the first presentation. I think my brain popped my ear open, snuck out, and dropped down to Mexico for a few days for some Tequila. Once it's properly smashed out of its mind, it mentioned coming back and trying this again.
I feel much the same way. I need to rewatch both, repeatedly. And buy his book.
I didn't realize this was Oceanic Shelf level of depth either. Gyeaaahhh... I just wanted to take a fast dip in the kiddy pool.
I think the second one is Mariana trench depth. I listened to it while shopping tonight. Came home without half the stuff I needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply