Re: How do PerfMon counters work?
From: Greg Linwood (g_linwoodQhotmail.com)
Date: 07/22/04
- Next message: karl bond: "Performance Problem"
- Previous message: Oskar: "Re: How do PerfMon counters work?"
- In reply to: Oskar: "Re: How do PerfMon counters work?"
- Next in thread: Oskar: "Re: How do PerfMon counters work?"
- Reply: Oskar: "Re: How do PerfMon counters work?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 22 Jul 2004 23:55:00 +1000
Hi Oskar
Lock waits is explained fairly clearly in the Performance Monitor app -
click on "Explain" on any counter & you'll see a good explanation. For Lock
waits, this is: "Total wait time (milliseconds) for locks in the last
second." So, the counter shows the total amount of time in milliseconds that
locks were queued / waiting over the previous second.
Deadlocks are not so well explained in the PerfMon. For that counter, it
states: "Number of lock requests that resulted in a deadlock." However, the
way that deadlocks are detected in SQL Server is that a dedicated System
process (thread) wakes up on a regular, scheduled basis & goes looking for
connections deadlocked in the lock manager sub-system. The more often the
deadlock detection process detects deadlocks, the more often it runs. As it
finds fewer deadlocks, it runs less often etc - so you might describe it as
a self-throttling process. The PerfMon is typically not the tool of choice
for identifying deadlocks unless you simply want the pure number of
incidences. More typically, a DBA wants more detailed information about
deadlocks & will use either the SQL Profiler or DBCC commands. If you have a
problem with deadlocks, I urge you to look these up as they help you not
only identify the incidences of deadlocks but also provide innformation on
how to solve them. This is important to do as your system make actually be
losing information if deadlocks are occurring regularly & not beinng handled
properly.
Both counters do have a relevance to "speed" in that the higher the number,
the slower SQL Server will be performing.
Regards,
Greg Linwood
SQL Server MVP
"Oskar" <anonymous@discussions.microsoft.com> wrote in message
news:208301c46ff1$96f38640$a501280a@phx.gbl...
> Greg,
>
> OK, "speed" might not be the right term. What I meant was
> that "Lock Waits/sec" it is the "frequence" at which
> processes get blocked and "Deadlocks/sec" is
> the "frequence" at which deadlocks occur. Is that so? I
> just wanted to know how SQL Server calculates
> this "frequence".
>
> Hopefully I made sense this time.
>
> --
> Many thanks,
> Oskar
>
>
> >-----Original Message-----
> >Hi Oskar
> >
> >What do you mean by "speed"?
> >
> >Both of these counters indicate conditions that slow down
> the performance of
> >SQL Server for related but not quite the same reasons.
> >
> >SQL Server uses locks to control access by multiple
> connections that want to
> >access (read or update) the same resources (tables, rows,
> indexes etc). Lock
> >Waits/sec indicates how often connections are "blocked"
> in accessing
> >database resources by the locks held by other
> connections. This is normal
> >behaviour but excessive blocking indicates sub-optimal
> performance due to
> >high degrees of resource contention between connections.
> >
> >Deadlocking is one step worse, where multiple connections
> are in a deadly
> >embrace, waiting to access & lock each others' resources
> but neither will
> >give up first. In this case, SQL Server chooses one
> connection & releases
> >its locks to allow the other to continue. This is a
> performance / speed
> >problem, but also far worse as a transaction is being
> rolled back by SQL
> >Server & unless you've coded to take this situation into
> consideration, you
> >might actually be losinng work..
> >
> >HTH
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"Oskar" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:1f1301c46fe2$33bb9540$a601280a@phx.gbl...
> >> Hi,
> >>
> >> Does anyone know how "SQL Server:Locks:Lock Waits/sec"
> >> and "SQL Server:Locks:Number of Deadlocks/sec" counters
> >> get their values? This is essentially speed, right? I
> >> wonder how it is measured.
> >>
> >> --
> >> Many thanks,
> >> Oskar
> >>
> >>
> >
> >
> >.
> >
- Next message: karl bond: "Performance Problem"
- Previous message: Oskar: "Re: How do PerfMon counters work?"
- In reply to: Oskar: "Re: How do PerfMon counters work?"
- Next in thread: Oskar: "Re: How do PerfMon counters work?"
- Reply: Oskar: "Re: How do PerfMon counters work?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
