SQLSACT (1/31/2013)
ThanksI understand the workspace memory area (Well, I hope I do). A query will request required memory and once the query has been granted that, if it needs to, it will request additional memory for processing a hash join for example, which might have to spill to disk it memory can't accomodate that additional memory. Correct?
I'm not experiencing any particular issue, I'm just trying to understand the behaviour.
Where I got thrown off a bit was here
All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time.
Thanks
Sorry, but they are. You can watch it occur in the allocations. None of the temporary objects are memory only. They all require some disk allocation. It's just that the majority of the storage is, usually, depending on the system, etc., in memory.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning