El SQL Server té instruccions de manteniment que a vegades triguen molt (per exemple ALTER INDEX ... REORGANIZE), i clar, apart d'un cercle que dóna voltes hi ha alguna forma de veure com evoluciona? I un cop es veu com evoluciona amb un Excel es poden fer 4 càlculs per saber quan acabarà.

La instrucció és aquesta:

SELECT percent_complete FROM sys.dm_exec_requests WHERE session_id = ZZ

Per veure les consultes en curs i obtenir el session_id que ens interessa es pot fer servir la consulta:

SELECT 
	   (SELECT TEXT FROM SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE)) AS CONSULTA,
	   S.SESSION_ID AS [ID SESIÓN], 
	   S.LOGIN_NAME AS [USUARIO],   
	   ISNULL(DB_NAME(R.DATABASE_ID), N'') AS [BASE DE DATOS], 
	   ISNULL(T.TASK_STATE, N'') AS ESTADO, 
	   ISNULL(R.COMMAND, N'') AS COMANDO, 
	   ISNULL(S.PROGRAM_NAME, N'') AS APLICACIÓN,
	   ISNULL(R.OPEN_TRANSACTION_COUNT, 0) AS TRANSACCIONES,  
	   ISNULL(CONVERT (VARCHAR, W.BLOCKING_SESSION_ID), '') AS [BLOQUEADO POR],
	   ISNULL(W.WAIT_TYPE, N'') AS [TIPO ESPERA],
	   W.WAIT_DURATION_MS AS [TIEMPO ESPERA MS],
	   S.LOGIN_TIME AS [INICIO SESIÓN]
	FROM SYS.DM_EXEC_SESSIONS AS S 
	LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS AS C 
		ON (S.SESSION_ID = C.SESSION_ID)
	LEFT OUTER JOIN SYS.DM_EXEC_REQUESTS AS R 
		ON (S.SESSION_ID = R.SESSION_ID)
	LEFT OUTER JOIN SYS.DM_OS_TASKS AS T 
		ON (R.SESSION_ID = T.SESSION_ID 
			AND 
			R.REQUEST_ID = T.REQUEST_ID)
	LEFT OUTER JOIN 
	(
    
		SELECT *, 
		ROW_NUMBER() OVER (PARTITION BY WAITING_TASK_ADDRESS ORDER BY WAIT_DURATION_MS DESC) AS ROW_NUM
		FROM SYS.DM_OS_WAITING_TASKS 

	) AS W ON (T.TASK_ADDRESS = W.WAITING_TASK_ADDRESS) AND W.ROW_NUM = 1

	LEFT OUTER JOIN SYS.DM_EXEC_REQUESTS AS R2 ON (R.SESSION_ID = R2.BLOCKING_SESSION_ID)
	OUTER APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) AS ST

	WHERE S.SESSION_ID > 50 AND S.SESSION_ID NOT IN (@@SPID) AND T.TASK_STATE IS NOT NULL
	ORDER BY S.SESSION_ID;