다음을 통해 공유

데이터베이스 애플리케이션과 SSMS 간의 쿼리 성능 차이 문제 해결

  • 아티클

데이터베이스 애플리케이션에서 쿼리를 실행하면 SQL Server Management Studio(SSMS), Azure Data Studio 또는 SQLCMD와 같은 애플리케이션에서 동일한 쿼리보다 느리게 실행됩니다.

이 문제는 다음과 같은 이유로 발생할 수 있습니다.

  • 쿼리는 다른 매개 변수 또는 변수를 사용합니다.

  • 쿼리는 서로 다른 네트워크를 통해 서버에 제출되거나 애플리케이션이 데이터를 처리하는 방식에 차이가 있습니다.

  • 데이터베이스 애플리케이션 및 SSMS의 SET 옵션은 다릅니다.

문제를 해결하려면 다음 단계를 수행합니다.

1단계: 쿼리가 동일한 매개 변수 또는 변수를 사용하여 제출되었는지 확인

이러한 쿼리를 비교하고 모든 면에서 동일한지 확인하려면 다음 단계를 수행합니다.

  1. SSMS를 열고 사용 중인 데이터베이스 엔진에 연결합니다.

  2. 다음 명령을 실행하여 확장 이벤트 세션을 만듭니다.

    CREATE EVENT SESSION <EventSessionName> ON SERVER
ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.rpc_completed,
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name))
ADD TARGET package0.event_file(SET filename=N'<FilePath>')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 
SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

    참고

    자리 표시자 <EventSessionName> 및 <FilePath> 를 만들려는 개체로 바꿉니다.

  3. 다음 명령을 실행하여 EventSessionName 세션을 시작합니다.

    ALTER EVENT SESSION <EventSessionName> ON SERVER
STATE = START

  4. 쿼리를 실행하여 문제를 재현합니다.

  5. 다음 방법 중 하나를 사용하여 수집된 데이터를 분석합니다.

    • Windows Explorer 열고 대상 .xel 파일을 찾은 다음 두 번 클릭합니다. 파일을 보고 분석하는 데 사용할 수 있는 다른 SSMS 창에서 열립니다.

    • 개체 탐색기관리>확장 이벤트>세션>EventSessionName을 확장하고 package0.event_file 마우스 오른쪽 단추로 클릭한 다음 대상 데이터 보기...를 선택합니다.

    • .xel 파일의 위치를 찾고 함수 sys.fn_xe_file_target_read_file 사용하여 이 파일을 읽습니다.

  6. 다음 이벤트를 확인하여 Field 문을 비교합니다.

    • sp_statement_completed
    • sql_batch_completed
    • sql_statement_completed
    • rpc_completed

동일한 쿼리에 대한 자세한 내용은 다음 예제를 참조하세요.

  • 저장 프로시저 또는 함수에 다른 매개 변수 값이 있는 경우 쿼리 시간이 다를 수 있습니다.

    • SpUserProc @p1 = 100

    • SpUserProc @p1 = 270

  • 다음 쿼리는 다릅니다. 첫 번째 쿼리는 카디널리티 추정을 위해 히스토그램의 평균 밀도를 사용하고 두 번째 쿼리는 카디널리티 추정에 히스토그램 단계를 사용합니다.

    • declare @variable1 = 123
select * from table where c1 = @variable1

    • select * from table where c1 = 123

위와 같은 이유로 저장 프로시저의 실행과 동등한 임시 쿼리 실행(지역 변수 사용)을 비교하는 것은 다를 수 있습니다. 동일한 문을 비교해야 합니다.

2단계: 서버의 실행 시간 측정

쿼리 기간을 정확하게 비교하려면 네트워크 대기 시간 또는 애플리케이션별 데이터 처리 시간을 제외할 수 있습니다. 다음 방법 중 하나를 사용하여 SQL Server 실행 시간만 측정합니다.

  • SET STATISTICS TIME을 사용하여 쿼리를 실행합니다.

    SET STATISTICS TIME ON
<YourQuery>
SET STATISTICS TIME OFF

  • 1단계의 XEvent를 사용하여 쿼리의 기간 또는 경과 시간(이벤트 클래스 SQL:StmtCompleted, SQL:BatchCompleted또는 RPC:Completed)을 검사합니다.

경우에 따라 쿼리 간의 시간 차이는 다른 네트워크 또는 애플리케이션 자체에서 실행되는 한 애플리케이션으로 인해 발생할 수 있습니다. 서버의 실행을 비교할 때 서버에서 쿼리를 실행하는 데 걸린 기간을 비교합니다.

3단계: 각 연결에 대한 SET 옵션 확인

쿼리 계획에 영향을 주는 SET 옵션이 있습니다. 즉, 쿼리 계획의 선택을 변경할 수 있습니다. 따라서 데이터베이스 애플리케이션이 SSMS의 다른 집합 옵션을 사용하는 경우 각 집합 옵션은 다른 쿼리 계획을 가져올 수 있습니다. 예를 들어 ARITHABORT, NUMERIC_ROUNDABORT, ROWCOUNT, FORCEPLAN 및 ANSI_NULLS. SSMS와 .NET 애플리케이션 간에 관찰되는 가장 일반적인 차이점은 SET ARITHABORT 옵션입니다. 기본적으로 옵션은 SSMS에서 ON으로 설정되지만 대부분의 데이터베이스 애플리케이션에서 OFF로 설정됩니다. 애플리케이션 요구 사항에 따라 둘 사이의 유효한 비교를 위해 SSMS와 애플리케이션 모두에서 ARITHABORT를 동일한 설정으로 설정합니다.

경고

SQL Server Management Studio 대한 기본 ARITHABORT 설정은 ON입니다. ARITHABORT를 OFF로 설정하는 클라이언트 애플리케이션은 다른 쿼리 계획을 받을 수 있으므로 성능이 저조한 쿼리 문제를 해결하기가 어렵습니다. 즉, 동일한 쿼리가 Management Studio에서 빠르게 실행되지만 애플리케이션에서는 느릴 수 있습니다. Management Studio를 사용하여 쿼리 문제를 해결할 때는 항상 클라이언트 ARITHABORT 설정과 일치합니다.

계획에 영향을 주는 모든 옵션 목록은 옵션 설정을 참조하세요.

SSMS 및 애플리케이션의 SET 옵션이 유효한 비교를 수행할 수 있도록 하려면 다음 단계를 수행합니다.

  1. 1단계에서 수집된 데이터를 사용합니다.

  2. 이벤트 login 및, 특히 options_text 및 existing_connection옵션 열을 확인하여 집합 옵션을 비교합니다.