OPENXML and memory leak

This was a post I replied in a newsgroup to answer a question about OPENXML and memory leak. This is a common problem when xml is passed to an SP and is converted to relational data in the SP.

If you have OPENXML inside your transaction, you can potentially have memory leak.

1) XACT_ABORT set to ON. When PK or constarint error occurs, the statment is terminated immediately. The SP does not have the chance to execute sp_xml_removedocument to release the memory.

2) XACT_ABORT set to OFF. The error in 1) can be captured by @@ERROR and the memory can be released in error handling. But the timeout still cannot be captured and causes memory leak.

To preven this happening, 

1) Define @idoc as an output parameter of the SP;

2) SET XACT_ABORT OFF

3) Define table variables and parsing the xml into those table variables

4) Release the xml memory immediately after the data is saved in table variables.

5) Start transaction processing

6) In the client side code, check the output parameter @idoc. If it’s not 0, call another SP to release it.

3) and 4) can make memory utilization more efficiently.

Sample code:

CREATE PROCEDURE usp_InsertXMLData @idoc int=0 OUTPUT, @xmlData ntext

AS

SET NOCOUNT ON

SET @idoc=0

DECLARE @ParsedxmlData TABLE (…)

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData

IF @@ERROR<>0 GOTO EXIT_FAILED

INSERT @ParsedxmlData  (…)

SELECT … FROM OPENXML (…)

— if timeout here, the @idoc output will be >0

IF @@ERROR<>0 GOTO EXIT_FAILED

EXECUTE sp_xml_removedocument @idoc

IF @@ERROR=0 SET @idoc=0

BEGIN TRAN

COMMIT TRAN

RETURN 0

EXIT_ROLLBACK:

ROLLBACK TRAN

RETURN -1

EXIT_FAILED:

IF @idoc>0

BEGIN

EXECUTE sp_xml_removedocument @idoc

IF @@ERROR=0 set @idoc=0

END

RETURN -2

GO

 

CREATE usp_ReleaseXML @idoc int

AS

SET NOCOUNT ON

IF @idoc>0

BEGIN

EXECUTE sp_xml_removedocument @idoc

set @idoc=0

END

RETURN 0

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

3 Responses to OPENXML and memory leak

  1. Jeff says:

    it is cool. Peter. Just found the MSN space add one star on yr MSN head icon 🙂

  2. Youjin says:

    Peter, you are genius!! 🙂

  3. Youjin says:

    It is very good tip, I used it today when I was creating sp for f***. thanks peter

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s