Trace Flag 610 and Hash Matches

rpcasey001 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?

/storage/attachments/1814-tmp3D4F.pesession

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.

December 13, 2016

No Comments