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
Azure Data Engineer, apasionado del mundo de los datos y con más de 6 años de experiencia.
