Right Index scan when used XML

Question

below is T-SQL generated by application

DECLARE @xml_0 XML
SET @xml_0 = N'<Val>8e4cd3e3-de98-4f55-9c55-57881157a0f0</Val>
               <Val>2f483275-7333-4786-aca8-454e5bf4823f</Val>
               <Val>ce1ce763-1f68-48ec-bedf-f4641e40d8f8</Val>
               <Val>6b471d5e-fd5c-4db8-aa31-abb910651e18</Val>
               <Val>89064e42-0592-4845-b21e-38f788ab0d2e</Val>
               <Val>d54793f0-cbfb-428e-ba08-db70cab1af07</Val>
               <Val>8027e6bd-09e5-4a5b-aae7-54aff4a0e6c0</Val>
               <Val>53f1a5e3-b2a8-49c3-935b-a5ac7fe0c1d8</Val>
               <Val>faceabad-1d0c-4f3f-8d94-674bbf1c3428</Val>
               <Val>f8e0a43d-cff7-45aa-b73f-6858b1d17cd1</Val>
               <Val>94e9bc76-5bb3-4cf9-9b59-fc3163c904d7</Val>
               <Val>e4be8c69-5166-40cc-b49a-18adec78e356</Val>
               <Val>5c564b82-64e1-46c5-a41d-bc30104f14a5</Val>
               <Val>dc246c2c-7edd-407a-b378-747789bd5a75</Val>
               <Val>411ac1e9-3d4f-447c-808a-b82d388816dd</Val>'

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]

here is execution plan of T-SQL above

so it scans index it scans correct indexes

missing_index_FOR_Value_INC_RowID - on HM_Cells table and PK_HM_Rows - on HM_Rows table

any idea?

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)

also without XML and HM_Rows table it working fine e.g SELECT ID FROM HM_Cells WHERE Value IN (.........) works excellent

Thanks a lot :)

Answer 1

Try using a JOIN instead of an IN, because this way you can force a loop strategy, which will probably use a seek instead of a scan:

SELECT COUNT(*) FROM 
(
    SELECT [t0].[ID]
    FROM (
        SELECT DISTINCT node.value('.', 'NVARCHAR(200)') AS Val 
        FROM @xml_0.nodes('/Val') xml_0(node)
    ) q1 INNER LOOP JOIN [dbo].[HM_Cells] AS [t1] ON q1.Val=t1.Value
    CROSS JOIN [dbo].[HM_Rows] AS [t0]
) AS [r2487772634]