Saturday, December 22, 2012

 

SQL Server: Can a stored procedure cause a memory leak?

Yes, a memory leak is possible if you forget to call sp_xml_removedocument (for each matching sp_xml_preparedocument):

A parsed document is stored in the internal cache of SQL Server. The MSXML parser (Msxmlsql.dll) uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

Example usage:

DECLARE @xml_text VARCHAR(4000), @i INT

SELECT @xml_text =
'<root>
... some valid xml ...
</root>'


EXEC sp_xml_preparedocument @i OUTPUT, @xml_text

....

EXEC sp_xml_removedocument @i



Another form of memory leak is forgetting to both close and deallocate a cursor:

DECLARE c CURSOR   
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT ...

....

CLOSE c;
DEALLOCATE c;



Just for the record, even though I always like to see an explicit CLOSE and DEALLOCATE for cursors:


LOCAL cursors are implicitly de-allocated when the stored procedure, trigger, or batch in which they were created terminates, unless the cursor has been passed back as a parameter. The LOCAL cursor will then be implicitly de-allocated when the parameter or variable referencing the cursor in the code that called the procedure goes out scope.

Ref.


[Note: I rarely use cursors. Wherever possible and appropriate, I always try to do it the set-based way]



    

Powered by Blogger