I've seen in Oracle 10g a feature that estimates the remaining time for a long running query and I was wondering if this is possible too in SQL Server (at least in 2008?)?
Suppose I have a very large table with tens of millions of rows (well indexed etc. etc.) and I need to search for some particular rows. I know it will take a lot of time and I'm cool with that but I would like to present the user with some kind of a progress bar.
How can I show progress?
I'm not aware of a tool that will do this automatically, but there are a couple of alternatives. Break your query into blocks ...
select blah from table where IdRange between (1 and 100000)
select blah from table where IdRange between (100001 and 200000)
as each sql completes so update the progress bar.
Or you could record the length of time taken for each of your selects, store those values maybe on a per user basis. Then use that information to return a progress bar length.
Both these approaches are pretty kludgy, hopefully someone knows a better approach.
Of course you could try to decipher the query plan and make a judgement based on that, but in code that would be hard.
I'd forget about it and just put a spinning circle!
Seriously though, to take MrTelly's idea further, there are dynamic management views that can give you average execution times for certain queries - maybe that can get you somewhere.