간헐적인 SQL 시간 초과 오류 문제 해결 방법

간헐적인 SQL 시간 초과 오류 문제 해결 방법

여러 애플리케이션(시스템)에서 SQL Timeout 오류가 많이 발생하는 경우가 하루에 몇 가지 발생하고 있습니다.Data.SqlClient.SqlException:시간 제한이 만료되었습니다.작업이 완료되기 전에 경과한 시간 초과 기간 또는 서버가 응답하지 않습니다.)당사의 네트워크에는 웹 애플리케이션과 데스크톱 애플리케이션 모두 100개 이상의 다양한 애플리케이션이 있습니다.VB6 및 Classic 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 열을 사용하여 다른 모든 사용자가 대기하고 있는 잠금을 다시 추적할 수 있습니다.이는 하루에 몇 번만 발생하기 때문에 수동으로 실행할 경우 충분한 데이터를 확보하는 데 문제가 있을 수 있으므로 자동 시스템을 설치하여 이 출력을 정기적으로 덤프하거나 애플리케이션 시간 초과 예외로 인해 트리거되도록 하는 것이 좋습니다.또한 작업 모니터를 사용하여 피어에서 제안한 대로 쿼리 응답성 저하를 실시간으로 모니터링할 수 있습니다.

장기간 실행 중인 쿼리와 쿼리를 실행하는 응용프로그램을 찾으면, 해당 단일 응용프로그램의 시간 초과를 다른 모든 응용프로그램보다 줄여 시간 초과 Domino를 즉시 해결할 수 있습니다.그런 다음 코드를 검사하여 더 나은 솔루션을 결정해야 합니다.저장 프로시저 내에서 트랜잭션을 더 빨리 커밋하여 잠금이 유지되는 시간을 줄이거나 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에 내장된 Syn Attack Flood Protection을 트리거하고 있음을 의미합니다.짜증스럽게도 이 메시지를 누르면 Windows Server 내 또는 SQL 내에 기록된 메시지가 없습니다. 연결에 실패한 증상만 볼 수 있습니다. 이는 창에서 메시지를 수락하는 속도가 느려지고 대기열을 만들 수 있기 때문입니다.연결 관점에서 서버가 응답해야 할 때 응답하지 않는 것으로 나타납니다(메시지 도착을 확인하지도 않음).

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

SynAttackProtect까지 아래로 스크롤하면 Windows Server 2003 sp1 이후의 기본값이 이 기능을 기본적으로 활성화하는 것이었습니다.DDOS 보호 메커니즘은 사실상 DDOS 보호 메커니즘이며, 트리거하는 로깅이 부족하기 때문에 서버가 이 작업을 수행할 때 탐지하기가 매우 어렵습니다.

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

당신은 100개의 연결을 언급했고, 우리는 지속적으로 연결하고, 쿼리를 실행하고, 연결을 끊는 앱을 가지고 있었습니다. 그것은 연결을 열어두지 않았습니다.이는 각 기계 연결에 여러 개의 스레드가 있다는 것을 의미하며, 10개의 머신마다 여러 개의 스레드가 있으며, 이는 방어를 시작하기에 충분할 정도로 서로 다른 연결이 지속적으로 이루어지거나 삭제되는 것으로 간주되었습니다.

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

다른 포스터에서 제안한 것처럼 잠금 경합 문제가 있는 것 같습니다.몇 주 전에도 비슷한 문제에 직면했지만, 훨씬 더 간헐적으로 문제가 발생했으며 서버에서 dBA가 sp_who2를 실행하여 문제를 추적하기 전에 해결하는 경우가 많았습니다.

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

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

잠금이 문제가 되고 아직 잠금을 설정하지 않았다면 행 버전 기반 분리 수준을 구성하는 것이 좋습니다.

추적 및 프로파일링이 올바른 방향으로 진행되고 있습니다.시간 초과된 쿼리의 공통점을 찾는 것이 필요합니다. 모든 쿼리가 테이블 또는 인덱스의 작은 하위 집합에 도달할 가능성이 높습니다.일부 응용 프로그램에서 업데이트/삽입의 영향을 받는 인덱스를 사용하는 테이블의 쿼리에 영향을 주는 업데이트/삽입이 장기간 실행되고 있는 것 같습니다.

시간이 초과된 테이블의 하위 집합을 고려할 때 테이블에 어떤 인덱스가 있는지 확인하려면 약간 뒤로 작업해야 합니다.해당 테이블/인덱스에 연결되는 동시에 실행 중인 다른 쿼리를 찾습니다.이 작업을 수행하는 작은 업데이트/삽입 세트를 찾을 수 있을 것으로 확신합니다.

그럼 당신은 결정을 내려야 합니다.한 가지 옵션은 시간 초과되는 쿼리에 대한 잠금 힌트를 변경하는 것입니다.하지만 그것은 일반적으로 나쁜 관행입니다. 왜냐하면 그것은 진짜 문제를 한동안 가릴 것이기 때문입니다.시간 초과가 잠시 사라지는 것을 볼 수는 없지만, 선택한 힌트에 따라 더티 읽기가 발생하고 이러한 쿼리에서 가짜 데이터가 반환될 수 있습니다.그것은 타임아웃보다 더 나쁜 것으로 판명될 수도 있습니다 - 말하기 어렵습니다.

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

멋진 SQL 서버의 Dynamic Management Views 기능을 자세히 살펴보시기 바랍니다.

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

이 문서는 SQL 2005(DMV 기능의 첫 등장)를 위해 작성되었지만 DMV를 시작하는 데 좋은 출발점이 됩니다.SQL Server 2005의 성능 문제 문제 해결, 특히 '차단' 장.

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

제 생각에 100개의 연결은 너무 많은 것 같습니다.(다시 한 번 경험해 보니) 한 대의 기계로 20개의 연결을 요청해도 지나치게 낙관적일 수 있습니다.

이미 답을 알고 있는 것처럼 들리지만 한 곳을 더 찾아야 할 경우 임시 DB의 크기와 활동을 확인해 보는 것이 좋습니다.하루에 몇 번씩 성능이 심각하게 저하되고 때때로 시간이 초과되는 클라이언트 사이트에서 이러한 문제가 발생한 적이 있습니다.이 문제는 전체 서버 성능에 영향을 미칠 정도로 임시 DB에 영향을 미치고 있는 별도의 애플리케이션인 것으로 밝혀졌습니다.

계속되는 문제 해결에 행운을 빕니다!

SQL 서버에 안티바이러스가 설치된 경우에도 비슷한 문제가 발생하는 것을 보았습니다.AV의 자동 업데이트 기능이 서버를 클럭킹하고 SQL Server에 충분한 CPU를 허용하지 않았습니다.

또한 "SELECT GETDATE();"와 같이 매우 기본적인 SQL을 실행하거나 연결할 수 있는지 확인하는 작은 애플리케이션을 SQL 서버 자체에 설치했습니까?이렇게 하면 네트워크 가능성이 없어집니다.

업무의 일부로 매일 문제 해결을 수행하기 때문에 다음과 같은 작업을 수행하고자 합니다.

1. SQL Server 2008 R2이므로 제품의 일부로 제공되는 SQL Diag를 실행할 수 있습니다.자세한 내용은 온라인에서 책을 참조할 수 있습니다.간단히 말해서, 서버 측 추적 및 차단 스크립트를 캡처합니다.

2. 추적이 캡처되면 "주의" 이벤트를 찾습니다.그것은 오류를 받은 spid일 것입니다.SPID로 필터링하면 "주의" 앞에 RPC:Completed 이벤트가 표시됩니다.저기 시간 좀 확인해 보세요.그 시간이 30초입니까?"예"인 경우, 클라이언트는 SQL의 응답을 받기 위해 30초 동안 기다렸다가 "시간 초과" 상태가 되었습니다. [SQL이 절대 중지되지 않고 연결되므로 이는 클라이언트 설정입니다.]

3. 이제 실행 중인 쿼리가 실제로 30초가 걸리는지 확인하십시오.

4. 예인 경우 쿼리를 조정하거나 클라이언트에서 시간 초과 설정을 늘립니다.

5. 아니오인 경우 이 쿼리는 일부 리소스(차단됨)를 대기하고 있어야 합니다.

6. 이 시점에서 차단 스크립트로 돌아가서 "주의"가 온 시간 범위를 확인합니다.

위는 네트워크와 관련이 없는 SQL Server의 문제를 가정한 것입니다!

C# 애플리케이션 내에서 SQL Command 개체를 통해 쿼리를 실행할 때 SQL Server 2012/SP3에서 이 문제가 발생했습니다.명령은 테이블 매개 변수가 하나 있는 저장 프로시저를 간단히 호출한 것입니다. 약 300개의 정수 목록을 전달하고 있었습니다.이 절차는 세 개의 사용자 정의 함수를 호출하고 테이블을 매개 변수로 각 함수에 전달했습니다.CommandTimeout이 90초로 설정되었습니다.

SQL Server Management Studio 내에서 동일한 인수로 정확히 동일한 저장 프로시저를 실행하면 쿼리가 15초 만에 실행되었습니다.그러나 위의 설정을 사용하여 애플리케이션에서 실행할 때 SqlCommand가 시간 초과되었습니다.동일한 SqlCommand(데이터는 다르지만 비교 가능)가 몇 주 동안 성공적으로 실행되었지만, 이제 20개 이상의 정수를 포함하는 테이블 인수로 인해 실패했습니다.추적한 결과, SqlCommand 개체에서 실행할 때 데이터베이스는 잠금을 획득하는 데 90초 동안 전체 시간을 소비하고 시간 초과 시점에만 절차를 실행합니다.CommandTimeout 시간을 변경하여 선택한 시간에 상관없이 저장된 proc는 해당 기간이 끝날 때만 호출됩니다.따라서 SQL Server가 동일한 잠금을 무한정 반복적으로 획득하고 있었고, Command 개체의 시간 초과로 인해 SQL Server가 무한 루프를 중지하고 쿼리를 실행하기 시작했을 뿐이므로 성공하기에는 너무 늦은 것으로 추정됩니다.유사한 데이터를 사용하여 유사한 서버에서 동일한 프로세스를 시뮬레이션한 결과 이러한 문제가 발생하지 않았습니다.우리의 해결책은 전체 데이터베이스 서버를 재부팅하는 것이었고, 그 후 문제가 사라졌습니다.

따라서 일부 리소스가 누적적으로 사용되고 해제되지 않는 SQL Server에 문제가 있는 것으로 보입니다.결국 SqlConnection을 통해 연결하고 테이블 매개 변수가 포함된 SqlCommand를 실행하면 SQL Server는 무한 루프 수집 잠금으로 전환됩니다.루프는 SqlCommand 개체의 시간 초과에 의해 종료됩니다.솔루션은 재부팅하여 SQL Server에 대한 안정성을 (일시적으로) 복원하는 것입니다.

이 문제는 잘못된 쿼리로 인해 쿼리 실행 시간이 60초 이상 걸리거나 테이블에서 잠금이 발생하기 때문입니다.

이 문제는 교착 상태가 발생하고 있는 것 같습니다. 쿼리를 제때 완료하지 못하도록 차단하는 쿼리가 있습니다.쿼리의 기본 시간 초과는 60초이며, 그 이후에는 시간 초과에 대한 SQL 예외가 적용됩니다.

SQL Server 로그에서 교착 상태를 확인하십시오.명령 개체(Temp Solution)의 시간 초과를 증가시키기 위해 문제를 해결하는 다른 방법.

이러한 서버는 가상화되어 있습니까?다른 게시물에서 메모리 부족으로 인해 SQL 서버가 매우 느리게 실행된다는 기사를 읽었습니다.이 문제는 가상화 프로그램이 해당 가상 서버에서 사용하는 메모리 양을 제한하기 위해 사용한 이른바 메모리 벌룬 때문에 발생했습니다.물리적 메모리에 대한 부담은 SQL 서버 자체와 관련이 없기 때문에 찾기가 어려웠습니다.

일시적인 성능 저하의 또 다른 일반적인 원인은 바이러스 스캐너일 수 있습니다.새 바이러스 정의가 설치되면 다른 모든 프로세스에 문제가 발생하고 실행 속도가 매우 느려집니다.다른 자동 업데이트 프로세스를 확인하십시오. 예상치 못하게 많은 리소스가 필요할 수도 있습니다.행운을 빌어요!

Windows 팀에서 TLS-DHE* 암호를 해제하여 문제를 해결했습니다.

문제가 있었습니다. 한 서버(SQL Server 2012 및 윈도우즈 2012 R2)에서 실행되고 다른 서버(SQL Server 2016 SP2 및 윈도우즈 2019)에 연결되는 SSIS 패키지가 있으며, 일부 SSIS 패키지에 대해 때때로 시간 초과가 발생하여 무작위로 오류가 발생했습니다.윈도우즈 팀에서 TLS-DHE 암호를 해제한 후 문제가 해결되었습니다.

https://support.microsoft.com/en-us/topic/transport-layer-security-tls-connections-might-fail-or-timeout-when-connecting-or-attempting-a-resumption-326bd5b1-52a1-b367-8179-b154e5c01e90

이와 유사한 문제가 발생하여 기본값 때문이라는 것을 알게 되었습니다.넷프레임워크 설정

Sql 명령입니다.시간 초과

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

기본값은 Microsoft에서 위의 URL에 설명한 대로 30초입니다. 연결을 열기 전에 이 값을 더 큰 시간(초) 또는 -1로 설정하여 문제가 해결되는지 확인하십시오.

웹.config 또는 app.config 파일 또는 응용 프로그램 / 웹 서버 구성 파일의 설정일 수 있습니다.

저도 같은 문제를 겪고 있습니다.그리고 자주 실행되는 몇 가지 기능에 로그를 작성합니다.제가 자주 말하는 것은 약 2%의 시간을 의미합니다.그래서 로그의 일부는 절차나 쿼리의 시작 시간과 종료 시간을 삽입했습니다.그런 다음 총 실행 시간을 내림차순으로 며칠 동안의 로그를 정렬하는 간단한 보고서를 작성했습니다.여기 제가 발견한 것이 있습니다.

긴 실행 인스턴스는 항상 HH:00과 HH:02 사이 또는 HH:30과 HH:32 사이에 시작되었으며 이 시간 사이에 실행된 짧은 실행 쿼리는 없습니다.흥미롭군요...

이제 제가 경험하고 있던 혼란에는 실제로 더 많은 질서가 있는 것 같습니다.데이터베이스에 구현된 "간접 체크포인트"를 사용하여 복구 시간을 거의 1분 만에 달성했습니다.30분마다 체크포인트가 생성됩니다.

와, 정말 우연의 일치군요!

데이터베이스 복구 시간 변경에 대한 Microsoft의 온라인 설명서에는 다음과 같은 작은 경고가 포함되어 있습니다.

"간접 체크포인트용으로 구성된 데이터베이스의 온라인 트랜잭션 워크로드는 성능이 저하될 수 있습니다."

와, 상상해봐요...

그래서 나는 나의 회복 시간을 수정했고 더 이상의 문제는 없습니다.

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