Re: Partitioning Advice

On 2012-06-06, Ben Carbery <ben(dot)carbery(at)gmail(dot)com> wrote:

> In the current environment I am given a single VHDD which I have not
> partitioned at all. The SAN performance is pretty good, but we have noticed
> slowdowns at various times.. The database does a lot of logging - constant
> small writes, with some probably insignificant reads of smaller tables.
> Delays in logging can effect the service which is doing the logging and
> cause problems upstream. Typically this does not happen, but there are
> hourly jobs which generate stats from the logs. Due to their complexity the
> query planner always chooses to do sequential scans on the main log
> table. This table is truncated monthly when the data is archived to another
> table, but peaks in size at around 10GB at the end of the month. Generally
> any time the stats jobs are running there are delays which I would like to
> reduce/eliminate. There is also a fair bit of iowait on the cpu.

do many of the statistical queries use the whole month's data?
have you considered partitioning the log table by day? if you can
reduce the number of rows involved in the table-scans by partitioning
it'll be help performance.

--
⚂⚃ 100% natural