What you're proposing isn't an in-place upgrade. You're saying that you want to install 2005 on the same server as 2000 and them migrate the databases over. An in-place upgrade...

Using the ONLINE rebuild will reduce locking & contention but will increase tempdb use, so be read for that.

I wouldn't get too locked into particular time frames. It reall depends...

Since we've gone through two year searches in the past for DBA's, I sure can't tell you how to find them. While we weren't trying to get them to know...

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...