sp_xml_removedocument

Question

sp_xml_removedocument

Kris-155042

Hall of Fame

Points: 3460
More actions
November 24, 2021 at 4:47 am

#3956338

Hi,
I'm trying to run SQL Tuning Advisor on SQL 2014 and I get the following error.
XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents. (Microsoft SQL Server, Error: 6624)
I've read a bunch of articles and they have things like
DECLARE @hdoc INT = 1
EXEC sp_xml_removedocument @hdoc
Which gives back the error "Could not find prepared statement with handle 1."
I have no idea what stored proc has caused this. I have also run
SELECT *
FROM sys.dm_os_memory_objects
WHERE type LIKE '%XML'; as suggested
I get two results one type is MEMOBJ_MSXML and the other is MEMOBJ_DEADLOCKXML
The server has 700Gb memory 12 CPU.
Thanks,
Kris

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

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

First, don't use the tuning advisor. It's actually more than a little bit dangerous in its recommendations.

Second, are you providing an XML input document? Is it that it's too large for the machine you're running it on? Don't run the tuning advisor directly on your production server. Run it on another machine.

The XML documents you're showing are system things, so I don't think you need to be pulling them.

How are you running the DTA?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

I agree with Grant...I wouldn't bother with DTA at all. It is actually possible to get such an error simply because of an overly complex query or a simple looking query that would actually have an overly complex execution plan.

My suggestion would be to first look at an actual execution plan rather than DTA.

If that comes back with a similar error, then you're going to need to do some Divide'n'Conquer stuff. For example, I once wrote a bit of dynamic SQL to generate a whole bunch of rather simple but cascading CTEs (not to be confused with Recursive CTEs). I went over some limit and it gave me an error about SQL Server not being able to even resolve the query. It's not exactly the same as your error because it never even got to trying to create an execution plan (in XML) but I'm thinking something similar is your problem.

--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)

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 3

I'm running it from my local machine. While I imagine it is a poorly written SP I don't know what it is. Is there a way to find out? I have watched the series on execution plans you did and would like to try and use what ever this is as an example. Sorry for my ignorance.

Thanks,

Kris

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Nah, don't apologize. This forum is here to expand knowledge, so please, use it.

I haven't tried troubleshooting the DTA too much, so I'm not sure what you can do there. Generally, I fall back on Extended Events as my mechanism for understanding behaviors within SQL Server in general. There's probably something that can help there with the DTA. Or, you could just look at the query behaviors using a session on rpc_completed and sql_batch_completed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning