SA0060 : The sp_xml_preparedocument procedure call is not paired with a following sp_xml_removedocument call
Introduction
Section titled “Introduction”Ensure proper cleanup of XML documents to avoid memory issues.
Description
Section titled “Description”This problem arises when an XML document is loaded into memory in SQL Server using the sp_xml_preparedocument procedure without subsequently calling sp_xml_removedocument to release the resources. This can lead to significant memory consumption issues.
For example:
-- Example of problematic XML handlingDECLARE @idoc INT;EXEC sp_xml_preparedocument @idoc OUTPUT, '';-- Missing cleanup step-- EXEC sp_xml_removedocument @idoc;The above example is problematic because the XML document is kept in memory, consuming resources indefinitely until SQL Server reclaims them or runs out of memory. This oversight can have serious consequences in environments with limited memory resources.
-
Increased risk of memory pressure which may affect SQL Server’s performance or cause it to run out of memory.
-
Failure to follow best practices for resource management in SQL Server, highlighting potential gaps in application resilience and reliability.
How to fix
Section titled “How to fix”Ensure proper cleanup of XML documents in SQL Server by using the sp_xml_removedocument stored procedure to release allocated memory.
Follow these steps to address the issue:
1.Identify where sp_xml_preparedocument is used to load XML documents into memory.
2.Add a call to sp_xml_removedocument immediately after processing the XML document to release the allocated memory. Pass the same document handle used in sp_xml_preparedocument .
3.Ensure that all code paths that load an XML document also include the cleanup step with sp_xml_removedocument to avoid memory leaks.
For example:
-- Proper XML handling with cleanupDECLARE @idoc INT;EXEC sp_xml_preparedocument @idoc OUTPUT, 'value';-- Process the XML document hereEXEC sp_xml_removedocument @idoc; -- Ensure resources are releasedThe rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”13 minutes per issue.
Categories
Section titled “Categories”Design Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”DECLARE @hdoc int,@hdoc1 intDECLARE @doc varchar(1000)SET @doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/> </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> </Order></Customer></ROOT>'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>'EXEC sp_xml_preparedocument @hdoc1 OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>'
EXEC sp_xml_removedocument @hdocEXEC sp_xml_removedocument @hdocAnalysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0060 : The sp_xml_preparedocument procedure call is not paired with a following sp_xml_removedocument call and the document referenced by the handle @hdoc1 is not released. | 21 | 28 |