The Query Wait option controls how long
a query that needs additional memory waits if that memory is not available. A
setting of –1 means that the query waits 25 times the estimated execution time
of the query, but it always waits at least 25 seconds with this setting.
A
value of 0 or more specifies the number of seconds that a query waits. If the wait
time is exceeded, SQL Server generates error 8645:
Server: Msg 8645, Level 17, State 1, Line 1 A time out
occurred while waiting for memory resources to execute the query. Re-run the
query.
Even though memory is allocated dynamically, SQL Server can
still run out of memory if the memory resources on the machine are exhausted.
If your queries time out with error 8645, you can try increasing the paging fi
le size or even adding more physical memory. You can also try tuning the query
by creating more useful indexes so that hash or merge operations aren’t needed.
Keep in mind that this option affects only queries that have to wait for memory
needed by hash and merge operations. Queries that have to wait for other
reasons are not affected.
