Unable to display estimated execution plan

  • sizal0234

    SSCrazy

    Points: 2194

    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: 14686

    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: 996665

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    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

    Mr or Mrs. 500

    Points: 559

    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