4

I created a Extended Event to monitor and tune a Selective Xml Index

CREATE EVENT SESSION [SelectiveXmlIndexing] ON SERVER 
ADD EVENT sqlserver.selective_xml_index_no_compatible_sql_type,
ADD EVENT sqlserver.selective_xml_index_no_compatible_xsd_types,
ADD EVENT sqlserver.selective_xml_index_path_not_indexed,
ADD EVENT sqlserver.selective_xml_index_path_not_supported
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

In SSMS when I view live data and then execute the xml queries and views, no results are being returned.

All i see is:

enter image description here

Is there another step I need to acomplish before seeing the results?

I followed:

http://www.mssqltips.com/sqlservertip/2731/managing-sql-server-extended-events-in-management-studio/

http://www.sqlskills.com/blogs/bobb/choosing-paths-for-selective-xml-index-part-2-using-the-sxi-xevents/

http://www.sqlskills.com/blogs/jonathan/sql-server-2012-extended-events-update-3-viewing-target-data/

Edit,

Adding requested Query and Index

Query

WITH XMLNAMESPACES ( 'http://tempuri.org/tst.xsd' as tst )

SELECT 

    x.XmlId

    ,a.value('@id', 'int') As ColumnA
    ,b.value('.', 'NVARCHAR(25)') ColumnB               

        From
    dbo.XmlTable As X

    Cross Apply x.XmlContent.nodes('tst:Root/tst:Stuff') aa(a)
    Cross Apply a.nodes('tst:Forms') bb(b) 

And Index for the root element and attribute

CREATE SELECTIVE XML INDEX [xsi_XmlTable] ON [dbo].[XmlTable]
(
    [XmlColumn]
)
WITH XMLNAMESPACES
(
DEFAULT 'http://tempuri.org/tst.xsd'
)

FOR
(
[1] = '/Root/Stuff' as XQUERY 'node()', 
[2] = '/Root/Stuff/@id' as SQL [int] SINGLETON 
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO  

I ultimately want to index the element and the text() of the Forms Element, but the below executes, but is not getting indexed for paths 3 and 4.

[1] = '/Root/Stuff' as XQUERY 'node()', 
[2] = '/Root/Stuff/@id' as SQL [int] SINGLETON ,
[3] = '/Root/Forms' as XQUERY 'node()',
[4] = '/Root/Forms/text()' as SQL [nvarchar](25) SINGLETON

Edit: The below will not work either because I have the same path included and receive the error

Paths with same path expression indexed with selective XML index 'xsi_XmlTable' should have SINGLETON option either specified for all of them or for none of them.

So I am trying to test which will work better if I remove Singleton

[3] = '/Root/Forms' as XQUERY 'node()',
[4] = '/Root/Forms' as SQL [nvarchar](25) SINGLETON

Edit, was able to return some results after removing the join from the below query. I am not sure why the join was not allowing it to return results, but just running the straight xquery I was able to return the results but path 3 and 4 i still not getting indexed.

WITH XMLNAMESPACES ( 'http://tempuri.org/tst.xsd' as tst )

    SELECT 

         x.XmlId
        ,c.Content

        ,a.value('@id', 'int') As ColumnA
        ,b.value('.', 'NVARCHAR(25)') ColumnB               

            From
        dbo.XmlTable As X

        Cross Apply x.XmlContent.nodes('tst:Root/tst:Stuff') aa(a)
        Cross Apply a.nodes('tst:Forms') bb(b) 

        Inner Join TableC c on c.id = a.value('@id', 'int')

Here is a simple Xml Sample

<tst:Root xmlns:tst="http://tempuri.org/tst.xsd" SchemaVersion="0.1">
  <tst:Stuff id="1">
    <tst:Forms>Sample1</tst:Forms>
  </tst:Stuff>
</tst:Root> 
6

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000).

The mistake you did with your extra path expressions is that you forgot Stuff in the path.

Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found that one extra path is enough for your queries to make it use only the index to fetch the data [4] = '/Root/Stuff/Forms' as SQL nvarchar(25). You can have [3] = '/Root/Stuff/Forms' as XQUERY 'node()' in there as well and perhaps it will make a difference or not depending on what your XML look like.

Note 1: You have mixed up the names of the columns in the index and your query. That might contribute to the problems you have with extended event.

Note 2: The events for missing selective XML indexes is generated when the query is compiled. If the query plan is used from the cache you will not see any events.

Your Answer

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

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