Trace Flag 610 and Hash Matches

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?

tmp3D4F.pesession (64.9 kB)
avatar image By rpcasey001 0 asked Dec 13, 2016 at 07:49 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jan 08, 2017 at 04:56 PM

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.

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Dec 13, 2016 at 07:49 PM

Seen: 702 times

Last Updated: Jan 08, 2017 at 04:56 PM