Get Live Index Creation Percentage in SQL Server
In our day to day as DBA we must create indexes to improve the performance of the queries launched against the production tables. A necessary tool would be to know what percentage of creation our index has.
In many cases, we do not have an SQL Server Enterprise version and, we cannot create “online” these indexes. What is the problem? Blocking the table on which the index creation is being performed.
How many times are you creating an index in SQL Server, blocking an important table and doubting how long it will take? Or … Is it worth canceling the creation of the index? And if it’s almost done?
SQL Server does not directly provide us the information on the percentage of index creation, but we have the solution.
The first action will be to open a new query where we will activate the profile using the following code:
SET STATISTICS PROFILE ON
After this, we will find out what is the id of the session of our query to use it later.
SELECT @@SPID as Session_id
Once the previous steps have been completed, we can proceed to the creation of the index, the code should look like the following:
SET STATISTICS PROFILE ON GO SELECT @@SPID as Session_id GO USE [database] GO CREATE NONCLUSTERED INDEX [index_name] ON [table_name] ( [field1] ASC, [field2] ASC, [fieldN] ASC ) Go
Let’s go with the important part of the post, discover the creation percentage and the estimated time left for this to be completed, we only have to execute it in a new query (window) while the index is being created.
IMPORTANT: Modify the @session_id variable with the value obtained previously.
-- Percentage index creation by M.Angel Motos @aleson-itc email@example.com -- Declare @session_id to set session_id of index creation DECLARE @session_id AS int SET @session_id = 72 -- cte to simplify the code ;WITH tempdb_cte (node_id, Time_Taken) AS ( SELECT node_id, (DATEDIFF(SECOND, DATEADD(SECOND, - 3610, DATEADD(MILLISECOND, eqp.last_active_time % 1000, DATEADD(SECOND, eqp.last_active_time / 1000, ( SELECT create_date FROM sys.databases WHERE NAME = 'tempdb' )))), DATEADD(SECOND, - 3610, DATEADD(MILLISECOND, eqp.first_active_time % 1000, DATEADD(SECOND, first_active_time / 1000, ( SELECT create_date FROM sys.databases WHERE NAME = 'tempdb' ))))) * - 1 ) AS Time_Taken FROM sys.dm_exec_query_profiles AS eqp ) -- Begin the Query SELECT eqp.Node_Id, eqp.Physical_Operator_Name, SUM(eqp.row_count) Row_Count, SUM(eqp.estimate_row_count) AS Estimate_Row_Count, CAST(SUM(eqp.row_count)*100 AS float)/SUM(eqp.estimate_row_count) AS Estimate_Percent_Complete, CASE WHEN eqp.node_id = 2 THEN (SELECT CAST(SUM(eqp.row_count)*50 AS float)/SUM(eqp.estimate_row_count)) ELSE CASE WHEN eqp.row_count = 0 THEN 0 ELSE (SELECT 50+CAST(SUM(eqp.row_count)*50 AS float)/SUM(eqp.estimate_row_count)) END END AS 'Total_Percent_Complete', CASE WHEN eqp.row_count != 0 and node_id = 2 THEN ( SELECT Time_Taken FROM tempdb_cte WHERE node_id = 2) WHEN eqp.row_count != 0 AND node_id = 1 THEN ( SELECT Time_Taken FROM tempdb_cte WHERE node_id = 1) - ( SELECT Time_Taken FROM tempdb_cte WHERE node_id = 2) ELSE NULL END as Time_Taken, CASE WHEN eqp.row_count != 0 AND node_id = 1 THEN ROUND(((( SELECT Time_Taken FROM tempdb_cte WHERE node_id = 1) - ( SELECT Time_Taken FROM tempdb_cte WHERE node_id = 2) )* 100) / ((SELECT CAST(SUM(eqp.row_count)*100 AS float)/SUM(eqp.estimate_row_count) FROM sys.dm_exec_query_profiles AS eqp WHERE node_id = 1)),2,1) WHEN eqp.row_count != 0 AND node_id = 2 THEN ROUND(((SELECT Time_Taken FROM tempdb_cte WHERE node_id = 2) *100) / (CAST(SUM(eqp.row_count)*100 AS float) /SUM(eqp.estimate_row_count)),2,1) ELSE NULL END AS Estimate_Time_Taken FROM sys.dm_exec_query_profiles AS eqp WHERE session_id = @session_id GROUP BY node_id,physical_operator_name,row_count,estimate_row_count,last_active_time,first_active_time ORDER BY node_id DESC;
The execution of this code will show us something like this:
Where we can see the operation that is carried out, in order of appearance: the rows that have been analyzed, the total rows of the table, the percentage of creation of the step in real life, the total percentage of creation, the time we have spent and the estimated missing time.
When we execute the query again, the times will be updated.
Once the first step is over, the elapsed time will start again from zero in the next step and the creation percentage will be updated too with the estimated time for this to end. We can see it in the following example of the creation of the same index having completed the first step in 57 seconds and taking 13 seconds of the second step:
When the creation percentage of the second step reaches 100%, the index will have been created successfully.
If you are considering migrating your platform to the cloud we have the necessary experience to help you, contact us at firstname.lastname@example.org or call us at +34 962 681 242
Business Intelligence Analyst, trabajando principalmente con plataforma de datos Microsoft, SQL Server, SSIS, Powerapps y Azure Data Factory.