웹에서 호출하면 저장 프로 시저가 느리고 Management Studio에서 빠르게

웹에서 호출하면 저장 프로 시저가 느리고 Management Studio에서 빠르게

---

웹 응용 프로그램에서 호출 될 때마다 시간 초과되는 저장 프로 시저가 있습니다.

Sql Profiler를 실행하고 시간이 초과 된 호출을 추적하고 마침내 다음을 발견했습니다.

1. 동일한 인수를 사용하여 MS SQL Management Studio 내에서 명령문을 실행하면 (실제로 SQL 프로필 추적에서 프로 시저 호출을 복사하여 실행했습니다.) 평균 5 ~ 6 초 만에 완료됩니다.
2. 그러나 웹 응용 프로그램에서 호출하면 30 초 이상 (추적 중)이 걸리므로 웹 페이지가 실제로 시간 초과됩니다.

내 웹 응용 프로그램에 자체 사용자가 있다는 사실 외에도 모든 것이 동일합니다 (동일한 데이터베이스, 연결, 서버 등). 또한 웹 응용 프로그램의 사용자와 함께 스튜디오에서 직접 쿼리를 실행하려고 시도했으며 6 개 이상 걸리지 않습니다. 비서.

무슨 일이 일어나고 있는지 어떻게 알 수 있습니까?

트레이스가 실제 절차에 지연이 있음을 분명히 보여 주므로 BLL> DAL 레이어 또는 테이블 어댑터를 사용한다는 사실과 관련이 없다고 가정합니다. 그게 제가 생각할 수있는 전부입니다.

편집 나는 이 링크 에서 ADO.NET ARITHABORT이 true로 설정한다는 것을 알았습니다. 대부분의 경우 좋지만 언젠가는 이런 일이 발생하며 제안 된 해결 with recompile방법은 저장된 proc에 옵션을 추가하는 것입니다. 제 경우에는 작동하지 않지만 이와 매우 유사한 것 같습니다. ADO.NET의 다른 기능이나 사양을 어디에서 찾을 수 있는지 아는 사람이 있습니까?

---

과거에 비슷한 문제가 발생 했으므로이 질문에 대한 해결책을 찾고 싶습니다. OP에 대한 Aaron Bertrand의 의견으로 인해 웹에서 실행할 때 Query 시간이 초과되었지만 SSMS 에서 실행되면 매우 빠르며 질문이 중복되지는 않지만 대답은 귀하의 상황에 매우 잘 적용될 수 있습니다.

본질적으로 SQL Server에 손상된 캐시 된 실행 계획이있는 것처럼 들립니다. 웹 서버에서 잘못된 계획을 세우고 있지만 ARITHABORT 플래그에 다른 설정이 있기 때문에 SSMS는 다른 계획에 도달합니다 (그렇지 않으면 특정 쿼리 / 저장된 프로세스에 영향을주지 않음).

ADO.NET에서 T-SQL 저장 프로 시저를 호출하면 SqlTimeoutException이 발생 하고보다 완전한 설명과 해결 방법이있는 다른 예제를 참조하십시오 .

---

또한 쿼리가 웹에서 느리고 SSMS에서 빠르게 실행되는 것을 경험했으며 결국 문제가 매개 변수 스니핑이라는 것을 발견했습니다.

나를위한 수정 사항은 sproc에서 사용되는 모든 매개 변수를 지역 변수로 변경하는 것이 었습니다.

예. 변화:

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
SELECT * FROM Table WHERE ID = @param1 

에:

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
DECLARE @param1a int
SET @param1a = @param1
SELECT * FROM Table WHERE ID = @param1a

이상하게 보이지만 문제가 해결되었습니다.

---

스팸이 아니라 다른 사람들에게 도움이되는 해결책으로 우리 시스템은 높은 수준의 시간 초과를 보았습니다.

사용하여 재 컴파일 할 저장 프로 시저를 설정하려고했는데 sp_recompile이로 인해 한 SP의 문제가 해결되었습니다.

궁극적으로 SP의 타이밍 아웃, 사용하여, 그렇게 해본 적이없는 많은 것이 있었다 그 많은 수 있었다 DBCC DROPCLEANBUFFERS및 DBBC FREEPROCCACHE시간 제한의 입사 비율이 크게 하락했다 - 고립 된 사건이 아직도있다, 나는 계획의 재생을 의심 곳 일부는 복용입니다 일부 SP의 성능이 실제로 저조하여 재평가가 필요한 경우도 있습니다.

---

웹 응용 프로그램이 SP를 호출하기 전에 만들어진 다른 DB 호출이 트랜잭션을 열린 상태로 유지하는 것일 수 있습니까? 웹 애플리케이션에서이 SP를 호출 할 때이 SP가 대기하는 이유 일 수 있습니다. 웹 응용 프로그램의 일부 이전 작업이이 문제를 일으키는 지 확인하기 위해 웹 응용 프로그램에서 호출을 분리 (새 페이지에 배치)한다고 말합니다.

---

저장 프로 시저 (제 경우에는 테이블 함수)를 다시 컴파일하는 것이 효과적이었습니다.

---

@Zane이 매개 변수 스니핑 때문일 수 있다고 말한 것처럼. 동일한 동작을 경험했고 프로 시저의 실행 계획과 sp의 모든 문을 연속으로 살펴 보았습니다 (프로 시저를 구성하는 모든 문을 복사하고 매개 변수를 변수로 선언하고 변수에 대해 동일한 값을 매개 변수가 있었다). 그러나 실행 계획은 완전히 달랐습니다. sp 실행에는 3-4 초가 걸렸고 정확히 동일한 값을 가진 행의 문이 즉시 반환되었습니다.

인터넷 검색을 한 후 그 동작에 대한 흥미로운 읽기를 찾았 습니다. 응용 프로그램에서 느리게, SSMS에서 빠르십니까?

When compiling the procedure, SQL Server does not know that the value of @fromdate changes, but compiles the procedure under the assumption that @fromdate has the value NULL. Since all comparisons with NULL yield UNKNOWN, the query cannot return any rows at all, if @fromdate still has this value at run-time. If SQL Server would take the input value as the final truth, it could construct a plan with only a Constant Scan that does not access the table at all (run the query SELECT * FROM Orders WHERE OrderDate > NULL to see an example of this). But SQL Server must generate a plan which returns the correct result no matter what value @fromdate has at run-time. On the other hand, there is no obligation to build a plan which is the best for all values. Thus, since the assumption is that no rows will be returned, SQL Server settles for the Index Seek.

문제는 내가 null로 남을 수있는 매개 변수가 있고 null로 전달되면 기본값으로 초기화된다는 것입니다.

create procedure dbo.procedure
    @dateTo datetime = null
begin
    if (@dateTo is null)
    begin
        select @dateTo  = GETUTCDATE()
    end

    select foo
    from dbo.table
    where createdDate < @dateTo
end

내가 그것을 변경 한 후

create procedure dbo.procedure
    @dateTo datetime = null
begin
    declare @to datetime = coalesce(@dateTo, getutcdate())

    select foo
    from dbo.table
    where createdDate < @to
end 

그것은 다시 매력처럼 작동했습니다.

참고 URL : https://stackoverflow.com/questions/6585417/stored-procedure-slow-when-called-from-web-fast-from-management-studio