Xml index, slow count of rows
Data differences

With a low amount of records to seek on, the optimizer is able to use the SIX_T_pathXQUERY index:



and filter on moretext with a seek predicate:



An interesting part here is that it executes the key lookup to get the path_1_id values that are not null.



Since that is a filter definition on the nonclustered xml index...



...while not being present in the index itself.

As a result, for the optimizer to consider using the index it knows that it has to complete these steps:


Filter on the XML value with the secondary index on the internal
table
Match these returned values with the SIX_T clustered index on the
internal table and filter on path_1_id is not null since path_1_id is
not included in the secondary index
Match these values with the actual table, dbo.T to return the ID to count on


Tipping points

When the expected rows to be returned are higher, it favours using the selective clustered XML Index, to be able to run a merge join &amp; no key lookup instead:



with a residual predicate:



To filter on the xml column and path_1_id

Comparing the plans

You could (but you shouldn't) use the USE PLAN hint to force the plan with the seek on the XML column and see what would happen if we where to search on these values. 



With  the execution time =

   CPU time = 218 ms,  elapsed time = 215 ms.


And the execution time for the scan + merge join plan:

   CPU time = 62 ms,  elapsed time = 58 ms.


In short, I believe that the scan with residual predicate + merge join choice was the right choice by the optimizer.

No way around it

While I might be wrong, I don't think there is a way to improve the count query with regular XML Indexes. 
We also cannot change these internal tables or even query them:

SELECT * FROM 
[sys].[xml_sxi_table_1463676262_256000];


It even gives a missing index hint on the merge join query plan, which you cannot create:

CREATE NONCLUSTERED INDEX [&lt;Name of Missing Index, sysname,&gt;]
ON [sys].[xml_sxi_table_1463676262_256000] ([pathXQUERY_1_value])
INCLUDE ([path_1_id])


To improve the queries, you would have to look into non-xml index solutions.



Edit


  How do i use USE PLAN hint? You have example code for this query?


I would advise against this, but for testing purposes it would be fine.

Step 1: You would have to get the actual execution plan of the query and get
    the xml:



If you don't have the query with the low estimates, enter a value that does not exist like:

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/SomeText[. eq "bbb"]') = 1


Step 2: Replace all 's with ''s in the execution plan xml, we will need this later. 



Step 3: Paste the plan between OPTION( USE PLAN '')

SELECT count(*)
FROM dbo.T as T
WHERE T.XMLDoc.exist('/SomeText[. eq "MoreText"]') = 1
OPTION(USE PLAN 
'');


Step 4: I had to change utf-16 to utf-8 to get the use plan hint to work

From: 

OPTION(USE PLAN
'&lt;?xml version="1.0" encoding="utf-16"?&gt;


To:

OPTION(USE PLAN
'&lt;?xml version="1.0" encoding="utf-8"?&gt;


Step 5: Run the query.

My query now looks like this:

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/SomeText[. eq "MoreText"]') = 1
OPTION(USE PLAN
'&lt;?xml version="1.0" encoding="utf-8"?&gt;
&lt;ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.481" Build="14.0.3223.3" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt;
  &lt;BatchSequence&gt;
    &lt;Batch&gt;
      &lt;Statements&gt;
        &lt;StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.00986014" StatementText="select count(*)&amp;#xD;&amp;#xA;from dbo.T as T&amp;#xD;&amp;#xA;where T.XMLDoc.exist(''/SomeText[. eq &amp;quot;bbb&amp;quot;]'') = 1" StatementType="SELECT" QueryHash="0x412154B6AD55BBFC" QueryPlanHash="0x280B174BF20902E3" RetrievedFromCache="true" SecurityPolicyApplied="false"&gt;
          &lt;StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /&gt;
          &lt;QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="394" CompileCPU="301" CompileMemory="648"&gt;
            &lt;MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /&gt;
            &lt;OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419404" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="3655432" /&gt;
            &lt;QueryTimeStats CpuTime="0" ElapsedTime="0" /&gt;
            &lt;RelOp AvgRowSize="11" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00986014"&gt;
              &lt;OutputList&gt;
                &lt;ColumnReference Column="Expr1017" /&gt;
              &lt;/OutputList&gt;
              &lt;ComputeScalar&gt;
                &lt;DefinedValues&gt;
                  &lt;DefinedValue&gt;
                    &lt;ColumnReference Column="Expr1017" /&gt;
                    &lt;ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1020],0)"&gt;
                      &lt;Convert DataType="int" Style="0" Implicit="true"&gt;
                        &lt;ScalarOperator&gt;
                          &lt;Identifier&gt;
                            &lt;ColumnReference Column="Expr1020" /&gt;
                          &lt;/Identifier&gt;
                        &lt;/ScalarOperator&gt;
                      &lt;/Convert&gt;
                    &lt;/ScalarOperator&gt;
                  &lt;/DefinedValue&gt;
                &lt;/DefinedValues&gt;
                &lt;RelOp AvgRowSize="11" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.00986014"&gt;
                  &lt;OutputList&gt;
                    &lt;ColumnReference Column="Expr1020" /&gt;
                  &lt;/OutputList&gt;
                  &lt;RunTimeInformation&gt;
                    &lt;RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" /&gt;
                  &lt;/RunTimeInformation&gt;
                  &lt;StreamAggregate&gt;
                    &lt;DefinedValues&gt;
                      &lt;DefinedValue&gt;
                        &lt;ColumnReference Column="Expr1020" /&gt;
                        &lt;ScalarOperator ScalarString="Count(*)"&gt;
                          &lt;Aggregate AggType="countstar" Distinct="false" /&gt;
                        &lt;/ScalarOperator&gt;
                      &lt;/DefinedValue&gt;
                    &lt;/DefinedValues&gt;
                    &lt;RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.00985904"&gt;
                      &lt;OutputList /&gt;
                      &lt;RunTimeInformation&gt;
                        &lt;RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" /&gt;
                      &lt;/RunTimeInformation&gt;
                      &lt;StreamAggregate&gt;
                        &lt;DefinedValues /&gt;
                        &lt;GroupBy&gt;
                          &lt;ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" /&gt;
                        &lt;/GroupBy&gt;
                        &lt;RelOp AvgRowSize="11" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985804"&gt;
                          &lt;OutputList&gt;
                            &lt;ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" /&gt;
                          &lt;/OutputList&gt;
                          &lt;RunTimeInformation&gt;
                            &lt;RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" /&gt;
                          &lt;/RunTimeInformation&gt;
                          &lt;NestedLoops Optimized="false"&gt;
                            &lt;OuterReferences&gt;
                              &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" /&gt;
                            &lt;/OuterReferences&gt;
                            &lt;RelOp AvgRowSize="16" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038"&gt;
                              &lt;OutputList&gt;
                                &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" /&gt;
                              &lt;/OutputList&gt;
                              &lt;RunTimeInformation&gt;
                                &lt;RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" /&gt;
                              &lt;/RunTimeInformation&gt;
                              &lt;NestedLoops Optimized="false"&gt;
                                &lt;OuterReferences&gt;
                                  &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" /&gt;
                                  &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" /&gt;
                                &lt;/OuterReferences&gt;
                                &lt;RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="150002"&gt;
                                  &lt;OutputList&gt;
                                    &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" /&gt;
                                    &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" /&gt;
                                  &lt;/OutputList&gt;
                                  &lt;RunTimeInformation&gt;
                                    &lt;RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" /&gt;
                                  &lt;/RunTimeInformation&gt;
                                  &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"&gt;
                                    &lt;DefinedValues&gt;
                                      &lt;DefinedValue&gt;
                                        &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" /&gt;
                                      &lt;/DefinedValue&gt;
                                      &lt;DefinedValue&gt;
                                        &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" /&gt;
                                      &lt;/DefinedValue&gt;
                                    &lt;/DefinedValues&gt;
                                    &lt;Object Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Index="[SIX_T_pathXQUERY]" Filtered="true" Alias="[SomeText:1]" IndexKind="SecondarySelectiveXML" Storage="RowStore" /&gt;
                                    &lt;SeekPredicates&gt;
                                      &lt;SeekPredicateNew&gt;
                                        &lt;SeekKeys&gt;
                                          &lt;Prefix ScanType="EQ"&gt;
                                            &lt;RangeColumns&gt;
                                              &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pathXQUERY_1_value" /&gt;
                                            &lt;/RangeColumns&gt;
                                            &lt;RangeExpressions&gt;
                                              &lt;ScalarOperator ScalarString="N''bbb''"&gt;
                                                &lt;Const ConstValue="N''bbb''" /&gt;
                                              &lt;/ScalarOperator&gt;
                                            &lt;/RangeExpressions&gt;
                                          &lt;/Prefix&gt;
                                        &lt;/SeekKeys&gt;
                                      &lt;/SeekPredicateNew&gt;
                                    &lt;/SeekPredicates&gt;
                                  &lt;/IndexScan&gt;
                                &lt;/RelOp&gt;
                                &lt;RelOp AvgRowSize="461" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="150002"&gt;
                                  &lt;OutputList /&gt;
                                  &lt;RunTimeInformation&gt;
                                    &lt;RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" /&gt;
                                  &lt;/RunTimeInformation&gt;
                                  &lt;IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"&gt;
                                    &lt;DefinedValues /&gt;
                                    &lt;Object Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Index="[SIX_T]" Alias="[SomeText:1]" TableReferenceId="-1" IndexKind="SelectiveXML" Storage="RowStore" /&gt;
                                    &lt;SeekPredicates&gt;
                                      &lt;SeekPredicateNew&gt;
                                        &lt;SeekKeys&gt;
                                          &lt;Prefix ScanType="EQ"&gt;
                                            &lt;RangeColumns&gt;
                                              &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" /&gt;
                                              &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" /&gt;
                                            &lt;/RangeColumns&gt;
                                            &lt;RangeExpressions&gt;
                                              &lt;ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[pk1] as [SomeText:1].[pk1]"&gt;
                                                &lt;Identifier&gt;
                                                  &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" /&gt;
                                                &lt;/Identifier&gt;
                                              &lt;/ScalarOperator&gt;
                                              &lt;ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[row_id] as [SomeText:1].[row_id]"&gt;
                                                &lt;Identifier&gt;
                                                  &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" /&gt;
                                                &lt;/Identifier&gt;
                                              &lt;/ScalarOperator&gt;
                                            &lt;/RangeExpressions&gt;
                                          &lt;/Prefix&gt;
                                        &lt;/SeekKeys&gt;
                                      &lt;/SeekPredicateNew&gt;
                                    &lt;/SeekPredicates&gt;
                                    &lt;Predicate&gt;
                                      &lt;ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[path_1_id] as [SomeText:1].[path_1_id] IS NOT NULL"&gt;
                                        &lt;Logical Operation="IS NOT NULL"&gt;
                                          &lt;ScalarOperator&gt;
                                            &lt;Identifier&gt;
                                              &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="path_1_id" /&gt;
                                            &lt;/Identifier&gt;
                                          &lt;/ScalarOperator&gt;
                                        &lt;/Logical&gt;
                                      &lt;/ScalarOperator&gt;
                                    &lt;/Predicate&gt;
                                  &lt;/IndexScan&gt;
                                &lt;/RelOp&gt;
                              &lt;/NestedLoops&gt;
                            &lt;/RelOp&gt;
                            &lt;RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="200002"&gt;
                              &lt;OutputList&gt;
                                &lt;ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" /&gt;
                              &lt;/OutputList&gt;
                              &lt;RunTimeInformation&gt;
                                &lt;RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" /&gt;
                              &lt;/RunTimeInformation&gt;
                              &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"&gt;
                                &lt;DefinedValues&gt;
                                  &lt;DefinedValue&gt;
                                    &lt;ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" /&gt;
                                  &lt;/DefinedValue&gt;
                                &lt;/DefinedValues&gt;
                                &lt;Object Database="[adventureworks]" Schema="[dbo]" Table="[T]" Index="[PK__T__3214EC27EF7043C5]" Alias="[T]" IndexKind="Clustered" Storage="RowStore" /&gt;
                                &lt;SeekPredicates&gt;
                                  &lt;SeekPredicateNew&gt;
                                    &lt;SeekKeys&gt;
                                      &lt;Prefix ScanType="EQ"&gt;
                                        &lt;RangeColumns&gt;
                                          &lt;ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" /&gt;
                                        &lt;/RangeColumns&gt;
                                        &lt;RangeExpressions&gt;
                                          &lt;ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[pk1] as [SomeText:1].[pk1]"&gt;
                                            &lt;Identifier&gt;
                                              &lt;ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" /&gt;
                                            &lt;/Identifier&gt;
                                          &lt;/ScalarOperator&gt;
                                        &lt;/RangeExpressions&gt;
                                      &lt;/Prefix&gt;
                                    &lt;/SeekKeys&gt;
                                  &lt;/SeekPredicateNew&gt;
                                &lt;/SeekPredicates&gt;
                              &lt;/IndexScan&gt;
                            &lt;/RelOp&gt;
                          &lt;/NestedLoops&gt;
                        &lt;/RelOp&gt;
                      &lt;/StreamAggregate&gt;
                    &lt;/RelOp&gt;
                  &lt;/StreamAggregate&gt;
                &lt;/RelOp&gt;
              &lt;/ComputeScalar&gt;
            &lt;/RelOp&gt;
          &lt;/QueryPlan&gt;
        &lt;/StmtSimple&gt;
      &lt;/Statements&gt;
    &lt;/Batch&gt;
  &lt;/BatchSequence&gt;
&lt;/ShowPlanXML&gt;');