Hey guys!
In this article, I would like to share with you a very simple, but very interesting script that helps you estimate the progress of creating an index through the sys.dm_exec_query_profiles DMV, available from SQL Server 2014.
If you would like to estimate progress and how much time is left to finish your BACKUP, RESTORE, or DBCC command, take a look at my article. How to estimate how much time is left to finish the backup on SQL Server?.
From SQL Server 2017, you can REBUILD indexes with the RESUMABLE = ON parameter, and you can pause and resume this operation and track the progress of this process using the sys.index_resumable_operations DMV. To learn more about this feature, read my article. SQL Server 2017 - How to Pause Rebuild an Index Using Resumable Online Index Rebuilds, remembering that from SQL Server 2019, you can even create indexes using the RESUMABLE parameter, as I commented in the article SQL Server 2019 - News and Features List.
How to estimate the progress of index creation
Given the above messages, let's now find out how to estimate the progress of creating an index in SQL Server 2014 onwards, using the sys.dm_exec_query_profiles DMV and the row_count and estimate_row_count (Live Query Statistics base view) columns, which are generated based on Bank statistics, which in order to generate an estimated value close to the real, should always be as current as possible.
To be able to monitor the progress of index creation, you must use one of two ways below:
- Session Level (Recommended): To enable monitoring in a given session, simply use the SET STATISTICS PROFILE ON command and execute the CREATE INDEX command, as follows:
1234567891011121314USE [dirceuresende]GO-- Ativa o recurso de monitoramento nesta sessãoSET STATISTICS PROFILE ONGO-- Cria o índice normalmenteCREATE NONCLUSTERED INDEX SK01_Senhas ON dbo.Senhas(Senha) WITH(DATA_COMPRESSION=PAGE)GO-- Desativa o recurso de monitoramento nesta sessãoSET STATISTICS PROFILE OFFGO
- Instance Level: To enable monitoring on all instance sessions, you can enable traceflag 7412 by using the command below:
12DBCC TRACEON (7412, -1);GO
Now that we have enabled monitoring on the session that is running the CREATE INDEX command, let's start tracking how this index builds, which can take a long time depending on the size of the table. To facilitate your analysis, I will make available the script below, already consolidated with some very interesting additional information:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
;WITH dadosSessao AS ( SELECT B.session_id, B.login_time, B.[host_name], B.[program_name], B.nt_user_name, B.original_login_name, D.[text], MIN(C.start_time) AS start_time, SUM(A.[row_count]) AS Qt_Linhas_Processadas, SUM(A.[estimate_row_count]) AS [Qt_Linhas_Total], MAX(A.last_active_time) - MIN(A.first_active_time) AS [Qt_Tempo_Decorrido_MS], MAX(IIF(A.[close_time] = 0 AND A.[first_row_time] > 0, [physical_operator_name], N'<Transition>')) AS [Ds_Operador_Atual] FROM sys.dm_exec_query_profiles A JOIN sys.dm_exec_sessions B ON B.session_id = A.session_id JOIN sys.dm_exec_requests AS C WITH (NOLOCK) ON B.session_id = C.session_id CROSS APPLY sys.dm_exec_sql_text(C.[sql_handle]) D WHERE C.command = 'CREATE INDEX' GROUP BY B.session_id, B.login_time, B.[host_name], B.[program_name], B.nt_user_name, B.original_login_name, D.[text] ), contabilizacao AS ( SELECT *, ( [Qt_Linhas_Total] - Qt_Linhas_Processadas ) AS [Qt_Linhas_Restantes], ( [Qt_Tempo_Decorrido_MS] / 1000.0 ) AS [Qt_Segundos_Decorridos] FROM dadosSessao ) SELECT session_id, start_time, [text], [Ds_Operador_Atual], [Qt_Linhas_Total], Qt_Linhas_Processadas, [Qt_Linhas_Restantes], CONVERT(DECIMAL(5, 2), (( Qt_Linhas_Processadas * 1.0 ) / [Qt_Linhas_Total] ) * 100) AS [Vl_Percentual_Completado], [Qt_Segundos_Decorridos], (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ) AS [Qt_Segundos_Restantes], DATEADD(SECOND, (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ), GETDATE()) AS [Dt_Prevista], [host_name], [program_name], nt_user_name, original_login_name FROM contabilizacao |
Result:
Well guys, I hope you enjoyed this post, which is a really cool tip to use in your daily life. Who has never needed to create an urgent index in production and was agonized over the delay in creating this index, without having a clue how long it was left? LOL
References
- https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-profiles-transact-sql?view=sql-server-2017
- https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/12/22/create-index-monitoring-progress/
- https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Utilizando-a-e2600ca6
- https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command
I started to use here in the company, think about suffering before knowing this routine
Kkkkkkkkkk I'm glad to hear that you liked it and are being helpful in your daily life
Nice! Is 1-Dirceu estimated with query normal tmb? If so, how? 2-Other questions, which you suggest to monitor the execution of jobs other than SQL agent. One that is more detailed. Some external program. I ask that because my environment has many jobs. 3-Do you have other blogs in your national or international style that you recommend too? Thanks!