Using "Include Actual Plan" slows down query

Posted on 2012-08-16
Medium Priority
Last Modified: 2012-08-17

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.
Question by:yechan
  • 3
  • 2
LVL 40

Expert Comment

ID: 38301010
"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?
LVL 40

Accepted Solution

lcohan earned 1000 total points
ID: 38301024

Author Comment

ID: 38301192
again....it 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.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

LVL 40

Expert Comment

ID: 38301321
"again....it 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 or....is 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?
LVL 10

Assisted Solution

Banthor earned 1000 total points
ID: 38301982
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.

Author Comment

ID: 38304549
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.

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question