Slow running query

We recently got a call from one of our developers that they had a slow running application on one of the databases. It was a lot longer than usual and with this rate they were estimating nearly a day of processing as compared to their usual couple of hours.

First: I ran the sp_whoisactive (an awesome proc by Adam Machanic) and sp_who2 to see is there was any blocking or long running query on the server. After that was ruled out.
Second, I started the activity monitor for that server in SSMS. That showed the processor time and recent expensive queries. As was evident our query was in there.
Thirdly, I started the SQL profiler to trace and get a few paramters on running query. The reads, writes and the duration column in the profiler easily led us to the proc in the application that was running the slowest.
It also showed that this part of the application was being run very frequently as might be the processing need. Since the number of iterations * time taken was proving costly for it, we decided to tune that query.

Again back to the great sp_whoisactive @get_plans=2 (or @get_plans=1),
we were able to catch the exact execution plan for that proc. The query plan was doing a index scan where it was to do a clustered seek and key lookup. It had the necessary index, but did not look like using it. We looked at query plans in other databases and they looked like they had an index seek and key lookup, which made us sure of our assumption. The database needed updated statistics to generate the correct query plan.
We ran the sp_updatestats on that database and the duration for that part of the query came down from half a minute to 10 milliseconds, taking it back to its original time. The processing finished within a couple of hours.