sp_xml_removedocument

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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