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.