Trace Flag 610 and Hash Matches
2016-12-13 19:49:41
I am new to using your SQL Sentry Plan Explorer.
I have a hash Match (Aggregate) with a 45.7% cost and a Table Insert with as 52% cost. The SQL uses a clusted index scan and 2 hash matches. One hash match on the join and one for the aggregate. The SQL is below.
I wanted to try using Trace Flag 610 to help with the inset into (#temptable) to minimal log the insert to increase performance. Will that work?
Also, what can I do with the 2 Hash Matches?
— Trace Flag 610 On
dbcc traceon(610)
-- Create and load temporary table to join with HoldHistory to remove HoldHistory -- duplicates SELECT hh.UniversityID, ISNULL(ApplicantID, '') AS ApplicantID, ISNULL(StudentID, '') AS StudentID, ISNULL(APHoldSource, '') AS APHoldSource, ISNULL(HoldTypeCode, '') AS HoldTypeCode, ISNULL(HoldTypeDescription, '') AS HoldTypeDescription, ISNULL(HoldDate, '') AS HoldDate, ISNULL(HoldStatusCode, '') AS HoldStatusCode, ISNULL(HoldStatusDescription, '') AS HoldStatusDescription, ISNULL(ProgramCode, '') AS ProgramCode, MAX(ID) AS MaxID INTO #tmpRemoveHoldDuplicates FROM APStage.dbo.HoldHistory hh INNER JOIN #tmpUniversityList ul ON ul.UniversityID = hh.UniversityID WHERE LoadDate >= @LastETLStartDate GROUP BY hh.UniversityID, ISNULL(ApplicantID, ''), ISNULL(StudentID, ''), ISNULL(APHoldSource, ''), ISNULL(HoldTypeCode, ''), ISNULL(HoldTypeDescription, ''), ISNULL(HoldDate, ''), ISNULL(HoldStatusCode, ''), ISNULL(HoldStatusDescription, ''), ISNULL(ProgramCode, '')
— Trace Flag 610 Off
dbcc traceoff(610)
Do I need additional indexing?
Can I check indexing with SQL Sentry Explorer?
Aaron Bertrand 2017-01-08 16:56:12
Can you generate an actual plan from within Plan Explorer? This appears to be a plan generated in Management Studio. If you generate one from within Plan Explorer, you'll have all sorts of valuable runtime metrics, the ability to really see where actual costs are happening, and you'll also get plenty of insight from the index analysis tab.