MSSQL Architecture & Design: Improving Report Performance XML Columns
Posted on 2011-04-19
Techies, Gurus and Wizards--
One of my colleagues has been working with a 3rd party product which stores xml data in an xml defined column. As this 3rd party product continues to accrue more and more data, the reports which run off this data have gone from 10 minutes to nearly 2 hours. I've agreed to look at this with him this week. I'm not sure what I'll find, but I suspect whatever the search criteria is for the where clauses filtering these reports will be dependent upon isolating string values found against specific nodes stored within the xml docs in the xml column.
For those of you who've encountered similar issues, how did you approach isolating the problem? If I do discover that values within the xml doc are used to filter data for the reports what approach do I take?
In reading the MS literature on XML data types, I see that there are primary and secondary indexes available. I'm a little confused on the clustered XML primary. Does this mean that if a table already has something like a primary, clustered integer identifier that it is still possible to create an XML primary, clustered key in addition or not?
In any case, I will be grateful for your suggestions on how to apply best practices to resolving poor performance issues against XML data stored in SQL Server 2005!:-)