오늘 물류팀에서 쓰는 출고 전표 조회 화면이 특정 조건에서 10초 가까이 멈춘다는 연락을 받았다.

이상한 건 로그에 찍힌 쿼리를 그대로 복사해서 SSMS에서 돌려보면 0.1초도 안 걸려서 바로 나온다는 점이었다. 서버 CPU가 튀는 것도 아니고 락 걸린 세션도 없는데 유독 C# WinForms 화면에서 조회할 때만 먹통이 됐다.

삽질: 파라미터 스니핑인 줄 알았다

SSMS에선 빠르고 프로그램에서만 느리길래 처음엔 무조건 파라미터 스니핑(Parameter Sniffing) 문제인 줄 알았다. 급한 대로 쿼리 맨 뒤에 OPTION (RECOMPILE)까지 붙여서 테스트해 봤는데도 똑같이 느렸다.

도대체 C#에서 쿼리를 던질 때 뭐가 다르게 날아가는지 보려고 오랜만에 SQL Server Profiler를 켜서 실제로 날아오는 RPC 구문을 잡아봤다.

1
2
3
4
5
6

-- 프로파일러에 찍힌 실제 실행 구문
exec sp_executesql N'
SELECT ORD_NO, CUST_CD, ITEM_CD, ORD_QTY, INP_DT
FROM TB_CUST_ORD_HIST
WHERE INVOICE_NO = @pInvoiceNo',
N'@pInvoiceNo nvarchar(4000)', @pInvoiceNo = N'INV20261016-00891'

맨 아랫줄 파라미터 선언부를 보고서야 원인을 찾았다. DB 테이블의 INVOICE_NO(송장번호) 컬럼은 VARCHAR(20)인데, C#에서 넘어온 @pInvoiceNo 변수는 NVARCHAR(4000)으로 찍혀 있었다.

원인: 아무 생각 없이 쓴 AddWithValue

C# 코드에서 파라미터 넘길 때 귀찮아서 습관적으로 AddWithValue를 쓴 게 화근이었다.

1
2
3
4
5
6
7

// 기존 C# 코드
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    // 이렇게 값을 바로 넘기면 무조건 NVARCHAR로 잡힌다
    cmd.Parameters.AddWithValue("@pInvoiceNo", txtInvoiceNo.Text.Trim());
    ...
}

C#이나 VB.NET의 기본 문자열(string)은 유니코드라서 타입을 안 정해주고 AddWithValue로 그냥 넘기면 드라이버가 알아서 NVARCHAR로 묶어서 던진다.

문제는 MSSQL에서 VARCHAR 컬럼이랑 NVARCHAR 변수를 비교하면 우선순위가 높은 NVARCHAR 쪽으로 맞춰서 형변환을 해버린다는 점이다. 파라미터 하나를 바꾸는 게 아니라, 테이블에 있는 500만 건짜리 INVOICE_NO 컬럼을 전부 NVARCHAR로 바꿔가면서 비교하느라 인덱스를 못 타고 풀 스캔에 가까운 Index Scan을 돌고 있었다.

프로파일러에 찍힌 그대로 변수를 NVARCHAR(4000)으로 선언해서 SSMS에서 실행 계획을 다시 봐보니 노란색 경고 아이콘이 떠 있었다.

1
2

식 CONVERT_IMPLICIT(nvarchar(20), [TB_CUST_ORD_HIST].[INVOICE_NO], 0)에서의 
형식 변환으로 인해 쿼리 계획에서 'IX_TB_CUST_ORD_HIST_01' 인덱스의 Seek 연산을 사용할 수 없습니다.

결국 어제 정리했던 날짜 컬럼에 CONVERT 씌운 거랑 똑같은 꼴이 난 셈이다. DB가 내부적으로 좌변 컬럼에 CONVERT_IMPLICIT을 걸어버리니 인덱스가 무용지물이 됐다.

해결: 귀찮아도 타입이랑 길이 다 적어주기

해결은 간단하다. AddWithValue를 버리고 DB 컬럼 스펙 그대로 SqlDbType.VarChar와 길이(20)를 명시해 주면 된다.

1
2
3
4
5
6

// 수정 후 C# 코드
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.Add("@pInvoiceNo", SqlDbType.VarChar, 20).Value = txtInvoiceNo.Text.Trim();
    ...
}

옆에 있던 VB.NET 구형 화면 코드도 같이 고쳐줬다.

1
2

' VB.NET 수정 코드
cmd.Parameters.Add("@pInvoiceNo", SqlDbType.VarChar, 20).Value = txtInvoiceNo.Text.Trim()

이때 뒤에 길이(20)까지 같이 적어주는 게 좋다. 길이를 안 적으면 검색어 글자 수가 바뀔 때마다(NVARCHAR(5), NVARCHAR(12) 등) DB가 다른 쿼리로 인식해서 실행 계획을 매번 새로 만드는 부하까지 생길 수 있다.

수정 후 결과

고치고 나서 SET STATISTICS IO, TIME ON; 켜고 다시 찍어봤다.

1
2
3
4
5
6
7

-- 수정 전 (AddWithValue 그대로 썼을 때)
테이블 'TB_CUST_ORD_HIST'. 스캔 수 1, 논리적 읽기 수 46280, 물리적 읽기 수 0
SQL Server 실행 시간: CPU 시간 = 1422ms, 경과 시간 = 1580ms.

-- 수정 후 (VarChar(20) 명시했을 때)
테이블 'TB_CUST_ORD_HIST'. 스캔 수 1, 논리적 읽기 수 4, 물리적 읽기 수 0
SQL Server 실행 시간: CPU 시간 = 0ms, 경과 시간 = 1ms.
  • 수정 전: 논리적 읽기 46,280회 (약 1.6초, 운영 서버에선 동시 호출 때문에 체감 5~10초)
  • 수정 후: 논리적 읽기 4회 (0.01초 미만)

국내 SI 프로젝트는 코드나 번호 컬럼이 거의 다 VARCHAR로 되어 있다. 타이핑 좀 줄이겠다고 AddWithValue 썼다가 인덱스 다 날려먹기 딱 좋으니 앞으로 문자열 파라미터는 무조건 타입이랑 사이즈를 박아주는 걸로.