XML Documents using sp_xml_preparedocument and not using sp_xml_RemoveDocument

  • 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? 

  • 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

  • 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.

  • 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/

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

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