1

I have executed a stored procedure in SQL Server but it took a very long time to complete.

Any SQL queries I can run to get estimated completion time?

  • If the proc has never been run then no, there are no queries to get the estimated completion time on a proc. It depends on too many factors, many of which have nothing to do with the execution plan (PC setup, Server setup, memory, CPU's etc). Run it once and that will be your estimated run-time for next time (mot not necessarily). – user122754 May 12 '17 at 16:00
3

You can get the average run time since last restart, last run time, shortest run time and longest run time among all run. One catch here is the plan still have to reside in your plan cache.

total_elapsed_time

Total elapsed time, in microseconds, for completed executions of this stored procedure.

last_elapsed_time

Elapsed time, in microseconds, for the most recently completed execution of this stored procedure.

min_elapsed_time

Minimum elapsed time, in microseconds, for any completed execution of this stored procedure.

max_elapsed_time

Maximum elapsed time, in microseconds, for any completed execution of this stored procedure.

You can also change ORDER BY clause to order it by read, write etc.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',   
    d.cached_time, d.last_execution_time, d.total_elapsed_time,  
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],  
    d.last_elapsed_time, d.execution_count  
FROM sys.dm_exec_procedure_stats AS d  
ORDER BY [total_worker_time] DESC; 
0

Depending on what the proc does, and if you're on 2014 and up you can try to utilize Live Query Statistics, they aren't always perfect, but it does give you a good idea of progress.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.