Ad-hoc 쿼리와 실행계획

jevida(강성욱) 2017.01.11 08:28 Views : 2604

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;

 

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

 

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

 

 

[참고자료]

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

No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 527
2006 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3387
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4215
2004 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2499
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3155
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3807
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1316
2000 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5482
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1635
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1040
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1443
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1381
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 3930
1994 데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기 jevida(강성욱) 2017.01.11 1699
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1613
» Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2604
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 3075
1990 클러스터된 SQL 서버 인스턴스에 대한 호스트 이름 확인 jevida(강성욱) 2017.01.11 1392
1989 sys.dm_tran_locks 를 이용한 잠금 정보 확인 jevida(강성욱) 2017.01.11 1598
1988 외래키 제약 조건 삭제 후 재작성 스크립트 생성하기 jevida(강성욱) 2017.01.11 1849
1987 페이지 ID로 테이블 이름 찾기 jevida(강성욱) 2017.01.11 1455





XE Login