XML Documents using sp_xml_preparedocument and not using sp_xml_RemoveDocument

Question

XML Documents using sp_xml_preparedocument and not using sp_xml_RemoveDocument

m.fetros

SSC Veteran

Points: 200
More actions
March 8, 2018 at 8:49 am

#344336

I need to find out more info and haven't had any luck.
What happens to the memory allocated for a XML document when a query uses sp_xml_preparedocument and then the proc sp_xml_RemoveDocument isn't used.
I am not looking for an alternative to using the XML prepare document. I know there are alternatives.
If a query uses sp_xml_preparedocument and the memory is allocated, I can see those documents with handles pretty easily using Select * from sys.dm_exec_xml_handles(0)
That shows me info about the XML documents. But what if the connection that originally created the document is no longer active or the query ended and connection is closed.
when the connection is closed, does the memory get released? does it leak somewhere and become unavailable?
Most importantly, If the memory is still being held somewhere (or leaked and lost) how do I identify it and get it release?

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

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

Alexander Zhang Default port Points: 1497 More actions · Answer 1

m.fetros - Thursday, March 8, 2018 8:49 AM
I need to find out more info and haven't had any luck.
What happens to the memory allocated for a XML document when a query uses sp_xml_preparedocument and then the proc sp_xml_RemoveDocument isn't used.
I am not looking for an alternative to using the XML prepare document. I know there are alternatives.
If a query uses sp_xml_preparedocument and the memory is allocated, I can see those documents with handles pretty easily using Select * from sys.dm_exec_xml_handles(0)
That shows me info about the XML documents. But what if the connection that originally created the document is no longer active or the query ended and connection is closed.
when the connection is closed, does the memory get released? does it leak somewhere and become unavailable?
Most importantly, If the memory is still being held somewhere (or leaked and lost) how do I identify it and get it release?

Basically, sp_xml_RemoveDocument does the following two things.
1. Reclaim memory
2. Close Handle
If connection is closed without calling it, the memory and HANDLE will not get released. How can we check it. Here is the one.

SELECT *

FROM sys.dm_os_memory_objects

WHERE type = 'MEMOBJ_MSXML';

If memory keeps growing, we need to pay attention.

If it happens, freesystemcache might not help. To release them, you can try the following workaround.
DECLARE @i INT=1;
WHILE (SELECT * FROM sys.dm_os_memory_objects WHERE type ='MEMOBJ_MSXML' AND pages_allocated_count > 8)
BEGIN

BEGIN TRY

EXEC sp_xml_removedocument @i;

END TRY

BEGIN CATCH
....
END CATCH
SET @i=@i+1
END

BTW, I never tried it. Not sure if it works or not. If no luck, I'm afraid, you need to restart instance to remove them.

GASQL.com - Focus on Database and Cloud

m.fetros SSC Veteran Points: 200 More actions · Answer 2

your query have a greater than 8 in it. I know that each xml document is allocated 1/8th of the memory. but does that mean only 8 can exist at a time. Is the 1/8th allocation of total memory, so it is static, or is it 1/8th of available.
Also, the database I am having problems with is high volume (thousands of concurrent users) with hundreds or even thousands of transactions a minute.

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

m.fetros - Thursday, March 8, 2018 12:28 PM
your query have a greater than 8 in it. I know that each xml document is allocated 1/8th of the memory. but does that mean only 8 can exist at a time. Is the 1/8th allocation of total memory, so it is static, or is it 1/8th of available.
Also, the database I am having problems with is high volume (thousands of concurrent users) with hundreds or even thousands of transactions a minute.

It uses 1/8 of the total memory available to SQL Server. There can certainly be more than 8 of these occurring at the same time. Which is why you are likely having issues.

My question is this. Would it make more sense to re-write these procedures and eliminate the issue?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/