Using "Include Actual Plan" slows down query


I don't know if someone encountered this as well.  When I do not include the "Actual Execution Plan" in SSMS the query responds in about ~1minutes.  However, when I do include the "Actual Execution Plan", the query basically takes forever.  

Has anyone encountered this?  What a weird behavior.
Who is Participating?
lcohanDatabase AnalystCommented:
lcohanDatabase AnalystCommented:
"What a weird behavior. " - if your query takes 1 minute (?!?!?) to complete unless you return 1million + rows to client that is weird in my opinion.

Obviously you must have some missing indexes or very slow SQL box and obviously SQL needs to do some work to "Include the Actual plan"

How often do you reindex/refresh stats on your SQL DB's?
yechanAuthor Commented: takes about 1 minute for the query to run without including the actual execution plan and 45 minutes when i do include the actual execution plan.  it's the same query.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

lcohanDatabase AnalystCommented:
" takes about 1 minute for the query to run "

again (and please don't get me wrong) there must be something wrong with the query itself if it takes that long to complete. Did you checked the execution plan? I bet there must be some huge record sets produced on the back end it some huge loop through a very large number of records? in that case a plan is produced for each iteration and depending on the resources it may take quite some time to generate and paste them for you to see.

What hapens if you try form the SSMS Query menu to "Display Estimated Execution Plan"? same effect?
What permissions do you have on the SQL Server?
Does Display Estimated Plan also increase length unreasonably

My Guess is that you do not have permissions to see system tables or execute system functions in the master database where query_plan is evaluated.
yechanAuthor Commented:
After working with the DBA, it turns out that the Idera monitoring tool is the culprit.  After turning it off, the query was performing just fine.  Not sure how or why but I am glad it works.  thanks for responding.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.