Memory Leak usando OPENXML

  • Article

Existem duas formas de acessar dados XML de dentro do SQL Server.

  • Função OPENXML
  • Tipo nativo XML

Nesse post, falaremos sobre o uso do OPENXML – disponível desde o SQL Server 2000.

SQL Books Online apresenta um exemplo bastante simples:

Examples: Using OPENXML
https://technet.microsoft.com/en-us/library/ms187897.aspx

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<ROOT>
< Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
           OrderDate="1996-07-04T00:00:00">
      < OrderDetail ProductID="11" Quantity="12"/>
      < OrderDetail ProductID="42" Quantity="10"/>
   </Order>
< /Customer>
< Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
          OrderDate="1996-08-16T00:00:00">
      < OrderDetail ProductID="72" Quantity="3"/>
   </Order>
< /Customer>
< /ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20))
EXEC sp_xml_removedocument @DocHandle

Surpresa!

Adivinhe o que acontece quando esquecemos de chamar a procedure sp_xml_removedocument.

Msg 6624, Level 16, State 7, Procedure sp_xml_preparedocument, Line 1
XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.

Criamos uma situação de memory leak, na qual a memória fica lotada com documentos XML. Por segurança, esse consumo é limitado em 1/8 da memória total da instância SQL Server.

Normalmente essa situação ocorre em stored procedures que utilizam o OPENXML, mas deixam de remover a referência ao documento. A causa “óbvia” é que o desenvolvedor esqueceu. Mas existem outras condições que causam esse comportamento, como um Command Timeout antes de finalizar a execução da procedure.

Resolver esse problema é fácil: basta matar a sessão com referências XML. A parte difícil é identificar qual a sessão.

Minha sugestão é procurar as sessões usuárias que estejam inativas (sleeping).

select session_id, memory_usage from sys.dm_exec_sessions
where status = 'sleeping' and is_user_process = 1

image

Nesse exemplo, poderia suspeitar da sessão 56 – que possui 3080 páginas de 8Kb alocadas (24MB). KILL nele!

Ao matar o processo 56, todos os recursos XML associados são automaticamente devolvidos ao gerenciador de memória.

A correção final é identificar as stored procedures que preparam o documento (sp_xml_preparedocument), mas esquecem de liberar o documento (sp_xml_removedocument).

Comentário: Aparentemente, a sessão 56 consumiu 3080 páginas de 8kb = 24MB. Essa informação está IMPRECISA! No próximo post vou explicar como identificar memory leak.