Sql-server – DB Index Maintenance

azure-sql-databasesql server

I have a database which has almost 130 tables.

I am using the Ola Hallengren maintenance solution and IndexOptimize scripts.

I have two tables in particular that in my opinion needs to be better handled:

  • Table1: PK INT, almost 1.5 millions of rows
  • Table2: PK INT, foreign Key to Table 1, almost 6.5 millions of rows

On table2 I have created an index some time ago, following an azure recommendation, like this:

CREATE NONCLUSTERED INDEX [IX_Table2_ForeignKeyField] 
ON[dbo].[Table2] (
    [ForeignKeyField]
) 
INCLUDE([AnotherField]) 
WITH( ONLINE = ON )

When the IndexOptimize runs I can see that to rebuild the IX_Table2_ForeignKeyField index the stored procedure uses the following command

ALTER INDEX [IX_Table2_ForeignKeyField] 
ON [mydb].[dbo].[Table2] 
REORGANIZE 
WITH (LOB_COMPACTION = ON)

The results are that it takes more than two hours to reorganize this index (previous reorganization was done 3 days ago) and at the end it says that

<ExtendedInfo>
  <PageCount>644096</PageCount>
  <Fragmentation>0.752683</Fragmentation>
</ExtendedInfo>

Is there any procedure that I can use to make this command run faster?

EDIT: One more info: Table1 has from 500 to 2500 insert per day. On table2 I have almost 10k insert per day growing.

Best Answer

Index maintenance is an I/O intensive workload. Under these circumstances please consider to scale to premium tiers prior to run index maintenance tasks, and when the workload finishes scale down to previous tier. You can automate that using T-SQL. That should reduce maintenance time.

Additionally, compacting large objects data (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml) can save disk space but it can increase the time it takes to maintain indexes. If you have a restricted time window for reindexing you can set LOB_COMPACTION to OFF.

Hope this helps.

Related Question