Query memory

jmamed · August 29th, 2010, 12:22 PM

Hi.

Thank you for very informative book.

On page 54 of query memory section:

"Query memory is used to temporarily store results during hash and sort operations ..."

My understanding is for sort type operation whenever is not enough memory available then tempDB will be used.

But, you have not mentioned tempDb here. When you say result of operation; do you mean that engine might sort the data on tempDB but, must have a memory to store all the results of operation?

Can you ,please clarify?

Brent Ozar · August 29th, 2010, 12:28 PM

Yes, because that particular chapter is about memory, not TempDB. If you'd like to get information on how sessions are using space in TempDB for those operations, check out chapter 7. Page 292 starts discussing how to identify which sessions are using TempDB for sorts, spools, hash joins, and so on.