Per my reading books on SQL Server 2008 Internals and Troubleshooting (borrowed from local library in Illinois) by Christian Bolton, Brent Ozar etc. I am trying to seek understanding and confirmation on SQL server and lots of searching on the web I would appreciate if someone can confirm or correct my understanding.
Every query or operation that requires query memory grant will need work space memory. In general query using Sort, Hash Match Join, Parallelism (Not sure about this), Bulk Insert (not sure), Index Rebuild etc. will need query workspace memory..
Workspace Memory is part of SQL Server buffer pool (it is allocated as part of buffer pool) and maximum workspace memory is 75% of memory allocated to buffer pool. By default a single query can not get more than 25% of workspace memory (in SQL 2008/SQL 2012 — controlled by Resource Governor default workload group out of the box).
Seeking a confirmation of my understanding
1) Considering system with 48 GB RAM and max server memory configured to 40 GB does this mean max workspace memory is limited to 30 GB and a single query can not get workspace memory (query memory) more than 10 GB. So if you have a bad query working with a billion rows that is doing massive hash join and need more than 10 GB of memory (workspace memory) would it even care to go through this memory grant queue or right away spill to the disk?
2) If a query doing a massive sort operation has been assign a workspace memory of 5 MB and during the query execution of the query if query optimizer realize that due to bad statistics or missing indexes this query will actually need 30 MB of workspace memory it will immediately spill to tempdb. Even if system has plenty of workspace memory available during the execution once the query exceeded the granted workspace memory during the execution it will has to spill to the disk. Does my understanding is correct?
Best Answer
It doesn't work that way. Once a plan was chosen that requires a memory grant, the query must obtain the grant so it goes through the queue. Spilling, if any, occurs much later in the execution cycle. A query cannot decide to go ahead without the grant and 'spill' instead. It must get the grant and, based on that, start the execution. If the grant turns out to be insufficient (due to bad estimate or due to obtaining a much lower grant than requested) then the query will be forced to spill.
Strictly speaking it's not the optimizer, it's the query execution. Query Optimizer has only a say in deciding the plan, but once the plan is chosen and launched into execution the optimizer is out of the picture. Also, 'missing indexes' play no role here. A missing index can force a bad plan, but it cannot influence how that 'bad' plan is executed since that plan was built considering exactly what indexes actually exist so it knows exactly what it can and cannot do. Spills occur almost always due to bad estimates, ie. bad stats or bad cardinality estimation algorithms in the optimizer and in the execution (it gets really complex if you dig into the details so I'll stop here).
Unfortunately, yes. However the optimizer should come up with a plan that leverages the available RAM.
I recommend reading Understanding SQL server memory grant, which is the best info on the subject by a wide margin.