excessive locking and blocking

At the last night school. there was mention of some
documentation and methods for monitoring Blocking and
locking. Please could someone point me in the direction
of these. They might have been on the SQL Server
Magazine site.
My problem is when an end of day process runs on some
occassions it will run in 1:30 and other days for some
reason it can take upto 7 hours. The workload is faily
constant. I am trying to figure out what is different.
I checked with profiler and none of the sql takes an
overly excessive time to complete. CPU is okay. However
there is some deadlocking and when I used Spotlight which
is a SQL monitoring tool the locking and blocking was
excessive. Infact at times it was registering upto
500,000 locks.
Does anyone have any suggestions of how I can monitor and
filter the locks to see what is blocking what.
Thanks Jamie...Jamie
<http://support.microsoft.com/direct...B;EN-US;Q224453>--
-- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems (Q224453)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4f101c44ec5$99f6d1f0$a601280a@.phx
.gbl...
> At the last night school. there was mention of some
> documentation and methods for monitoring Blocking and
> locking. Please could someone point me in the direction
> of these. They might have been on the SQL Server
> Magazine site.
> My problem is when an end of day process runs on some
> occassions it will run in 1:30 and other days for some
> reason it can take upto 7 hours. The workload is faily
> constant. I am trying to figure out what is different.
> I checked with profiler and none of the sql takes an
> overly excessive time to complete. CPU is okay. However
> there is some deadlocking and when I used Spotlight which
> is a SQL monitoring tool the locking and blocking was
> excessive. Infact at times it was registering upto
> 500,000 locks.
> Does anyone have any suggestions of how I can monitor and
> filter the locks to see what is blocking what.
> Thanks Jamie...
>|||Jamie,
'INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems'
http://support.microsoft.com/?id=224453
'HOW TO: Troubleshoot Application Performance with SQL Server'
http://support.microsoft.com/?id=224587
'INF: How to Monitor SQL Server 7.0 Blocking'
http://support.microsoft.com/?id=251004
'INF: How to Monitor SQL Server 2000 Blocking'
http://support.microsoft.com/?id=271509
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4f101c44ec5$99f6d1f0$a601280a@.phx
.gbl...
> At the last night school. there was mention of some
> documentation and methods for monitoring Blocking and
> locking. Please could someone point me in the direction
> of these. They might have been on the SQL Server
> Magazine site.
> My problem is when an end of day process runs on some
> occassions it will run in 1:30 and other days for some
> reason it can take upto 7 hours. The workload is faily
> constant. I am trying to figure out what is different.
> I checked with profiler and none of the sql takes an
> overly excessive time to complete. CPU is okay. However
> there is some deadlocking and when I used Spotlight which
> is a SQL monitoring tool the locking and blocking was
> excessive. Infact at times it was registering upto
> 500,000 locks.
> Does anyone have any suggestions of how I can monitor and
> filter the locks to see what is blocking what.
> Thanks Jamie...
>