Estimated remaining time for a SPID

Question

Estimated remaining time for a SPID

suhas.wadadekar

Ten Centuries

Points: 1219
More actions
October 7, 2009 at 9:15 am

#136264

Hi,
Does anyone know how to determine the estimated time for completion of a query run.
I have manually run a stored procedure and expect it to take a long time.
However, it would be really handy to know how much (estimated) time the procedure will take to finish.
I am not looking for estimated plan of execution, estimated time for completion after the command has run.
Thanks,
Suhas.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Silverfox SSC-Insane Points: 23150 More actions · Answer 1

suhas.wadadekar (10/7/2009)
Hi,
Does anyone know how to determine the estimated time for completion of a query run.
I have manually run a stored procedure and expect it to take a long time.
However, it would be really handy to know how much (estimated) time the procedure will take to finish.
I am not looking for estimated plan of execution, estimated time for completion after the command has run.
Thanks,
Suhas.

So.., let me get this straight, you are running a query, it is part way through and you want to know how long it will take to complete. hard to say without knowing what it is doing, if it is manipulating data and depending how that is being handled, you could in theory run other queries to see how far the changes have been made. otherwise you could have changed the query to log every so often either to screen or other table.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

suhas.wadadekar Ten Centuries Points: 1219 More actions · Answer 2

You guessed it right.

I am already running a query and want to find the amount of time it will take to finish.

Thanks for the suggestions about logging at each step.

The situation here is I am running a procedure developed by someone earlier and I am using it for a particular data fetch now (I know the result set is going to be huge)

So I am just trying to get an idea about time remaining, and not looking to improve query performance or modify the procedure.

This is plain curiosity - I know there is something similar in Oracle.

Thanks again,

Suhas.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

A few functions, mainly system stuff like backups, will show up with estimated completion times in the sys.dm_exec_requests DMV. You can try it, but no guarantee it will work for you.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

To Grant's good point... estimated times for anything with a loop, some forms of recursion, and some functions will be surprising low while the actual time will be surprisingly high. This is because the estimates for those things are based on just one interation of each loop, recursion, or function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions