Query의 Recompile 문제에 관하여 : 쿼리 매개변수화

특별히 악성 SQL이 확인되지 않는데도 CPU Usage가 지나치게 높다거나, Profiler에서 확인한 특정 SQL이 Read나 Duration에 비해 유독 CPU만 높다고 생각이 된다면 Recompile 문제를 의심해볼 수 있다.

모든 SQL문은 SQL Server에서 실행되기에 앞서 컴파일 과정을 거치게 되는데, 일단 컴파일된 SQL은 캐쉬에 저장되어 동일한 SQL이 다시 요청될 때는 컴파일 과정 없이 캐쉬에 컴파일된 쿼리를 바로 실행시키게 된다. 문제는 SQL이 재사용되기 위해서는 주석을 포함하여 모든 SQL이 한글자도 빼놓지 않고 동일해야 한다는 것이다. 즉, 아래와 같 SQL들은 재사용되지 않는다.

SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '29472'

SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '38749'
SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '32945'

이러한 SQL을 AdHoc Query 라고 하는데, AdHoc Query의 비율이 높이지게 되면 Query가 재사용되지 않고 모든 SQL에 대해 컴파일 과정을 거치게 되기 때문에 필요 이상으로 CPU 사용율이 높아지게 된다.

이러한 문제가 있는지를 점검해보려면 성능 모니터의 SQLServer:Plan Cache - Cache Hit Ratio 항목을 모니터링하는 방법도 있지만, 간단하게 아래와 같이 간단한 SQL로 확인해볼 수도 있다.

USE master SELECT objtype, count(objtype) FROM syscacheobjects WHERE dbid = db_id('DatabaseName') GROUP BY objtype

결과는 아래와 같다.

Prepared 는 SQL이 재활용된 경우이고 Proc는 프로시져 형태이다. 즉, 이 두가지 경우는 SQL이 재컴파일되지 않고 캐쉬에서 실행되었다고 볼 수 있다.
만약 AdHoc의 비율이 지나치게 높다면 Recompile 문제에 대한 튜닝을 생각해볼 수 있다.

튜닝방법은 간단하다.
SQL문을 Stored Procedure로 바꾸거나, sp_executesql 프로시져를 사용해 재활용 가능하도록 매개변수화 해주는 것이다. (sp_executesql 문은 오라클의 prepared statement + bind variable 에 해당한다.)

TCHAR szSQL[1024] = {0,}; _sntprintf (szSQL, _countof(szSQL), _T("SELECT IP_ADDR FROM IP_TABLE WHERE EMPNO = '%s' " ), lpszEmpNo);

위와 같은 코딩한 SQL은 재활용되지 않는다. 아래와 같이 코딩해야 한다.

TCHAR szSQL[1024] = {0,}; _sntprintf (szSQL, _countof(szSQL), _T("{call dbo.sp_executesql} ") _ _T(" @stmt=N'SELECT IP_ADDR ") _ _T(" FROM IP_TABLE ") _ _T(" WHERE EMPNO = @empno AND ") _ _T(" START_DATE > @startdate' , ") _ _T(" @params=N'@empno varchar(20), @start_date varchar(20)', ") _ _T(" @empno = N'%s', @start_date = N'%s' ") , szEmpNo, szStartDate );

sp_executesql 프로시져에 관한 자세한 내용은 다음 msdn 도움말을 참조한다.

http://msdn.microsoft.com/ko-kr/library/ms175170.aspx

코드로 표현하다보면 상당히 귀찮고 복잡하고... 무엇보다도 SingleQuote 짝 맞추는 것이 헷갈리고 짜증난다.
하지만 생각보다 효과는 크다.