데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

Ad-hoc 쿼리와 실행계획

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

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
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1587
» Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2574
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 3033
1990 클러스터된 SQL 서버 인스턴스에 대한 호스트 이름 확인 jevida(강성욱) 2017.01.11 1368
1989 sys.dm_tran_locks 를 이용한 잠금 정보 확인 jevida(강성욱) 2017.01.11 1532
1988 외래키 제약 조건 삭제 후 재작성 스크립트 생성하기 jevida(강성욱) 2017.01.11 1781
1987 페이지 ID로 테이블 이름 찾기 jevida(강성욱) 2017.01.11 1436
1986 DBCC CHECKPRIMARYFILE 사용법 jevida(강성욱) 2017.01.11 1331
1985 컬럼스토어 인덱스 대용량 데이터 로드 jevida(강성욱) 2017.01.11 1745
1984 컬럼스토어 인덱스 INSERT 작업과 동시성 jevida(강성욱) 2017.01.11 1493
1983 컬럼스토어 인덱스 동시성 jevida(강성욱) 2017.01.11 1783
1982 컬럼스토어 인덱스 ROW와 ROWGROUP 영향 jevida(강성욱) 2016.11.23 2952
1981 테이블 변수와 TF 2453 jevida(강성욱) 2016.11.23 3186
1980 Sp_trace_create MaxfileSize 오류 jevida(강성욱) 2016.11.23 2400
1979 RANDBETWEEN 함수 만들기 jevida(강성욱) 2016.11.23 4640
1978 Optimize for hint 쿼리 최적화 jevida(강성욱) 2016.11.23 3512
1977 TempDB 파일 사이즈 증가 시 경고 받기 jevida(강성욱) 2016.11.23 3047
1976 블록킹 세션을 찾아 우선순위 낮은 세션 종료하기 jevida(강성욱) 2016.11.23 3250
1975 다양한 포맷의 이름 파싱 하기 jevida(강성욱) 2016.11.23 2665
1974 비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리 jevida(강성욱) 2016.11.23 3151





XE Login