Sql-server – Improve performance of index reorganize on volatile table

This is occurring on SQL Server 2016 (Enterprise).
Example Table structure:

CREATE TABLE [dbo].[Foo](
    [FooId] [bigint] IDENTITY(1,1) NOT NULL,
    [FirstFkId] [bigint] NULL,
    [SecondFkId] [bigint] NULL,
    [ThirdFkId] [bigint] NULL,
    [FourthFkId] [bigint] NOT NULL,
    [ParentId] [bigint] NULL,
    [Flag1] [bit] NOT NULL,
    [Flag2] [bit] NOT NULL,
    [From] [datetime2](7) NOT NULL,
    [Until] [datetime2](7) NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [ChangedBy] [int] NOT NULL,
    [ChangedOn] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
(
    [FooId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[FooDetail](
    [FooDetailId] [bigint] IDENTITY(1,1) NOT NULL,
    [FooId] [bigint] NOT NULL,
    [FirstFkId] [bigint] NOT NULL,
    [SecondFkId] [bigint] NULL,
    [Column1] [bigint] NOT NULL,
    [Column2] [nvarchar](250) NULL,
    [Column3] [nvarchar](250) NULL,
    [Column4] [datetime2](7) NULL,
    [Column5] [datetime2](7) NULL,
    [Column6] [datetime2](7) NULL,
    [Column7] [datetime2](7) NULL,
    [Column8] [datetime2](7) NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [ChangedBy] [int] NOT NULL,
    [ChangedOn] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_FooDetail] PRIMARY KEY CLUSTERED 
(
    [FooDetailId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[FooDetail]  WITH CHECK ADD  CONSTRAINT [FK_FooDetail_Foo] FOREIGN KEY([FooId])
REFERENCES [dbo].[Foo] ([FooId])

This tables have data from a sliding window. There is a process that insert into the tables for a month every day and a different process that deletes older data. Users can insert/update rows.

Both tables have around 125.000 rows.

The data is read like this:

SELECT -- columns
FROM Foo f
INNER JOIN FooDetail fd on fd.FooId = f.FooId
WHERE f. -- Some condition

When investigating we found that the index reorganise for the clustered index of FooDetail takes between 90 minutes and 120 minutes. With the following command:

ALTER INDEX [PK_FooDetail] ON [DbName].[dbo].[FooDetail] REORGANIZE WITH (LOB_COMPACTION = ON)

Extend event info:

<ExtendedInfo>
  <PageCount>3739</PageCount>
  <Fragmentation>5.00134</Fragmentation>
</ExtendedInfo>

We use ola hallengren solution for index optimize (Default settings). With the following command:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b

Timings

The reporting db and reporting tempdb have around 200 user connections open from start to finish. They generate reports that are used through the day and they put a heavy load on the system. Creating the reports on the fly is not an option. They have to be available. Like in many cases 24 hours in a day is to short, to give every process its own time window.

Disk speed (ms) data file:

• Avg Read Stall: 6
• Avg Write Stall: 33

Disk speed (ms) log file:

• Avg Read Stall: 5
• Avg Write Stall: 6

My question how can we speed up the index rebuild, how can we get the best improvement?

Some ideas:

• Should we exclude the table from the general index rebuild and have a seperate job to rebuild the index of these tables when we know there is less write usage? To suffer less from locking. (The application is used 24/7 so locking will always be an issue.)
• Is it a good idea, to remove the clustered index and use a heap instead, since the data is so volatile?
  (We have a different index on FooDetail for FooId)