I'm trying to create a database solution that will enable me to optimise high CPU/IO/Duration queries to improve overall performance of the database.
Part of the optimisation requires persisting Query Plan XML to a table, then indexing it so that I can easily pull out the Warnings such as PlanAffectingConvert and fix these issues.
However, when I try to create a selective XML index on the table, like so:
CREATE SELECTIVE XML INDEX sxi_queryplan
ON dbo.Table(query_plan)
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
FOR
(
pathQueryWarnings = '/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/Warnings' AS XQuery 'node()'
)
...I get the following error:
An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:
There is an error in XML document (1, 1762).
Instance validation error: 'SelectiveXML' is not a valid value for IndexKindType.
The index is still created though, however whenever I then try to query the table, using the below, I get the same error as above.
WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT query_plan FROM dbo.Table WHERE query_plan.exist('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/Warnings') = 0
I've spent all morning trying to find the answer to this, but had no luck finding anythign about the Error Messages.
Without the index on the table, the above query works fine, suggesting the XML is not actually invalid. With it, I get the errors...
Any ideas?
