Ahh, the old "add an index to avoid a table-scan update" solution! Good job! 😎

Boy, if I had a nickel for every one of these types of posts - or every time I have seen a CLIENT do this . . . :w00t:

get the complete and FREE maintenance suite at ola.hallengren.com!!! Can't say enough good things about this - and it does much more than just index mx.

NOTE: SQL 2011 (aka Denali) will require ENTERPRISE EDITION WINDOWS SERVER AND ENTERPRISE EDITION SQL SERVER to do clustering/mirroring!!! Pretty much sucks in my book and I and others...

variety of things could be at play here. I HIGHLY recommmend you engage a performance tuning consultant for a day or two to dig into what is really going...

1) yep - don't shrink

2) make the database big enough to contain 12-18 MONTHS of expected data growth (including indexing sizes)

3) you MUST update ALL statistics with a FULL SCAN...

evenly divisible is fine - although if you are on a SAN you need to take raid group striping sizes into account. Win2008+ now aligns on 1024K (at least...

CAST and CONVERT in books online. There are many examples there.

I STRONGLY STRONGLY encourage you to stop using the MDW and purchase a third party product for your server monitoring. MDW is NOT ready for prime time and will...

I recommend to my clients to ONLY use temporary tables unless they have one of the following situations: a) VERY high volume OLTP hits where they need to avoid recompilations...

DO NOT USE LINQ TO SQL, and avoid...

MAX DATE method has one downside though - you can get duplicate output if more than one record has the same MAX DATE. This should not happen with ROW_NUMBER()...

1) check the DMV for index usage to see if the nonclustered index is actually being used

2) you may also want to run a DETAILED index physical stats DMV check...

Additional comments:

1) I advise my clients to size a database to contain 12-18 months worth of data and evaluate monthly or at least quarterly.

2) Recall that the clustering key is...

one last thing - did you check the Recycle bin on the backup server just in case?