Index partition defragmentation feature

[Thomas-Schultz1978]

I have an idea for an additional feature on index partition defragmentation.

I would be great if we could choose something like an offset from max partition number, in our case it would be max partition number - 2 partitions because we have no changes in older partitions. Also the possibility to disable the defragmentation calculation on the older partitions would be a nice feature.

Makes this sense for you please?

Thank you!

[Xyaran]

Hi Thomas,

I second you on this request, this is something that would be very helpful at work. We have many (400) partitions but most of them don't get updated at all but the last 4-5 of them!

It makes sense to me!

Thanks

Eric

[samot1]

if your table is partitioned, and you set @PartitionLevel to 'Y' (default), it will reorganize / rebuild only partitions that got fragmented (this parameter must be there since years). And when you really not modify your old partitions, then they should become not fragmented (except you use something as DBCC SHRINKFILE / SHRINKDB), so I don't see the problem

[Thomas-Schultz1978]

The fragmentation calculation of all partitions takes a lot of time and reads. Am 23.12.20, 12:23 schrieb Thomas Franz notifications@github.com:

if your table is partitioned, and you set @PartitionLevel to 'Y' (default), it will reorganize / rebuild only partitions that got fragmented (this parameter must be there since years). And when you really not modify your old partitions, then they should become not fragmented (except you use something as DBCC SHRINKFILE / SHRINKDB), so I don't see the problem —You are receiving this because you authored the thread.Reply to this email directly, view it on GitHub, or unsubscribe.

[samot1]

ok, I understand you, you do not want to even check the partitions 1-100 for fragmentation, when you know, that only 100 to 110 can be fragmented.

The problem I see is, that this may work, if you have only one or maybe two partition functions with a comparable amount of total partion numbers, but this script has to work universal (for everybody) and often you have a pf_day, a pf_month and a pf_year (365 / 12 / 1 partion per year) and maybe a bunch of other partition functions (maybe they partion by some file_ids or by zip or country codes).

And of course the number of partitions that could have modifications may change regularly. For example I by myself prefer to create in November all partitions for the next year (because you can't split / merge partitions that contain data, when you are using a columnstore index). If you say, that only the last 2 months can have modified data, then it will be today all partitions from Nov 2020 to Dec 2021 (even if the most newer partitions are empty), while in few months it may be only 6 partitions (July 2021 to Dec 2021).

For all this reasons it will become very complex to specify a single parameter to say how many partitions should be checked for modification (even if you use a comma separated list as for the @database parameter).

I did not test it by myself, but it should help, if you set the file groups for the older years to read only (of course this has it own drawbacks, since you can't even flip a single bit, even if this would cause no fragmentation).

[Thomas-Schultz1978]

Good morning!OK, i will check the index maintenance procedure. Perhaps i can insert such a function by my own.Set partitions read only is for sure a possible way but in our case difficult because the DB's belong to an application and we can't change anything without the application developer permission.Am 28.12.20, 13:37 schrieb Thomas Franz notifications@github.com:

ok, I understand you, you do not want to even check the partitions 1-100 for fragmentation, when you know, that only 100 to 110 can be fragmented. The problem I see is, that this may work, if you have only one or maybe two partition functions with a comparable amount of total partion numbers, but this script has to work universal (for everybody) and often you have a pf_day, a pf_month and a pf_year (365 / 12 / 1 partion per year) and maybe a bunch of other partition functions (maybe they partion by some file_ids or by zip or country codes). And of course the number of partitions that could have modifications may change regularly. For example I by myself prefer to create in November all partitions for the next year (because you can't split / merge partitions that contain data, when you are using a columnstore index). If you say, that only the last 2 months can have modified data, then it will be today all partitions from Nov 2020 to Dec 2021 (even if the most newer partitions are empty), while in few months it may be only 6 partitions (July 2021 to Dec 2021). For all this reasons it will become very complex to specify a single parameter to say how many partitions should be checked for modification (even if you use a comma separated list as for the @database parameter). I did not test it by myself, but it should help, if you set the file groups for the older years to read only (of course this has it own drawbacks, since you can't even flip a single bit, even if this would cause no fragmentation). —You are receiving this because you authored the thread.Reply to this email directly, view it on GitHub, or unsubscribe.