0

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?

Understanding SQL server memory grant

-- 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
1
  • 1
    SQL could be using a hash join because there isn't an index that supports ordered input for a merge join. If you hint a merge join, you could end up with memory consuming and disk spilling sorts to implement it. That doesn't help you. – Erik Darling Feb 9 '17 at 23:45
2

I suggest thinking about your problem in a different way. It's not true that there's some inherit advantage to a hash join that makes the SQL Server query optimizer want to pick it (except in batch mode with CCIs). The query optimizer chose a hash join because it thought it was a better option than a merge or nested loop join. Nested loop joins are a good choice when the outer table is small and the inner table is indexed. Merge joins are a good choice when the inputs are sorted and you need to access a non-small percentage of rows from the tables. Hash joins are a good choice when you don't have a better choice than scanning both tables. In most cases a hash join when it is not appropriate indicates missing indexes.

If you have a legitimate need to encourage a merge join there are a few things that you can do. If it is a 1:1 join define constraints so that SQL Server knows that. The query optimizer costs many-to-many merge joins much higher than 1:1 joins. Create indexes so that your data is presorted. If you cannot do that then make sure the sorts are in a parallel zone of the plan. Sorts benefit greatly from parallelism. Note that a merge join can lead to tempdb usage if there are large sorts or if the merge join is many-to-many. There may be other scenarios as well.

Nested loop joins aren't necessary a bad choice for a large, complicated plan. If you have a legitimate need to encourage a nested loop join make sure you have indexes that enable that. Your query may benefit from a parallel apply type of plan. Those plans can use less memory than a plan with large parallel hash joins. You should be aware that in some cases nested loop joins will do a sort on the outer table before the join operator to improve performance.

On the subject of memory, you could make sure that your server has an appropriate amount of memory and that other queries on the server aren't using too much memory. Otherwise, trying to pick query plan operators on the basis on memory seems kind of backwards. You should really fix your statistics. Use table partitioning, a partitioned view, a smaller sample size, filtered indexes, etc. You're playing a game that's very difficult to win if you don't fix your cardinality estimates.

2

I'm not sure I understand how a table is "too big" to update the statistics. When people start calling tables "too big" for anything, they usually start being candidates for table partitioning. In which case you can use incremental statistics to focus your stats updates on the active partitions (the optimizer won't use the per-partition stats independently, but it will still be beneficial for queries and reduces your maintenance because, presumably, not all of your partitions will have significant, or any, data fluctuations between every maintenance window).

Anyway, instead of trying to manipulate which operators are used in order to affect how much memory grant you have (which may make your performance or resource usage worse), why not use hints that control min/max grants directly? KB #3107401 talks about new query hints MIN_GRANT_PERCENT and MAX_GRANT_PERCENT that control the grant a query will get (the article says 2012 SP3, but these were also added in 2014 SP2).

Tread lightly, though. My suspicion is you are trying to solve the wrong problem with the wrong solution. If the source of the problem is bad estimates, fix that problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.