필터링 된 인덱스 및 통계

1       필터링 된 인덱스 및 통계

1.1 개요

 SQL Server 2008에서는 조건자를 사용하여 테이블 행의 하위 집합에 대해 필터링된 인덱스와 통계를 만들 수 있다.

 이전 버전의 SQL Server에서는 테이블의 모든 행에 대해 인덱스와 통계가 만들어다. 필터링된 인덱스와 통계는 대부분의 값이 NULL인 열, 여러 범주의 값이 포함된 열 및 특정 범위의 값이 포함된 열과 같이 잘 정의된 데이터의 하위 집합에서 선택하는 쿼리에 특히 적합하다.

 잘 디자인된 필터링된 인덱스는 전체 테이블 인덱스에 비해 쿼리 성능을 개선하고 인덱스 유지 관리 비용과 인덱스 저장소 비용을 줄일 수 있다.

 필터링된 통계는 필터링된 인덱스에 있는 행만 대상으로 하기 때문에 쿼리 계획의 품질을 높일 수 있다.

 데이터베이스 엔진은 필터링된 인덱스에 대한 필터링된 통계를 자동으로 만들고 유지 관리한다.

 인덱싱되지 않은 열에 대해 필터링된 통계를 만들어 필터링된 인덱스가 필요하지 않은 데이터 하위 집합의 쿼리 계획 품질을 높일 수도 있다.

   

1.2 필터링된 인덱스

   

 필터링된 인덱스 및 통계는 모두 간단한 비교연산자에 기반한 동일한 필터 조건자 식으로 정의된다.

   

 필터링된 인덱스는 데이터의 잘 정의된 하위 집합에서 선택하는 쿼리를 처리하는데 적합한 최적화된 비클러스터형 인덱스 이다. (필터 조건자를 사용하여 테이블의 일부 행을 인덱싱)

   

1.3 데이터의 하위 집합에 대한 필터링된 인덱스

   

 열에 적은 수의 쿼리 관련 값만 있는 경우 하위 집합에 필터링된 인덱스를 만들 수 있다.

   

 EX) 2679개의 행에서 NULL이 아닌 값이 들어있는 EndDate 열에는 199개의 행만 있고 나머지는 NULL일 때 필터링된 인덱스는 이 인덱스에서 정의된 열을 반환하고 EndDate에 필요한 NULL이 아닌 값이 있는 행만 선택하는 쿼리를 처리합니다.

   

USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'FIBillOfMaterialsWithEndDate' AND object_id = OBJECT_ID (N'Production.BillOfMaterials')) DROP INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials GO CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS NOT NULL; GO

   

SELECT ProductAssemblyID, ComponentID, StartDate FROM Production.BillOfMaterials WHERE EndDate IS NOT NULL; GO

   

<v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f><o:lock aspectratio="t" v:ext="edit"></o:lock><v></v>

   

   

   

1.4 서로 다른 데이터에 대한 필터링된 인덱스

   

 테이블에 서로 다른 데이터 행이 있는 경우 하나 이상의 데이터 범주에 대한 필터링된 인덱스를 만들 수 있다.

   

1.5 인덱싱된 뷰와 필터링된 인덱스 비교

   

 인덱싱된 뷰에비해 필터링된 인덱싱의 장점

   

①  줄어든 인덱스 유지 관리 비용.(필터링된 인덱스를 업데이트하는데 인덱싱된 뷰보다 적은 CPU리소스 사용)

②  향상된 계획 품질.

③  온라인 인덱스 다시 작성. 인덱싱된 뷰는 온라인 인덱스 다시 작성이 지원되지 않음.

④  필터링된 인덱스는 고유하지 않아도 되지만 인덱싱된 뷰는 반드시 고유해야 한다.

   

1.6 키열

   

 적은 수의 키 또는 포괄 열을 필터링된 인덱스 정의에 포함하고 쿼리 최적화에 필요한 열만 통합하여 쿼리 실행계획에 대한 필터링된 인덱스를 선택하는 것이 가장 좋다.

   

 필터링된 인덱스의 처리 여부에 상관없이 쿼리에 대한 필터링된 인덱스를 선택할 수 있다.

   

 필터링된 인덱스 식이 쿼리 조건자와 같고 쿼리가 쿼리 결과로 필터링된 인덱스 식의 열을 반환하지 않는다면 필터링된 인덱스 식의 열이 필터링된 인덱스 정의의 포괄 열 또는 키여야 할 필요는 없다.

   

 쿼리 조건자가 필터링된 인덱스 식과 다른 비교에 필터링된 인덱스 식의 열을 사용하면 해당 열은 필터링된 인덱스 정의의 포괄열 또는 키여야 한다.

 

1.7 필터 조건자의 데이터 변환 연산자

   

 필터링된 인덱스 식에 지정된 비교 연산자로 인해 암시적 또는 명시적 데이터 변환이 발생할 경우 비교 연산자의 왼쪽에서 변환이 일어나며 오류가 발생 한다.

   

 해결방법은 비교 연산자의 오른쪽에 데이터 변환연산자(CAST 또는 CONVERT)를 사용하여 필터링된 인덱스 식을 작성하는 것이다.

   

USE AdventureWorks; GO IF OBJECT_ID ('dbo.TestTable') IS NOT NULL DROP TABLE dbo.TestTable; GO CREATE TABLE TestTable (a int, b varbinary(4)); GO

   

   

/* 필터링된인덱스정의에서상수1과비교하기위해 b 열이정수데이터형식으로암시적으로변환됩니다. 이로인해오류메시지10611이생성되며그이유는 필터링된조건자에있는연산자의왼쪽에서변환이발생하기때문입니다. */ USE AdventureWorks; GO IF EXISTS ( SELECT name from sys.indexes WHERE name = N'TestTabIndex' AND object_id = OBJECT_ID (N'dbo.TestTable')) DROP INDEX TestTabIndex on dbo.TestTable GO CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b) WHERE b = 1;

   

/* 해결방법은다음예에서와같이b 열과동일한 유형이되도록오른쪽에있는상수를변환하는것입니다. */ CREATE INDEX TestTabIndex ON dbo.TestTable(a,b) WHERE b = CONVERT(Varbinary(4), 1); GO

   

<v></v>

1.8 종속성 참조

   

 Sys.sql_expression_dependencies 카달로그 뷰에서는 필터링된 인덱스 식의 각 열을 종속성 참조로 추적한다.

   

 필터링된 인덱스 식에 정의된 테이블 열의 정의에 대해 삭제, 이름변경 또는 변경을 수행할 수 없다.

   

1.9 필터링 된 인덱스를 사용하는 경우

   

 필터링된 인덱스는 쿼리가 SELECT문에서 참조하는 데이터의 잘 정의된 하위 집합이 열에 포함되는 경우 유용하다.

①  몇 개의 NULL이 아닌 값만 포함하는 스파스 열

②  포함하는 데이터의 범주가 서로 다른 열

③  달러 금액, 시간 및 날짜와 같은 값의 범위를 포함하는 열

④  열 값에 대해 간단한 비교 논리로 정의되는 테이블 파티션

   

 필터링된 인덱스는 하나의 테이블에서 정의되고 간단한 비교 논리만 지원한다.

   

 여러 테이블을 참조하거나 복잡한 논리를 사용하는 필터 식이 필요할 경우 뷰를 만들어야 한다.

   

2.0 필터링된 인덱스 기능 지원

   

  ALTERINDEX는 필터링된 인덱스를 지원한다. 필터링된 인덱스를 수정하려면 CREATE INDEDX WITH DROP_EXISTING을 사용한다.

   

  누락된 인덱스 기능은 필터링된 인덱스를 제안하지 않는다.

   

   

  데이터베이스 엔진튜닝 관리자는 인덱스 튜닝 조언을 권장 할 때 필터링된 인덱스를 검토한다.

   

  온라인 인덱스 작업은 필터링된 인덱스를 지원한다.

  테이블 힌트는 필터링된 인덱스를 지원하지만 필터링된 인덱스에는 적용되지 않는 몇 가지 제한 사항이 있다

   

  ????.

2    필터링된 통계

필터링된 통계는 잘 정의된 데이터의 하위 집합에서 선택하는 쿼리에 대한 통계의 정확도를 높이는데 특히 적합한 최적화된 통계로서, 필터 조건자를 사용하여 통계에 포함되는 데이터의 하위 집합을 선택한다.

   

 데이터베이스 엔진은 필터링된 인덱스에 대한 필터링된 통계를 자동으로 만들고 유지 관리한다.

   

 필터링된 통계는 펄터링된 인덱스의 행만 처리하므로 전체 테이블 통계보다 정확하다.

   

 열이 인덱스에서 정의되지 않았지만 쿼리 조건자에서 참조되는 경우 데이터베이스 엔진은 열에 전체 테이블 통계를 만들어 쿼리 계획을 향상 시킬 수 있다.

   

 쿼리가 다른 열의 값에 대해 상관 관계에 있는 데이터의 하위 집합을 포함하는 인덱싱되지 않은 열에서 선택할 경우 쿼리 계획을 향상 시킬 수 있다.

   

 필터링된 인덱스는 필터링된 통계를 만드는데 기본 샘플링 비율을 사용한다.

   

3    쿼리 힌트

   

 OPTIMIZE FOR 쿼리 힌트 옵션은 데이터베이스 엔진에서 쿼리를 최적화 하는 동안 초기 값 대신 통계 데이터를 사용하여 하나 이상의 지역 변수 값을 결정하도록 지정하는 UNKNOWN구문을 통해 향상되었다.

   

3.1 쿼리 힌트

   

 쿼리 힌트는 해당 힌트가 전체 쿼리에 사용해야 하는 힌트임을 나타낸다.

   

 쿼리 힌트는 모든 연산자에 영향을 준다.

   

 기본 쿼리에 UNION이 포함된 경우 UNION 연산과 연관된 마지막 쿼리에만 OPTION절을 포함할 수 있다.

   

 하나 이상의 쿼리로 인해 쿼리 최적화 프로그램에서 유효한 계획을 생성할 수 없는 경우 8622 오류가 발생 합니다.

   

3.2 옵션 인수

   

 {HASH | ORDER} GROUP : 쿼리의 GROUP BY 또는 COMPUTE절에 지정된 집계에서 해시나 정렬을 사용하도록 지정한다.

   

USE AdventureWorks; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (HASH GROUP, FAST 10); GO

   

<v></v>

   

 {MERGE | HASH | CONCAT} UNION : UNION 집합을 병합, 해시 또는 연결하여 모든 UNION 연산을 수행하도록 지정합니다. 둘 이상의 UNION힌트 사용 시 가장 부담이 적은 전략을 선택한다.

   

USE AdventureWorks; GO SELECT * FROM HumanResources.Employee AS e1 UNION SELECT * FROM HumanResources.Employee AS e2 OPTION (MERGE UNION); GO

   

<v></v>

   

 {LOOP | MERGE | HASH} JOIN : 전체 쿼리에서 모든 연산이 힌트에 의해 수행되도록 지정한다.

   

USE AdventureWorks; GO SELECT * FROM Sales.Customer AS c INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID WHERE TerritoryID = 5 OPTION (MERGE JOIN); GO

   

 FAST number_rows : 첫번째 음수가 아닌정수 number_rows를 빨리 검색하기 위해 쿼리를 최적화 하도록 지정한다. 첫 번째 number_rows를 반환한 후 쿼리는 계속 실행하여 전체 결과 집합을 만든다.

   

 FORCE ORDER : 쿼리 구문에 지정된 조인 순서가 최적화 시 유지되도록 지정합니다.

   

 MAXDOP number : Sp_configure 및 리소스 관리자의 max degree of parallelism 구성옵션을 무시한다.

   

USE AdventureWorks ; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (MAXDOP 2); GO

   

 OPTIMIZE FOR : 지역 변수에 대해 특정 값을 사용하도록 지시. 최적화시에만 사용되고 쿼리 실행시에는 사용되지 않음.

   

USE AdventureWorks; GO DECLARE @city_name nvarchar(30); DECLARE @postal_code nvarchar(15); SET @city_name = 'Ascheim'; SET @postal_code = 86171; SELECT * FROM Person.Address WHERE City = @city_name AND PostalCode = @postal_code OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) ); GO

   

 RECOMPILE

   

 ROBUST PLAN : 최대 잠재적 행 크기를 정의할 수 있는 계획을 세우도록 한다. 쿼리가 처리될 때 중간 테이블 및 연산자가 입력 행보다 큰 행을 저장하고 처리해야 할 수 있다. 행이 너무 커서 처리하지 못한 경우에는 실행 중 오류를 생성한다. ROBUST PLAN을 사용하면 이러한 문제가 발생할 수 있는 쿼리 계획을 고려하지 않도록 할 수 있다.

   

 KEEP PLAN : 쿼리 최적화 프로그램에서 쿼리에 대한 예상 재컴파일 임계값을 완화 하도록 한다. 재컴파일 임계값은 UPDATE, DELETE, MERGE또는 INSERT문을 실행하여 인덱싱된 열을 예상 수만큼 변경했을 때 쿼리가 자동으로 재컴파일 되는 시점.

   

 KEEPFIXED PLAN : 통계 변경시 쿼리를 재컴파일 하지 않도록 한다. 원본으로 사용하는 테이블의 스키마가 바뀌거나 테이블에 대해 sp_recompile이 실행되는 경우에만 재컴파일.

   

 EXPAND VIEWS : 인덱싱된 뷰를 확장하고 쿼리 최적화 프로그램에서 인덱싱된 뷰를 쿼리 일부를 대체하는 것으로 간주하지 않도록 지정. 이 힌트는 쿼리 계획에서 인덱싱된 뷰와 인덱싱된 뷰의 인덱스를 직접 사용하도록 허용하지 않는다.

   

 MAXRECURSION number : 해당 쿼리에 대해 허용되는 최대 재귀 횟수 지정. 범위는 0-32767의 정수. 서버기본한도는 100.

   

USE AdventureWorks; GO --Creates an infinite loop WITH cte (EmployeeID, ManagerID, Title) AS ( SELECT EmployeeID, ManagerID, Title FROM HumanResources.Employee WHERE ManagerID IS NOT NULL UNION ALL SELECT cte.EmployeeID, cte.ManagerID, cte.Title FROM cte JOIN HumanResources.Employee AS e ON cte.ManagerID = e.EmployeeID ) --Uses MAXRECURSION to limit the recursive levels to 2 SELECT EmployeeID, ManagerID, Title FROM cte OPTION (MAXRECURSION 2); --재귀수준을2로제한 GO

   

<v></v>

   

 USE PLAN N'xml_plan : 쿼리 최적화 프로그램이 'xml_plan'에 의해 지정된 쿼리에 대해 기존의 쿼리 계획을 사용하도록 한다.

   

4. 테이블 힌트

1. FORCESEEK 테이블 힌트

   

 FORCESEEK 테이블 힌트는 쿼리 최적화 프로그램이 INDEX Seek 연산만 쿼리에 참조된 테이블이나 뷰의 데이터에 대한 액세스 경로로 사용 하도록 한다.

   

 인덱스 통합 및 교차 지원.

   

   

 LIKE, IN 사용하는 쿼리에 FORCESEEK 사용.

   

 FORCESEEK 테이블 힌트를 사용하기 전에 데이터베이스의 통계가 현재 통계이며 정확한지 확인.

   

   

 INDEX 힌트 INDEX(0)을 FORCESEEK 힌트와 함께 사용하지 않는다. INDEX(0)은 강제로 기본테이블검색, FORCESEEK 같이 사용할 경우 8622 에러 반환.

   

 USE PLAN 쿼리 힌트를 FORCESEEK 힌트와 함께 사용하지 않는다. FORCESEEK힌트 무시.

   

5. 잠금

   

5.1 잠금 모드

   

공유잠금 : 데이터를 읽을 때 자동으로 공유 잠금을 얻는다. 공유 잠금은 테이블이나 페이지나 인덱스 키나 개별 행들에 유지될 수 있다. 어떤 프로세스도 공유 잠금이 있는 데이터에서 단독 잠금을 얻을 수 없다.

   

단독잠금 : 데이터 삽입이나 업데이트, 삭제등 데이터가 변경될 때 자동으로 단독 잠금을 얻는다. 특정 데이터 리소스에 대해서 한번에 한 프로세스만이 단독 잠금을 유지할 수 있다. 단독 잠금은 트랜잭션이 보장된다. 힌트를 사용함으로써 잠금이 유지된 데이터를 읽기로 만들 수 있다.

   

업데이트 잠금 : SQL Server가 데이터 변경 동작을 수행하지만 먼저 변경될 리소스를 찾기 위해 테이블을 검색할 필요가 있을 때 사용된다.

   

축약어	잠금 모드	잠금모드 내부코드	설명
S	공유잠금	4	읽는 것은 허용, 변경 허용 않음
X	단독잠금	6	읽거나 변경할 수 없음
U	업데이트 잠금	5	다른 프로세스들이 업데이트 잠금이나 단독 잠금을 얻지 못하도록 방지. 변경할 데이터를 찾을 때 사용.
IS	내재된 공유잠금	7	잠금 수준이 테이블 수준이나 페이지 수준에서만 얻어질 수 있다.
IU	내재된 업데이트 잠금	8	잠금 수준이 테이블 수준이나 페이지 수준에서만 얻어질 수 있다.
IX	내재된 단독잠금	9	잠금 수준이 테이블 수준이나 페이지 수준에서만 얻어질 수 있다.
SIX	공유 및 내재된 단독잠금	11	공유잠금을 유지하고 있는 리소스가 단독 잠금으로 잠겨진 구성 요소도 갖고 있다는 것을 나타낸다.
Sch-S	스키마 안정성 잠금	2	이 테이블을 사용하고 있는 쿼리가 컴파일 되고 있다는 것을 나타낸다.
Sch-M	스키마 수정잠금	3	테이블의 구조가 변경되고 있다는 것을 나타낸다.
BU	대량 업데이트 잠금	13	대량 복사 동작이 데이터를 테이블로 복사하고 있고 TABLOCK 힌트가(수동 또는 자동으로) 적용되고 있을 때 사용된다.
RangeS-S		14	키 사이의 범위에 있는 공유 잠금. 범위 끝의 키에 있는 공유 잠금.
RangeS-U		15	키 사이의 범위에 있는 공유 잠금
RangeIn-Null		16	키 사이의 범위에서 삽입을 방지하는 단독 잠금. 키 자체에는 어떤 잠금도 없다.
RangeX- X		22	키 사이의 벙위에 있는 단독 잠금. 범위 끝의 키에 있는 단독 잠금.
RangleIn-S		17	S 잠금과 RangeIn_Null 잠금에 의해 만들어진 변환 잠금
RangeIn-U		18	U 잠금과 RangeIn_Null 잠금에 의해 만들어진 변환 잠금
RangeIn-X		19	X 잠금과 RangeIn_Null 잠금의 변환
RangeIn-U		20	RangeIn_Null 잠금과 RangeS_S 잠금의 변환
RangeX-U		21	RangeIn_Null 잠금과 RangeS_U 잠금의 변환

   

5.2 잠금 힌트

   

트랜잭션 격리수준과 관련된 잠금힌트

   

 READUNCOMMITTED (NOLOCK) : 트랜잭션이 현재 데이터 페이지에 있는 어떤 데이터든지 읽을 수 있게 해 준다.

   

 READCOMMITTED

   

   

 REPEATABLEREAD : 트랜잭션이 데이터를 다시 액세스 하려고 하거나 쿼리가 다시 던져질 때 데이터가 바뀌어 있지 않을 것이라는 것을 보증.

   

 SERIALIZABLE (HOLDLOCK) : 쿼리가 다시 실행되었을 때 그 동안 행들이 추가 되지 않았을 것이라는 것을 보증한다.

   

잠금 크기와 관련된 잠금 힌트

   

 ROWLOCK : 공유 행 잠금을 지정한다.

  

 PAGLOCK : 공유 페이지 잠금을 지정한다.

  

 TABLOCK : 공유 테이블 잠금을 지정한다.

  

 TABLOCKX : 단독(exclusive) 테이블 잠금을 지정한다.

   

5.3 그 밖의 힌트

   

 XLOCK : 단독 잠금을 지정한다. PAGLOCK이나 TABLOCK과 함께 사용하여 잠금의 크기도 지정할 수 있다.

   

 UPDLOCK : 업데이트 잠금을 지정한다.

   

 READPAST : 잠긴 행은 건너뛰고 잠기지 않는 행들만 액세스한다. READ COMMITTED 격리 수준에서 작동하는 트랜잭션에만 적용되고 행 수준의 잠금만 건너뛸 수 있다. 블락킹을 방지하기 위해 지정이 필요할 때가 있다.

   

5.4 SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

 테이블에 대해 허용되는 잠금 에스컬레이션 방법을 지정한다.

5.4.1         AUTO

 SQL Server 데이터베이스 엔진에서 테이블 스키마에 적절한 잠금 에스컬레이션 세분성을 선택할 수 있다.테이블이 분할되지 않은 경우에는 잠금이 파티션으로 에스컬레이션된다. 잠금이 파티션 수준으로 에스컬레이션 된 후에는 나중에 잠금이 TABLE 세분성으로 에스컬레이션 되지 않는다.

  테이블이 분할되지 않은 경우에는 잠금이 TABLE 세분성으로 에스컬레이션 된다.

5.4.2         TABLE

 테이블이 분할되었는지 여부에 관계없이 잠금이 테이블 수준 세분성으로 에스컬레이션됩니다. 이 동작은 SQL Server 2005의 경우와 동일합니다. TABLE이 기본값입니다.

5.4.3 DISABLE

 대부분의 경우 잠금 에스컬레이션이 허용되지 않는다. 테이블 수준 잠금은 부분적으로 허용된다.

 예를 들어 직렬화 가능 격리 수준에서 클러스터형 인덱스가 없는 테이블을 검색하면 데이터베이스 엔진에서 테이블 잠금을 사용하여 데이터 무결성을 보호해야 한다.