Re: Does query optimizer use locking hints?
From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 08/24/04
- Next message: Jeff Cochran: "GUI Master needs to walk new path..."
- Previous message: Roji. P. Thomas: "Re: Multiple insert in one pass"
- In reply to: Palmer Eldritch: "Re: Does query optimizer use locking hints?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 24 Aug 2004 20:48:05 +0200
I am not aware of any simple way to see the locking impact. Maybe
somebody else around here knows a trick...
In Profiler, one of the counters is elapsed time. This includes the time
that was spent while waiting for locks to get available, but it does not
give a breakdown of the elapsed time.
As a reference, you could run the same query under READ UNCOMMITTED
isolation level, or (if this is not possible) under READ COMMITTED
isolation level, and measure the difference in elapsed time. If all
other counters are approximately the same (CPU and I/O), then you will
have a rough indication of the time spent on locking.
HTH,
Gert-Jan
Palmer Eldritch wrote:
>
> Well, I'm not really interested in locking details.
> The goal was to optymize some queries that run under COM+ with isolation
> level serializable.
>
> Is there any simple way of seeing the impact of locking on the execution time?
> I know a little about SQL Profiler and I'm wonder if Duration of
> SQL:BatchCompleted event includes time used by locking.
>
> "Gert-Jan Strik" wrote:
>
> > Palmer,
> >
> > Louis is correct, in SQL-Server the locking strategy is a runtime
> > decision, not a compile time decision. Whenever possible, locking hints
> > will be used.
> >
> > If you run sp_lock before committing the transaction, you can view the
> > all (current) locks.
> >
> > HTH,
> > Gert-Jan
> >
-- (Please reply only to the newsgroup)
- Next message: Jeff Cochran: "GUI Master needs to walk new path..."
- Previous message: Roji. P. Thomas: "Re: Multiple insert in one pass"
- In reply to: Palmer Eldritch: "Re: Does query optimizer use locking hints?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
