locked
How can I find the cause of lock timeout? RRS feed

  • Question

  • I have a problem related to sql server performance.

    I'm managing a big DB with many stored procedured and users connecting to it. Some times it is very slow. I was checking the blocked processes using sp_who and no process was blocked but in the same time there were many lock:timeout events in the profiler!!!! what is the reason? how can I find which query/stored procedure is causing the lock timeout and why there was no blocked process in sysProcesses??

    Any help is appreciated.

    Thanks

    Thursday, March 5, 2009 10:05 AM

Answers

  • Hi,


    The following KB article presents how to monitor blocking in SQL Server 2005 and SQL Server 2000.

    http://support.microsoft.com/kb/271509



    If you have any more questions, please let me know.


    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, March 10, 2009 7:51 AM
  • maryam1123 said:

    I'm managing a big DB with many stored procedured and users connecting to it. Some times it is very slow.


    That is typical with most production DB-s due to varying user volume.

    If you don't already have it, I suggest that you implement a weekend index REBUILD job.  For extremely dynamic tables you may have to setup a nightly INDEX REBUILD with 80% FILLFACTOR.

    The SQL Server 2005 Performance Dashboard Reports  are very helpful in SQL Server database performance tuning.  They will help you to identify costly stored procedures as well as other performance factors.

    Let us know if helpful.

    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Tuesday, March 10, 2009 1:23 PM

All replies

  • I have a problem related to sql server performance.

    I'm managing a big DB with many stored procedured and users connecting to it. Some times it is very slow. I was checking the blocked processes using sp_who and no process was blocked but in the same time there were many lock:timeout events in the profiler!!!! what is the reason? how can I find which query/stored procedure is causing the lock timeout and why there was no blocked process in sysProcesses??

    Any help is appreciated.

    Thanks

    • Merged by Madhu K Nair Thursday, March 5, 2009 12:25 PM both threads are started by same person for same issue
    Thursday, March 5, 2009 9:48 AM
  • There are mainly 2 reasons for such case, one is memory issues, please check the buffer cache hit ratio, and the other is the lock is used up, you can enlarge the lock number using sp_configure.  

    Please keep update your case.


    zhudonhua
    Thursday, March 5, 2009 2:00 PM
  • Hi,


    The following KB article presents how to monitor blocking in SQL Server 2005 and SQL Server 2000.

    http://support.microsoft.com/kb/271509



    If you have any more questions, please let me know.


    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, March 10, 2009 7:51 AM
  • maryam1123 said:

    I'm managing a big DB with many stored procedured and users connecting to it. Some times it is very slow.


    That is typical with most production DB-s due to varying user volume.

    If you don't already have it, I suggest that you implement a weekend index REBUILD job.  For extremely dynamic tables you may have to setup a nightly INDEX REBUILD with 80% FILLFACTOR.

    The SQL Server 2005 Performance Dashboard Reports  are very helpful in SQL Server database performance tuning.  They will help you to identify costly stored procedures as well as other performance factors.

    Let us know if helpful.

    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Tuesday, March 10, 2009 1:23 PM