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:

After this, we will find out what is the id of the session of our query to use it later.

Once the previous steps have been completed, we can proceed to the creation of the index, the code should look like the following:

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.

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

Recommended Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.