Ad-hoc 쿼리와 실행계획

Ad-hoc 쿼리와 실행계획

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

Ad-hoc 쿼리를 실행 할 때 대소문자, 띄어쓰기, 스키마에 따라 기존의 실행 계획이 사용되지 않고 새로운 실행 계획이 생성되어 사용된다. Ad-hoc 쿼리는 컴파일 과정을 거치며 실행 계획을 생성하며 컴파일과 실행 계획 생성에 따른 성능 병목이 발생 할 수 있다.

 

[대소문자, 공백에 따른 실행계획 생성]

Ad-hoc으로 쿼리를 사용 할 경우 Case와 공백에 따라서도 다른 쿼리로 인식되어 새로운 실행계획이 생성된다고 하였다. 다음 예제를 통해 알아보자.

USE AdventureWorks2014; DBCC FREEPROCCACHE WITH NO_INFOMSGS; GO SELECT StoreID FROM Sales.Customer; GO -- original query GO SELECT StoreID FROM Sales.Customer; GO ----^---- extra space GO SELECT storeid FROM sales.customer; GO ---- lower case names GO select StoreID from Sales.Customer; GO ---- lower case keywords GO

 

위 쿼리를 실행하면 동일한 계획을 생성하지만 캐시된 실행계획을 확인해 보면 쿼리별로 실행 계획이 생성된 것을 확인 할 수 있다.

SELECT t.[text], p.size_in_bytes, p.usecounts FROM sys.dm_exec_cached_plans AS p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t WHERE LOWER(t.[text]) LIKE N'%sales'+'.'+'customer%';

 

 

 

[스키마에 따른 실행 계획]

테이블을 호출하거나 프로시저를 호출 할 때 스키마 접두사를 사용하는 것은 중요하다. 동일한 쿼리라도 스키마에 따라 호출 되는 우선순위가 다르며 실행 계획 또한 재사용하지 못한다.

 

다음 스크립트는 Sales와 Person 스키마를 사용하여 동일한 쿼리를 호출 한다.

USE AdventureWorks2014; DBCC FREEPROCCACHE WITH NO_INFOMSGS; GO   CREATE USER SQLPerf1 WITHOUT LOGIN WITH DEFAULT_SCHEMA = Sales; CREATE USER SQLPerf2 WITHOUT LOGIN WITH DEFAULT_SCHEMA = Person; GO   CREATE TABLE dbo.AnErrorLog(id INT); GRANT SELECT ON dbo.AnErrorLog TO SQLPerf1, SQLPerf2; GO   EXECUTE AS USER = N'SQLPerf1'; GO SELECT id FROM AnErrorLog; GO REVERT; GO EXECUTE AS USER = N'SQLPerf2'; GO SELECT id FROM AnErrorLog; GO REVERT; GO

 

Sys.dm_exec_plan_attributes를 사용하여 캐시된 플랜을 확인해 보면 두 쿼리에 대해 서로 다른 계획을 생성한 것을 확인 할 수 있다.

SELECT t.[text], p.size_in_bytes, p.usecounts, [schema_id] = pa.value, [schema] = s.name FROM sys.dm_exec_cached_plans AS p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa INNER JOIN sys.schemas AS s ON s.[schema_id] = pa.value WHERE t.[text] LIKE N'%AnError'+'Log%' AND pa.attribute = N'user_id';

 

 

다음 스크립트는 복수의 결과를 가지고 있는 실행 계획을 확인 할 수 있다.

SELECT p.plan_handle, p.usecounts, p.size_in_bytes, set_options = MAX(a.value) FROM sys.dm_exec_cached_plans AS p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a WHERE t.objectid = OBJECT_ID(N'dbo.procedure_name') AND a.attribute = N'set_options' GROUP BY p.plan_handle, p.usecounts, p.size_in_bytes;

 

이처럼 같은 쿼리라 생각했던 것이 띄어쓰기나 대소문자로 기존의 실행 계획을 사용하지 못하는 것을 확인 할 수 있다.

 

저장된 실행계획에 대한 확인 및 활용은 다음 포스팅을 참고 한다.

• 저장된 Plan Cache 확인 및 활용 : http://sqlmvp.kr/140197291703

 

 

[참고자료]

http://sqlperformance.com/2014/11/t-sql-queries/multiple-plans-identical-query

 

2014-11-25 / 강성욱 / http://sqlmvp.kr

 

 

Sqlserver, mssql, plan cache, ad-hoc, shcema, query plan, 쿼리 플랜, 플랜 재사용, 플랜 캐시, 저장된 플랜, 쿼리 실행 계획, 실행계획 재사용, DB튜닝, 쿼리튜닝