I have yet to find a mechanism for reading the missing indexes DMV's and then connecting it to actual queries and query plans. Instead, I've used the fact that the...

OK. Forget the DTC. I'm just a moron.

However, size may be a factor. From the documentation:

Some Transact-SQL statements are not cached, such as bulk operation statements or statements containing string...

Does this proc include the use of DTC or is it called such that it uses DTC or extended procedures? I'm thinking it might be that the execution plan is...

Estimated costs are not always the best measures. However, a clustered index scan is the same thing as a table scan. So you need to adjust the query to make...

I didn't want bring up the cursor and add to your misery.

Have your core questions regarding the index scans & key lookups been answered for the moment?

Actually, if it's still in sys.dm_exec_sql_text and in sys.dm_exec_query_stats, then it's still in cache. It hasn't aged out yet. If it were not in cache, then not only would you...

Do you have the actual execution plan? Zip it up & post it.

I agree with Jack. In most circumstances, the cost of the FK is well worth the benefits of the FK. In exceedingly rare circumstances you can have the kind of...

Largely... What Gail said.

The only way you can get the actual execution plan on the fly is to use a trace.

I've looked through the query, but I haven't read all of it (there's a lot going on there). In general, your queries seem to be structured generally OK (the one's...

Sounds like a one to many relationship.

Use the primary key of the main table that links to X number of rows in the second table. So for your array sitation:

MainTable

MainTableId...

To my knowledge... No, this won't change performance. Let's also add a clustered index to the table. Assuming it's the same clustered index before and after the alter, all the...

How long since the last execution did you query the dmv's? If it's hours or minutes, you may have a serious memory issue. If it's days or weeks... not at...

Just hit the microsoft documentation. There's tons of information available there.

The error is very straight forward. You've got duplicate data. The key is to identify what constraints you have and then break down the procedure and capture the data output...