Hello there,

I need your advices/comments.

After in-place upgrade to SQL Server 2005, we've noticed some strange
query optimizer behavior when executing an INSERT SELECT query. There
were absolutely no performance problems with this query in MSSQL2k
(its execution time was close to 0.0 s). I must complain that SQL
Server 2005 needs about a minute to process it.

Actually that's not an issue yet.

I understand that query optimizer can use non-optimal execution plan
for so many reasons, there may be resource lock problems etc. but I'm
little confused when INSERT SELECT query is poorly optimized (1 minute
execution time) and SELECT query without INSERT INTO part is evaluated
almost as fast as a "SELECT 1" statement... The situation is always
repeatable - INSERT SELECT: 1 minute, SELECT only: ~0 seconds.

Problematic query (with table definitions) can be found here:
http://dev.guzowski.info/files/insert_select_issue.sql

Actual execution plans (for INSERT SELECT and SELECT) can be
downloaded from here:
http://dev.guzowski.info/files/insert_select.sqlplan
http://dev.guzowski.info/files/select_only.sqlplan

I'm interested in finding possible reason(s), not necessarily the
remedy (rewriting the query is not a difficult task). I can provide
additional information (server configuration etc.) if you would like
me to.

Thanks for all your comments
and
Merry Christmas!

Top 100 ... Order by in views SQL 2005 problem
Stored procedure to change not nulls to nulls
To write SPs, or not to write SPs, that is the question....
Default Values Question
Optimize query with many NOT IN