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
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
