This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Tying Plan Hash to query_plan_hash in SQL Server

I have a problematic plan that I can find in DPA.  I would like to drop this plan from SQL server using DBCC FREEPROCCACHE.  The problem I am running into is that I can't find any way to correlate the Plan Hash in DPA (Plan Hash: 4468022644) to SQL Server DMV hash values such as query_plan_hash: 0xE57B30BE47CDFD80

Is there any way to correlate the two?

  • You can find the query_plan_hash in the repository. So if you run "Select name, ID from COND;" on the repository and find the ID of the monitored instance. Then use this value on as a replacement for the xx in the table CONSPH_XX

    example:  "Select * from CONSPH_27 where PLAN_HASH_VALUE = 4468022644"

  • Having the ability to click on a button or link to retrieve the query_plan_hash for the displayed SQL Hash without having to manually hunt for it in the repository would be a great enhancement to the UI.

  • FormerMember
    0 FormerMember over 7 years ago in reply to m60freeman

    Thanks Mark.  I will create a feature request for this!

    Kathy

  • FormerMember
    0 FormerMember over 6 years ago in reply to FormerMember

    Can I upvote this? Would be extremely useful emoticons_happy.png

    Thanks for creating a feature request Kathy!

  • I agree, this feature would be very useful. While I understand that the numeric plan-hashes in DPA are "easier to read" than the binary/hexadecimal handles that SQL Server assigns to the plans, it would be extremely valuable for us as DPA users to say "This is a bad plan, show me the handle so I can tell SQL to get rid of it!".