| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-12 : 07:47:37
|
| Ben writes "Hello,All of a sudden, our SQL server is taking about 1,000,000 locks 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" |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-12 : 08:54:21
|
| Most SQL queries use some kind of lock. SELECTs use shared locks, Deletes, updates and inserts use exclusive locks.Is there any blocking occurring? Are you witnessing any lock escalations? What is the output of sp_lock and sp_who?Did this start occurring after installing SP4? Are you using AWE memory?====================================================Regards,Sean RoussyThank you, drive through |
 |
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-08-12 : 09:24:51
|
| mssql cannot put lock "for no reason", launch Profiler and watch what transaction is running... |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-08-12 : 11:27:56
|
| Review the query. Is it targeted well? By that, are all of the search arguments (SARGS) valid?valid SARG:where orderdate > dateadd(dd, 7, getdate())invalid SARG:where dateadd(dd, -7, orderdate) > getdate()Get the estimated plan for this query. Are there lots of scans?What type of joins are being used?If you suspect the query plan is not right, you can try to UPDATE STATISTICS WITH FULLSCAN or sp_recompile the table.As always, try this in a test environment first. |
|
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-08-13 : 00:06:41
|
| >>If you suspect the query plan is not right, you can try to UPDATE STATISTICS WITH FULLSCAN or sp_recompile the table.Did you undestand what were you writing ?Tables can't be managed by sp_recompile, this procedure force stored procedures or triggers to be recompiled the next time they are run... And UPDATE STATISTICS is not the first thing to do when optimizing the query... In fact, you mssql updates statistic automaticly, so normally you update it manually not often... |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-08-15 : 10:00:33
|
| True, I am assuming this query is in a stored procedure. Keep in mind though, that the query plan can jump especially if the stored procedure accepts parameters that define a range of values. Not all ranges are equal. Also, the auto-update statistics will only estimate statistics. I think they take 30% of the table by default. I have seen rare cases where this genrates questionable statistics. |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-15 : 10:09:59
|
| Doing some reading this weekend and I fumbled on something. SQL Server will not even always choose the most efficient query plan if it decides that cost of evaluating all of the possible query plans is too much once it finds a good enough plan. I believe Delaney said (the book is at home) this is most likely to occur in large complicated queries. I finally got off the couch and started studying again a couple of weeks ago.====================================================Regards,Sean RoussyThank you, drive through |
|
|
|
cmd
Starting Member
1 Post |
Posted - 2005-09-01 : 05:29:46
|
| I have had the same problem as this but my problem only arised after moving from service pack 3 to service pack 4, I think that Microsoft may have much something up, because not only do I get loads of locks but they are against tables that do not exist, and this did not happen until I upgraded. |
|
|
|
|
Excessive locking on simple queries => performance hell