SQL Server 2019 Indexes

Hi again, everybody! Today we are going to see a feature of SQL Server 2019 regarding indexes. We will see how to use it and how it can be useful to us.

We start from a scenario of index creation either by optimization or maintenance of the environment. The index to be created presents a relevant size and, having derived performance issues, we decided create it immediately.

Working with Indices

The index begins to generate blocking problems after 1 hour of creation, so, without knowing how much is left to finish, we have to make the decision to stop it and start over at another time or… .. use the new clause “ SUMMARY ”.

CREATE
INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, RESUMABLE = ON);

This clause allows us to stop creation and return it when the environment is favorable.

To stop it we will execute the command

ALTER INDEX test_idx1 ON test_table PAUSE;
SQL Server 2019 Indexes

Also, running:

SELECT
name, percent_complete FROM sys.index_resumable_operations;

We can see the% that leads of creation.

Creation percentage

We can see that the index is paused.

********* DBA TIP ********

The new rows that are inserted into the table from that we stopped the creation will not be reflected in said %. Let’s see.

We insert twice the volume of data that the company already has table.

INSERT INTO dbo.t1 (a)
SELECT a from t1
Go

The creation of the index on the original table (10000000 rows) had a progress of 9.999%, when adding another 10000000 rows, we see that the% is not divided in half but rises by 20%

Index paused

More information on indexes at https://docs.microsoft.com/es-es/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15

So far today’s delivery friends! If you have any questions about how to manage your indexes or about another SQL Server question, more features of SQL Server 2019 in https://www.gpsos.es/2020/06/nuevas-funcionalidades-sql-server-2019 / , do not hesitate and contact us! Until next time!

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn