Estimated stored procedures time

Question

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?

Answer 1

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; 

Answer 2

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