Very Computer

Hello.  I have an issue with SQL 2000 where when I run profiler I see multiple
locks that are acquired then release many, many times over.  I have an
application with minimal users 15-20 and the clients are accessing a Compaq
Proliant 8000 with dual 800Mhz processors, (6) 18GB drives in a RAID 5 config,
2GB of ram and all connected to a Cisco 3548 Switch via Gigabit link.  I am
seeing poor performance from the database.  I have a feeling it may be due to the
locking issue.  
Running Profiler for 1 hr and filtering it down to 3 users (that do not have a
real heavy load; simple selects, updates, inserts) the Profiler returns over
22000 Lock:Acquired/Released entries!  Is this normal?  The app they are using
isn't too transaction intensive.  Mostly selects.  I was going to attach a
Profiler Trace file, but they are pretty big.  If this would be of help, please
let me know.
Thanks in advance for any help or comments!

Jacob Bolan
Federated Rural Electric Insurance
913-541-0150

Locks are how SQL works.  There are many levels of locks (you didn't mention what instance you were watching) and many types.

While possible, i don't know that I would look at locks first for performance related issues.

A quick check of a Compaq with SQL 7 Enterprise using 3 gig of memory and 4 processors shows a max of 224056 RID lock requests and an average of 15810.  Probably of greater concern would be Lock Waits/sec.  Which would mean a process had to wait for a lock.  I have no instances that show any lock waits/sec.

Hello.  I have an issue with SQL 2000 where when I run profiler I see multiple
locks that are acquired then release many, many times over.  I have an
application with minimal users 15-20 and the clients are accessing a Compaq
Proliant 8000 with dual 800Mhz processors, (6) 18GB drives in a RAID 5 config,
2GB of ram and all connected to a Cisco 3548 Switch via Gigabit link.  I am
seeing poor performance from the database.  I have a feeling it may be due to the
locking issue.  
Running Profiler for 1 hr and filtering it down to 3 users (that do not have a
real heavy load; simple selects, updates, inserts) the Profiler returns over
22000 Lock:Acquired/Released entries!  Is this normal?  The app they are using
isn't too transaction intensive.  Mostly selects.  I was going to attach a
Profiler Trace file, but they are pretty big.  If this would be of help, please
let me know.
Thanks in advance for any help or comments!

Jacob Bolan
Federated Rural Electric Insurance
913-541-0150

Jacob,

As Mike stated locks are an inherent part of how SQL Server does its
business.  And you need to know what types of locks are being
requested/acquired.  One thing to check for are exclusive locks on
resources.  This can lead to blocking which leads to performance problems.  
There is a KB article located at
http://support.microsoft.com/support/kb/articles/q271/5/09.asp that details
how to monitor for blocking.

John Gose
Microsoft SQL Server Support
--------------------



| Subject: Re: Excessive Locking
| Date: Tue, 8 May 2001 15:59:17 -0500
| Lines: 45
||
<Snip of Header Stuff>
|
| Locks are how SQL works.  There are many levels of locks (you didn't
mention what instance you were watching) and many types.
| While possible, i don't know that I would look at locks first for
performance related issues.
| A quick check of a Compaq with SQL 7 Enterprise using 3 gig of memory and
4 processors shows a max of 224056 RID lock requests and an average of
15810.  Probably of greater concern would be Lock Waits/sec.  Which would
mean a process had to wait for a lock.  I have no instances that show any
lock waits/sec.


| Hello.  I have an issue with SQL 2000 where when I run profiler I see
multiple
| locks that are acquired then release many, many times over.  I have an
| application with minimal users 15-20 and the clients are accessing a
Compaq
| Proliant 8000 with dual 800Mhz processors, (6) 18GB drives in a RAID 5
config,
| 2GB of ram and all connected to a Cisco 3548 Switch via Gigabit link.  I
am
| seeing poor performance from the database.  I have a feeling it may be
due to the
| locking issue.  
| Running Profiler for 1 hr and filtering it down to 3 users (that do not
have a
| real heavy load; simple selects, updates, inserts) the Profiler returns
over
| 22000 Lock:Acquired/Released entries!  Is this normal?  The app they are
using
| isn't too transaction intensive.  Mostly selects.  I was going to attach
a
| Profiler Trace file, but they are pretty big.  If this would be of help,
please
| let me know.
| Thanks in advance for any help or comments!
| Jacob Bolan
| Federated Rural Electric Insurance
| 913-541-0150
|