간헐적 인 SQL 시간 초과 오류를 해결하는 방법

간헐적 인 SQL 시간 초과 오류를 해결하는 방법

---

여러 애플리케이션 (System.Data.SqlClient.SqlException : Timeout expired)에서 SQL Timeout 오류가 많이 발생하는 인스턴스가 하루에 몇 개 발생했습니다. 작업이 완료되기 전에 제한 시간이 경과했거나 서버가 응답하지 않습니다. .) 네트워크에는 웹 및 데스크톱 앱 모두 100 개 이상의 서로 다른 애플리케이션이 있습니다. VB6 및 클래식 ASP에서 .NET 4에 이르는 모든 것. 부작용을 보여주는 모든 종류의 데이터를 찾을 수 있지만 원인을 정확히 파악할 수 없습니다. DBA는 SQL 서버에 아무런 문제가 없다고 말하고 IT는 웹 서버 나 네트워크에 문제가 없다고 말 했으므로 물론이 문제를 해결하기 위해 노력하고 있습니다.

나는 이것을 시도하고 추적하기 위해 내가 할 수있는 다른 문제 해결에 대한 제안을 찾고 있습니다.

클러스터에서 SQL Server 2008 R2를 실행하고 있습니다. Windows Server 2003에서 2008까지 다양한 종류의 다양한 서버가 연결되어 있습니다.

지금까지 내가 한 작업은 다음과 같습니다.

• 장기 실행 쿼리 및 교착 상태에 대한 SQL 추적을 실행합니다. 이것은 문제가 발생했을 때 교착 상태가 없음을 보여 주며, 장기 실행 쿼리는 모두 타임 아웃 오류와 일치하지만 원인이 아닌 부작용으로 보입니다. 일반적으로 즉시 반환되는 매우 기본적인 쿼리는 실행하는 데 30, 60 또는 120 초가 걸립니다. 이것은 몇 분 동안 발생하고 모든 것이 선택되고 그 후에 잘 작동합니다.
• 성능 모니터를 사용하여 연결 풀 연결을 추적하십시오. 이로 인해 제한 시간 근처에 연결 수가 급증하는 경우도 있지만 여전히 기본 연결 제한 인 100 개까지 절반도되지 않습니다. 다시 말하지만, 여기에는 원인을 가리키는 것처럼 보이는 것이 없습니다.
• 웹 애플리케이션을 다른 앱 풀로 분리합니다. 우리는 주요 문제 (대부분 수다 스러움 등)라고 생각하는 앱의 범위를 좁히고 별도의 응용 프로그램 풀에 배치하려고했지만 아무런 영향을주지 않거나 범위를 좁히는 데 도움이되지 않습니다.
• SQL Server에서 디스크 사용량을 모니터링합니다. SQL 서버에서 몇 가지 모니터링을 수행했으며 이러한 시간 초과가 발생할 때 스파이크 나 문제 징후가 보이지 않습니다.
• 확인 된 TempDB 가 문제의 원인이 아닙니다.

다시 돌아와서 우리가 시도한 다른 것을 생각하면 더 추가하겠습니다. 다음에 문제를 해결할 방법에 대한 몇 가지 아이디어를 알려주세요.

---

장기 실행 쿼리 및 교착 상태에 대한 SQL 추적을 실행합니다. 이것은 문제가 발생했을 때 교착 상태가 없음을 보여 주며, 장기 실행 쿼리는 모두 타임 아웃 오류와 일치하지만 원인이 아닌 부작용으로 보입니다. 일반적으로 즉시 반환되는 매우 기본적인 쿼리는 실행하는 데 30, 60 또는 120 초가 걸립니다. 이것은 몇 분 동안 발생하고 모든 것이 선택되고 그 후에 잘 작동합니다.

일부 쿼리 / 트랜잭션이 완료 될 때까지 데이터베이스를 잠그는 것처럼 보입니다. 어떤 쿼리가 차단되고 있는지 확인하고 다른 프로세스를 차단하지 않도록 다른 시간에 다시 작성 / 실행해야합니다. 이 순간 대기 쿼리는 시간 초과됩니다.

파헤쳐 야 할 추가 포인트는 트랜잭션 로그 및 데이터베이스의 자동 증가 크기입니다. 현재 파일의 백분율 대신 고정 크기로 설정하십시오. 파일이 커지면 충분한 공간을 할당하는 데 걸리는 시간이 결국 트랜잭션 시간 초과로 길어집니다. 그리고 귀하의 db가 중단됩니다.

---

성능 문제는 CPU, IO 또는 잠금 경합으로 귀결됩니다. IO를 배제한 것 같습니다. 이것은 숫자 크 런처가 아니라 데이터베이스이기 때문에 CPU가 문제가 아니라고 생각합니다. 따라서 잠금 경합이 남습니다.

쿼리 시간이 초과되는 동안 sp_who2를 실행할 수있는 경우 BlkBy 열을 사용하여 다른 모든 사람이 대기중인 잠금을 유지하는 것으로 역 추적 할 수 있습니다. 이것은 하루에 몇 번만 발생하기 때문에 수동으로 실행하는 경우 충분한 데이터를 포착하는 데 문제가있을 수 있습니다. 따라서 정기적으로이 출력을 덤프하도록 자동화 된 시스템을 조작하거나 응용 프로그램 시간 초과 예외. 또한 Activity Monitor를 사용하여 피어가 제안한대로 쿼리 응답 성능 저하를 실시간으로 볼 수 있습니다.

장기 실행 쿼리와이를 실행하는 응용 프로그램을 찾으면 해당 단일 응용 프로그램의 시간 제한을 다른 모든 응용 프로그램 아래로 줄임으로써 시간 제한의 도미노를 즉시 해결할 수 있습니다 (지금은 더 길어야 함). 그런 다음 코드를 검사하여 더 나은 솔루션을 결정해야합니다. sproc 내에서 트랜잭션을 더 빨리 커밋하여 잠금이 유지되는 시간을 줄이거 나 NOLOCK 또는 UPDLOCK과 같은 힌트를 사용하여 읽기 쿼리에 필요한 잠금을 줄일 수 있습니다.

sp_who2에 대한 추가 정보는 다음과 같습니다. http://sqlserverplanet.com/dba/using-sp_who2/

및 쿼리 힌트 : http://msdn.microsoft.com/en-us/library/ms181714.aspx http://msdn.microsoft.com/en-us/library/ms187373.aspx

---

약간 긴 샷이지만, 얼마 전에 실험실에서 SQL Server가 응답하지 않는 것처럼 보였습니다. CPU 나 SQL Server 내에서 추적 할 수있는 모든 항목이 급증했기 때문이 아니라 모든 테스트에서 작동하는 것처럼 보였지만 연결이 실패했습니다. 약간의 부하에서.

이 문제는 서버에 대한 트래픽 양으로 인해 Windows 내에서 내장 된 Windows Syn Attack Flood Protection을 트리거하는 것으로 밝혀졌습니다. 성가 시게도 Windows 서버 또는 SQL 내에 기록 된 메시지가 없습니다. 연결에 실패한 기호 만 표시됩니다. 이는 Windows가 메시지 수신 속도가 느려지고 대기열을 작성하기 때문입니다. 연결 관점에서 서버가 응답해야 할 때 응답하지 않는 것처럼 보입니다 (메시지 도착을 확인하지도 않음).

http://msdn.microsoft.com/en-us/library/ee377084(v=bts.10).aspx

SynAttackProtect까지 아래로 스크롤하면 Windows Server 2003 sp1에서 기본적으로이 기능을 활성화하는 것이 기본값 인 것을 볼 수 있습니다. 이는 사실상 DDOS 보호 메커니즘이며, 트리거되는 로깅이 없기 때문에 서버가이를 수행 할 때 감지하기가 매우 어렵습니다.

그것이 밝혀지기까지 MS 실험실에서 3 일이 걸렸습니다.

100 개의 연결을 언급 하셨는데, 지속적으로 연결하고 쿼리를 실행 한 다음 연결을 끊는 앱이 있었지만 연결을 열어 두지 않았습니다. 이것은 우리가 이것을 수행하는 각 머신 연결에 여러 스레드, 10 개의 머신, 머신 당 다중 스레드를 가지고 있음을 의미하며, 방어를 트리거하기에 충분한 다른 연결이 지속적으로 생성 / 삭제되는 것으로 간주되었습니다.

당신이 그 수준에 있는지 (MS가 명확하게 정의한 임계 값이 아니기 때문에) 말하기 어렵습니다.

---

다른 포스터가 제안한 것처럼 잠금 경합 문제가있는 것 같습니다. 우리는 몇 주 전에 비슷한 문제에 직면했습니다. 그러나 우리는 훨씬 더 간헐적이며 종종 문제를 추적하기 위해 sp_who2를 실행할 DBA를 서버에 가져 오기 전에 정리했습니다.

결국 잠금이 특정 임계 값을 초과하면 전자 메일 알림을 구현했습니다. 이를 배치 한 후에는 잠긴 프로세스를 식별하고 문제를 해결하기 위해 적절한 경우 커밋되지 않은 읽기로 격리 수준을 변경할 수있었습니다.

다음은 이러한 유형의 알림을 구성하는 방법에 대한 개요를 제공하는 문서입니다.

잠금이 문제인 것으로 판명되고 아직 그렇게하고 있지 않다면 행 버전 관리 기반 격리 수준 을 구성 하는 것이 좋습니다 .

---

추적 및 프로파일 링으로 올바른 길을 가고 있습니다. 당신이해야 할 일은 타임 아웃이 공통적으로 가지는 쿼리가 무엇인지 찾는 것입니다. 그들은 모두 테이블이나 인덱스의 작은 부분 집합에 부딪 힐 가능성이 높습니다. 일부 응용 프로그램에는 업데이트 / 삽입의 영향을받는 인덱스를 사용하는 테이블의 쿼리에 영향을 미치는 장기 실행 업데이트 / 삽입이 있다고 생각합니다.

약간 거꾸로 작업해야합니다. 테이블의 하위 집합이 시간 초과 된 경우 해당 테이블에 어떤 인덱스가 있는지 확인합니다. 해당 테이블 / 인덱스를 터치하는 smae 시간에 실행중인 다른 쿼리를 찾으십시오. 이 작업을 수행하는 작은 업데이트 / 삽입 세트를 찾을 수있을 것입니다.

그런 다음 몇 가지 결정을 내릴 수 있습니다. 한 가지 옵션은 시간 초과 된 쿼리에 대한 잠금 힌트를 변경하는 것입니다. 그러나 그것은 한동안 진짜 문제를 가릴 것이기 때문에 엄청나게 나쁜 습관입니다. 시간 제한이 잠시 사라지는 것을 확인하는 동안 선택한 힌트에 따라 더티 읽기가 끝나고 해당 쿼리에서 가짜 데이터가 돌아올 수 있습니다. 그것은 시간 초과보다 더 나쁠 수 있습니다-말하기 어렵습니다.

가장 좋은 방법은 발견 한 업데이트 / 삽입물을 제출하는 응용 프로그램을 파악하고 시간이 오래 걸리는 이유를 알아내는 것입니다.

---

정말 멋진 SQL Server의 동적 관리 뷰 기능을 자세히 살펴 보시기 바랍니다 .

동적 관리보기 및 기능은 서버 인스턴스의 상태를 모니터링하고 문제를 진단하며 성능을 조정하는 데 사용할 수있는 서버 상태 정보를 반환합니다.

이 문서는 SQL 2005 용으로 작성되었지만 DMV (DMV 기능 첫 등장) : SQL Server 2005의 성능 문제 해결 , 특히 '차단'장에 대한 좋은 시작입니다 .

---

이러한 문제에 대한 나의 경험은 (SQL Server가 아니라) 과도한 멀티 태스킹이 종종 문제의 원인이라는 것입니다. 비슷한 / 연결된 데이터 / 테이블이 많은 연결에서 (거의) 동시에 쿼리되는 경우 DBMS는 모든 격리를 확인하는 데 문제가있을 수 있습니다. 이것은 일부 연결이 다른 연결에 의해 수행되기를 기다리도록 만드는 것과 관련하여 디스크 사용 문제가 아닙니다. 동기화는 CPU 사용량 측면에서 매우 비쌉니다.

제 생각에는 100 개의 연결이 너무 많습니다. (내 경험으로 다시 한 번) 한 대의 컴퓨터에서 20 개의 연결을 요청해도 지나치게 낙관적 일 수 있습니다.

---

Sounds like you may already have your answer but in case you need one more place to look you may want to check out the size and activity of your temp DB. We had an issue like this once at a client site where a few times a day their performance would horribly degrade and occasionally timeout. The problem turned out to be a separate application that was thrashing the temp DB so much it was affecting overall server performance.

Good luck with the continued troubleshooting!

---

I've seen similar problems happen if anti-virus was installed on the SQL server. The AV's auto-update features were clocking the server and not allowing enough CPU for SQL Server.

Also, have you put a small application on the SQL server itself that verifies that connections can be made or runs very basic SQL like "SELECT GETDATE();"? This would eliminate network possibilities.

---

Since I do troubleshooting everyday as a part of my job, here is what I would like to do:

1. Since it's SQL Server 2008 R2, you can run SQLDiag which comes as a part of the product. You can refer books online for more details. In brief, capture Server Side trace and blocker script.

2. Once trace is captured, look for "Attention" event. That would be the spid which has received the error. If you filter by SPID, you would see RPC:Completed event before "Attention". Check the time over there. Is that time 30 seconds? If yes, then client waited for 30 second to get response from SQL and got "timed out" [This is client setting as SQL would never stop and connection]

3. Now, check if the query which was running really should take 30 seconds?

4. If yes then tune the query or increase the timeout setting from the client.

5. If no then this query must be waiting for some resources (blocked)

6. At this point go back to Blocker Script and check the time frame when "Attention" came

Above is assuming that issue is with SQL Server not network related!

---

The issue is because of a bad query the time to executing query is taking more than 60 seconds or a Lock on the Table

The issue looks like a deadlock is occurring; we have queries which are blocking the queries to complete in time. The default timeout for a query is 60 secs and beyond that we will have the SQLException for timeout.

Please check the SQL Server logs for deadlocks. The other way to solve the issue to to increase the Timeout on the Command Object (Temp Solution).

---

Are these servers virtualized? On another post I've read about a SQL server running sometimes very slowly because of lack of sufficient memory. This in turn was caused by a so-called memory balloon that the virtualizer used to limit the amount of memory used by that virtual server. It was hard to find because the pressure on physical memory had nothing to do with the SQL server itself.

Another common cause for a temporary performance degradation might be a virus scanner. When a new virus definition is installed, all other processes will suffer and run very slow. Check out any other automatic update process, this might also take a lot of resources quite unexpectedly. Good luck with it!

---

We experienced this with SQL Server 2012 / SP3, when running a query via an SqlCommand object from within a C# application. The Command was a simple invocation of a stored procedure having one table parameter; we were passing a list of about 300 integers. The procedure in turn called three user-defined functions and passed the table as a parameter to each of them. The CommandTimeout was set to 90 seconds.

When running precisely the same stored proc with the same argument from within SQL Server Management Studio, the query ran in 15 seconds. But when running it from our application using the above setup, the SqlCommand timed out. The same SqlCommand (with different but comparable data) had been running successfully for weeks, but now it failed with any table argument containing more than 20 or so integers. We did a trace and discovered that when run from the SqlCommand object, the database spent the entire 90 seconds acquiring locks, and would invoke the procedure only at about the moment of the timeout. We changed the CommandTimeout time, and no matter time what we selected the stored proc would be invoked only at the very end of that period. So we surmise that SQL Server was indefinitely acquiring the same locks over and over, and that only the timeout of the Command object caused SQL Server to stop its infinite loop and begin executing the query, by which time it was too late to succeed. A simulation of this same process on a similar server using similar data exhibited no such problem. Our solution was to reboot the entire database server, after which the problem disappeared.

So it appears that there is some problem in SQL Server wherein some resource gets cumulatively consumed and never released. Eventually when connecting via an SqlConnection and running an SqlCommand involving a table parameter, SQL Server goes into an infinite loop acquiring locks. The loop is terminated by the timeout of the SqlCommand object. The solution is to reboot, apparently restoring (temporary?) sanity to SQL Server.

---

I had an issue similar to this and found out is was due to a default .Net framework setting

Sqlcommand.Timeout

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=VS.100).aspx

The default is 30 seconds as sated in the above url by Microsoft, try setting this to a higher number of seconds or maybe -1 before opening the connection to see if this solves the issue.

It maybe a setting in your web.config or app.config files or on you applicaiton / web server config files.

참고URL : https://stackoverflow.com/questions/7743725/how-to-troubleshoot-intermittent-sql-timeout-errors