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 )
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.