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

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

저는 웹 애플리케이션에서 호출될 때마다 미친 듯이 시간이 초과되는 저장 프로시저를 가지고 있습니다.

SQL Profiler를 실행하고 시간이 초과된 통화를 추적한 결과 다음과 같은 사실을 알게 되었습니다.

1. MS SQL Management Studio 내에서 동일한 인수를 사용하여 문을 실행했을 때(실제로는 sql 프로파일 트레이스에서 프로시저 호출을 복사하여 실행했습니다):평균 5~6초면 끝납니다.
2. 그러나 웹 응용 프로그램에서 호출할 때 추적하는 데 30초 이상 걸리기 때문에 웹 페이지가 실제로 그때까지 시간 초과됩니다.

내 웹 애플리케이션에 자체 사용자가 있다는 것 외에도 모든 것이 동일(동일한 데이터베이스, 연결, 서버 등)합니다. 또한 웹 애플리케이션 사용자와 스튜디오에서 직접 쿼리를 실행해 보니 6초 이상 걸리지 않습니다.

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

추적 결과 실제 절차에 지연이 있는 것이 분명하므로 BLL > DAL 레이어 또는 Table 어댑터를 사용하는 것과는 무관하다고 생각합니다.그게 제가 생각할 수 있는 전부입니다.

EDIT 이 링크에서 ADO.NET이 설정하는 것을 발견했습니다.ARITHABORT- 일이 발생합니다. 은 true - 이대의시동간발때며이하생일런때이로, 제된은해는것입다니추가하결안책는지만부분좋안▁true▁-▁to▁to다▁-것-니▁add입▁truearound▁is▁work▁which▁suggested제안는,▁is▁and▁the추가하▁sometime▁happens를 추가하는 것입니다.with recompile저장 프로시저에 대한 옵션입니다.저 같은 경우에는 안 되는데 아마 이와 비슷한 것 같아요.ADO.NET이 또 무엇을 하는지 또는 어디서 사양을 찾을 수 있는지 아는 사람?

저는 과거에 비슷한 문제가 발생한 적이 있기 때문에 이 질문에 대한 해결책을 기대하고 있습니다.OP에 대한 Aaron Bertrand의 언급은 웹에서 실행될 때 쿼리 시간이 초과되었지만 SSMS에서 실행될 때 매우 빠르며 질문이 중복되지는 않지만 답변이 귀하의 상황에 매우 잘 적용될 수 있습니다.

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

참고 항목 ADO.NET 호출 T-SQL 저장 프로시저가 SqlTimeout 발생더 완전한 설명과 해결책을 포함하는 다른 예에 대한 예외.

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

저는 저장고에 사용되는 모든 매개변수를 로컬 변수로 변경했습니다.

예: 변경:

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에 대한 문제를 해결했습니다.

, 더, 그 중 수가 이전에 한 한 적이 .DBCC DROPCLEANBUFFERS그리고.DBCC FREEPROCCACHE시간 초과의 발생률이 크게 떨어졌습니다. 여전히 고립된 발생이 있으며, 일부는 계획 재생에 시간이 걸릴 것으로 의심되며, 일부는 SP가 실제로 성능이 저하되어 재평가가 필요합니다.

웹 애플리케이션이 SP에서 트랜잭션을 호출하기 전에 다른 DB 호출이 발생했을 수도 있습니다.웹 응용 프로그램에서 호출할 때 이 SP가 대기해야 하는 이유가 될 수 있습니다.웹 응용 프로그램의 이전 작업이 이 문제를 야기하는지 확인하기 위해 웹 응용 프로그램의 통화를 분리합니다(새 페이지에 배치).

다음을 통해 특정 캐시 실행 계획을 대상으로 지정할 수 있습니다.

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%your troublesome SP or function name etc%'

그런 다음 문제가 발생하는 실행 계획만 제거합니다. 예:

DBCC FREEPROCCACHE (0x050006003FCA862F40A19A93010000000000000000000000)

이제 느린 실행 절차나 기능을 찾고 다음 사항이 발견되면 자동으로 실행 계획을 정리하는 작업을 5분마다 실행하고 있습니다.

if exists (
    SELECT cpu_time, *
    FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
    --order by req.total_elapsed_time desc
    WHERE ([text] LIKE N'%your troublesome SP or function name etc%')
    and cpu_time > 8000
)
begin

    SELECT cp.plan_handle, st.[text]
    into #results
    FROM sys.dm_exec_cached_plans AS cp 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE [text] LIKE N'%your troublesome SP or function name etc%'
    delete #results where text like 'SELECT cp.plan_handle%'
    --select * from #results

    declare @handle varbinary(max)
    declare @handleconverted varchar(max)
    declare @sql varchar(1000)

    DECLARE db_cursor CURSOR FOR  
    select plan_handle from #results

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @handle

    WHILE @@FETCH_STATUS = 0   
    BEGIN   

        --e.g. DBCC FREEPROCCACHE (0x050006003FCA862F40A19A93010000000000000000000000)
        print @handle
        set @handleconverted = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@handle"))', 'VARCHAR(MAX)')
        print @handleconverted
        set @sql = 'DBCC FREEPROCCACHE (' + @handleconverted + ')'
        print 'DELETING: ' + @sql
        EXEC(@sql)

        FETCH NEXT FROM db_cursor INTO @handle
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor

    drop table #results

end

저장 프로시저(내 경우 테이블 기능)를 다시 컴파일하는 것만으로 가능

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

몇 번 검색한 후에 저는 그 행동에 대한 흥미로운 읽을거리를 발견했습니다.애플리케이션 속도가 느리고 SSMS 속도가 빠릅니까?

프로시저를 컴파일할 때 SQL Server는 @fromdate의 값이 변경되는 것을 알지 못하지만 @fromdate의 값이 NULL이라는 가정 하에 프로시저를 컴파일합니다.NULL을 사용하는 모든 비교는 UNKNOWN(알 수 없음)을 산출하므로 런타임에 @fromdate가 여전히 이 값을 가지고 있으면 쿼리가 행을 반환할 수 없습니다.SQL Server가 입력 값을 최종 진실로 사용할 경우 테이블에 전혀 액세스하지 않는 상수 검색만 사용하여 계획을 구성할 수 있습니다(SELECT * FROM Orders WHERE OrderDate > NULL 쿼리 실행).그러나 SQL Server는 런타임에 @fromdate의 값에 관계없이 올바른 결과를 반환하는 계획을 생성해야 합니다.반면에, 모든 가치에 최선의 계획을 세워야 할 의무는 없습니다.따라서 행이 반환되지 않는 것으로 가정하므로 SQL Server는 인덱스 검색에 사용됩니다.

문제는 매개 변수가 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 

그것은 다시 매력적으로 작동했습니다.

--BEFORE
CREATE PROCEDURE [dbo].[SP_DEMO]
( 
    @ToUserId bigint=null
 )
AS
BEGIN
SELECT * FROM tbl_Logins WHERE LoginId = @ToUserId 
END
--AFTER CHANGING TO IT WORKING FINE
CREATE PROCEDURE [dbo].[SP_DEMO]
( 
    @ToUserId bigint=null
 )
AS
BEGIN
DECLARE @Toid bigint=null
SET @Toid=@ToUserId
SELECT * FROM tbl_Logins WHERE LoginId = @Toid 
END

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