Let's say I have a complex query and for this query there are huge Differences between the estimated and actual execution plans.
These differences are causing spillage into tempDb as described here.
The problem is that in this query there are tables that are too big for me to update the statistics.
How can I manipulate the query plan so that I can have more control on memory grants?
I want to avoid memory spillage to tempdb.
In order for this question to be objective and not too broad, we could concentrate for instance on the hash join operator, when can I replace it by a merge join operator?
Are there other operators with memory grant that could be replaced depending on the work load?
-- Search cache for queries with memory grants: SELECT t.text, cp.objtype,qp.query_plan FROM sys.dm_exec_cached_plans AS cp JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t WHERE qp.query_plan.exist('declare namespace n=''http://schemas.microsoft.com/sqlserver/2004/07/showplan''; //n:MemoryFractions') = 1