asked
Need help with SQL,IIS timeout
Hi, I need from experts to help me with troubleshooting with SQL Timeouts We have 4 servers with the below Environment: Server - 64 bit. OS - Windows 2012. 23GB Ram,8 cpu. SQL Server 2012 SP1. The First server has the database, Second server has the application, and the other servers are domain controller. We have many clients connect to the network about 200 pc , 150 data entry insert data through asp application design in one page, the 50 data entry audit and check the insert from the first group (150pc). When the audit step starting to audit the Web Server starts throwing SQL Timeout exceptions. A few per minutes at first and rapidly increasing to hundreds per minute; The fix is to restart IIS. And then everything goes back to normal, after 20 minters stop again and restart the IIS then everything goes back to normal, after 2 or 1 hours it stop again , if I stop the audit step the timeout will not happen anymore. I have many counter I did in database server, and application server I checked No deadlock found but with Context Switches/sec counters I have the read below:- ![alt text][1] I checked the event viewer for windows in database and application server no error found on time with the timeout.but I found Event ID 7036. Any troubleshooting that help me to figure out how to solve this problem , or how to start to looking at. Thanks in advance. [1]: /storage/temp/1354-digram2.jpg
Hard to say without a lot more information, but the suggestion based on what you have is that you're hitting lots of CPU and that may be leading to slow performance. Instead of looking for deadlocks, which are going to generate very clear errors, I'd suggest looking for blocking or blocked processes on the server. I'd also suggest checking sys.dm_os_wait_stats to understand what is causing the server to run slow. You can also look at that DMV before and after you start processing these queries to understand what they're waiting on. Other than that, I'd guess that you're down to traditional performance troubleshooting to understand what is being called, how it's being called and why you're experiencing so many timeouts.
Also I have these information:- From Extended Events I have error_reported and the message appear every mantes. message Network error code 0x2746 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 0 ms, enqueued 0 ms, network writes 0 ms, network reads 0 ms, establishing SSL 0 ms, network reads during SSL 0 ms, network writes during SSL 0 ms, secure calls during SSL 0 ms, enqueued during SSL 0 ms, negotiating SSPI 0 ms, network reads during SSPI 0 ms, network writes during SSPI 0 ms, secure calls during SSPI 0 ms, enqueued during SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms.
Sorry. That doesn't narrow things down much. In fact, it vaguely suggests the problem may be outside SQL Server, your security layer is configured incorrectly, you're using the wrong logins, something wrong with the network topology or technology...
You can see which processes might be blocking others by selecting from sys.dm_exec_requests. That will also show you what resources any active queries are waiting on. You can join that dynamic management view to others such as sys.dm_exec_sql_text and sys.dm_exec_query_plan to look at the query and the execution plan respectively.
14 People are following this question.
