Generalized perfomance problems with long running queries

Hi,
My problems with SQL Server 2000 SP4 multiplies. Long running queries are
blocking each other (even themselves); i.e.:
SPID:58 blocked by 58 locktype: PAGEIOLATCH_SH
I've discovered a lot of I/O contention due to PAGEIOLATCH_SH lock type. I'm
going to review the queries and database options in order to optimize them
(new indexing, bulk inserting, constraints disabling, frequent checkpointing
,
simple model restore, etc), but... is there a "brute force" solution for
this? (not increasing hardware resources, please; a 4 CPU Dell computer with
4 Gigs of RAM and a SCSI RAID ought to be enough to handle a few avg. 5 Mil.
rows tables, I believe).
Anyone can help, please?
Luis FernándezOn 25.08.2006 17:19, Luis Fernndez wrote:
> Hi,
> My problems with SQL Server 2000 SP4 multiplies. Long running queries are
> blocking each other (even themselves); i.e.:
> SPID:58 blocked by 58 locktype: PAGEIOLATCH_SH
> I've discovered a lot of I/O contention due to PAGEIOLATCH_SH lock type. I
'm
> going to review the queries and database options in order to optimize them
> (new indexing, bulk inserting, constraints disabling, frequent checkpointi
ng,
> simple model restore, etc), but... is there a "brute force" solution for
> this? (not increasing hardware resources, please; a 4 CPU Dell computer wi
th
> 4 Gigs of RAM and a SCSI RAID ought to be enough to handle a few avg. 5 Mi
l.
> rows tables, I believe).
The first thing I'd probably check is memory settings, just to make sure
your 2k doesn't hit a 128MB limit.
I'd do a profiler trace of a typical workload (a day, a week whatever)
which includes execution plans. Then check execution plans of long
runners. You can even feed the trace to the ITW but look carefully at
recommendations.
Kind regards
robert|||have you try to reduce your maxdop option?
(maximum CPU used in parrallel queries)
this reduce the blocking behavior, but also slow down the queries.
review your queries and try to create pre-aggregated tables.
try to force the maxdop option per query. you can also force SQL Server in
these queries to not change the join order
by default SQL Server optimize the queries and choose the best plan; but
this plan is not always the best choice.
so you can manually choose how the joins will be used (hash join or merge
join...) and in which order (join 2 tables first, then the third you want
etc...)
can you describe your disk subsystem?
during the execution of these queries, how is your disk activity?
"Luis Fernndez" <LuisFernndez@.discussions.microsoft.com> wrote in message
news:714859AD-B6BF-40F0-853C-C935D1129C9D@.microsoft.com...
> Hi,
> My problems with SQL Server 2000 SP4 multiplies. Long running queries are
> blocking each other (even themselves); i.e.:
> SPID:58 blocked by 58 locktype: PAGEIOLATCH_SH
> I've discovered a lot of I/O contention due to PAGEIOLATCH_SH lock type.
> I'm
> going to review the queries and database options in order to optimize them
> (new indexing, bulk inserting, constraints disabling, frequent
> checkpointing,
> simple model restore, etc), but... is there a "brute force" solution for
> this? (not increasing hardware resources, please; a 4 CPU Dell computer
> with
> 4 Gigs of RAM and a SCSI RAID ought to be enough to handle a few avg. 5
> Mil.
> rows tables, I believe).
> Anyone can help, please?
> Luis Fernndez