Sql-server – Sql Server 2012 Extended Events for Selective Xml Indexing not showing results

extended-eventssql serversql-server-2012xml

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>

Best Answer

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.

Related Solutions

Sql-server – Selective Xml Indexing

A selective index will not be used when using query to retrieve the data.

From Selective XML Indexes (SXI) - Supported XML Features

Selective XML indexes support the XQuery supported by SQL Server inside the exist(), value() and nodes() methods.

For the query() and modify() methods, selective XML indexes may be used for node filtering only.

For the query() method, selective XML indexes are not used to retrieve results.

However testing shows that the index is used when you rewrite the query using nodes. These queries returns the same but the second is faster because it uses the the selective XML index.

select data.query('/Log/CallStack/ExceptionThread/Line')
from LogData


select T.X.query('.')
from LogData
  cross apply data.nodes('/Log/CallStack/ExceptionThread/Line') as T(X)

The selective XML index used in the second query is defined as..

[Line] = '/Log/CallStack/ExceptionThread/Line' as XQUERY 'node()',

I used a table of my own just to be able to show what the difference in this case is.

(67992 row(s) affected)
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 1840865, lob physical reads 313514, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 129792 ms,  elapsed time = 180087 ms.

(1850106 row(s) affected)
Table 'xml_sxi_table_622625261_256000'. Scan count 1, logical reads 8416, physical reads 3, read-ahead reads 8403, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 927882, lob physical reads 0, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 65271 ms,  elapsed time = 67636 ms.

Sql-server – SQL Server 2012 Intellisense Support for Extended Events

I haven't come across Intellisense support for XEs in SSMS yet. It will tell you when you have an error in your syntax (my common mistake was omitting ON SERVER). SQL Prompt 5 (latest version) has support for the control commands (CREATE EVENT SESSION ... ALTER EVENT SESSION, etc.) but not for the packages and events yet. For now I'd recommend just sticking with the metadata queries from Jonathan Kehayias to get a sense for the packages, events, predicates, maps, etc.

I think this is probably the case because Extended Events are still a work in progress and are undergoing such rapid change and development that more effort would have to be put into generating the Intellisense XML than they'd like to put into it until it's stabilized (from a feature perspective, not a reliability perspective).

Best Answer

Related Solutions

Sql-server – Selective Xml Indexing

Sql-server – SQL Server 2012 Intellisense Support for Extended Events

Related Question