Why insert-select to variable table from XML varia

Question

I'm trying to insert some data from a XML document into a variable table. What blows my mind is that the same select-into (bulk) runs in no time while insert-select takes ages and holds SQL server process accountable for 100% CPU usage while the query executes.

I took a look at the execution plan and INDEED there's a difference. The insert-select adds an extra "Table spool" node even though it doesn't assign cost. The "Table Valued Function [XML Reader]" then gets 92%. With select-into, the two "Table Valued Function [XML Reader]" get 49% each.

Please explain "WHY is this happening" and "HOW to resolve this (elegantly)" as I can indeed bulk insert into a temporary table and then in turn insert into variable table, but that's just creepy.

I tried this on SQL 10.50.1600, 10.00.2531 with the same results

Here's a test case:

declare @xColumns xml
declare @columns table(name nvarchar(300))

if OBJECT_ID('tempdb.dbo.#columns') is not null drop table #columns

insert @columns select name from sys.all_columns

set @xColumns = (select name from @columns for xml path('columns'))

delete @columns

print 'XML data size: ' + cast(datalength(@xColumns) as varchar(30))

--raiserror('selecting', 10, 1) with nowait

--select ColumnNames.value('.', 'nvarchar(300)') name
--from @xColumns.nodes('/columns/name') T1(ColumnNames)

raiserror('selecting into #columns', 10, 1) with nowait

select ColumnNames.value('.', 'nvarchar(300)') name
into #columns
from @xColumns.nodes('/columns/name') T1(ColumnNames)

raiserror('inserting @columns', 10, 1) with nowait

insert @columns
select ColumnNames.value('.', 'nvarchar(300)') name
from @xColumns.nodes('/columns/name') T1(ColumnNames)

Thanks a bunch!!

Answer 1

This is a bug in SQL Server 2008. Use

insert @columns 
select ColumnNames.value('.', 'nvarchar(300)') name
from @xColumns.nodes('/columns/name') T1(ColumnNames)
OPTION (OPTIMIZE FOR ( @xColumns = NULL ))

This workaround is from an item on the Microsoft Connect Site which also mentions a hotfix for this Eager Spool / XML Reader issue is available (under traceflag 4130).

The reason for the performance regression is explained in a different connect item

The spool was introduced due to a general halloween protection logic (that is not needed for the XQuery expressions).

Answer 2

Looks to be an issue specific to SQL Server 2008. When I run the code in SQL Server 2005, both inserts run quickly and produce identical execution plans that start with the fragment shown below as Plan 1. In 2008, the first insert uses Plan 1 but the second insert produces Plan 2. The remainder of both plans beyond the fragment shown are identical.

Plan 1

Plan 2