How can I identify the INDEX a given query is using?
My proc runs in 15 min's on SQL 2000. We migrated to SQL 2008 now the same proc is running in 1 hour. As the DBA, I backed up the 2K DB and resoted it to the 2008 machine. I ran DBCC REINDEX, Updated the Stats, and Recompiled the procs. I activated my server side trace to dump the SQL activity and I can see the specific SQL Statement causing the problem. I attempted to tune it as well --- matching index col's to the predicates in the Join and Where clauses.
What I'd like to know, on SQL 2000, where can I look to see what index(s) is/are being used to satisfy this query? ( a hsow execution plan?)
BT