Bairagi From Bapatla Posted August 19, 2015 Report Posted August 19, 2015 for example oka index create chesthunam on a large table Entha percentage complete ayyindi and estimated completion time details ela thelusukovachu? is there any dmv or any query to get that info?
Sambadu Posted August 19, 2015 Report Posted August 19, 2015 select percent_complete,command from sys.dm_exec_requests where command = 'CREATE INDEX'
Suhaas Posted August 19, 2015 Report Posted August 19, 2015 SELECT R.session_id, R.percent_complete, R.total_elapsed_time/1000 AS elapsed_secs, R.wait_type, R.wait_time, R.last_wait_type, DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) estim_completion_time, ST.text, SUBSTRING(ST.text, R.statement_start_offset / 2, ( CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text) ELSE R.statement_end_offset END - R.statement_start_offset ) / 2 ) AS statement_executing FROM sys.dm_exec_requests R CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST WHERE R.percent_complete > 0 AND R.session_id <> @@spid OPTION(RECOMPILE);
Raja123 Posted August 19, 2015 Report Posted August 19, 2015 SELECT R.session_id, R.percent_complete, R.total_elapsed_time/1000 AS elapsed_secs, R.wait_type, R.wait_time, R.last_wait_type, DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) estim_completion_time, ST.text, SUBSTRING(ST.text, R.statement_start_offset / 2, ( CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text) ELSE R.statement_end_offset END - R.statement_start_offset ) / 2 ) AS statement_executing FROM sys.dm_exec_requests R CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST WHERE R.percent_complete > 0 AND R.session_id <> @@spid OPTION(RECOMPILE);
Sambadu Posted August 19, 2015 Report Posted August 19, 2015 SELECT R.session_id, R.percent_complete, R.total_elapsed_time/1000 AS elapsed_secs, R.wait_type, R.wait_time, R.last_wait_type, DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) estim_completion_time, ST.text, SUBSTRING(ST.text, R.statement_start_offset / 2, ( CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text) ELSE R.statement_end_offset END - R.statement_start_offset ) / 2 ) AS statement_executing FROM sys.dm_exec_requests R CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST WHERE R.percent_complete > 0 AND R.session_id <> @@spid OPTION(RECOMPILE); nenu chepindi kuda ade sys.dm_exec_request table.. no need this level of big query..
Suhaas Posted August 19, 2015 Report Posted August 19, 2015 nenu chepindi kuda ade sys.dm_exec_request table.. no need this level of big query.. Ok. Estimated completion time ledu kada nuvvu cheppina danlo add that as well.
pjung88 Posted August 19, 2015 Report Posted August 19, 2015 for example oka index create chesthunam on a large table Entha percentage complete ayyindi and estimated completion time details ela thelusukovachu? is there any dmv or any query to get that info? Insteaf of dmv try adam machanic's sp_whoisactive...it will give you full picture on how much is done, what is it waiting on , cpu/reads etc... Google for it...
Recommended Posts