I'm researching an issue with the following information:

We had a logging table with about 90k records in it that had inserts taking several seconds(approximately 10 to 20s) in extreme cases. One of the columns of the table stores XML as the XML datatype. The XML isn't being parsed during the insert, just stored.

We tried truncating the table assuming that the issue was related the number of records(althought 90k seemed "normal") and the inserts still are performing poorly.

While I know there are other issues that can cloud the issue, what would be some "check this first" ideas that could help me debug this issue? Thanks for any suggestions and help in advance.


Ah, the XML is being parsed, sorry.

XML data type does NOT store the data as text, it decomposes it into a key/value style of internal structure, and for that it has to parse them ;) THis is to facilitate search, btw. ;)

So, sorry, the server does parse the XML.

What are your identified as the bottleneck? CPU? IO? Locks?

Inserts should not have a performance relation to table length that is measurable, unless you do something utterly stupid like a unique constraint WITHOUT index (and thus a table scan on every insert). Before giving advice, you need to start looking into the real reasons why it takes so long, and what goes on on the server.

  • From what I know of traces, they are generally expensive. Is there a way to implement a trace to avoid adding detremental overhead to confuse the issue even more? – Achilles May 6 '10 at 15:30
  • Well, hat do you care how expensive they are? You need more info to know what goes on. Noone says let it run for days. Put a full tracing suite in (performance counters, full sql trace), repro the problem, turn tracing off again. – TomTom May 6 '10 at 15:35
  • agreed on that; lots are afraid to do traces for fear of impact. In @Achilles case, I would filter on duration > 10seconds, that would have very little impact. If the insert is taking 10 seconds, there could be another blocking query that is taking 11 seconds and you'll see it finish just before the insert. – SqlACID May 6 '10 at 16:12

This is probably the same as https://stackoverflow.com/questions/2782378/which-inserts-faster-xml-field-or-varcharmax-field/2783521#2783521

I'll repeat my advice from SO:

Apply a well tested and proven performance investigation methodology, like Waits and Queues. Guessing will land you nowhere fast.


Take a look at how you have chosen to store the data. Here is the xml storage guide for 2005 The various levels of document fidelity could impact performance. Also take a look at the basics for monitoring performance

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.