Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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> 
share|improve this question
3  
Show us your XML queries. The four events you've added to the session are all "missing index" type of events. You may simply not be hitting them. –  Jon Seigel Jul 8 '13 at 22:21
2  
Do you have a selective XML index in place for the column you are querying? There will be no events generated if you don't. –  Mikael Eriksson Jul 9 '13 at 6:27
    
I updated the question from the replies, sorry took so long to get back! Tell me if their is anything else I missed. The last query I added was actually the first one I was running, after removing the join and running the straight xquery I could see the results. Not sure why the join would not let the Event capture results. –  KeyboardFriendly Jul 9 '13 at 15:28
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
1  
Thanks Mikael I see the errors! It works! –  KeyboardFriendly Jul 9 '13 at 18:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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