INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Deadlocks

Deadlocks

(OP)
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

RE: Deadlocks

Have you looked at fragmentation and last update of statistics?

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

RE: Deadlocks

(OP)
Some, but I can take a deeper look...

Simian

RE: Deadlocks

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close