필터 통계 사용과 파리미터 사용

필터 통계 사용과 파리미터 사용

 

• Version : SQL Server 2008, 2008R2, 2012

 

옵티마이저가 플랜을 생성하기 위해서는 통계를 이용한다. 통계는 테이블 또는 인덱싱된 뷰에 하나 이상의 열에 대한 쿼리 최적화 통계(필터링된 통계 포함)을 만든다. 대부분의 통계는 옵티마이저에 의해 기본적으로 생성된다. 추가로 사용자가 쿼리의 성능을 더 좋게 만들기 위해서 Create Statistics를 사용하여 추가 통계를 만들 수 있다. 하지만 사용자가 통계를 생성하거나 수정을 할 때에는 매우 주의해야 한다. 다른 쿼리에 영향을 줄 수 있기 때문이다.

 

통계 생성 방법 : http://msdn.microsoft.com/ko-kr/library/ms188038.aspx

 

필터링된 통계는 Where 절의 필터 조건자를 사용하여 통계에 포함되는 데이터의 하위 집합을 선택 한다. 통계를 작성하기 위해서느 Tempdb를 사용하여 행 샘플 정렬이 발생 할 수 있다.

 

그렇다면 필터링된 통계를 만들었다고 하여 모든 상황의 경우에 사용할 수 있을까?

 

다음의 경우를 통하여 필터 통계를 사용하지 못하는 경우를 살펴 보자. 예제 데이터베이스는 AdventureWoks2008R2이다.

 

통계 생성 명령을 이용하여 통계를 만들었다. 이때 필터 조건은 EmailPromotion 컬럼으로 지정 하였다.

select top 10 * from person.Person GO   CREATE STATISTICS Person_Business ON Person.Person ([BusinessEntityID], [PersonType])WHERE [emailpromotion] = 1; GO

 

 

 

[상수 경우]

아래 스크립트를 실행하고 나면 초기 컴파일러에 의해 다음과 같은 메시지가 나타난다.

필터된 통계가 로드된 것을 확인 할 수 있다.

SELECT     DISTINCT [BusinessEntityID], [PersonType], [EmailPromotion] FROM person.person AS c WHERE [EmailPromotion] = 1     AND [BusinessEntityID] = 17677     AND [PersonType] = 'IN' OPTION (QUERYTRACEON 3604, QUERYTRACEON 9204); GO

 

Filtered stats loaded: DbName: AdventureWorks2008R2, ObjName: person.person, IndexId: 5, ColumnName: BusinessEntityID, Expr: ([emailpromotion]=(1)), EmptyTable: FALSE

 

[파라메터 경우]

위의(상수) 경우와 동일한 스크립트에서 Where절의 조건만 파라메터를 이용하도록 하였다.

필터된 통계가 로드 되지 않는 것을 확인 할 수 있다.

declare @EmailPromotion int = 1   SELECT     DISTINCT [BusinessEntityID], [PersonType], [EmailPromotion] FROM person.person AS c WHERE [EmailPromotion] = @EmailPromotion     AND [BusinessEntityID] = 17677     AND [PersonType] = 'IN' OPTION (QUERYTRACEON 3604, QUERYTRACEON 9204); GO

 

 

 

 

파라메터를 사용하는 경우에는 상수 값과 입력 값이 동일 하더라도 어떤 값이 들어올지 알 수 없기에 필터된 통계를 사용할 수 없는 것이다.

 

그렇다면 파마레터를 사용하면 무조건 필터 통계를 사용 할 수 없는 것일까? 다음과 같이 파라메터 바인딩을 통하여 통계를 이용 할 수 있다.

declare @SQL nvarchar(2000) declare @EmailPromotion int = 1   set @SQL = (' SELECT     DISTINCT [BusinessEntityID], [PersonType], [EmailPromotion] FROM person.person AS c WHERE [EmailPromotion] = ' + convert(nvarchar(10), @EmailPromotion) + '     AND [BusinessEntityID] = 17677     AND [PersonType] = ''IN'' OPTION (QUERYTRACEON 3604, QUERYTRACEON 9204)')   exec (@SQL)

 

 

 

2013-03-14 / 강성욱 / http://sqlmvp.kr