SQL Server High Hash Match and Sort I have an SQL statement that is taking about 40 seconds to run and the only thing I see is an high hash match and sort. I have added indexs to the temp table but still taking an long time to complete.

https://www.brentozar.com/pastetheplan/?id=rJ7iMDC9m

SQL Server Execution Times:
   CPU time = 46735 ms,  elapsed time = 9004 ms.

SELECT g.CustomerId, g.LogDate
INTO #TempGuidelineLog
FROM vwGuidelineLog g --nolock
where g.LogDate &gt;= '2017-10-01' and g.LogDate &lt; dateadd(day, 1, '2018-09-30')

CREATE NONCLUSTERED INDEX ix_temp1Customer ON #TempGuidelineLog (CustomerId)
INCLUDE ([LogDate])


select
g.*,
a.StateId, 
a.CountryId
into #Temp
from #TempGuidelineLog g
JOIN [vwCustomerAddress] a ON a.CustomerId = g.CustomerId

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CountryId)
INCLUDE ([CustomerId])

SELECT States=(SELECT Total=COUNT(c.CustomerID), c.StateId, st.Name
,[Distinct] =  COUNT(DISTINCT c.CustomerId)
FROM #Temp c
JOIN [State] st ON st.StateId = c.StateId
where c.CountryId = 1 and st.StateId NOT IN (65,66)
GROUP BY c.StateId, st.Name
ORDER BY 1 DESC
FOR XML PATH('State'),type)

,Dates = (SELECT StartDate = CONVERT(VARCHAR(10), '2017-10-01' , 101), EndDate = CONVERT(VARCHAR(10), '2018-09-30', 101)
FOR XML PATH('Date'),type)

,Countries=(SELECT Total=COUNT(c.CustomerID), co.CountryID, co.Name, ISOCode=co.TwoLetterISOCode
,[Distinct] =  COUNT(DISTINCT c.CustomerId)
FROM #Temp c
JOIN Country co ON co.CountryID = c.CountryId
GROUP BY co.CountryID, co.Name,co.TwoLetterISOCode
ORDER BY 1 DESC
FOR XML PATH('Country'),type)
FOR XML PATH('Report')
First of all true test of sql is to take variable instead of constant value.
So ,

Declare @From date='2017-10-01' 
Declare @To date='2018-09-30'


Immediate correction ,put the filter here

select
g.*,
a.StateId, 
a.CountryId
into #Temp
from #TempGuidelineLog g
JOIN [vwCustomerAddress] a ON a.CustomerId = g.CustomerId
where g.CountryId = 1 and g.StateId NOT IN (65,66)


and remove the same filter from where it is now.

I see no requirement of LogDate in #TempGuidelineLog so remove it.

so new index,

CREATE NONCLUSTERED INDEX ix_temp1Customer ON #TempGuidelineLog (CustomerId)


Since countryid is more selective,

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CountryId,Stateid,CustomerId)

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CustomerId)


IMO, group by St.Name and co.Name is also hurting your performance.

so you can seperatly query group by function and then with that resultset join State and country table.hope it is clear.

The way you are creating XML is also complicated.You can choose one of the xml design which suite you from here,XML