What could be causing this very large constant scan?
There is a primary XML index on the xml column being queried.
There are absolutely no literal constants in the query, and only about 5k rows of actual data in the source table.
create table T ( ID int identity primary key, XMLCol xml not null ) create primary xml index IX_T on T(XMLCol) go insert into T(XMLCol) select '<root><item '+ ( select ' Name'+cast(number as varchar(10))+'="1"' from master..spt_values where type = 'P' and number between 1 and 200 -- 200 attributes for xml path('') )+ ' /></root>' from master..spt_values where type = 'P' and number between 1 and 100 -- 100 rows
A query that fetches two attributes from T
select I.N.value('@Name1', 'varchar(10)'), I.N.value('@Name2', 'varchar(10)') from T cross apply T.XMLCol.nodes('/root/item') as I(N)
will give you a query plan that uses a Clustered Index Scan on the internal table for the nodes() part and a Clustered Index Seek to get the values from the attributes with a predicate on the seek to get the nodes you want.
That will hold true until you specify 16 or more attributes. For more than 16 attributes but less than 65 the predicate on the seek is moved out to a filter operator of its own.
When you query 65 or more attributes in the query you will get the nested loop join to the constant scan.
All above is true when you query against a table that has a fair amount of data and the attributes actually has values. With less data in the table you will get different query plans.
Here is a stored procedure you can use to test the threshold values if you like.
create procedure QueryT @NumAtt int as declare @S nvarchar(max) set @S = 'select '+stuff((select ',I.N.value(''@Name'+cast(number as varchar(10))+''', ''varchar(10)'')' from master..spt_values where type = 'P' and number between 1 and @NumAtt for xml path('')), 1, 1, '')+' from T cross apply T.XMLCol.nodes(''/root/item'') as I(N)' exec (@S) go exec QueryT 15 exec QueryT 16 exec QueryT 65
Query plans above is generated with SQL Server 2012
I guess this answers your question but sadly it will not help you improve the performance of your query.