SQL Server: How do we track the progress of the CREATE INDEX command?

1.3K Asked by ankur_3579 in SQL Server , Asked on Apr 16, 2021

SQL Server 2014, Std Ed I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help. I currently use the method below, which at least shows me movement (that the index creation is not blocked). But I have no idea if I am 10% through the process or 99%. I tried the method described here: https://dba.stackexchange.com/a/102545/6229 but it shows a clearly wrong est completion time (it basically shows 'now' for a 60+ minute process that I am 10 min into) How can I get some help?

Answered by Claudine Tippins

I guess we can remove the @SPID variable with a reference to sys.dm_exec_requests: 

Example of SQL Server CREATE INDEX

The SQL statement shown below creates an index named "idx_lastname" on the "LastName" column in the "Persons" table:

However, if you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

Your Answer

Answer (1)

Ranjana

Tracking the progress of the CREATE INDEX command in SQL Server is important, especially when dealing with large tables, as the operation can take significant time and resources. SQL Server provides several ways to monitor the progress of CREATE INDEX operations:

  Using Dynamic Management Views (DMVs)sys.dm_exec_requests:

This DMV provides information about requests currently executing on SQL Server, including index creation operations.

  SELECT     session_id,    status,    command,    percent_complete,    start_time,    estimated_completion_time,    cpu_time,    total_elapsed_timeFROM     sys.dm_exec_requestsWHERE     command = 'CREATE INDEX';

The percent_complete column indicates how much of the operation has been completed.

sys.dm_exec_query_profiles:

This DMV can provide more detailed insights into the progress of long-running queries, including index creation.

  SELECT     session_id,    node_id,    physical_operator_name,    SUM(row_count) AS row_count,    SUM(estimated_row_count) AS estimated_row_countFROM     sys.dm_exec_query_profilesGROUP BY     session_id, node_id, physical_operator_name;sys.dm_tran_locks:

This DMV shows locks being held by current transactions, which can be useful to see if there are any locks related to the CREATE INDEX operation.

  SELECT     request_session_id,    resource_type,    resource_database_id,    resource_associated_entity_id,    request_mode,    request_statusFROM     sys.dm_tran_locksWHERE     request_session_id = [Your Session ID];Using SQL Server Management Studio (SSMS)Activity Monitor:

Open Activity Monitor by right-clicking on the server in Object Explorer and selecting "Activity Monitor."

In the Processes pane, look for the session running the CREATE INDEX command. You can see details such as CPU usage, I/O, and status.

  SQL Server Profiler:Use SQL Server Profiler to trace the progress of the CREATE INDEX operation. Look for events related to SQL, SQL, and Progress Report events.Using Extended EventsCreate a Session to Track Index Creation:sqlCopy codeCREATE EVENT SESSION CreateIndexTrackingON SERVERADD EVENT sqlserver.progress_report_online_index_operation(    ACTION (sqlserver.session_id, sqlserver.sql_text))ADD TARGET package0.event_file&#40;    SET filename = 'C:TempCreateIndexTracking.xel'&#41;;Start the Session:sqlCopy codeALTER EVENT SESSION CreateIndexTracking ON SERVER STATE = START;Query the Event Data:sqlCopy codeSELECT    event_data.value('(event/@timestamp)[1]', 'datetime2') AS timestamp,    event_data.value('(event/action[@name="session_id"])[1]', 'int') AS session_id,    event_data.value('(event/data[@name="physical_operator_name"])[1]', 'nvarchar(max)') AS physical_operator_name,    event_data.value('(event/data[@name="percent_complete"])[1]', 'float') AS percent_completeFROM     sys.fn_xe_file_target_read_file&#40;'C:TempCreateIndexTracking*.xel', NULL, NULL, NULL&#41;     AS event_data;

By using these methods, you can effectively track the progress of the CREATE INDEX command in SQL Server, helping you to manage resources and anticipate completion times.

4 Months