Optimized sp_executesql
SQL Server 2025의 두 번째로 소개하고 싶은 새로운 기능입니다. .NET Entity Framework(Core)과 같은 ORM 기반이든 명시적으로 실행되는 형식이건 일반적인 매개변수 쿼리는 SQL Server에서 sp_executesql로 호출됩니다. sp_executesql과 Optimized의 기본 소개는 MS Learn 문서를 참조하세요. 이 방식이 동시에 대량 호출되는 경우 그 이득이 감소되거나 예상치 못한 성능이 나올 수 있습니다. 이를 방지하기 위해 새로운 매커니즘이 도입되었으며 하위 호환성을 위해 DB 단위 옵션 설정이 필요합니다.
1. sp_executesql 용 plan cache가 중복 생성
저장 프로시저와 달리 sp_executesql로 호출되는 매개변수 쿼리를 Plan Cache에 저장할 때 OBJECT X 잠금을 이용한 일명 “줄세우기”가 사용되지 않는다고 합니다. 그 말인 즉 동일한 매개변수 쿼리가 동시에 여러 개 호출되면 하나의 Plan Cache를 생성하고 공유하지 못하고 여러 개의 Cache가 생성될 수 있다는 것입니다. 그 경우 동시 컴파일 부하, Cache 공간 중복 소비, 재사용 이득 감소 등의 손해를 볼 수 있다는 것입니다 (물론 반대의 이득도 생각할 수 있습니다)
아래와 같은 간단한 예제로 재현해 봤습니다. 동시 호출이 필요하므로 Microsoft에서 제공하는 osql 같은 유틸리티를 이용해도 되고 제가 만들어서 무료로 배포한 SQLBigEyes Hammer를 이용하셔도 됩니다.
원본) 아래 예제는 Microsoft의 발표 자료를 참고했습니다.
주의) 설마 운영 서버에 테스트하진 않으시겠죠? ^^ 개인 장비나 테스트 장비를 이용하세요.
USE AdventureWorks;
GO
— optimized_sp_executesql, DB 단위 구성 옵션이며 기본적으로 OFF 상태입니다.
SELECT * FROM sys.database_scoped_configurations
WHERE name = 'OPTIMIZED_SP_EXECUTESQL';
— 결과 확인을 쉽게하기 위해 현재 DB의 Plan Cache 지우기
ALTER DATABASE SCOPED CONFIGURATION
CLEAR PROCEDURE_CACHE;
— 아래 3개의 매개변수 쿼리를 osql이나 SQLBigEyes Hammer 등의 이용해서 동시의 N개를 호출
EXECUTE sp_executesql
N'SELECT LoginID, JobTitle, Birthdate FROM dventureWorks.HumanResources.Employee
WHERE BusinessEntityID = @level',
N'@level tinyint',
@level = 1; — 이 값이 다름
EXECUTE sp_executesql
N'SELECT LoginID, JobTitle, Birthdate FROM dventureWorks.HumanResources.Employee
WHERE BusinessEntityID = @level',
N'@level tinyint',
@level = 2
EXECUTE sp_executesql
N'SELECT LoginID, JobTitle, Birthdate FROM dventureWorks.HumanResources.Employee
WHERE BusinessEntityID = @level',
N'@level tinyint',
@level = 3
저는 그림-1과 같이 SQLBigEyes Hammer에서 3개의 쿼리 창을 띄우고 [# of iterations] = 4, [# of Threads] = 4로 호출하도록 설정하고 [View Statistics] 옵션을 해제한 뒤 툴바의 [실행] 버튼으로 동시에 호출했습니다
[그림-1]
이후 아래 쿼리를 이용 Plan Cache를 조회한 결과입니다.
SELECT
cp.cacheobjtype,
cp.objtype,
cp.usecounts,
st.text AS sql_text
FROM
sys.dm_exec_cached_plans AS cp
CROSS APPLY
sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY
sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE st.text LIKE '%BusinessEntityID%'
AND cp.objtype = 'Prepared';
아래 그림과 같이 동일한 sp_executesql에 대해서 두 개 이상의 Compiled Plan이 Cache에 생성되었습니다.
[그림-2]
Optimized sp_executesql
USE AdventureWorks;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;
“OPTIMIZED_SP_EXECUTESQL” DB 구성 옵션을 ON 한 뒤 동일한 테스트를 진행하면 아래와 같이 Cache가 한 개가 생성되었습니다.
[그림-3]
고려사항
Cache 생성 시 줄 세우기를 위해 부가적인 잠금이 사용되므로 저장 프로시저에서 발생할 수 있는 Compile 잠금 대기와 같은 중대한 성능 문제가 커질 수도 있을 것으로 예상됩니다. 디폴트로 OFF 상태인 만큼 sp_executesql 의 cache 오용이 많은 경우 또는 향후 실무 적용 사례들이 많이 나오고 나면 그 결과를 바탕으로 적용 여부를 판단하는 것도 좋겠습니다.
마치면서
새로운 기능으로 추가되었다는 것은 그 만큼의 실무적 중요도나 효율성이 높음을 나타냅니다. 저는 개인적으로 경험한 적이 없지만 동시 호출이 많은 환경이라면 충분히 검토해 볼만한 기능이라 생각됩니다. 계속 지켜보죠.
감사합니다.
