Category: SQL Server

by Miguel Ángel Motos 2020-02-20

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 

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   m.motos@aleson-itc.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 info@aleson-itc.com or call us at +34 962 681 242