Is there any way/tool to identify estimate query run time in SQL sERVER

Question

I have been google about this for a while..is there any way to identify the estimated query execution time>

There are actual execution plan and estimated execution plan on the ssms .The thing is none of these have estimated time.

Is it something lacking in the Sql Server?

Solution 1

Currently, no. Microsoft is currently researching ways to do this using a combination of work already completed and an estimated execution plan (See the details of their research on the Microsoft Research site), so we can expect to see something soon. But this is the only development that I am aware of.

The solution I've used with the most success in the past, for processes that take a lot of time, is to break the process up into smaller tasks, and set milestones at the end of each task. The total time for all executions of each task is recorded, and this is used to benchmark the progress of the current execution. This depends heavily on linearity of your queries (i.e. the time taken to execute is linearly proportional to the number of records). Milestones can either be measured by steps through a process, by breaking the data into smaller segments, or both.

Solution 2

SQL Server doesn't estimate execution time.

Estimated query cost used to be misleadingly equated in the documentation as estimated time in seconds (for features such as query governor ) but it is really just a unitless value.

Solution 3

The only way you can estimate query execution time by actually running the query. And even after that if may differ next time as it depends on lots of factors like how busy is your server, or many processes are trying to access that table or the quantity of data you are trying to access.

Comments

[Marc B]

estimating time would mean that the DB engine can know in advance what the results are, meaning it'd have to basically run the query anyways.

[user1254579]

I heard that databses like Teradata can show the estimated time to run a query..does that facility available in sql server

[Navin]

Does not answer the question. Of course the actual time changes. We are looking for an estimate that is close on average. Or at least a metric for how expensive a query will be.