Unable to display estimated execution plan

Question

Unable to display estimated execution plan

sizal0234

SSCrazy

Points: 2272
More actions
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

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

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: 1004339 More actions · Answer 2

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)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

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: 580 More actions · Answer 4

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