1

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)
4
  • How many records are currently in the table? How often are you INSERTing new records? Have you ever rebuilt or reorganized the nonclustered index before?
    – J.D.
    Jan 5, 2021 at 2:13
  • As an aside, have you looked at kejser.org/table-pattern-rotating-log-ring-buffer? I implemented this in our production environment and it's great, no need to mess with changing the partition function, just decide on the number of slots and the granularity (daily, weekly, monthly) to put data in. Jan 5, 2021 at 3:38
  • I'm new to this forum so apologies if i'm not replying to comments the correct way. @JonathanFite There are about 1B rows in the table (~25GB), I also have both indexes using PAGE compression. The inserts are happening at a rate of ~20M per day. I use the maintenance tools from ola.hallengren.com which is what tipped me off to the constant fragmentation on the right most partition. I've since started using a script to ignore the right most partition during maintenance. Jan 6, 2021 at 17:23
  • @JonathanFite I'm glad you mentioned that, I had found that awhile back but never had time to get it working. I think might meet our needs and will look into it again. Jan 6, 2021 at 17:27

2 Answers 2

3

I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation

You've got the partition key in the nonclustered index, so it's on the partition scheme (by default), and only the head partition is going to get inserts and additional fragmentation.

So you can rebuild the older partitions rarely and they won't get fragmented, and rebuild the index for just the head partition on a more frequent basis.

But for an index with insertions spread across the sort order you'll always generate fragmentation. And fragmentation doesn't always matter. It really depends on the storage design and the workload.

Also you're keeping two complete copies of this table, one sorted by ChangedAt and one sorted by (AccountId,InventoryId,ChangedAt). You can probably just make the (AccountId,InventoryId,ChangedAt) index the clustered index, and only store the table one time.

6
  • David - Thanks for this info, I think you hit on something that I probably should have done a better job explaining which is i'm not currently seeing any negative performance issues from the index fragmentation, it was more of just an annoyance and the extra time it was taking to run our defragmentation jobs. We have had times when the index maintenance on the new partition caused some blocking and I guess that is my biggest worry. Jan 6, 2021 at 17:40
  • I noticed that the Ola scripts don't let you ignore a partition so I started using something that would just let me do maintenance on that partition on a different schedule. I do have a question about your last point, for some reason I've been under the impression that you need to include the partition key column as the index key in order to efficiently query the table. Jan 6, 2021 at 17:40
  • Are you saying we could get the same performance (assuming we always include ChangedAt) by swapping the cluster key to (AccountId,InventoryId,ChangedAt)? If so, I totally agree I'm wasting space here. Would I have problems when querying when just using ChangedAt and not a specific account? Jan 6, 2021 at 17:40
  • "Would I have problems when querying when just using ChangedAt and not a specific account" Perhaps. That would require a scan, but only of a single partition. Jan 6, 2021 at 17:42
  • David. Very helpful thanks. Does this summarize your statement? "Partitioning Clustered Indexes When partitioning a clustered index, the clustering key must contain the partitioning column. When partitioning a nonunique clustered index, and the partitioning column is not explicitly specified in the clustering key, SQL Server adds the partitioning column by default to the list of clustered index keys. If the clustered index is unique, you must explicitly specify that the clustered index key contain the partitioning column" Jan 6, 2021 at 17:48
0

The problem here is that your secondary nonclustered index is not partiotion aligned so when a new record is added it generates fragmentation because that index follow a different order. New records don't generate fragmentation for the clustered index and this is very good. You can't prevent fragmentation on indexes ordered by accountid like in the secondary.

You can eventually align the secondary index with the partition to take advantage of the partition switching to execute periodic cleaing and partition defragmentation. This bring less performance in the query when you look up for accountid records but usually it's sustainable in a log table. But it's depend on you goal.

If your storage is ssd based you might as well skip defragmentation and do only statistics update on the secondary index.

1
  • I probably should have included the full script in my example but wanted to say that I think my nonclustered/secondary index is actually partitioned aligned as that is the default when you don't specify ON. I could include more information if you are curious but I just double-checked and it is aligned. I agree with the solution being dependent on my goal, will try a few things out and see if I can better articulate my goal in my next post. Thanks! Jan 6, 2021 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.