4

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?

  • What is @@VERSION? Did you try applying the latest service pack to your client tools to make sure it is >= @@VERSION (this will also potentially update your showplan XML schema etc.)? On the outside chance that it is relevant, is your database compatibility set to something < 110? – Aaron Bertrand Jan 31 '14 at 17:25
  • I think 11.0.2100 was SQL Server 2012 RTM. Selective XML indexes was added in SP1 11.0.3000. You clearly have SP1 on the database. There might be changes in SSMS that is there to cope with the new properties in the queryplan xml. Try to upgrade SSMS. – Mikael Eriksson Jan 31 '14 at 17:49
  • @@VERSION shows it's SP1 on the DB, compatability level is set to 110. I'm guessing the issue is due to SSMS being out of date, so I'll need to get that upgraded. Thanks both for your help! – Mark Sinkinson Feb 3 '14 at 9:10
  • @MarkSinkinson - did you get this resolved? – Max Vernon Aug 25 '16 at 15:16
  • 1
    @MaxVernon Yes, SSMS was out of date. I simply upgraded – Mark Sinkinson Aug 25 '16 at 15:33

Your Answer

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

Browse other questions tagged or ask your own question.