|
Home > Archive > MS SQL Server > March 2005 > NOLOCK Hint yet Profiler reports Lock:Acquired
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 |
NOLOCK Hint yet Profiler reports Lock:Acquired
|
|
| ReuvenT 2005-03-30, 7:03 pm |
| Hi,
I'm perplexed by the large amount of lock activity in our SQL Server 2000
sp3 system. Thousands of requests per second with only a handful of users
touching less than 30 tables. In trying to understand this, I running a
simple select query with the NOLOCK hint. In Profiler, I see a number of
Lock:Acquired events occuring. This surprises me as I thought this hint would
not request nor consider locks. Any insight to this would be very much
appreciated, as well as an opinion if this should be a performance concern,
which is where I'm coming from.
Many Thanks,
Reuven
| |
| Jasper Smith 2005-03-30, 7:03 pm |
| Check the DatabaseID,Mode and ObjectID. You'll see that 99% of them are
hitting system objects - this is normal behaviour. The only locks you should
see on your table accessed with nolock are Schema Stability (and possibly
Compile) table level locks. You can check the "Exclude system IDs" checkbox
on the Filters tab in Profiler to cut down the noise of the system objects.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ReuvenT" <ReuvenT@discussions.microsoft.com> wrote in message
news:99AA5475-9887-4733-8AB7- D466B222A60E@microso
ft.com...
> Hi,
> I'm perplexed by the large amount of lock activity in our SQL Server 2000
> sp3 system. Thousands of requests per second with only a handful of users
> touching less than 30 tables. In trying to understand this, I running a
> simple select query with the NOLOCK hint. In Profiler, I see a number of
> Lock:Acquired events occuring. This surprises me as I thought this hint
> would
> not request nor consider locks. Any insight to this would be very much
> appreciated, as well as an opinion if this should be a performance
> concern,
> which is where I'm coming from.
>
> Many Thanks,
> Reuven
| |
| ReuvenT 2005-03-31, 8:01 pm |
| Thanks for the suggestion and info - very helpful.
Reuven
"Jasper Smith" wrote:
> Check the DatabaseID,Mode and ObjectID. You'll see that 99% of them are
> hitting system objects - this is normal behaviour. The only locks you should
> see on your table accessed with nolock are Schema Stability (and possibly
> Compile) table level locks. You can check the "Exclude system IDs" checkbox
> on the Filters tab in Profiler to cut down the noise of the system objects.
>
> --
> HTH
>
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "ReuvenT" <ReuvenT@discussions.microsoft.com> wrote in message
> news:99AA5475-9887-4733-8AB7- D466B222A60E@microso
ft.com...
>
>
>
|
|
|
|
|