SQL Server 튜닝가이드

수칙 1. SELECT는 필요한 결과값만을 요구하는가?

select title , price from titles where title_id = 'BU1032'
select title , price from titles
select * from titles

수칙 2. 적절한 WHERE 조건을 사용하는가?(인덱스 사용 고려)

가. 범위 스캔이 불가능하거나 인덱스 사용이 아예 불가능한 경우

아래와 같이 인덱스 선두 칼럼을 조건절에서 가공하면 (FBI 인덱스를 정의하지 않는 한) 정상적으로 인덱스를 사용할 수 없다.

select * from 업체 where substr(업체명, 1, 2) = '대한'

또한 아래 처럼 부정형 비교를 사용해도 마찬가지다.

select * from 고객 where 직업 <> '학생'

is not null 조건도 부정형 비교에 해당하므로 정상적인 인덱스 사용은 어렵다.

select * from 사원 where 부서코드 is not null

나. 인덱스 칼럼의 가공

인덱스 칼럼을 가공하면 정상적인 Index Range Scan이 불가능해진다고 했다. 가장 흔한 인덱스 칼럼 가공 사례는 [표 Ⅲ-4-2]와 같고, 오른쪽 칼럼은 각 사례에 대한 튜닝 방안이다.

다. 묵시적 형변환

인덱스 칼럼을 사용자가 명시적으로 가공하지 않더라도 조건절에서 비교되는 두 값의 데이터 타입이 다르면 내부적으로 형변환이 일어난다.

select * from emp where deptno = '20'

수칙 3. COUNT(컬럼명) 대신 COUNT(*)을 사용하는가?

COUNT() 와 COUNT(컬럼명)의 차이는 중요합니다. COUNT 하는 해당 테이블 컬럼에 NULL 값을 포함하고 있다면 이 두 예제는 서로 다른 결과를 반환합니다. COUNT(컬럼명)은 그룹에 포함된 각 행을 평가하여 NULL이 아닌 값의 개수를 반환합니다. COUNT()는 NULL 값과 중복된 값을 포함한 그룹의 항목 개수를 반환합니다. 일반적으로, COUNT(컬럼명)을 사용하여 특정한 컬럼의 행 개수를 세는 것보다 COUNT(*)을 사용하여 옵티마이저가 행의 개수를 반환하는 최상의 방법을 선택하도록 해주는 것을 더 선호하는 방식이다

수칙 4. 커서 및 임시 테이블의 내용을 최대한 자제하는가?

결론부터 말씀 드리자면 커서보다는 임시테이블이 임시테이블보다는 테이블 변수를 사용하는 것이 성능에 보탬이 됩니다. 커서는 내부적으로 임시 테이블을 사용하기 때문에 임시테이블을 쓴다고 부하가 더 발생하진 않습니다. 오히려 커서의 부가적 기능 때문에 서버 자원을 더 낭비하게 됩니다. (커서로 할 수 있는 건 임시테이블이나 테이블 변수로도 모두 처리가 가능합니다.)

수칙 5. VIEW의 총사용을 줄였는가?

VIEW는 보안과 편리성에 관련된 이슈를 다루는 데 있어 최고입니다, 그러나, 일반적으로 보안상에 이슈를 제외한 경우에는 불필요한 부하가 가중될 수 있고 많은 경우에 더 불필요한 데이터를 반환합니다 예를 들면 VIEW에서 10개를 가져오고 거기에 WHERE 조건을 붙여서 7개만 가져오는 경우가 그렇습니다. select lastname,firstname from employees VS select * from EmployeesView 중간 단계가 있는 쪽이 효율이 떨어집니다.

수칙 6. 저장 프로시저를 사용하는가?

저장 프로시저는 복잡한 SQL문을 단순화 시켜주고, 보안 문제를 해결해주며 더 나아가 빠른 성능에 매개변수,출력매개변수,리턴 값을 사용할 수 있습니다.

가. 저장 프로시저의 역할 7가지

데이터 무결성의 시행
복잡한 비즈니스 규칙과 제약의 강화
캡슐형 설계
유지보수
네트워크 트래픽 감소(오고 가는 긴SQL 구문을 축소)
보다 빠른 실행(컴파일을 하지 않습니다)
보안강화

나. 저장 프로시저의 생성과 반복사용 시 발생하는 일

제작
구문분석
표준화
보안 점검(프로시저 생성권한)
저장(syscomments)
첫 번째 실행 시
보안 점검(프로시저 실행 권한)
최적화
컴파일과 이에 따른 실행계획을 캐쉬에 저장
실행
반복해서 실행 시
캐쉬에 실행 계획 있을 때는 그대로 실행
캐쉬에 실행 계획이 없을 때는 첫 번째로 저장 프로시저 실행하는 것과 동일
쿼리는 한번만 실행할 때는 일반 SQL이 훨씬 간단합니다. 그러나 반복적으로 실행되면 저장 프로시저가 월등히 빠르고 편리합니다.

수칙 7. 저장 프로시저를 적절하게 리컴파일 하는가?

데이터가 변화하면(인덱스를 추가하거나 인덱스된 열의 데이터를 변경하는 등의 작업 수행 시) 그에 걸맞게 실행계획도 변화해 갑니다. 그에 대처하기 위해서 다음과 같은 리컴파일 방법를 제공합니다. 저장 프로시저 리컴 파일 모드에는 다음의 3가지가 있습니다.

CREATE PROCEDURE [WITH RECOMPILE] 는 SQL SERVER가 이 저장 프로시저의 계획을 캐시하지 않기 때문에 이 저장 프로시저가 실행 할 때 마다 다시 컴파일 됩니다(실행 속도가 느려짐).

EXECUTE [WITH RECOMPILE] 는 지금 이순간만 리컴파일 하고 다시 저장 프로시저 실행하면 예전 실행 계획대로 작동하는 것입니다. 제공하는 매개 변수가 불규칙하거나 저장 프로시저를 만든 다음 데이터가 많이 변경되었을 경우 이 옵션을 사용합니다.

sp_recompile 는 저장 프로시저가 다음에 실행될 때 첫 실행처럼 컴파일되고 실행되도록 하는 것입니다

수칙 8. 저장 프로시저 작명 시 SP외의 접두어를 사용한다

시스템 저장 프로시저는 master 데이터베이스내에서 sp_라는 접두어로 시작하는 것이 좋으며 모든 데이터베이스에서 실행될 수 있습니다. 각 사용자 데이터베이스에서는 다른 접두어를 사용하는 것이 보기에도 좋고 알아보기에도 수월합니다. 또한 시스템 저장 프로시저는 어느 데이터베이스에서 수행하건 해당 데이터베이스의 내용을 참조합니다. 모든 데이터베이스에서 사용하는 프로시저의 경우 sp_로 시작하게 작성한 후 sp_MS_marksystemobject로 시스템 프로 시저화 작업을 하는게 필요합니다. 이 내용은 엄격하게 구분되서 실행되는 것이 혼란을 줄일 수 있습니다

수칙 9. 모든 개체의 소유자는 DBO 이다

소유자가 다르면 복잡한 소유권 체인문제가 발생합니다.

수칙 10. SET NOCOUNT ON을 사용하는가?(Return이 없는 sp의 경우)

불필요한 메시지가 네트워크 트래픽을 낭비하고 있습니다. 특히’몇 개 행이 적용 되었습니다’ 같은 메시지가 그런 대표적인 예입니다.

참고

http://www.dbguide.net 내 MS-SQL 튜닝가이드 및 인덱스 튜닝 참고

zatourie