title: Right Index scan when used XML
body: <p>below is T-SQL generated by application</p>
  
  <pre><code>DECLARE @xml_0 XML
  SET @xml_0 = N'&lt;Val&gt;8e4cd3e3-de98-4f55-9c55-57881157a0f0&lt;/Val&gt;
                 &lt;Val&gt;2f483275-7333-4786-aca8-454e5bf4823f&lt;/Val&gt;
                 &lt;Val&gt;ce1ce763-1f68-48ec-bedf-f4641e40d8f8&lt;/Val&gt;
                 &lt;Val&gt;6b471d5e-fd5c-4db8-aa31-abb910651e18&lt;/Val&gt;
                 &lt;Val&gt;89064e42-0592-4845-b21e-38f788ab0d2e&lt;/Val&gt;
                 &lt;Val&gt;d54793f0-cbfb-428e-ba08-db70cab1af07&lt;/Val&gt;
                 &lt;Val&gt;8027e6bd-09e5-4a5b-aae7-54aff4a0e6c0&lt;/Val&gt;
                 &lt;Val&gt;53f1a5e3-b2a8-49c3-935b-a5ac7fe0c1d8&lt;/Val&gt;
                 &lt;Val&gt;faceabad-1d0c-4f3f-8d94-674bbf1c3428&lt;/Val&gt;
                 &lt;Val&gt;f8e0a43d-cff7-45aa-b73f-6858b1d17cd1&lt;/Val&gt;
                 &lt;Val&gt;94e9bc76-5bb3-4cf9-9b59-fc3163c904d7&lt;/Val&gt;
                 &lt;Val&gt;e4be8c69-5166-40cc-b49a-18adec78e356&lt;/Val&gt;
                 &lt;Val&gt;5c564b82-64e1-46c5-a41d-bc30104f14a5&lt;/Val&gt;
                 &lt;Val&gt;dc246c2c-7edd-407a-b378-747789bd5a75&lt;/Val&gt;
                 &lt;Val&gt;411ac1e9-3d4f-447c-808a-b82d388816dd&lt;/Val&gt;'
  
  SELECT COUNT(*) FROM 
  (
      SELECT [t0].[ID]
      FROM [dbo].[HM_Rows] AS [t0], [dbo].[HM_Cells] AS [t1]
      WHERE 
      (
          [t1].[Value] IN 
          (
              SELECT node.value('.', 'NVARCHAR(200)') FROM @xml_0.nodes('/Val') xml_0(node)
          )
      )
  ) AS [r2487772634]
  </code></pre>
  
  <p>here is execution plan of T-SQL above
  <img src="https://i.stack.imgur.com/ZhWHd.png" alt="Exec Plan"></p>
  
  <p>so it scans index 
  it scans correct indexes</p>
  
  <p>missing_index_FOR_Value_INC_RowID - on HM_Cells table
  and
  PK_HM_Rows - on HM_Rows table</p>
  
  <p>any idea?</p>
  
  <p>P.S tables are large
  Row counts
  HM_Rows - 17'736'181
  HM_Cells - 1'048'693'775
  AND YES i have rebuilded indexes and updated statistics
  HM_Cells.Value is NVarChar(200)</p>
  
  <p>also without XML and HM_Rows table it working fine
  e.g 
      SELECT ID FROM HM_Cells WHERE Value IN (.........) 
  works excellent</p>
  
  <p>Thanks a lot :)</p>