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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI 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?
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;
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