Get Blocked Objects

Hi All,
Whenever there is a timeout occured from Application which was connected to
SQL Server.
I tried to get the Blocking objects from SysProcesses table of master DB.
But most of the time it will clear very soon so i couldnt able to find the
actual objects which is Blocking.
Could any one suggests some ways to get the blocking objects.
Thanks in advance...
Regards,
Herbert R.SQL Server Profiler may help, but be reminded to log only the required
information as logging eats up resources.
References
- SQL Server 2000 Administrator's Pocket Consultant: Profiling and
Monitoring (Solving Performance Problems with Profiler)
http://www.microsoft.com/technet/pr...s/c10ppcsq.mspx
Martin C K Poon
Senior Analyst Programmer
====================================
"Herbert" <Herbert@.discussions.microsoft.com> bl
news:1E649539-6330-4D18-878C-276251E4F915@.microsoft.com g...
> Hi All,
> Whenever there is a timeout occured from Application which was connected
to
> SQL Server.
> I tried to get the Blocking objects from SysProcesses table of master DB.
> But most of the time it will clear very soon so i couldnt able to find the
> actual objects which is Blocking.
> Could any one suggests some ways to get the blocking objects.
> Thanks in advance...
> Regards,
> Herbert R.|||Check out http://support.microsoft.com/kb/271509/ for a script that can help
monitor blocking and record the objects involved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:1E649539-6330-4D18-878C-276251E4F915@.microsoft.com...
> Hi All,
> Whenever there is a timeout occured from Application which was connected
> to
> SQL Server.
> I tried to get the Blocking objects from SysProcesses table of master DB.
> But most of the time it will clear very soon so i couldnt able to find the
> actual objects which is Blocking.
> Could any one suggests some ways to get the blocking objects.
> Thanks in advance...
> Regards,
> Herbert R.

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