Take the 2-minute tour ×
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. It's 100% free, no registration required.

I'm running a query that is processing some nodes out of an XML document. My estimated subtree cost is in the millions and it seems it all comes from a sort operation sql server is performing on some data that I extract from xml columns via XPath. The Sort operation has an estimated number of rows to be around 19 million, whereas the actual row count is about 800. The query itself runs reasonably well (1 - 2 seconds), but the discrepancy has me wondering about the query performance and why this difference is so large?

share|improve this question
2  
This is possibly due to outdated stats, but really impossible to tell without more information (including the table structure / indexes, the query, and an actual - not estimated - execution plan). –  Aaron Bertrand Jun 20 '13 at 14:51
1  
From my experience, query plans that involve shredding XML always have grossly inflated cost estimates. Like, to the point that if the query performs well in terms of execution time, I simply ignore the cost estimate numbers. I have no idea why it does that, but it may have something to do with not knowing how much XML is going to be used as input. If your goal is to improve performance of the query, though, one way I've found to do that is to use XML schema collections, as I blogged about here. –  Jon Seigel Jun 20 '13 at 17:50

1 Answer 1

up vote 8 down vote accepted

There is no statistics generated on XML columns. The estimates is guessed based on the expressions used when querying the XML.

Using this table:

create table T(XMLCol xml not null)
insert into T values('<root><item value = "1" /></root>')

And this rather simple XML query:

select X.N.value('@value', 'int')
from T
  cross apply T.XMLCol.nodes('root/item') as X(N)

Will give you one row returned but the estimated rows returned is 200. It will be 200 regardless of what XML or how much XML you stuff into the XML column for that one row.

This is the query plan with the estimated row count displayed.

enter image description here

A way to improve, or at least change, the estimates is to give the query optimizer some more information about the XML. In this case, because I know that root really is a root node in the XML, I can rewrite the query like this.

select X2.N.value('@value', 'int')
from T
  cross apply T.XMLCol.nodes('root[1]') as X1(N)
  cross apply X1.N.nodes('item') X2(N)

That will give me an estimate of 5 rows returned.

enter image description here

The rewrite of the query will probably not speed up the shredding of the XML but if the estimates are better, chances are that the query optimizer can make smarter decisions for the rest of the query.

I have not found any documentation on what the rules are for the estimates other than a presentation by Michael Rys that says "Base cardinality estimate is always 10’000 rows!".

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.