This is an INCREDIBLY COMMON misunderstanding I see at clients all the time. You simply CANNOT rely on ORDER of data unless it is EXPLICITLY stated with an ORDER...

please read this post (http://www.sqlservercentral.com/articles/SQLServerCentral/66909/) and then reply to this thread with a create table statement, insert statements with your sample data and then formatted output of the desired output....

Lynn Pettis (4/3/2013)

---

Actually, the TVF being used is an...

refactor your TVF to be an INLINE TVF and use that. Also push that SELECT ...(SELECT...) out like previous poster did.

Hierarchical data is very difficult in relational structures. If you can refactor the table you might get a win from using the built-in hierarchy datatype, which is CLR based...

1) a picture of the query plan is NOT the same thing as the ACTUAL query plan. Please post that. Also, since it is failing probably need to...

That isn't an article, that is the actual BOL entry for MERGE. It has examples (including one that is specifically for your solution). However, I STRONGLY recommend you...

Are you on a 32 bit sql server install by any chance?

EL_oh_EL (3/27/2013)

---

Thanks! what i am trying to accomplish is to update the stats since they are not being kept up. I have thought about rebuilding the indexes since...

1) ALWAYS USE BOL!!! It is an AWESOME resource. I NEVER have it closed, and I have been working with SQL Server for 15+ years! 🙂

2) Your sample...

it is always best to provide table scripts, sample data and expected outcomes.

BTW, did you review MERGE in Books Online? There are some VERY good examples there to guide...

A) does DBCC memorystatus reveal anything?

B) review the SQL and windows error logs (especially around startup) for anything unusual

C) review hyperV and host config, especially everything related to memory (dynamic...

Given that profiler shows no queries with large numbers of reads took away my normal response.

Complete guesses here:

1) someone accidentally put windows/sqlserver 32 bit on the system (yes,...

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Note there are 2 additional parts to this blog series.

also see this:

http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx