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;
Also, running:
SELECT name, percent_complete FROM sys.index_resumable_operations;
We can see the% that leads of creation.
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%
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