SQL Server Performance

Application connection terminates abruptly and randomly

Discussion in 'ALL SQL SERVER QUESTIONS' started by Brahma, Feb 23, 2012.

  1. Brahma New Member

    Dear all,

    User complaints that application throws our timed out error, I could find nothing in logs and this issue happens random mostly in weekends. And if app guys restart the process by terminating existing one they are doing it well.

    Please suggest me a how to and where to start with ?
  2. FrankKalis Moderator

    A time-out is a client side concept, the server doesn't know about. So, most likely you won't find anything in the server logs. Maybe you have some client logs to check? Also, check if that happens always around the same time, what statements do time out, then check the code for these statements and see if there is something to optimize. You might also request the app developers to increase the time-out in their code in these are crucial parts that need to complete successfully.
  3. Brahma New Member

    Sure, I should mention that with app guys, and get back so that would help others.
  4. Brahma New Member

    i would be getting logs, and connection parameters from app people, lets ee if that would be helpfull
  5. Shehap MVP, MCTS, MCITP SQL Server

    As Frank Said, let SQL Server log the last one of your checklist of DB checks , thereby let me enumerate for you what you have to check basically for such timeouts :

    1. Check all locks /Deadlocks by the same time slot of .net timeout .

    2. Check all expensive queries by the same time slot (Follow up my article for this regard http://www.sqlserverpath.org/blog/page/3/ )

    3. Check all failed logins took place by the same time slot (Using Server Audit of 2008 better to check Network error codes as well)

    4. Check all different types of overload on the 4 HW resources :

    · IO Subsystem (Local /SAN storage )

    · CPU

    · Network

    · Memory

    5. If found something , you have to identify its reason which might be of 3 probable assumptions basically :

    · Expensive Queries of end users

    · SQL Server activities like rebuild indexes , check integrity ….etc

    · Activities of other third party tools like Antivirus or backup tools…etc (Could be figured out using UCP monitor of 2008 R2)

    6. Check DB files & storage capacity using UCP monitor of 2008 R2

    7. Check SQL Server log for any strange incident

    8. Check DTC accessibility between APP +DB Servers in case of that SQL Service depends on DTC Service for OLTP transactions.

    9. Check event viewer of DB servers to find out any strange incidents that could correlate with these timeouts

    10. Run profiler to trace specific events if some problem is frequently repeated at specific times.

    If nothing , so it has nothing to do with DB end and APP support has to figure out relevant incidents from other layers like .net , network ..etc

    Please let me know if you need any help regarding any of the above points

Share This Page