1

I have been using OLA's scripts for a long time and love them.

I have a very large database - 20+ TB with hundreds of partitioned tables.

I purge old data by dropping (merging the old partitions).

It is partitioned by date with weekly partitions going back about 6 months. I have to run daily index maintenance by partition to have any hope of keeping caught up (actually never catch up).

I am now on SQL 2017 and am trying ONLINE Rebuilds by Partition using the Resumable option.

What I find is every day I am rebuilding the current partition in almost every table only as it is always fragmented and likely will be fragmented tomorrow again due to massive # of inserts.

What I would like to do is exclude this partition.

This is the partition the current date falls in.

Ie, I would like to only rebuild past partitions.

Having this as an option would be very helpful.

2

Having this as an option would be very helpful.

Ola's scripts are now on github and a Feature request is already there.

1

Omit the table from OLA and do it as a separate step reindex the problematic segment.

/*https://www.mssqltips.com/sqlservertip/1914/sql-server-database-partitioning-myths-and-truths/ */

-- to return fragmentation information on partitioned indexes
SELECT
 object_name(a.object_id) AS object_name,
 a.index_id,
 b.name,
 b.type_desc,
 a.partition_number,
 a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') a
JOIN sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id
order by object_name(a.object_id), a.index_id, b.name, b.type_desc, a.partition_number


--Rebuild only partition 11.
ALTER INDEX IX_alert_events_timestamp
ON dbo.alert_events
REBUILD Partition = 11;
GO
  • Since I have over 500 Tables this is not feasible. And OLA's scripts do rebuild the partitions. I just want the ability to exclude current. – Kirby Richter Aug 10 '18 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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