SET STATISTICS TIME ON, run the SP and check the messages tab.

Alternatively this view sys.dm_exec_query_stats has elapsed time columns.

Well we were mainly moving to new structures so the migration was two part, moving recent data to a faster server and the older data shifted to cheaper archive storage....

Apologies, I am not sure what you are asking.

If it what action to take next, then it depends on the requirements of the business perhaps. Is the data that...

We have done similar things in the past, essentially using SSIS to archive/move data to a new server. On enterprise you could partition the data instead.

OK, SQL Agent is for jobs so non essential, or rather not related to this problem. SQL Browser would be the one to check. Can you post the...

How are you attempting to connect, SSMS? All services running? Can you connect with a sql login?

Is the original source database used purely for this reporting access or are there other production processes going on? If it is used for a bundle of things then...

Have you tried removing the index hint, just to guage any difference in performance?

What I would do, as suggested, is move all csv rows into a staging table, perform DELETE...

We have several data capture systems where we utilise transactional replication for reporting purposes. I used to think it was a little misuse of a high availability solution but...

Absolutely re the above.

I use the following query with getdate in there and schedule it once per hour (with a clear of the waits afterwards) to monitor peaks...

Might help...

If the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. For example, this...

I might be confused, is it the xml plans that come up in the results, or an execution plan of the query you posted?

MyDoggieJessie (3/13/2013)

---

Have you tried inserting a tracer token to verify where the delay is actually occurring?

You can also create custom alerts (in your SQL Server Agent >> Alerts) via the...

Presumably the plans are simply not in the cache any more. Can you re-run any of the queries and requery the DMV to see if it then appears?

Sound advice above and much better than the words I had scribbled down 🙂

I let the business needs determine the backup strategy to a point. For example, if a...