Unable to display estimated execution plan

  • sizal0234

    SSCrazy

    Points: 2064

    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

     

  • frederico_fonseca

    SSChampion

    Points: 14216

    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

  • Jeff Moden

    SSC Guru

    Points: 995116

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21193

    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

  • askcoffman

    SSC-Addicted

    Points: 468

    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 5 (of 5 total)

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