Why over 1.3TB desired memory for this query?
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.
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:
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?
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
link text
link text