|
Home > Archive > MS SQL Server > February 2006 > Service Broker Lock Waits
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Service Broker Lock Waits
|
|
|
| We're currently running SQL 2005 Std. Ed. and utilizing the Service Broker
functionality. I've noticed complaints from our system monitor about lock
waits exceeding 35/min. All of the common hardware perfmon counters (memory,
disk, CPU, network) are very low so it makes wonder what's going on inside of
SQL. Is there any maintenance that should be performed on Service Broker
objects? Just wondering if this amount of lock waits is normal (shouldn't
be, our apps get timeout complaints) and what we can do to improve the
availability of resources. Thanks.
LC
| |
| David Browne 2006-02-15, 8:23 pm |
|
"LC" <LC@discussions.microsoft.com> wrote in message
news:CCB1ACBE-02DC-428B-9758- A4E8BD212A4C@microso
ft.com...
> We're currently running SQL 2005 Std. Ed. and utilizing the Service Broker
> functionality. I've noticed complaints from our system monitor about lock
> waits exceeding 35/min. All of the common hardware perfmon counters
> (memory,
> disk, CPU, network) are very low so it makes wonder what's going on inside
> of
> SQL. Is there any maintenance that should be performed on Service Broker
> objects? Just wondering if this amount of lock waits is normal (shouldn't
> be, our apps get timeout complaints) and what we can do to improve the
> availability of resources. Thanks.
>
> LC
Lock Waits exceeding 35/min is not a problem per se, but may be an
indication of lock contention. Depending on the number of users and the
amount of activity and the duration of the lock waits this may be quite
normal.
Are there any other indication of a performance problem?
David
| |
| Remus Rusanu [MSFT] 2006-02-16, 3:23 am |
| As a general idea there isn't any special maintenance required for Service
Broker.
It would be usefull to narrow down the items that are being waited for
locks. I really don't know a nice way to achieve this. A friend of mine
sugested to pool for select * from sys.dm_os_waiting_tasks and/or select *
from sys.dm_tran_locks and see if there's any obvious pattern there.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"LC" <LC@discussions.microsoft.com> wrote in message
news:CCB1ACBE-02DC-428B-9758- A4E8BD212A4C@microso
ft.com...
> We're currently running SQL 2005 Std. Ed. and utilizing the Service Broker
> functionality. I've noticed complaints from our system monitor about lock
> waits exceeding 35/min. All of the common hardware perfmon counters
> (memory,
> disk, CPU, network) are very low so it makes wonder what's going on inside
> of
> SQL. Is there any maintenance that should be performed on Service Broker
> objects? Just wondering if this amount of lock waits is normal (shouldn't
> be, our apps get timeout complaints) and what we can do to improve the
> availability of resources. Thanks.
>
> LC
| |
| David Browne 2006-02-16, 9:23 am |
|
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon> wrote
in message news:%23fZ$b4rMGHA.1312@TK2MSFTNGP09.phx.gbl...
> As a general idea there isn't any special maintenance required for Service
> Broker.
>
> It would be usefull to narrow down the items that are being waited for
> locks. I really don't know a nice way to achieve this. A friend of mine
> sugested to pool for select * from sys.dm_os_waiting_tasks and/or select *
> from sys.dm_tran_locks and see if there's any obvious pattern there.
>
A Lock:Acquired with duration > 0 is a lock wait.
Start a profiler trace using the "Empty" template. Add the Lock:Acquired
event, and put an event filter where Duration is grater than or equal to 1.
This will show you every lock wait as the lock wait is ending, how long the
wait was (duration in ms), and what object and mode the lock was on.
David
| |
| Remus Rusanu [MSFT] 2006-02-17, 7:23 am |
| Right, tracing the Lock::Acquired can be used indeed to find the problem,
although is quite intrusive.
I've been also directed to this white papaer, that has some scipts that can
show the lock hot spots:
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:edw%23aYwMGHA.1124@TK2MSFTNGP10.phx.gbl...
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon>
> wrote in message news:%23fZ$b4rMGHA.1312@TK2MSFTNGP09.phx.gbl...
>
> A Lock:Acquired with duration > 0 is a lock wait.
>
> Start a profiler trace using the "Empty" template. Add the Lock:Acquired
> event, and put an event filter where Duration is grater than or equal to
> 1.
>
> This will show you every lock wait as the lock wait is ending, how long
> the wait was (duration in ms), and what object and mode the lock was on.
>
> David
>
|
|
|
|
|