Looking for some advice before I follow my gut on this and rebuild the nonclustered index to lead with our partition key. This is an insert-only table which is never updated or deleted, we keep a sliding window my truncating/merging periods on the left and adding new periods on the right. The nonclustered index is constantly at 99% fragmentation due to the makeup of data we are inserted (many unique accountids and inventoryids). I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation or maybe I shouldn't worry about the fragmentation?
I'm aware that our table doesn't have an explicit unique index, we rely on the [UNIQUIFIER] that is added to changedAt automatically.
Usage pattern: Table is always queried with a date filter (ChangedAt)
Partition function:
CREATE PARTITION FUNCTION pf_Weekly_QuantityHistory (datetime2(2)) AS RANGE RIGHT FOR VALUES ( '01 Jul 2019','08 Jul 2019','15 Jul 2019',etc )
CREATE PARTITION SCHEME [ps_Weekly_QuantityHistory] AS PARTITION [pf_Weekly_QuantityHistory]
ALL TO ( [ExampleFG] );
CREATE TABLE [dbo].[QuantityHistory](
[AccountId] [int] NOT NULL,
[InventoryID] [int] NOT NULL,
[QuantityBefore] [int] NULL,
[QuantityAfter] [int] NULL,
[ChangedAt] [datetime2](2) NOT NULL DEFAULT (getutcdate())
) ON [ps_Weekly_QuantityHistory](ChangedAt)
CREATE CLUSTERED INDEX [CX_QuantityHistory] ON [dbo].[QuantityHistory]
(
[ChangedAt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE)
CREATE NONCLUSTERED INDEX [IX_AccountId] ON [dbo].[QuantityHistory]
(
[AccountId] ASC,
[InventoryId] ASC,
[ChangedAt] ASC
) INCLUDE(QuantityBefore,QuantityAfter) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE)
INSERT
ing new records? Have you ever rebuilt or reorganized the nonclustered index before?