Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm looking for some advice on OPENXML. Specifically, the performance. I am seeing very slow performance on a very small piece of XML.

Something about this is causing a Remote Scan. Any ideas on how to go about tuning it?

declare @tab_office as table (id varchar(100))
    declare @doc_filter int
    exec sp_xml_preparedocument @doc_filter output,@office_id
    insert into @tab_office (id) (select office_id from Openxml(@doc_filter,'//filter') with (office_id varchar     (100) 'office_id'))

enter image description here

xml data

' OOT9 OOT8 OOT5 '

share|improve this question
Why did you rollback the edit? Are you happy with the presentation of the XML data now? – Mikael Eriksson Mar 11 '14 at 8:23
Do you use sp_xml_removedocument in you actual code? If you don't your openxml queries will slow down dramatically over time. The remote scan operator is what is parsing the XML, the MSXML parser (Msxmlsql.dll). – Mikael Eriksson Mar 11 '14 at 8:24
thank you but still showing the same plan – Nikhil VC Mar 11 '14 at 8:46
Yes of course. Why would you expect a different plan just because you free the resources you have allocated? Executing this query once takes less than a millisecond and executing it in a loop 1000 times takes 600 milliseconds. If you don't use sp_xml_removedocument the execution time will increase. In my case the execution times in milliseconds changes like this. 615, 660, 737, 846, 927 after 20 executions it takes 2374 milliseconds the execute your query 1000 times. To reclaim the allocated resources and make your query to go faster again you have to restart SQL Server. – Mikael Eriksson Mar 11 '14 at 9:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.