SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Documents using sp_xml_preparedocument and not using sp_xml_RemoveDocument


XML Documents using sp_xml_preparedocument and not using sp_xml_RemoveDocument

Author
Message
m.fetros
m.fetros
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 7
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?

Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1085 Visits: 264
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
m.fetros
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 7

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
Michael L John
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20611 Visits: 10015
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search