This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Tying Plan Hash to query_plan_hash in SQL Server

code_joe over 9 years ago

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?

Top Replies

Parents

0 jaminsql over 9 years ago

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"
Cancel
Vote Up +1 Vote Down

Cancel
0 m60freeman over 9 years ago in reply to jaminsql

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.
Cancel
Vote Up +1 Vote Down

Cancel

Reply

0 m60freeman over 9 years ago in reply to jaminsql

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.
Cancel
Vote Up +1 Vote Down

Cancel

Children

0 FormerMember over 9 years ago in reply to m60freeman

Thanks Mark. I will create a feature request for this!
Kathy
Cancel
Vote Up +1 Vote Down

Cancel
0 FormerMember over 8 years ago in reply to FormerMember

Can I upvote this? Would be extremely useful
Thanks for creating a feature request Kathy!
Cancel
Vote Up +1 Vote Down

Cancel
0 njohnson over 6 years ago in reply to FormerMember

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!".
Cancel
Vote Up 0 Vote Down

Cancel