Deadlocks

[simian336]

ok, here is my scenario. Our users are complaining about "general system slowness". Some of my developers have heard about some locks or deadlocks that "buried way down deep in SQL Server and they think it might be a problem"

CPU on the Production server never goes about 5%, Memory is low. No Metric I can see is anything but happy happy happy. I don't see any unusual locks we have seen a few deadlocks but they were days ago.

Doing some more research I see that by default

select @@LOCK_TIMEOUT
SET LOCK_TIMEOUT -1; --Wait Forever

But all of the examples that I see end fairly shortly. If I

SET LOCK_TIMEOUT 500;

Then is deadlocks end quickly. But there does not appear to be a system wide way to SET LOCK_TIMEOUT only per session.

Any thoughts?

Thanks

Simian

 

[djj55]

Have you looked at fragmentation and last update of statistics?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[simian336]

Some, but I can take a deeper look...

Simian

 

[gmmastros]

As djj alluded to....

The best way to prevent deadlocks is to make your code execute as fast as possible. By reducing fragmentation and updating statistics, you can speed up execution.

Most of the time when deadlocks occur, it's because a table is locked by a query. This usually happens when a query deletes and updates data in a table that affects a lot of rows. For example, imagine a table has a couple hundred thousand rows and that each row takes multiple kilobytes. This table would probably consume 50,000 pages or so. If you run an update or delete that effects a couple hundred pages, SQL Server will likely determine that it's faster to lock the entire table than it is to lock the individual pages.

To prevent locks from causing you problems, you should break up queries that update many rows so that it only updates smaller rows, but doing so in a loop. This would cause SQL Server to lock pages instead of the whole table. Your delete/update may take a little longer, but it may also be a bit quicker (depending on your log settings).

Basically, the faster your code, the less likely locks will cause you problems.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom