2009. 1. 14. 15:03
MSSQL Hint 사용 Work/SQL Server2009. 1. 14. 15:03
온라인 설명서나 학습서 등 여러 문서와 웹사이트에서 MSSQL의 Hint의 사용을 권장하지 않습니다.
그래서 옵티마이저의 선택을 믿을 수 있게 환경설정을 해 주는 것을 최우선으로 설명하고 있습니다.
저도 기본적으로 Hint를 사용하는 것을 권장하고 싶지는 않지만, 사용법을 알고 하지 않는 것과 몰라서 못하는 것은 차이가 많기 때문에 사용법에 대한 조사를 해본 적이 있습니다.
바라지는 않지만, 언젠가 사용해야만 하는 상황이 온다면 빠른 시간내에 일을 처리하고 퇴근을 해야되기 때문입니다.
--------------------------------------------------------------------------------------
MSSQL
Join Hint
두 Table 간의 Join 전략을 강제 적용하도록 지정.
표기예
SELECT Col1, Col2
FROM TAB1 INNER LOOP JOIN TAB2
ON TAB1.Col3 = TAB2.Col3
LOOP
Join을 Nasted Loop으로 실행
HASH
Join을 Hash로 실행
MERGE
Join을 Merge로 실행
REMOTE
오른쪽 Table에서 Join 작업을 수행하도록 지정.
왼쪽 Table이 Local Table이고 오른쪽 Table이 원격 Table인 경우에 유용.
INNER JOIN 작업에 대해서만 사용가능.
Query Hint
OPTION 절의 일부로 지정.
표기예
SELECT Col1, Col2
FROM TAB1 INNER JOIN TAB2
ON TAB1.Col3 = TAB2.Col3
OPTION (MERGE JOIN)
{ HASH | ORDER } GROUP
Query의 GROUP BY, DISTINCT 또는 COMPUTE 절에 지정된 집계에서 Hash나 정렬을 사용하도록 지정.
{ MERGE | HASH | CONCAT } UNION
UNION 집합을 병합, Hash 또는 연결하여 모든 UNION 연산을 수행하도록 지정.
둘 이상의 UNION Hint를 지정한 경우 Query 최적화 프로그램에서는 지정한 Hint 중 가장 부담이 적은 전략을 선택.
참고:
FROM 절에서 Join된 특정 Table 쌍에 대해 <joint_hint>를 지정한 경우 OPTION 절에 지정한 <join_hint>보다 우선적으로 적용.
{ LOOP | MERGE | HASH } JOIN
전체 Query에서 모든 Join 연산이 LOOP JOIN, MERGE JOIN 또는 HASH JOIN에 의해 수행되도록 지정.
둘 이상의 Join Hint를 지정한 경우 최적화 프로그램에서는 허용되는 Hint 중 가장 부담이 적은 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);
FAST number_rows
첫 번째 음수가 아닌 정수 number_rows.를 빨리 검색하기 위해 Query를 최적화하도록 지정.
FORCE ORDER
Query 구문에 지정된 Join 순서가 Query 최적화 시 유지되도록 지정.
MAXDOP number
SQL의 병렬처리를 유도.
이 Option을 지정한 Query에 대해서만 sp_configure의 max degree of parallelism 구성 Option을 무시.
OPTIMIZE FOR
Query가 Compile되고 최적화될 때 Query 최적화 프로그램이 Local 변수에 대해 특정 값을 사용하도록 지시.
표기예
DECLARE @city_name nvarchar(30)
SET @city_name = 'Ascheim'
SELECT * FROM Person.Address
WHERE City = @city_name
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle') )
PARAMETERIZATION { SIMPLE | FORCED }
SQL Server Query 최적화 프로그램에서 Query Compile 시 적용하는 매개 변수화 규칙을 지정합.
PARAMETERIZATION Query Hint는 계획 지침 내에서만 지정. Query 내에서 직접 지정할 수는 없다.
SIMPLE은 Query 최적화 프로그램이 단순 매개 변수화를 시도하도록 지시.
FORCED는 최적화 프로그램이 강제 매개 변수화를 시도하도록 지시.
PARAMETERIZATION Query Hint는 계획 지침 내에서 PARAMETERIZATION 데이터베이스 SET Option의 현재 설정을 무시하는 데 사용.
RECOMPILE
Query를 실행한 후 SQL Server 2005 데이터베이스 엔진 에서 해당 Query에 대해 생성된 계획을 삭제.
다음에 같은 Query가 실행될 때 Query 최적화 프로그램이 Query 계획을 다시 Compile하도록 지시.
ROBUST PLAN
Query 최적화 프로그램에서 성능이 저하되더라도 최대 잠재적 행 크기를 정의할 수 있는 계획을 유도.
KEEP PLAN
Query 최적화 프로그램에서 Query에 대한 예상 다시 Compile 임계값을 완화.
Table이 자주 Update되는 경우 KEEP PLAN을 지정하면 Query가 지나치게 자주 다시 Compile되지 않아 유용.
KEEPFIXED PLAN
통계 변경 시에는 최적화 프로그램이 Query를 다시 Compile하지 않도록 한다.
원본으로 사용하는 Table의 스키마가 바뀌거나 Table에 대해 sp_recompile이 실행되는 경우에만 Query를 다시 Compile.
EXPAND VIEWS
Indexed View를 확장하고 Query 최적화 프로그램에서 Indexed View를 Query 일부를 대체하는 것으로 간주하지 않도록 지정.
Query 텍스트에 있는 View 정의에 의해 View 이름이 바뀌면 View가 확장.
MAXRECURSION number
해당 Query에 대해 허용되는 최대 재귀 횟수를 지정.
number는 0에서 32767 사이의 음수가 아닌 정수. 0을 지정하면 한도가 적용되지 않는다.
이 Option을 지정하지 않은 경우 서버에 대한 기본 한도는 100.
Query 실행 중에 MAXRECURSION 한도로 지정된 횟수 또는 기본 횟수에 도달하면 Query가 종료되고 오류가 반환.
이 오류로 인해 문의 모든 결과가 Rollback. 문이 SELECT 문인 경우 결과의 일부가 반환되거나 아무런 결과도 반환되지 않을 수 있음.
반환된 일부 결과에는 지정한 최대 재귀 수준을 초과한 재귀 수준의 모든 행이 포함되지 않을 수 있음.
USE PLAN N'xml_plan'
Query 최적화 프로그램이 'xml_plan'에 의해 지정된 Query에 대해 기존의 Query 계획을 사용.
Table Hint
주로 FROM 절에 with 와 함께 사용되는 Hint를 말합니다.
한번에 두개 이상의 Hint를 허용하지 않는 경우도 존재합니다.
표기예
SELECT Col1, Col2
FROM TAB1 with (nolock) INNER JOIN TAB2 with (nolock)
ON TAB1.Col3 = TAB2.Col3
NOEXPAND
Indexed View에 대한 Index를 강제로 사용하게 하려면 NOEXPAND Option을 지정.
INDEX ( index_val [ ,...n ] )
Query 최적화 프로그램이 문을 처리할 때 사용할 Index의 이름이나 ID를 지정.
Table당 하나의 Index Hint만 지정.
FASTFIRSTROW
OPTION(FAST 1)과 동일. (Query Hint > FAST number_rows 참고.)
HOLDLOCK
SERIALIZABLE과 동일.
NOLOCK
READUNCOMMITTED와 동일.
NOWAIT
Table에 잠금이 있으면 SQL Server 2005 데이터베이스 엔진 에서 바로 메시지를 반환하도록 지정.
NOWAIT는 특정 Table에 SET LOCK_TIMEOUT 0을 지정하는 것과 동일.
PAGLOCK
일반적으로 행 또는 Key에 개별 잠금이 사용되거나 일반적으로 단일 Table 잠금이 사용되는 곳에서 Page 잠금을 사용.
기본적으로 작업에 적합한 잠금 모드를 사용.
READCOMMITTED
읽기 작업이 잠금 또는 행 버전 관리를 사용하여 READ COMMITTED 격리 수준에 대한 규칙을 따르도록 지정.
READCOMMITTEDLOCK
읽기 작업이 잠금을 사용하여 READ COMMITTED 격리 수준에 대한 규칙을 따르도록 지정.
READPAST
Data베이스 엔진 이 다른 Transaction에 의해 잠긴 행 및 Page를 읽지 않도록 지정.
READUNCOMMITTED
Commit되지 않은 Data 읽기를 허용하도록 지정.
REPEATABLEREAD
REPEATABLE READ 격리 수준에서 실행되는 Transaction과 동일한 잠금 기능으로 검색이 수행되도록 지정합.
ROWLOCK
Page 또는 Table 잠금이 일반적으로 사용될 때 행 잠금을 사용하도록 지정.
SERIALIZABLE
HOLDLOCK과 동일.
필요한 Table 또는 Data Page가 더 이상 필요 없을 때 Transaction의 완료 여부과 관계없이 즉시 공유 잠금을 해제하지 않고 Transaction 완료 시까지 유지.
TABLOCK
Table에 사용되는 공유 잠금이 문이 끝날 때까지 유지되도록 지정.
TABLOCKX
Table에 배타적 잠금을 사용하도록 지정.
UPDLOCK
Update 잠금을 사용하고 Transaction이 완료될 때까지 유지하도록 지정.
XLOCK
배타적 잠금을 사용하고 Transaction이 완료될 때까지 유지하도록 지정.
참고
HOLDLOCK 부터 XLOCK까지는 Table 격리 수준에 대한 것으로 아래의 SET 절로도 지정할 수 있다.
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
[ ; ]
KEEPIDENTITY
OPENROWSET에 BULK Option을 사용하는 경우 INSERT 문에만 적용.
가져온 Data 파일의 ID 값이 ID 열에 사용되도록 지정.
표기예 1
USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
FROM 'C:\MyTestEmptyField2-c.Dat'
with (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
KEEPNULLS -- KEEPIDENTITY, KEEPDEFAULTS
);
GO
표기예 2
INSERT INTO MyTestDefaultCol2
--with (KEEPDEFAULTS)
SELECT *
FROM OPENROWSET(BULK 'C:\MyTestEmptyField2-c.Dat',
FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'
) as t1 ;
GO
KEEPDEFAULTS
OPENROWSET에 BULK Option을 사용하는 경우 INSERT 문에만 적용.
Data 레코드에 열의 값이 없는 경우 NULL 대신 Table 열의 기본값(있는 경우)을 삽입하도록 지정.
표기예
KEEPIDENTITY 의 표기예와 동일
IGNORE_CONSTRAINTS
OPENROWSET에 BULK Option을 사용하는 경우 INSERT 문에만 적용.
대량 가져오기 작업에서 Table의 모든 제약 조건을 무시하도록 지정.
IGNORE_TRIGGERS
OPENROWSET에 BULK Option을 사용하는 경우 INSERT 문에만 적용.
대량 가져오기 작업에서 Table에 정의된 모든 Trigger를 무시하도록 지정.
