Unexplained query plan differences between two near identical queries

• Tim Januario-145496

  Ten Centuries

  Points: 1391

  More actions

  March 5, 2013 at 9:25 am

  #274874

  All:

  I have noticed some behavior that I can't explain with sql similar to the following. It is reproducible on multiple machines in our enterprise so isn't machine specific. Basically by moving the insert statement to put data into a temp table outside of dynamic sql vs. inside the dynamic sql, we are seeing completely different query plans and performance which seems ... strange.

  It seems that this is a scope issue but I'm curious if somebody has a more specific explanation of what we are seeing.

  My example:

  CREATE #table (myFoo varchar(10) )

  DECLARE @sql NVARCHAR(MAX) = '

  INSERT INTO #table

  SELECT foo FROM bar

  '

  EXEC (@sql)

  Results in:

  SQL Server Execution Times:

  CPU time = 858 ms, elapsed time = 862 ms.

  VS:

  CREATE #table (myFoo varchar(10) )

  DECLARE @sql NVARCHAR(MAX) = '

  SELECT foo FROM bar

  '

  INSERT INTO #table

  EXEC (@sql)

  Results in:

  SQL Server Execution Times:

  CPU time = 47 ms, elapsed time = 51 ms. and a much better and different plan

  Can somebody please explain what is happening?

  Thanks,

  Tim Januario