Using "Include Actual Plan" slows down query

Posted on 2012-08-16
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
    LVL 39

    Expert Comment

    "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 39

    Accepted Solution

    LVL 1

    Author Comment

    by:yechan 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.
    LVL 39

    Expert Comment

    " 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?
    LVL 10

    Assisted Solution

    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.
    LVL 1

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now