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

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

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

 

  • 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)

 

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3628
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1582
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1694
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1432
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1308
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1560
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1237
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3595
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1238
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
» 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1179
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1065
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1129
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1301
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1652
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1228
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1418
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1066
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 958
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3105





XE Login