Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
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 http://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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

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

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