none
Lock Timeouts/Sec is CRITICAL

    Question

  • Hi All,

    Today in the morning it has been observed that lock timeouts per sec is HIGH hence the service is in critical state. Would you please suggest us the procedure on how to bring this back to NORMAL state.

    CRITICAL - 10.7278 lock timeouts / sec for _Total, 8.2444 lock timeouts / sec for Page, 1.6944 lock timeouts / sec for RID, 0.0000 lock timeouts / sec for Object, 0.0000 lock timeouts / sec for Metadata, 0.7889 lock timeouts / sec for Key, 0.0000 lock timeouts / sec for HoBT, 0.0000 lock timeouts / sec for File, 0.0000 lock timeouts / sec for Extent, 0.0000 lock timeouts / sec for Database, 0.0000 lock timeouts / sec for Application, 0.0000 lock timeouts / sec for AllocUnit

    CRITICAL - 23.8500 lock timeouts / sec for _Total, 21.9722 lock timeouts / sec for Page, 1.3278 lock timeouts / sec for RID, 0.0000 lock timeouts / sec for Object, 0.0000 lock timeouts / sec for Metadata, 0.5389 lock timeouts / sec for Key, 0.0000 lock timeouts / sec for HoBT, 0.0000 lock timeouts / sec for File, 0.0111 lock timeouts / sec for Extent, 0.0000 lock timeouts / sec for Database, 0.0000 lock timeouts / sec for Application, 0.0000 lock timeouts / sec for AllocUnit

    CRITICAL - 21.1250 lock timeouts / sec for _Total, 20.6333 lock timeouts / sec for Page, 0.0000 lock timeouts / sec for RID, 0.0000 lock timeouts / sec for Object, 0.0000 lock timeouts / sec for Metadata, 0.4917 lock timeouts / sec for Key, 0.0000 lock timeouts / sec for HoBT, 0.0000 lock timeouts / sec for File, 0.0000 lock timeouts / sec for Extent, 0.0000 lock timeouts / sec for Database, 0.0000 lock timeouts / sec for Application, 0.0000 lock timeouts / sec for AllocUnit

    Please let us know if you need any further details on this. Thanks for your time and support.

    Regards,

    Kalyan


    ----Learners Curiosity Never Ends----

    Thursday, January 02, 2014 12:57 PM

Answers

  • Thank You. Can we also assume this is happening because of long running queries?

    Regards,

    Kalyan


    ----Learners Curiosity Never Ends----


    Yes quite possible.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by Shiva Shakthi Monday, January 06, 2014 7:20 PM
    Friday, January 03, 2014 5:39 AM
    Moderator

All replies

  • Are you maintaining the database? i.e. index defrags etc?


    Jack of all trades, mastered by my wife.

    Thursday, January 02, 2014 1:01 PM
  • Hello Kalyan,

    Are you getting these alerts from Some Third party monitoring tool ?

    Did you ran Sp_who2 ,are you seeing any blocking as of now.SQl server by default does not timeout on locks unless you change it using set LOCK_TIMEOUT


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, January 02, 2014 1:05 PM
    Moderator
  • Hi Dolfie99 & Shanky,

    1) Yes we have maintenance plans for database.

    2) At the the time of lock time outs we have not seen any blockings.

    3) As you are saying our administrator might have set LOCK_TIMEOUT. But what we can do next to minimize these lock time out per secs issue.

    Please let us know if you need any further details on this. Thanks for your time and support.

    Regards,

    Kalyan 


    ----Learners Curiosity Never Ends----


    Thursday, January 02, 2014 1:39 PM
  • Hi Dolfie99 & Shanky,

    1) Yes we have maintenance plans for database.

    2) At the the time of lock time outs we have not seen any blockings.

    3) As you are saying our administrator might have set LOCK_TIMEOUT. But what we can do next to minimize these lock time out per secs issue.

    Please let us know if you need any further details on this. Thanks for your time and support.

    Regards,

    Kalyan 


    ----Learners Curiosity Never Ends----


    Hello,

    Ask him the reason why he did it .There is no use of setting lock timeout.When this period expires you get message as what you are getting.Nothing else is changed it just allows more concurrency.

    Read below article by Pinal In first he explained scenario where setting lock timeout would be helpful.Its totally your call to implement it or not we cannot do much about this message

    http://blog.sqlauthority.com/2013/01/25/sql-server-basic-explanation-of-query-hint-nowait-how-to-not-wait-on-locked-query/

    http://blog.sqlauthority.com/2013/01/28/sql-server-basic-explanation-of-set-lock_timeout-how-to-not-wait-on-locked-query/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, January 02, 2014 1:50 PM
    Moderator
  • Hi Shanky,

    Our DBA says he wants to have a track on how many lock outs are occurring so he has set that option. Still I have a question in mind regarding the performance impact due this critical lock time outs. As an SQL DBA do we have any procedure to minimize these lock outs.  Please share your views on this.  I also refer given articles lets hope it gives any solution about the issue. Thank You.

    Regards,

    Kalyan


    ----Learners Curiosity Never Ends----


    Thursday, January 02, 2014 2:08 PM
  • Hi Shanky,

    Our DBA says he wants to have a track on how many lock outs are occurring so he has set that option. Still I have a question in mind regarding the performance impact due this critical lock time outs. As an SQL DBA do we have any procedure to minimize these lock outs.  Please share your views on this. Thank You.

    Regards,

    Kalyan


    ----Learners Curiosity Never Ends----

    IMO this thinking is wrong .If you SET this option on busy system you are surely to get this message.There wont be any lock timeouts if you dont set this.As previously mentioned by default there is no timeout for locks, by setting this option you are forcing it to do so.Its very difficult to analyze or calculate after how much time will particular lock be released .what we should do is keep proper Isolation level tuned codes and let SQl server database engine decide this.

    One drawback i can see is if you set lock time out and if within that time request is not granted  your process or request will not complete so actually you are forcing your process to terminate .Same is shown by Pinal in the link.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, January 02, 2014 2:16 PM
    Moderator
  • Thank You. Can we also assume this is happening because of long running queries?

    Regards,

    Kalyan


    ----Learners Curiosity Never Ends----

    Thursday, January 02, 2014 2:24 PM
  • Thank You. Can we also assume this is happening because of long running queries?

    Regards,

    Kalyan


    ----Learners Curiosity Never Ends----


    Yes quite possible.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by Shiva Shakthi Monday, January 06, 2014 7:20 PM
    Friday, January 03, 2014 5:39 AM
    Moderator