5

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?

Improve this question
2
  • If the table (one) is well indexed and you are looking for "some" particular rows (I mean few) It has to return rows immediatly. Anyway, nice question! ;-) Commented Mar 5, 2009 at 23:52
  • 1
    even with good indexes in DataWarehouses some report generating queries can take well above 1 minute. Commented Mar 6, 2009 at 0:11

3 Answers 3

Reset to default
5

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.

http://msdn.microsoft.com/en-us/library/ms188754.aspx

edit: I have seen percent complete in the sp_whoisactive procedure from Adam Machanic. Maybe that is another place to look into. SQL 2016 has the query store which persists plan cache information - a substitute for the dmv plan cache, which is cleared on reboot.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Great idea. You could get the estimated query plan based on the user's query, then check the plan cache to see if that plan already exists, and how long the average duration is. It's messy, and it'll add load to the server, but it would work.
2

Related:

Today’s database systems provide little feedback to the user/DBA on how much of a SQL query’s execution has been completed. For long running queries, such feedback can be very useful, for example, to help decide whether the query should be terminated or allowed to run to completion. Although the above requirement is easy to express, developing a robust indicator of progress for query execution is challenging. In this paper, we study the above problem and present techniques that can form the basis for effective progress estimation. The results of experimentally validating our techniques in Microsoft SQL Server are promising.

Improve this answer

Comments

1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.