Post reply

Unable to display estimated execution plan

  • December 3, 2019 at 8:33 pm

    #3704000

    Hi,

    I am getting the below error even after closing and reopening ssms and no other query running under my login however it is quite possible other users...from what I see from sp_whoisactive only 1 user and few maintenance queries are running.  However, instance does not respond slow. I want to generate estimated plan so that I can look into possible tuning of query or maybe add IDX if required.

    Query is deleting records around 3.2k records.

    "An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."

    Sample below query

    USE ADVENTUREWORKS

    Declare @Srnumber int

    Declare @Srnumbermg int

    exec sp_phelp_remove ****, @Srnumber ,@Srnumbermg - the full query is multiple lines of same code with CHANGED Srnumber .

    exec sp_phelp_remove 0001   , @Srnumber ,@Srnumbermg

    exec sp_phelp_remove 0002   , @Srnumber ,@Srnumbermg

     

  • December 3, 2019 at 8:52 pm

    #3704005

    Think that error is on your pc - not the server.

    your pc may be struggling or your version of SSMS has issues - better to install the latest one and see if that if that fixes the issue

  • December 3, 2019 at 10:16 pm

    #3704027

    sp_phelp_remove isn't a native  SQL stored procedure.  Please provide the code for it or we're just guessing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 4, 2019 at 9:29 am

    #3704179

    this normally occurs for one of 2 reasons (they are the bane of my life)

    the execution plan is too big for management studio to handle (it's basically a big xml file that it can't put on screen) - this is normally caused by cursors or functions

    I also find it when people use "select into #table" rather than creating a temp table and using "insert into"

    if your query is just deleting, then check for triggers  that are making the plan quite large

    MVDBA

  • December 6, 2019 at 8:26 pm

    #3705546

    Use the "Include Live Query Statistics" in SSMS.  It will show the plan even if the query fails from memory exception.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply