수안이의 컴퓨터 연구실

다음 다섯 개의 팁이 성능을 향상 시킬 것이다.

Kalen Delaney

SQL 서버 매거진의 편집자는 지난 5년간 필자가 얻은 최고의 팁이라고 생각되는 것 다섯 개를 제출하도록 요구 해왔다. 처음에는 필자가 쓰고 있는 정보는 전형적으로 팁이라고 불리기에 적합하지 않은 것이라고 생각했지만 곧 그렇지 않다는 것을 발견했다. 종종, 필자는 SQL 서버의 내부 동작에 대해 다루어왔으며 이런 정보들을 어떻게 사용하여 이점을 얻을 수 있는지 제안 하곤 했다. 그래서 이런 내부 정보들의 가치를 발휘할 수 있도록 팁으로 만들어보고자 한다.

쿼리가 커버 되도록 한다.
TIP 넌클러스터 인덱스에 하나 혹은 두개의 컬럼을 추가하여 중요한 혹은 자주 수행되는 쿼리가 커버 되도록 해보자.
클러스터 인덱스는 데이터를 인덱스 키 순서대로 정렬하여 저장하기 때문에 그것은 많은 양의 중복된 데이터나 일정 범위의 데이터를 가져올 때 대단히 효율적이다. 그러나, 테이블은 오직 하나의 클러스터 인덱스만 가질 수 있다. 넌클러스터 인덱스 역시 정렬된 데이터를 가질 수 있지만 오직 인덱스의 리프레벨에서만 그렇다. 따라서, city 컬럼에 걸린 넌클러스터 인덱스는 모든 도시에 대해 알파벳 순서로 정렬되어 있지만 이들 도시와 관련된 고객의 이름까지 원한다면 각 데이터들과 연결되어 이들이 가리키고 있는 포인터(즉 책갈피, bookmark)를 따라가야만 한다. 수많은 고객에 대해 이런 작업을 해야 한다면 이것은 많은 양의 데이터 페이지를 처리하는 엄청난 부하를 가질 것이다. 하지만 고객 이름이 인덱스의 한 부분이라면 어떻게 될까? SQL 서버는 넌클러스터 인덱스에 최고 16개의 컬럼을 포함 할 수 있으며, 결국 인덱스 리프 레벨에 16개 컬럼을 포함 시킬 수 있다. 만약 쿼리하는 모든 데이터가 인덱스의 부분이라면 SQL 서버는 데이터 페이지를 가져와야 할 필요가 없으며 쿼리는 매우 빨라진다. 인덱스만 액세스해서 원하는 모든 데이터를 가져올 수 있는 쿼리를 커버된 쿼리 라고 하며 쿼리에 포함된 모든 컬럼을 가진 인덱스를 커버하는 인덱스라고 한다.
생각하는 것 보다 더 많은 커버를 하는 인덱스를 가질 수 있다. 테이블이 클러스터 인덱스를 이미 가지고 있다면 모든 넌클러스터 인덱스의 리프레벨을 위해 클러스터 인덱스가 책갈피로 사용됨을 기억하자. 즉, 모든 넌클러스터 인덱스는 항상 부수적인 키를 가진 셈이 된다. 만약 테이블의 lastname 과 firstname에 넌클러스터 인덱스가 있고, employeeID에 클러스터 인덱스가 있다면 모든 넌클러스터 인덱스의 리프 레벨 행은 이 세 개의 컬럼 값을 모두 가지고 있다. 이 넌클러스터 인덱스는 firstname, lastname, employeeID 만을 찾는 어떤 쿼리에 대해서도 커버한다.

힌트에 대해 바로 알자

TIP쿼리에 힌트 사용을 피하도록 하자. 어떤 상황에서 테스트 한 결과, 힌트가 필요하다고 하면, 주기적으로 재 테스트 하도록 계획을 세우자.
비록 SQL 서버 2000 이 30개 이상의 최적화기가 특정행동을 하도록 강제화 시키는 최적화 힌트를 제공하긴 하지만, 대부분의 공식적인 튜닝 가이드는 이 힌트를 쓰지 말라고 권장하고 있다. 대부분 경우에 필자는 이 제안에 동의 한다. 최적화기는 SQL 서버 쿼리 엔진에서 가장 복잡한 부분이다. 힌트를 사용함으로써 SQL 서버가 이런 복잡한 코드의 일부분을 수행하는 것을 방지하는 이점을 얻을 수 있다. 특정 경우에는 그 당시 그 쿼리를 위해서는 적절한 방법일 수 있다. 그러나 데이터가 변하거나, 최적화기가 향상된 기능을 가진 새로운 서비스 팩을 적용하면 그것은 더 이상 유용하지 않을 수 있다. 실제 서버에서 쿼리 힌트를 사용했다면 SQL 서버는 데이터 분포가 변한 경우에도 항상 그 쿼리를 지정된 방법으로만 수행한다. 즉, 힌트는 더 이상 최적의 방법을 제공하지 못함에도 불구하고, 지정된 방법으로만 수행되는 것이다.
필자는 기존의 자유롭게 최적화기 힌트를 사용하고 있는 응용 프로그램을 튜닝 하는 무수한 작업을 해왔다. 처음에는 유용했을 수도 있는 쿼리 힌트가 수 개월, 수 년이 지난 후에는 오히려 대부분 필요없거나 오히려 해가 된다고 늘 생각했고 이들 힌트를 성능 저하의 범인으로 의심해왔다. 그래서 이런 경우에 어떻게 할 것인가? 실제 사용하는 모든 코드를 뒤져서 이들 힌트를 제거하고, 성능 테스트를 할 것인가? 다행히도 SQL 서버는 더 쉽고 완벽한 방법을 제공한다. 세 개의 문서화 되지 않은 추적 플래그를 사용할 수 있는데, 이들은 SQL 서버의 시작 매개변수로 사용할 수 있으며 SQL 서버로 하여금 특정 종류의 힌트를 무시하도록 할 수 있다. 추적 플래그 8602 는 SQL 서버가 모든 인덱스 힌트를 무시하게 한다. 추적 플래그 8722 는 SQL 서버로 하여금 모든 쿼리 힌트 (OPTION 절에서 사용하는)를 무시하게 하며 추적 플래그 8755 는 SQL 서버로 하여금 모든 잠금 힌트를 무시하게 한다.
이 세 추적 플래그와 함께 응용 프로그램을 수행하면 이들 힌트를 제거했을 때 얼마나 이점을 얻을 수 있는지 알아낼 수 있다. 이것을 제거하는 것이 더 잇점이 있다고 판단했으면 실제로 힌트를 제거하고 다시 성능을 테스트해 볼 계획을 세우면 된다. 어떤 힌트들은 여전히 유용할 수 있지만 이 추적 플래그를 사용하면 기존의 모든 힌트를 가진 것보다 아무 힌트도 가지지 않은 것이 더 낫다는 것을 알려줄 것이다.

트랜잭션을 엉키게 하지 말자

TIP @@trancount 를 트랜잭션 시작하기 전에 점검하여 SQL Server 가 불필요한 잠금을 갖지 않도록 하자.
필자는 잠금과 트랜잭션의 관계에 관한 몇 개의 기사를 썼다. 필자는 항상 SQL 서버가 트랜잭션이 commit되거나 rollback되기 전에는 결코 배타 잠금을 풀지 않음을 목격했다. 대부분 사람들은 이런 관계를 알고 있지만 많은 사람들은 트랜잭션을 중첩 시켰을 때 어떤 일이 벌어지는지 알고 있지 못하다. 논리적으로 SQL 서버안에서는 어떤 트랜잭션도 중첩되는 것이 아니다. 한 컨넥션을 위해서 최대 하나의 트랜잭션만 가질 수 있다. T-SQL 코드가 여러 개의 BEGIN TRANSACTION 문장을 수행하면 문법적으로는 중첩된 트랜잭션을 갖게 된다. 다른 트랜잭션이 활성화 된 상태에서, 두 번째 BEGIN TRANSACTION 문장을 수행하면 SQL 서버는 내부적인 카운터만 증가시킨다. 이것이 바로 @@trancount 함수가 보여주는 값이다.
COMMIT TRANSACTION을 수행하면 SQL 서버는 @@trancount의 값을 감소시킨다. 이 값이 0 이 되었을 때만 SQL 서버는 활성화 된 트랜잭션을 commit 하고 모든 잠금을 풀게 된다. 그래서 활성화 된 트랜잭션을 commit하기 위해서는 정확하게 COMMIT TRANSACTION 문장을 BEGIN TRANSACTION 문장을 수행한 만큼 수행해야 한다. 그러나 단 하나의 ROLLBACK TRANSACTION 문장은 @@trancount 값을 0으로 리셋하며, 그래서 SQL 서버는 즉시 활성 트랜잭션을 rollback 하며 모든 잠금은 풀려진다.
특정 상황에서 트랜잭션이 롤백 되었다고 생각할 수 있지만 아닌 경우가 있다. 많은 오류들이 오류 메시지를 돌려주고 트랜잭션의 취소 없이 현재 문장을 취소한다. 필자는 응용 프로그램 개발자가 오류가 났음에도 불구하고 @@trancount 를 검사하지 않은 채, 새로운 BEGIN TRANSACTION 문장으로 트랜잭션을 다시 시작하는 것을 보아왔다. 그리고 COMMIT TRANSACTION 이 실행되면 개발자는 생각하기를 트랜잭션이 커밋되었다고 생각하지만 트랜잭션은 여전히 활성화 되어있고 잠금은 풀리지 않는다.
사용자나 프로세스가 SQL 서버에 CANCEL 명령을 보냈을 때도 이런 비슷한 상황에 처하게 된다. CANCEL 명령은 현재 수행 중인 배치를 취소하는 명령이지만 트랜잭션을 롤백하지는 않는다. 그래서 현재의 배치를 취소한 후에 새로운 BEGIN TRANSACTION 을 수행하면 빠져 나오기 위해서는 여러 개의 COMMIT TRANSACTION 명령이 필요한 문법적으로 중첩된 트랜잭션에 처하게 되는 것이다.
프로세스가 중첩된 트랜잭션에 엉켜있는지 아닌지를 판단하는 한가지 방법은 sysprocesses 테이블을 조회해보는 것이다. sysprocesses 테이블에 open_tran이란 컬럼이 있는데, 이 값이1 이상인 것을 찾으면 된다. 만약 프로세스가 계속적으로 1 이상의 값을 가지고 있다면, 이 프로세스가 가지고 있는 잠금을 조사하고 왜 프로세스가 중첩 트랜잭션에 빠졌는지 조사해 보아야 한다.

Blackbox 를 설정한다

TIP blackbox 가 수행되게 하고, 필요할 때 그것이 있다는 것을 잊지말자.
blackbox는 SQL 서버에 보내진 마지막 5MB 분량의 T-SQL 문장을 가진 특별한 추적 파일이다. 이것은 진정한 롤오버(rollover) 파일인데 5MB 의 분량에 이르면 자기 자신을 덮어쓴다. 2001년 2월호 "프로필러의 블랙박스 기능" 기사에서 필자는 이 추적 파일을 설정하고 관리하는 완전한 지침을 제공했으며 SQL 서버가 시작될 때 마다 저장 프로시저를 이용하여 이를 자동 수행하는 방법에 대해 다루었다.
blackbox 로 인한 오버헤드는 매우 작으며 그래서 필자는 늘 이것이 수행될 것을 권장한다. 설명할 수 없는 동작을 보거나, SQL 서버가 명확하지 않은 이유로 갑자기 죽는다면 이 블랙박스 기록을 조사하는 엄청난 이점을 얻을 수 있다.

성능 모니터를 개인화 하자
TIP user-defined counters 를 Performance Monitor에 포함시키므로써, SQL 서버 데이터나 메타 데이터로부터 가져올 수 있는 어떤 숫자 값이던 모니터 할 수 있다.
고객 중 하나는 과도한 블로킹 문제를 가지고 있었으며, 그래서 성능 모니터를 설정하여 SQL 서버 시스템의 잠금 수를 추적하고 싶어했다. 하지만, 이 정보는 약간의 도움만 되었다 왜냐하면 대부분의 잠금은 일시적이며 그 중 일부만이 나쁜 것이지 모든 잠금이 나쁜 것은 아니기 때문이다. 잠금은 SQL 서버가 적절한 방법으로 풀지 않아서 잠금을 얻으려는 다른 프로세스를 방해할 때만 나쁜 것이다.
필자는 다른 프로세스를 차단 하고 있는 프로세스의 개수를 추적하도록 제안했다. "Track Down Troublemakers" 기사에서 필자는 자신이 차단 당하지 않고 다른 프로세스를 차단하고 있는 프로세스를 보여주는 스크립트를 제공했다. 이런 프로세스는 잠금 체인의 선두(heads of lock chains)라고 불린다. 잠금 체인의 선두 프로세스를 모니터하는 것은 유용하지만 성능 모니터는 기본적으로 이를 모니터 할 방법을 제공하지 않는다. 하지만 SQL 서버는 10개의 사용자 정의 카운터를 제공하는데 이를 성능 모니터에서 모니터 할 수 있다. SQL Server: User Settable 개체를 선택하고 10개 인스턴스 중 하나를 선택하면 된다. 예를 들어, User Counter 1에 시스템 저장 프로시저 sp_user_counter1 을 사용하여 이 카운터의 값을 설정할 수 있다. (SQL 서버는 다른 카운터에 대해 비슷한 이름의 저장 프로시저를 가지고 있다). 이 프로시저를 호출하고, 숫자 값을 전달하면 성능 모니터는 전달받은 이 값을 보여준다. 나는 [리스트 1] 에서 보여주는 루프를 작성했는데 이것은 User Counter 1의 값에 매 5초마다 잠금 체인의 선두 값을 전달한다.
SQL 서버의 내부 동작을 이해하는 것은 작업을 단순히 하거나 더 나은 성능을 가져다 준다. 찾는 자만이 이런 팁을 얻을 수 있다. 여기 마지막 팁이 있다. SQL 서버 매거진 은 매달 이런 팁을 찾을 수 있는 놀라운 곳이다.

[리스트 1] 현재 잠금 체인의 선두 개수를 User Counter 1 값에 넣는 루프
WHILE 1 = 1 BEGIN DECLARE @numheads int SELECT @numheads = count(*) FROM master..sysprocesses WHERE ((blocked = 0 AND spid IN (SELECT blocked FROM master..sysprocesses))) EXEC sp_user_counter1 @numheads WAITFOR DELAY '0:0:05' END

출처: Windows & .NET [2004년 4월호]