re-indexing "sliding window" partitioned tables - is it necessary?

Hello
We have a partitioning strategy in place where we keep 10 days worth of data
each on its own day partition and a weekly sliding window where we remove old
days and add new days.
These tables only experience INSERTS (Bulk inserts and BCP), no updates or
deletes and our clustered index is also partitioned.
I would like to know if it is necessary to ever check for fragmentation or
re-index this table. My thinking is that it is not as all INSERTS will be
contigiuous.
thanks
--
-- cranfield, DBA> I would like to know if it is necessary to ever check for fragmentation or
> re-index this table. My thinking is that it is not as all INSERTS will be
> contigiuous.
It is likely you have at least some fragmentation unless you load data in
index key order of all indexes. You might consider including an ALTER INDEX
REBUILD or REORGANIZE of the last loaded partition as part of your daily
sliding window maintenance. If you SWITCH a fully loaded table into the
partitioned table, you can reorg before switching in.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:89CC380F-46B3-4E0B-927A-4552627BF667@.microsoft.com...
> Hello
> We have a partitioning strategy in place where we keep 10 days worth of
> data
> each on its own day partition and a weekly sliding window where we remove
> old
> days and add new days.
> These tables only experience INSERTS (Bulk inserts and BCP), no updates or
> deletes and our clustered index is also partitioned.
> I would like to know if it is necessary to ever check for fragmentation or
> re-index this table. My thinking is that it is not as all INSERTS will be
> contigiuous.
> thanks
> --
> -- cranfield, DBA|||Hi Dan
Yes, that makes sense. Our partitioned table gets loaded intra-businessday
and we the window gets moved only once a week on the weekend. We have 7 days
of future partitions always defined. So would you suggest a fragmentation
check at the end of each business day and then a rebuild of the entire index
should there be excessive fragmentation? Our maintenance window is very
small and these partitioned tables have approx 1 mill rows/day.
--
-- cranfield, DBA
"Dan Guzman" wrote:
> > I would like to know if it is necessary to ever check for fragmentation or
> > re-index this table. My thinking is that it is not as all INSERTS will be
> > contigiuous.
> It is likely you have at least some fragmentation unless you load data in
> index key order of all indexes. You might consider including an ALTER INDEX
> REBUILD or REORGANIZE of the last loaded partition as part of your daily
> sliding window maintenance. If you SWITCH a fully loaded table into the
> partitioned table, you can reorg before switching in.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:89CC380F-46B3-4E0B-927A-4552627BF667@.microsoft.com...
> > Hello
> >
> > We have a partitioning strategy in place where we keep 10 days worth of
> > data
> > each on its own day partition and a weekly sliding window where we remove
> > old
> > days and add new days.
> >
> > These tables only experience INSERTS (Bulk inserts and BCP), no updates or
> > deletes and our clustered index is also partitioned.
> >
> > I would like to know if it is necessary to ever check for fragmentation or
> > re-index this table. My thinking is that it is not as all INSERTS will be
> > contigiuous.
> >
> > thanks
> > --
> > -- cranfield, DBA
>|||> So would you suggest a fragmentation
> check at the end of each business day and then a rebuild of the entire
> index
> should there be excessive fragmentation? Our maintenance window is very
> small and these partitioned tables have approx 1 mill rows/day.
Assuming your indexes are aligned, you might consider an unconditional
REBUILD or REORGANIZE of only the last loaded partition since I expect
you'll have about the same level of fragmentation of the newly loaded
partition every day. If you don't have a large enough maintenance window to
REBUILD, you can still REORGANIZE online to reduce fragmentation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:8F5B6484-731A-4B99-8EAC-EBD811B42854@.microsoft.com...
> Hi Dan
> Yes, that makes sense. Our partitioned table gets loaded intra-businessday
> and we the window gets moved only once a week on the weekend. We have 7
> days
> of future partitions always defined. So would you suggest a fragmentation
> check at the end of each business day and then a rebuild of the entire
> index
> should there be excessive fragmentation? Our maintenance window is very
> small and these partitioned tables have approx 1 mill rows/day.
> --
> -- cranfield, DBA
>
> "Dan Guzman" wrote:
>> > I would like to know if it is necessary to ever check for fragmentation
>> > or
>> > re-index this table. My thinking is that it is not as all INSERTS will
>> > be
>> > contigiuous.
>> It is likely you have at least some fragmentation unless you load data in
>> index key order of all indexes. You might consider including an ALTER
>> INDEX
>> REBUILD or REORGANIZE of the last loaded partition as part of your daily
>> sliding window maintenance. If you SWITCH a fully loaded table into the
>> partitioned table, you can reorg before switching in.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:89CC380F-46B3-4E0B-927A-4552627BF667@.microsoft.com...
>> > Hello
>> >
>> > We have a partitioning strategy in place where we keep 10 days worth of
>> > data
>> > each on its own day partition and a weekly sliding window where we
>> > remove
>> > old
>> > days and add new days.
>> >
>> > These tables only experience INSERTS (Bulk inserts and BCP), no updates
>> > or
>> > deletes and our clustered index is also partitioned.
>> >
>> > I would like to know if it is necessary to ever check for fragmentation
>> > or
>> > re-index this table. My thinking is that it is not as all INSERTS will
>> > be
>> > contigiuous.
>> >
>> > thanks
>> > --
>> > -- cranfield, DBA