Is it worth partitioning?

Hello all,

We're currently upgrading to SQL Server 2005 and we're trying to
figure out if we should partition some of the tables in the database.

Specifically the two largest tables - both have around 1.5 million
rows and are expected to at least double in size over the lifetime of
the system. Both have the same type of activity happening to them -
rows always only being inserted at the end of the table, updates
always only happening on the last few hundred rows that were inserted,
and selects happening all over the place. The tables are often joined
off each other on a key that spans two columns.

We don't do any data import/export, so we would only be interested in
partitioning if it could give a performance benefit, rather than the
administrative benefit I've been reading about.

We currently have a RAID10 array. Would people recommend partitioning
the tables over just using sensible clustered indexes and letting the
RAID array handle the concurrancy?

Many thanks,
Tommy.

Re: Is it worth partitioning?

Three million rows is not considered particularly large these days and
would not generally justify partitioning. I would keep life simple,
without partitioning.

Roy Harvey
Beacon Falls, CT

On Tue, 25 Sep 2007 03:37:11 -0700, Tommy Hayes
wrote:

>Hello all,
>
>We're currently upgrading to SQL Server 2005 and we're trying to
>figure out if we should partition some of the tables in the database.
>
>Specifically the two largest tables - both have around 1.5 million
>rows and are expected to at least double in size over the lifetime of
>the system. Both have the same type of activity happening to them -
>rows always only being inserted at the end of the table, updates
>always only happening on the last few hundred rows that were inserted,
>and selects happening all over the place. The tables are often joined
>off each other on a key that spans two columns.
>
>We don't do any data import/export, so we would only be interested in
>partitioning if it could give a performance benefit, rather than the
>administrative benefit I've been reading about.
>
>We currently have a RAID10 array. Would people recommend partitioning
>the tables over just using sensible clustered indexes and letting the
>RAID array handle the concurrancy?
>
>Many thanks,
>Tommy.

Re: Is it worth partitioning?

Many thanks for the advice Roy...

Tommy.

Re: Is it worth partitioning?

On Sep 25, 4:21 am, "Roy Harvey (SQL Server MVP)"
wrote:

> Three million rows is not considered particularly large these days and
> would not generally justify partitioning. I would keep life simple,
> without partitioning.

I totally agree.
Three millions of rows is, actually, a "Small" table (Nowadays).

I'd keep it simple, too.

P