데이터베이스 개발자 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
2010 재해복구를 위한 SQL Server 역할 가져오기 jevida(강성욱) 2017.01.11 2283
2009 비관리자 계정에 Profiler 실행 권한 부여하기 jevida(강성욱) 2017.01.11 3125
2008 SQL Server Agent 공유 일정 생성하기 jevida(강성욱) 2017.01.11 2147
2007 인덱스 리빌드는 통계를 업데이트 할까? jevida(강성욱) 2017.01.11 2352
2006 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3313
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4087
2004 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2460
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3039
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3757
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1289
2000 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5283
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1612
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1001
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1421
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1361
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 3724
1994 데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기 jevida(강성욱) 2017.01.11 1662
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1586
» Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2574
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 3017





XE Login