Excessive locking for simple queries => performance decay 1

[casaben]

Hello,
All of a sudden, our SQL server is taking about 1,000,000 for executing a ridiculous simple query.
The query used to take only 100 lock or so, and without changing anything, it behaves like that.
We perform a mixture of select, update and insert transactions.
First, performance is great, but it decays rapidly. At the same time, the lock table increases all the time.
Putting traces on it, we see that 99% of the time, SQL Server is just aqcuiring/releasing locks (we do not know where they come from, it's not our application ;-), with sometimes a transaction getting executed (normal response time).
Why is the SQL server just acquiring and releasing locks for no appearent reason ??
System is a 4 CPU, 4Gb RAM / SQL Server 2000 SP4

 

[casaben]

In the above message, I mean 1,000,000 locks...that's right : 1,000,000

 

[DrSql]

Try to use NOLOCK
Example.
SELECT COUNT(Users.UserID)
FROM Users WITH (NOLOCK)


Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[casaben]

There is a problem in doing that, since we cannot access the query (it's compiled in Java code).
Similar setups on other servers do not have the problem.
Other databases do not have the problem either ;-)
We were more thinking about finding out why the excessive locking occurs. Traces learn us nothing we do not already know.
Thanks for response though, if we could access the SQL, that would solve it we think...

 

[Catadmin]

Have you run Profiler to see what SQL user name or Windows NT name are asking for the locks?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[casaben]

Don't have details on that right now, posting them tomorrow, but what I understand from my collegues is that the SQL server process itself aqcuires/releases the locks the entire time.
Thanks

 

[Catadmin]

Well, yes. The SQL Server process itself is what is generating the locks. But you should be able to use Profiler to find out which Users/Logins or Queries are causing SQL Server to generate those locks. That will give you a good idea right there what is going on, and if there is something running in the background that shouldn't be.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???