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
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:
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;
answered May 12 '17 at 16:00
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
