SQL Server 2014 - How to Estimate Progress and How Much Time to Create an Index

Views: 1.085
Reading Time: 3 minutes

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:
  • Instance Level: To enable monitoring on all instance sessions, you can enable traceflag 7412 by using the command below:

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:

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

Taking up 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