Why over 1.3TB desired memory for this query?

GokhanVarol 2016-02-25 01:37:35

SQL Server version is 2014 Developer SP1. Trace Flags 272, 610, 1118, 1206, 1222, 8048, 9481 are globally turned on. We had to turn on flag 9481 since the new cardinality estimator badly effected many of the query plans.
The query has no sort, hash or paralellism. When I run the query no results returned for minutes. Ideal memory is also reported over 1.3TB from sys.dm_exec_query_memory_grants dmv during execution.

alt text

alt text

alt text

link text

GokhanVarol 2016-02-25 04:07:45
I ran a very similar query. In ActualPlan.queryanalysis Desired memory is 124GB and granted is 44GB vs in ActualPlanWithLessMemoryGrant.queryanalysis Desired is only 10MB. The only difference between them is the left most Index seek underneath the Nested Loops removed from the latter ( I added an invalid filter 1 = 0 in the join condition to eliminate the table from the plan).

link text
link text

SQLkiwi 2016-02-26 05:44:31
The memory grant is for prefetching and batch sorting on the nested loops joins. See the "Optimized" and "WithUnorderedPrefetch" properties. There is a documented trace flag to turn Optimized off – 2340; otherwise, the optimizer decides based on cardinality estimates.

You may be able to tweak the query to lower the expected row counts such that the optimizer decides not to prefetch or batch sort. Such a rewrite may also do away with the 'need' for so many hints as well.

More information:

GokhanVarol 2016-02-26 15:32:42
Thank you for the answer. I am still confused why the implicit sort might be necessary.

alt text
nested loops plan

This is the query

SELECT  CAST('I' AS CHAR(1)) AS [Action]
      , u.UnvPclId
      , p.*
FROM    [tExtract].[ExtractCounty] f
INNER JOIN [tTax].[Property] p WITH ( NOLOCK, FORCESEEK ( 1 ( CntyCd ) ) )
            ON f.CntyCd = p.CntyCd
LEFT OUTER JOIN tCommon.UnvPclId u WITH ( NOLOCK, FORCESEEK ( 1 ( CntyCd, Edition ) ) )
            ON u.CntyCd = f.CntyCd
            AND u.Edition = f.Edition
            AND u.PclId = p.PclId
            AND u.PclSeqNbr = p.PclSeqNbr
WHERE   f.SchemaId = 1 /*tTax*/
        AND f.FullExtract = 1

These are the indexes used in the plan, all 3 clustered index seeks have ordered property true in the plan.

ALTER TABLE [tExtract].[ExtractCounty] ADD CONSTRAINT [tExtract_ExtractCounty_PKC] PRIMARY KEY CLUSTERED ([SchemaId], [CntyCd])
ALTER TABLE [tTax].[Property] ADD CONSTRAINT [tTax_Property_PKC] PRIMARY KEY CLUSTERED ([CntyCd], [PclId], [PclSeqNbr])
ALTER TABLE [tCommon].[UnvPclId] ADD CONSTRAINT [tCommon_UnvPclId_PKC] PRIMARY KEY CLUSTERED ([CntyCd], [Edition], [PclId], [PclSeqNbr])

tExtract_ExtractCounty_PKC index is filtered by SchemaId = 1 therefore CntyCd coming out of it is unique and sorted.
tTax_Property_PKC is joined on CntyCd to tExtract_ExtractCounty_PKC, coming out of it PclId,
PclSeqNbr Should be ordered.
tCommon_UnvPclId_PKC is joined with CntyCd and Edition to tExtract_ExtractCounty_PKC index which are the first two columns in the index and the remaining 2 are PclId, PclSeqNbr.
The data coming into the closest nested loop to select node is already sorted then why would another sort and therefore a memory grant be necessary?

As suggested I rewrote the query which benefited from the existing sort of tTax_Property_PKC and tCommon_UnvPclId_PKC having the same sort coming out of tExtract_ExtractCounty_PKC. The optimizer is smart enough to know the data is sorted and uses a merge join in this case. How come it was not smart enough to know in the nested loop join sample?

merge join analysis

merge join plan

SELECT  CAST('I' AS CHAR(1)) AS [Action]
      , f.LoadId
      , p.UnvPclId
      , p.*
FROM    [tExtract].[ExtractCounty] f
CROSS APPLY ( SELECT    p.*
                      , u.UnvPclId
              FROM      [tTax].[Property] p
              LEFT OUTER JOIN tCommon.UnvPclId u ON u.CntyCd = f.CntyCd
                                                    AND u.Edition = f.Edition
                                                    AND u.PclId = p.PclId
                                                    AND u.PclSeqNbr = p.PclSeqNbr
              WHERE     f.CntyCd = p.CntyCd
            ) p
WHERE   f.SchemaId = 1 /*tTax*/
        AND f.FullExtract = 1
GokhanVarol 2016-02-27 02:38:07
I had a connect item for this also. They requested to check the plan with different trace flags. With trace flag 2340 there were no memory grant.

Connect Item 2404817
Trace flag 2340
Plan With 2340