SQL Server 2008의 새로운 기능 중에 하나인 optimize for ad hoc workloads 옵션은 출시 전부터 많은 관심을 받던 기능이었습니다.
SQL Server 2008이전 버전에서는 ad-hoc쿼리가 자주 사용되는 환경에서는 플랜 캐시 사이즈가 계속 커지는 문제가 발생하여
전체적인 메모리 사용에 비효율을 가져왔습니다.
위와 같은 문제에 대해서 어느 정도 해결해 줄 수 있는 옵션이 optimize for ad hoc workloads 입니다.
이 옵션이 하는 역할은 간단하게 보면 처음으로 실행되는 ad-hoc쿼리가 실행되면 320바이트의 stub영역에 데이터를 저장합니다.
동일한 ad-hoc쿼리가 두 번째 다시 실행되면 그때 일반적인 플랜캐시에 저장하도록 합니다.
한번밖에 호출되는 ad-hoc쿼리의 비중이 많은 환경이라면, 전체적인 메모리를 꽤나 절약할 수 있을 것입니다.
아래는 BOL에서 설명하고 있는 내용입니다.
|
optimize for ad hoc workloads 옵션 optimize for ad hoc workloads 옵션은 여러 개의 일회용 임시 일괄 처리를 포함하는 작업에서 계획 캐시의 효율성을 높이는 데 사용됩니다. 이 옵션을 1로 설정하면 데이터베이스 엔진이 일괄 처리가 처음으로 컴파일되었을 때 전체 컴파일된 계획 대신 계획 캐시에 포함된 작은 컴파일된 계획 스텁을 저장합니다. 이렇게 하면 계획 캐시에 다시 사용할 수 없는 컴파일된 계획이 채워지지 않게 되므로 메모리 가중을 줄일 수 있습니다. 컴파일된 계획 스텁은 데이터베이스 엔진이 이러한 임시 일괄 처리가 이전에 컴파일되었지만 컴파일된 계획 스텁만 저장했다는 것을 인식하게 함으로써 이 일괄 처리(컴파일 또는 실행된)가 다시 호출될 때 데이터베이스 엔진이 일괄 처리를 컴파일하고, 계획 캐시에서 컴파일된 계획 스텁을 제거하고, 계획 캐시에 전체 컴파일된 계획을 추가하도록 합니다. optimize for ad hoc workloads를 1로 설정하면 새 계획만 영향을 받으며, 이미 계획 캐시에 있던 계획은 영향을 받지 않습니다. 컴파일된 계획 스텁은 sys.dm_exec_cached_plans 카탈로그 뷰로 표시되는 cacheobjtype 중 하나입니다. 여기에는 고유한 SQL 핸들 및 계획 핸들이 포함됩니다. 컴파일된 계획 스텁에는 연결된 실행 계획이 없으며 계획 핸들을 쿼리해도 XML 실행 계획이 반환되지 않습니다. |
간단하게 테스트를 하였습니다. 환경은 SQL Server 2008 with sp1입니다.
ad-hoc쿼리를 999번 수행하여 플랜 캐시의 메모리를 비교하였습니다.
간단히 결론만 알아보면 옵션을 사용하지 않은 경우 플랜 캐시 메모리는 40MB정도, 사용한 경우에는 1MB정도 입니다.
자세한 내용은 아래를 참조하세요.
/*
ad-hoc쿼리를 999번 실행합니다.
*/
declare @a int = 1
,@s varchar(1000)
while @a < 1000
BEGIN
SET @s = 'SELECT TOP 1 col1,count(*) FROM dbo.tbl WITH(NOLOCK)
WHERE col1 = '+str(@a)+'
GROUP BY col1'
exec (@s)
set @a = @a + 1
end
===============================================================================[ad-hoc쿼리 최적화 아닐때]
/*
옵션을 비활성화 합니다.
*/
DBCC FREEPROCCACHE
GO
exec sp_configure 'optimize for ad hoc workloads',0
reconfigure with override
exec sp_configure 'optimize for ad hoc workloads'
/*
DMV를 통해 확인해 보면,
쿼리당 40KB정도 차지하고 있는 것을 볼 수 있습니다.
*/
SELECT
qt.text
,cp.size_in_bytes
,cp.cacheobjtype
,cp.objtype
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce
on cp.memory_object_address=ce.memory_object_address
cross apply sys.dm_exec_sql_text(cp.plan_handle) as qt
SELECT * FROM sys.dm_os_memory_clerks
WHERE type like '%cp%'
--===============================================================================[ad-hoc쿼리 최적화]
/*
옵션을 활성화 합니다.
*/
DBCC FREEPROCCACHE
GO
exec sp_configure 'optimize for ad hoc workloads',1
reconfigure with override
exec sp_configure 'optimize for ad hoc workloads'
/*
DMV를 통해 확인해 보면,
쿼리당 320 바이트 정도 차지하고 있는 것을 볼 수 있습니다.
*/
SELECT
qt.text
,cp.size_in_bytes
,cp.cacheobjtype
,cp.objtype
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce
on cp.memory_object_address=ce.memory_object_address
cross apply sys.dm_exec_sql_text(cp.plan_handle) as qt
SELECT * FROM sys.dm_os_memory_clerks
WHERE type like '%cp%'
/*
그럼 다시 Ad-hoc쿼리를 다시 한번 수행하면?
설명대로 stub에 저장된 것들이 사라지고 모두 컴파일 되서 플랜 캐시에 저장되는 것을 볼 수 있습니다.
*/
declare @a int = 1
,@s varchar(1000)
while @a < 1000
BEGIN
SET @s = 'SELECT TOP 1 col1,count(*) FROM dbo.tbl WITH(NOLOCK)
WHERE col1 = '+str(@a)+'
GROUP BY col1'
exec (@s)
set @a = @a + 1
end
/*
DMV를 통해 확인해 보면,
쿼리당 40KB정도 차지하고 있는 것을 볼 수 있습니다.
*/
SELECT
qt.text
,cp.size_in_bytes
,cp.cacheobjtype
,cp.objtype
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce
on cp.memory_object_address=ce.memory_object_address
cross apply sys.dm_exec_sql_text(cp.plan_handle) as qt
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com