수안이의 컴퓨터 연구실

이 기사에서는 SQL Server 아키텍처의 내부 동작에 대해 자세히 다룹니다. 이 기사는 데이터베이스 엔진의 기능 향상에 대해 설명하며 사용 설명과 자세한 정보를 위한 지침을 제공합니다. SQL Server 엔진에 대한 자세한 정보를 이해하면 데이터베이스 관리자(데이터베이스 시스템 엔지니어)로서 데이터베이스 시스템을 디자인, 구축 또는 기능을 향상시킬 때 SQL Server의 장점을 최대한 활용할 수 있습니다. 이 자료가 데이터베이스 전문가들을 대상으로 하는 것이기는 하지만 교육이나 마케팅 측면에서도 유용하게 사용할 수 있습니다

데이터베이스 아키텍처: 저장소 엔진
Cathan Cook


요약: 이 기사에서는 SQL Server 아키텍처의 내부 동작에 대해 자세히 다룹니다. 이 기사는 데이터베이스 엔진의 기능 향상에 대해 설명하며 사용 설명과 자세한 정보를 위한 지침을 제공합니다. SQL Server 엔진에 대한 자세한 정보를 이해하면 데이터베이스 관리자(데이터베이스 시스템 엔지니어)로서 데이터베이스 시스템을 디자인, 구축 또는 기능을 향상시킬 때 SQL Server의 장점을 최대한 활용할 수 있습니다. 이 자료가 데이터베이스 전문가들을 대상으로 하는 것이기는 하지만 교육이나 마케팅 측면에서도 유용하게 사용할 수 있습니다.

목차
소개
저장소 엔진 향상
데이터와 상호 작용
테이블 및 인덱스
로깅 및 복구
관리 개선
데이터 저장소 구성 요소
결론

소개
이 기사에서는 Microsoft® SQL Server™ 2000의 새로운 저장소 엔진 기능에 대해 설명하고, 저장소 엔진 작동 방법에 대한 구체적인 설명과 함께 이러한 기능을 사용하는 데 유용한 세부 정보를 제공합니다. 저장소 엔진의 내부 동작에 대한 기본적인 사항을 이해하고 있으면 SQL Server의 성능을 최대한 활용할 수 있습니다.

높은 확장성이 주요 관심사가 되고 있는 상황에서 데이터베이스는 이제 단시일 내에 디자인되어 구현되고 있으며, 개발 요구 사항의 변화와 프로덕션 사용의 증가에 따라 끊임없이 진화하고 있습니다. 확장성, 가용성 및 사용의 용이성에 대한 요구는 민감하고도 유연한 데이터 저장소 엔진을 필요로 합니다.

SQL Server 2000의 여러 버전은 크기로는 포켓 PC용 소형 모바일 시스템에서부터 몇 테라바이트에 달하는 고성능 트랜잭션 프로세싱에 이르기까지 또는 클러스터된 Windows® 2000 Datacenter Server에서 실행되는 의사 결정 지원 시스템 등 아주 다양한 시스템을 지원합니다. 이러한 모든 시스템은 업무 지향적인 비즈니스 시스템이 요구하는 융통성, 보안성 및 안정성을 유지합니다.

저장소 엔진 작업이 지능화되고 자동화됨에 따라 다양한 용도와 규모의 프로젝트에 대해 SQL Server 2000 응용 프로그램을 구축할 수 있으며 정교한 아키텍처를 통해 성능, 가용성 및 확장성이 향상됩니다.

가용성
물리적 파일 상호 작용에 대한 새로운 알고리즘과 함께 안정성 및 동시성이 향상되었습니다. 이러한 알고리즘 덕분에 정기 점검 시 DBCC(database console command)를 실행할 필요가 없어졌습니다. 그러나, DBCC는 여전히 사용할 수 있으며 새로운 DBCC CHECK 명령은 온라인 처리가 진행 중인 과정에서도 실행될 수 있습니다.

확장성
실제 데이터베이스 파일과 디스크의 해당 레이아웃으로 구성된 저장소 하위 시스템은 소규모 데이터베이스에서 대규모 데이터베이스로의 확장을 지원합니다. SQL Server는 이제 최대 64GB의 실제 메모리(RAM)와 최대 32개의 프로세서를 지원할 수 있습니다.

사용의 용이성
데이터베이스 관리자(DBA)는 향상된 관리 기능을 사용하여 서버 관리를 자동화하고 집중화할 수 있습니다. 이렇게 하면 DBA가 모든 사이트를 방문하지 않고도 원격 서버와 응용 프로그램을 손쉽게 관리할 수 있습니다. 정교한 알고리즘으로 관리되는 서버 구성은 서버 사용 패턴에 대해 동적으로 응답하여 DBA가 데이터베이스 관리 및 최적화 작업에만 주력할 수 있도록 합니다.

저장소 엔진 향상
SQL Server 2000의 관계형 데이터베이스 서버는 관계형 엔진과 저장소 엔진의 두 부분으로 나뉘어져 있습니다. 이 두 개의 엔진은 OLE DB 같은 원시 데이터 액세스 구성 요소를 통해 상호 작용하면서 독립적으로 작동합니다. 관계형 엔진은 저장소 엔진에 인터페이스를 제공하는데, 이 저장소 엔진은 원본으로 사용하는 데이터베이스 저장소 구성 요소 및 기능과 상호 작용하는 서비스로 구성됩니다.

저장소 엔진의 주요 임무는 다음과 같습니다.

저장소 구성 요소 관리에 대한 사용의 용이성을 개선하기 위한 기능 제공
실제 파일에 대한 모든 I/O 및 데이터 버퍼 관리
동시성, 트랜잭션 관리, 잠금 및 로깅 제어
데이터를 저장하는 데 사용되는 실제 페이지 및 파일 관리
시스템 오류에서 복구

SQL Server 2000 저장소 엔진은 사소한 용량 계획 및 성능 조정에 대한 필요성을 줄이면서 개념적인 단순성과 실제 융통성을 추가하는 새로운 기능을 제공합니다. SQL Server 2000은 자신의 환경에 반응하며 데이터베이스 사용의 변화에 대해 정확하고 빠르게 동적으로 적응합니다. 이러한 기술적인 발전은 데이터베이스 관리에만 치중하던 것을 데이터를 서비스로 편리하게 이용하는 수준으로 향상시켰습니다. SQL Server 2000 DBA는 개별적인 매개 변수를 조정하는 데 시간을 보내는 대신 데이터 흐름과 사용에 민감한 시스템을 디자인하는 데 집중할 수 있습니다.

SQL Server 2000의 변경 사항은 SQL Server 7.0에 소개된 구조적 향상을 기반으로 하며 계속되는 개선과 혁신의 바탕이 되고 있습니다.저장소 엔진 팀의 주요 목표는 서버를 정기적으로 조정하는 데 드는 시간과 노력을 줄이는 것입니다. 대부분의 조정 매개 변수 설정은 데이터베이스 사용을 기반으로 하므로 엔진은 이제 적응 알고리즘에 따라 데이터베이스 환경의 상황에 맞게 동적으로 조정됩니다. 이 자동화된 융통성은 이전 버전에서 조정과 실험이 끊임없이 요구되었던 튜닝 매개 변수를 위해 구현되었습니다. 조정 기능을 수동으로 조절할 수도 있으나 SQL Server 2000을 사용하는 편이 더 낫습니다. SQL Server 고객 중 극히 일부만 튜닝 매개 변수를 조절할 필요가 있을 것입니다. 이런 유형의 조절은 공인된 데이터베이스 관리자의 감독 하에 주의 깊게 테스트해 본 후에 이루어져야 합니다.

다음은 SQL Server 2000 저장소 엔진에서 향상된 주요 기능을 요약한 테이블입니다. 이러한 기능에 대해서는 이 기사의 뒷부분에서 더 자세하게 다루겠습니다.

기능 설명 및 장점
응용 프로그램 잠금 관리자 양식과 같이 응용 프로그램이 정의된 리소스에 동시에 액세스하는 것을 제어하려면 SQL Server 응용 프로그램 잠금 관리자를 사용하여 새 저장 프로시저로 이러한 리소스를 잠글 수 있습니다.
DBCC(Database console command) 이제 업데이트 작업을 방해하지 않고도 온라인 처리가 수행되는 동안 DBCC CHECK 명령을 실행할 수 있습니다. 새로 향상된 기능을 사용하면 실제 페이지의 일관성을 확인하여 하드웨어로 인한 오류를 검색할 수 있습니다. SQL Server 2000 Enterprise Edition에서 DBCC는 이제 여러 프로세서 간에 병렬로 실행됩니다.
데이터베이스 옵션 모든 데이터베이스 옵션은 이제 ALTER DATABASE를 사용하여 수정할 수 있습니다. 이 기능은 관리 작업을 단순화합니다.
차등 백업 익스텐트 수준에서 데이터베이스 변경 내용을 추적하는 기능이 향상되었으므로 SQL Server 2000에서는 차등 백업이 더욱 빨라졌습니다.
동적 조정 서버는 동적 적응 알고리즘을 사용하여 이전의 정적 구성 설정에 자동으로 적응합니다. 관리 제어는 여전히 전체 시스템 리소스 관리에 사용할 수 있으나 일반적으로 사용할 필요가 없을 것입니다. 수동으로 설정된 매개 변수는 자신의 제한된 범위 내에서 동적으로 적응합니다.
행 텍스트 크기가 작고 자주 사용되는 텍스트 열이 포함된 테이블에서 작은 텍스트 값은 텍스트 값이 있는 페이지가 아닌 표준 데이터 행이 있는 같은 페이지에 저장될 수 있습니다. 자주 액세스되는 이런 텍스트 데이터가 있는 테이블에서 이 기능은 디스크 I/O의 많은 양을 줄일 수 있습니다.
병렬 인덱스 작성 엔터프라이즈 버전에서 인덱스 작성은 자동으로 병렬 처리를 위해 구성된 모든 프로세서를 사용하여 8개 프로세서가 있는 서버에서 6의 인수만큼 인덱스를 작성하는 데 걸리는 시간이 줄어듭니다. 또한 인덱스 작성에는 tempdb와 메모리에서 사용할 수 있는 리소스를 이용합니다.
인덱스 미리 읽기 인덱스 읽기 기능이 향상되어 인덱스 스캔의 성능이 향상되었습니다.
인덱스 재조직 DBCC SHOWCONTIG는 인덱스 조각화에 대한 보다 자세한 정보를 제공하도록 향상되었습니다. 새로운 DBCC 명령인 INDEXDEFRAG는 데이터베이스 서비스를 방해하거나 데이터베이스 일관성이나 복구에 위험을 초래하지 않고 인덱스 페이지를 온라인으로 재조직합니다.
인덱스의 내림차순 키 열 인덱스의 개별 키 열은 오름차순이나 내림차순으로 지정할 수 있습니다.
KILL 명령 이 명령은 이제 완료 진행률을 보고합니다. 이 명령이 롤백 같은 다른 프로세스를 처리 중이면 명령이 얼마나 실행되었는지 볼 수 있습니다. 이 명령은 사용자가 특정 세션과 연결되지 않은 Microsoft Distributed Transaction Coordinator(MS DTC) 트랜잭션을 중지할 수 있도록 기능이 향상되었습니다.
대용량 메모리 지원 Windows 2000 기술은 대용량 메모리를 사용하는 엔터프라이즈 버전 시스템의 성능을 향상시켰습니다. Windows 2000의 AWE 확장을 사용하여 SQL Server 2000은 실제 메모리(RAM)를 64GB까지 지원할 수 있습니다.
잠금 잠금 관리자는 스레드와 메모리 같은 추가 리소스를 통한 교착 상태를 검색하도록 기능이 향상되었습니다. 동시성이 향상되면 교착 상태는 줄어듭니다. 이것은 또한 SQL Server 2000에서의 확장성도 향상시킵니다.
논리 로그 표시 Transact-SQL 명령은 로그에 책갈피를 만들어 책갈피로 표시된 시점의 데이터베이스 상태로 복원할 수 있도록 합니다. 또한 이 기능은 같은 응용 프로그램에 사용된 여러 데이터베이스의 복원을 동기화합니다.
온라인 인덱스 재조직 DBCC SHOWCONTIG는 인덱스 조각화에 대한 보다 자세한 정보를 제공하도록 향상되었습니다. 새로운 DBCC 명령인 INDEXDEFRAG는 데이터베이스 서비스를 방해하거나 데이터베이스 일관성이나 복구에 위험을 초래하지 않고 인덱스 페이지를 온라인으로 재조직합니다.
최적화된 I/O 미리 읽기 SQL Server 2000은 스캔에 관련된 각 파일에 대해 한 번에 다중 직렬 미리 읽기를 실행합니다. 쿼리 최적화 프로그램은 테이블과 인덱스를 스캔할 때 성능 향상을 위해 직렬 미리 읽기 I/O를 사용합니다.
백업 시 암호 백업 미디어와 개별 백업은 암호를 사용하여 보호할 수 있습니다. 이렇게 하면 권한이 없는 사용자가 백업을 복원하거나 데이터베이스에 액세스할 수 없게 됩니다.
복구 모델 복구 모델을 사용하여 데이터베이스의 로깅 수준을 선택할 수 있습니다. 이렇게 하면 더욱 융통성 있게 트랜잭션 로그를 관리할 수 있습니다. 복구 모델을 온라인에서 변경하여 하루 중에 사용하는 여러 가지 데이터베이스를 보완할 수 있습니다.
공유 테이블 스캔 엔터프라이즈 버전에서 테이블의 여러 스캔은 디스크에 대한 실제 I/O를 줄이면서 해당 테이블의 다른 진행 중인 테이블의 장점을 사용할 수 있습니다.
로그 축소 로그를 축소하는 명령은 보다 많은 상황에서 즉시 실행됩니다. 로그를 즉시 축소할 수 없으면 SQL Server에서는 축소 작업을 계속하거나 완료하기 전에 실행되어야 할 작업에 대한 구조적인 피드백을 제공합니다.
스냅샷 백업 타 공급업체의 스냅샷 백업에 대한 지원이 향상되었습니다. 스냅샷 백업은 저장 기술을 활용하여 전체 데이터베이스를 몇 초 안에 백업하거나 복원합니다. 이러한 백업은 이제 기본 트랜잭션 로그 및 차등 백업과 결합하여 OLTP 데이터베이스를 완전하게 보호합니다. 이것은 가용성이 매우 중요한 중규모 또는 대규모 데이터베이스에 특히 유용합니다.
공간 효율적인 빈 테이블 및 인덱스 SQL Server 2000에서는 빈 테이블과 인덱스에 대해 디스크 페이지가 할당되지 않습니다. SQL Server 7.0에서는 빈 테이블과 인덱스에 대해 최대 세 페이지까지 할당되었습니다.
최상위 n 정렬 새로운 이 기능은 최상위 n 값의 검색을 최적화합니다(예: SELECT TOP 5 * FROM tablename).
Xlock SQL Server 2000에는 이 Transact-SQL 잠금 힌트가 새로 제공됩니다. 이것은 단독 트랜잭션 수준 페이지나 테이블 잠금을 명시적으로 호출하는 데 사용할 수 있습니다.

SQL Server 2000에는 보다 효율적으로 데이터와 상호 작용할 수 있고 보다 많은 관리상의 융통성을 허용하는 많은 기능이 추가되었습니다. 다음 절에서는 이러한 향상과 그 사용 방법에 대해 자세히 알아보겠습니다.

데이터와 상호 작용
SQL Server 2000에서는 저장소 엔진이 향상되어 데이터와 상호 작용할 때 더 많은 확장성과 성능을 제공합니다. 이러한 향상된 내용을 이해하면 SQL Server를 보다 효과적으로 사용할 수 있습니다.

데이터 교환은 사용자 인터페이스에서 시작하거나 자동화된 작업에서 시작하거나 상관 없이 쿼리로 시작됩니다. 데이터 요청은 관계형 엔진으로 전달되는데, 이 관계형 엔진은 저장소 엔진과 상호 작용하여 데이터를 구하고 사용자에게 다시 전달합니다. 사용자나 DBA의 입장에서 보면 저장소 엔진과 관계형 엔진의 기능을 구분하기가 어렵습니다.

보다 효과적으로 데이터 읽기
데이터는 일련의 트랜잭션을 통해 서버와 사용자 간을 이동합니다. 응용 프로그램이나 사용자가 작업을 시작하면 데이터베이스는 이것을 쿼리 프로세서로 전달하여 작업을 완료한 다음 최종 결과를 반환합니다. 쿼리 프로세서는 SQL 문을 받아들이고, 해석하고, 실행하여 작업을 수행합니다.

예를 들어, 사용자 세션에서 SELECT 문을 실행하면 다음 단계가 발생합니다.

관계형 엔진은 이 명령문을 실행 계획으로 컴파일하고 최적화합니다. 여기서 실행 계획이란 데이터를 가져오기 위해 필요한 일련의 단계입니다. 그런 다음 관계형 엔진은 실행 계획을 실행합니다. 실행 단계에는 저장소 엔진을 통해 테이블과 인덱스에 액세스하는 것도 포함됩니다.
관계형 엔진은 필요한 데이터를 얻기 위해 저장소 엔진으로 호출하면서 실행 계획을 해석합니다.
관계형 엔진은 저장소 엔진이 최종 결과 집합에 반환한 모든 데이터를 결합한 다음 사용자에게 돌려 보냅니다.
이 과정에서 성능 향상을 위해 두 가지 사항이 개선되었습니다. SQL Server 2000의 경우, 관계형 엔진은 정식 쿼리 서술어를 저장소 엔진으로 릴레이하여 프로세스의 초기에 적용될 수 있도록 합니다. 그 결과 저장소 엔진과 관계형 엔진 사이에는 교환이 훨씬 더 효율적으로 이루어집니다. 이렇게 하면 정식 쿼리에 대한 성능이 크게 향상됩니다.

최상위 n 향상
또 한 가지 개선된 것은 저장소 엔진이 결과 집합에서 최상위 n 레코드의 선택을 처리하는 방법입니다. SQL Server 2000의 경우 새로운 최상위 n 엔진이 다음과 같은 명령문을 수행하는 데 가장 적합한 경로를 분석합니다.

SELECT top 5 * from orders order by date_ordered desc

이 예제에서, 전체 테이블을 검색해야 하는 경우 엔진은 데이터를 분석하여 캐시의 최상위 n 값만 추적합니다. 이렇게 하면 전체 테이블이 정렬되는 대신 최상위 n 값만 정렬되므로 이런 유형의 SELECT 문에 대해 성능이 놀랄만큼 향상됩니다.

공유 스캔
SQL Server 2000 Enterprise Edition의 경우, 두 개 이상의 쿼리는 진행 중인 테이블 스캔을 공유할 수 있으며, 이것은 아주 대용량의 SQL Server 2000 데이터베이스의 성능을 향상시킬 수 있습니다. 예를 들어, 쿼리가 정렬되지 않은 스캔을 사용하여 아주 큰 테이블을 검색하는 경우 페이지는 캐시로 들어가 데이터가 들어 올 공간을 만듭니다. 다른 쿼리가 시작되면 같은 테이블의 두 번째 스캔이 디스크 I/O를 발생시켜 해당 페이지를 다시 검색합니다.테이블 스캔이 자주 발생하는 환경에서 이렇게 하면 양쪽 쿼리가 같은 데이터 페이지를 검색하게 되므로 디스크에 스레싱(thrashing)이 생길 수 있습니다.


프로세스를 최적화하면 이런 유형의 데이터 액세스 패턴에 의해 생긴 디스크 I/O 양을 줄일 수 있습니다. 테이블의 정렬되지 않은 첫 번째 스캔은 디스크의 데이터를 읽습니다. 디스크를 다시 읽는 대신에 같은 테이블의 정렬되지 않은 다음 스캔이 이미 메모리에 있는 정보를 기반으로 작성될 수 있습니다. 그림 1. 참고 같은 테이블에서 여러 스캔을 동시에 하는 동안 이 동기화 프로세스는 성능을 8배까지 증폭시킬 수 있습니다. 이러한 향상된 기능은 전체 테이블 크기가 캐시 크기보다 훨씬 큰 대용량 의사 결정 지원 쿼리에서 더욱 두드러집니다.

공유 스캔이란 사용 가능한 실행 계획이 없는 쿼리를 보조하기 위해 저장소 엔진에 의해 호출되는 기능입니다. 이 기능은 아주 큰 테이블을 자주 읽을 때 지원하기 위해 만들어졌습니다. 쿼리 프로세서가 최적의 실행 계획에 테이블 스캔이 포함되는 것으로 결정하면 이 기능이 호출됩니다. 하지만, 공유 스캔을 강제하는 데 쿼리나 인덱스 조정을 사용할 수는 있으나 제대로 유지 관리된 인덱스가 같거나 더 나은 작업을 수행할 수 있는 상황에서는 테이블 스캔을 강요해도 성능이 나아지지 않습니다.

동시성
많은 사용자가 데이터와 상호 작용하는 동안 트랜잭션 동시성을 유지하기 위해 저장소 엔진은 리소스를 잠궈 행, 페이지, 키, 키 범위, 인덱스, 테이블 및 데이터베이스에 대한 종속 관계를 관리합니다. 이러한 요소가 변경되는 동안 리소스를 잠그면 엔진은 두 명 이상의 사용자가 같은 데이터를 동시에 변경하는 것을 막을 수 있습니다. SQL Server 잠금은 트랜잭션에 필요한 가장 덜 제한적인 잠금을 선택하기 위해 여러 수준의 세분성에 동적으로 적용됩니다.

SQL Server 2000에서는 동시성이 향상되어 교착 상태와 피할 수 있는 리소스 잠금이 더욱 줄었습니다. 예를 들어, 잠금 관리자는 스레드와 메모리 같이 경합 상태일 수 있는 다른 리소스를 인식하도록 향상되었습니다. 이 새로운 기능은 데이터베이스 관리자가 더욱 다양한 디자인이나 하드웨어 제한을 식별하는 데 도움이 됩니다.

잠금 관리자에 대한 새로운 Transact-SQL 인터페이스가 소개되어 프로그래밍 코드 내에서 사용자 지정 잠금 논리를 지원합니다. 비즈니스 논리에 필요한 잠금은 사용자의 Transact-SQL 배치 내에서 sp_getapplock을 호출하여 시작됩니다. 이렇게 하면 사용자는 응용 프로그램이 정의된 잠겨질 리소스(예를 들면, 데이터 행의 잠금 대신 양식 같은 응용 프로그램 리소스의 잠금), 사용할 잠금 모델, 시간 제한 값 및 잠금의 범위가 트랜잭션인지 세션인지 여부 등을 지정할 수 있습니다. 새 응용 프로그램 잠금 관리자로 잠금이 시작되면 잠금은 저장소 엔진으로 시작된 것처럼 SQL Server의 표준 잠금 관리에 참여하므로 호출 트랜잭션이 종료되면 사용자가 응용 프로그램으로 시작한 잠금이 열려진 채 있지 않을까 걱정할 필요가 없습니다.

SQL Server 2000에서 잠금을 취득하는 프로세스는 페이지의 모든 데이터가 커밋되는지 여부를 고려합니다. 예를 들어, pubs 데이터베이스의 테이블처럼 최근에 데이터가 변경되지 않은 테이블에 대해 SELECT 문을 실행하면 활성 트랜잭션이 최근에 테이블을 업데이트하지 않았으므로 이 프로세스에서는 잠금이 생기지 않습니다. 저장소 엔진은 데이터 페이지의 로그 시퀀스 번호를 현재 활성 트랜잭션과 비교하여 이 작업을 수행합니다. 대부분의 데이터가 가장 오래된 활성 트랜잭션보다도 오래된 데이터베이스의 경우, 이렇게 하면 잠금이 크게 줄어들어 성능이 향상됩니다.

트랜잭션 동안 잠금이 데이터를 보호하는 반면, 다른 프로세스인 래치는 실제 페이지에 대한 액세스를 제어합니다. 래치는 아주 경량이면서 단기간의 동기화 개체로 트랜잭션 기간 동안 잠글 필요가 없는 작업을 보호합니다. 엔진이 페이지를 스캔하면 엔진은 페이지를 래치하고 행을 읽어 관계형 엔진에 되돌려준 다음, 다시 페이지의 래치를 풀어 다른 프로세스가 같은 데이터에 접근할 수 있도록 합니다. 지연 래치라고 부르는 프로세스 동안 저장소 엔진은 페이지가 다른 진행 중인 프로세스에서 요구되는 경우에만 래치를 해체하여 데이터 페이지에 대한 액세스를 최적화합니다. 같은 데이터 페이지를 필요로 하는 진행 중인 프로세스가 없으면 해당 페이지에서 전체 작업에 대해 한 개의 래치만 유효한 상태로 남습니다.

시스템의 동시성을 향상시키려면 데이터베이스 시스템의 디자인과 이에 접촉하는 코드 개체에 포커스를 맞춰야 합니다. SQL Server 2000은 여러 테라바이트의 데이터와 사실상 무한한 선형 확장성을 지원하도록 디자인되었습니다. DBA의 역할은 데이터베이스의 라이프 사이클(코드에서 디스크의 데이터 저장에 이르기까지 모든 데이터베이스 구성 요소의 최적화와 디자인 주기)을 관리하여 디자인이 서비스 수준 계약을 지속적으로 만족시키도록 하는 것입니다.

테이블 및 인덱스
또한 실제 데이터 구조를 개선하여 디자인과 유지 관리에 더 많은 융통성이 생겼습니다.

테이블이나 인덱스가 늘어남에 따라 SQL Server에서는 새로운 데이터 페이지를 여덟 개의 집합으로 나누었으며, 이것을 익스텐트라고 부릅니다. 한 데이터 행이 여러 페이지에 걸쳐 있을 수 없으므로 8KB의 데이터만 수용할 수 있습니다. 이 때 관련된 text, ntext 또는 image 열은 다른 페이지에 저장할 수 있습니다. 클러스터된 인덱스가 있는 테이블은 디스크에서 키 순서에 따라 실제로 저장됩니다. 힙이란 클러스터된 인덱스가 없고 저장되지 않는 테이블을 말합니다. 레코드는 삽입된 순서대로 저장됩니다.

SQL Server 2000은 인덱싱된 뷰를 지원하는데, 다른 데이터베이스 제품에서는 보통 구체화된 뷰라고 합니다. 클러스터된 인덱스가 뷰에서 만들어지는 경우, 이 뷰는 더 이상 파생 개체가 아니라 클러스터된 인덱스가 있는 테이블과 같은 구조를 가진 데이터베이스에 저장된 기본 개체가 됩니다. 유지 관리에 드는 비용이 성능 향상에 따른 이점보다 많지 않은 경우라면 인덱싱된 뷰는 미리 계산된 값이나 복합 조인의 결과를 저장하는 데 유용합니다. SQL Server 2000 Enterprise Edition의 경우, 인덱싱된 뷰가 쿼리 계획을 최적화할 수 있는 경우에는 언제든지 쿼리 프로세서에서 자동으로 인덱싱된 뷰가 사용됩니다. 인덱싱된 뷰는 거의 변하지 않지만 대개 복합 조인이나 계산 쿼리의 일부인 데이터의 쿼리 속도를 개선할 수 있습니다.

행 텍스트
행 텍스트를 사용하면 기본 페이지에 작은 텍스트 데이터를 저장할 수 있습니다. 예를 들어, text 열이 있는 테이블이 있으나 보통 텍스트 값이 다른 행이 있는 기본 페이지에 들어갈 만큼 작으면 text 열에 임계값을 설정할 수 있습니다. 임계값은 이보다 작은 데이터는 별도의 텍스트 페이지가 아닌 기본 페이지에 텍스트를 저장하도록 크기를 결정하기 위한 것입니다. 대부분의 데이터가 페이지 크기에 맞고, 데이터의 일부만 텍스트 페이지를 만들어야 할 만큼 큰 경우라면 이런 방식으로 작업하는 것이 훨씬 빠릅니다.

이 새로운 기능을 사용할 시기를 결정하려면 I/O 개선에 비해 각 데이터 페이지에 저장할 수 있는 행 수나 저장소 밀도에 균형을 맞추어야 합니다. 예를 들어 설명을 위한 text 열이 있다고 가정합시다. 테이블의 텍스트 값 중 20%는 크고, 80%는 100바이트 미만입니다. 이런 경우라면 행 텍스트 솔루션에 대한 논리적인 후보로 생각할 수 있지만 행 텍스트는 해당 열의 데이터가 자주 액세스되는 경우에만 사용해야 합니다. 사용자가 이 테이블에 자주 액세스하지만 특별한 연구를 하는 경우를 제외하고는 comments 열을 보지 않는다면 행 텍스트를 사용하는 것은 좋은 해결책이 아닐 수 있습니다. 저장소 밀도는 페이지 당 적은 수의 행이 저장되면 줄어듭니다. 또한 테이블에 더 많은 페이지가 들어 있으면 테이블 스캔의 응답 시간은 늘어납니다. 그러므로, 행 텍스트를 구현하는 가장 적절한 경우는 자주 액세스되는 text 열이 있고, 그 열에 행에 저장할 수 있는 8K보다 작은 값이 여러 개 있는 경우입니다.

새로운 데이터 형식
SQL Server 2000에서는 새로운 데이터 형식 세 가지가 소개됩니다. bigint는 8바이트 정수 형식이며, sql_variant는 다른 데이터 형식의 데이터 값 저장을 허용합니다. 세 번째 데이터 형식인 table은 성능을 최적화하는 데 유용합니다. 테이블 변수는 tempdb를 더욱 효과적으로 사용할 수 있게 하며 임시 테이블보다 빠릅니다. 다른 변수들처럼, table 변수는 자신이 선언된 배치로 제한됩니다. 임시 테이블과 거의 유사한 기능으로 테이블 변수는 임시 테이블이나 커서보다 더 빠르게 수행되며, 서버 리소스를 더욱 잘 활용할 수 있도록 합니다. 일반적으로 데이터베이스와 상호 작용할 코드를 만드는 경우 사용자의 서버에서 사용할 수 있는 리소스를 활용할 수 있는 가장 적합한 방법을 찾아보십시오.

인덱스
데이터 액세스는 인덱스 사용을 통해 최적화할 수 있습니다. 인덱싱 요구 사항은 사용에 따라 달라지므로 잘못된 인덱싱은 데이터베이스의 속도가 늦어지는 가장 일반적인 원인 중 하나가 됩니다. 표준 인덱스 관리에는 현재 인덱싱 스키마를 정기적으로 확인하고 인덱스를 적절하게 빼거나 추가하여 현재의 시스템 사용에 맞게 조절하는 것이 포함되어야 합니다.

SQL Server 2000의 몇 가지 새로운 기능 덕택에 관리에 대한 인덱스 유지 관리를 보다 쉽고 효과적으로 할 수 있게 되었습니다. 이러한 향상으로 인해 디스크 I/O는 줄고 인덱스 스캔의 성능은 향상되었습니다. 이것은 범위 스캔에 사용할 수 있는 보조 인덱스가 있는 경우 특히 유용합니다.

인덱스 작성
인덱스를 작성하는 경우 저장소 엔진은 행을 샘플링하여 인덱스를 작성하는 데 서버 리소스를 활용하는 가장 효율적인 방법을 계산합니다. 옵션을 사용하면 인덱스 작성 방법을 제어할 수 있으므로 시스템 리소스 할당 방법을 제어하도록 선택할 수 있습니다. 이러한 옵션을 사용하면 특정 데이터베이스 시스템에 대한 사용자의 지식에 따라 시스템 성능에 전반적으로 중요한 프로세스에서 리소스의 균형을 맞추어 인덱스 작성이 트랜잭션 프로세스에 최소한의 영향을 미치도록 할 수 있습니다.

리소스 명령 옵션 설명
메모리 sp_configure(고급) index create memory 모든 인덱스 작성에 사용된 메모리 양을 지정합니다.
TempDB create index sort_in_tempdb 인덱스를 작성하는 동안 정렬에 사용되는 디스크 공간이 tempdb에서 할당되도록 합니다. 이렇게 하면 tempdb가 별도의 디스크에 있는 경우 더 많은 I/O 대역폭이 생기며, 인접한 공간에서 데이터베이스가 부족하면 인덱스 페이지가 실질적으로 더욱 인접한 레이아웃이 됩니다.
CPU sp_configure(고급) max degree of parallelism 병렬 작업(서버쪽)에서 사용하는 프로세서(CPU) 수를 제한합니다.

이러한 옵션에 대한 자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오.

대용량 시스템에 대한 다른 확장성 기능은 SQL Server 2000 Enterprise Edition에서 사용할 수 있는 병렬 인덱스 작성입니다. 이 프로세스는 사용자가 단일 CREATE INDEX 문을 실행할 때 자동으로 호출됩니다. 저장소 엔진은 데이터에 대한 요구 사항을 계산한 다음 별도의 스레드를 만들며, 각 스레드는 인덱스의 구역을 작성합니다.



또한 인덱스 작성은 공유 테이블 스캔을 사용하여 프로세스를 세부적으로 최적화합니다.

인덱스 조각 모음 실행
SQL Server 2000에서는 온라인으로 인덱스를 재구성하는 것을 지원하며, 이것은 이전 버전에서 크게 발전한 것입니다. 온라인으로 인덱스를 재구성하면 트랜잭션 처리 속도에 최소한의 영향을 미치며, 작업을 손실하지 않고도 언제든지 중단하고 다시 시작할 수 있습니다. 재구성은 조금씩 증가하며 이루어지고 완전히 복구할 수 있습니다.

테이블에서 정보를 삽입하고 삭제하고 업데이트하는 데 따라 클러스터된 인덱스 페이지와 클러스터되지 않은 인덱스 페이지는 실제적으로 조각화될 수 있고 해당 데이터에 대한 범위 쿼리의 효율성은 줄어듭니다. 그러므로 정기적으로 인덱스의 조각 모음을 실행하는 것이 좋습니다. SQL Server 2000에서 개선된 DBCC SHOWCONTIG를 사용하여 조각화를 분석하고 보고할 수 있습니다. 자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오.

인덱스가 조각화되었다고 판단되면 DBCC INDEXDEFRAG를 사용하여 재구성합니다. 그러면 빈 공간을 압축하고 채우기 비율 설정에 맞도록 설정된 범위 안에서 행을 이동하여 페이지가 논리 키 순서로 재정렬됩니다. 이렇게 하면 페이지를 조밀하게 채워 읽기 성능을 향상시켜 데이터를 스캔하는 동안 더 적은 페이지가 읽혀지게 됩니다. 인덱스를 정기적으로 유지 관리해 왔고 완전히 조각화되지 않은 상태라면 DBCC INDEXDEFRAG를 실행하는 것은 인덱스를 다시 작성하는 것보다 온라인 성능에 훨씬 영향을 덜 미칩니다.

DBCC INDEXDEFRAG는 내부적으로 작은 트랜잭션을 사용하는 실행 시간이 오래 걸리는 많은 온라인 관리 작업 중 하나입니다. 이러한 작은 트랜잭션은 서버 내에서 동시성을 최대화하고, 작업 내용을 손상하지 않고도 작업을 중지할 수 있게 하며, 오류가 발생할 경우 다시 작업하지 않도록 완전히 로깅되어 있습니다.

로깅 및 복구
트랜잭션 로그란 데이터베이스가 만들어진 시점에서 현재 시점까지 데이터베이스의 변경 사항을 기록하는 레코드 스트림을 말합니다. 로깅된 모든 작업은 로그 레코드를 만듭니다. 트랜잭션에 의해 만들어진 로그 레코드는 트랜잭션이 커밋할 때 디스크에 기록됩니다. 반면에 트랜잭션에 의해 수정된 데이터 페이지는 디스크에 즉시 기록되지 않고 SQL Server 버퍼 캐시에 보존되었다가 나중에 디스크에 기록됩니다. 데이터를 디스크에 기록하는 것이 지연되면 데이터 페이지에 대한 다중 액세스 효율을 최대화하며 스캔을 방해하지 않게 됩니다. 커밋 시 디스크에 기록을 수행하면 서버가 다운되어도 커밋된 작업은 손상되지 않도록 할 수 있습니다.

복구 작업은 데이터베이스가 온라인으로 가져오기 이전과 트랜잭션 상으로 일관성 있도록 하는 것입니다. 데이터베이스가 트랜잭션 상으로 일관성을 가진다는 것은 커밋된 모든 작업은 존재하고 커밋되지 않은 작업은 실행 취소된다는 의미입니다. 로그는 항상 데이터베이스의 올바른 뷰를 정의합니다. 간단히 말해서 복구란 데이터를 특정 시점의 트랜잭션 로그와 일치하도록 만드는 프로세스입니다.

복구는 SQL Server가 시작되고, 데이터베이스가 연결되거나 백업의 데이터베이스를 복원하는 마지막 단계로 자동으로 수행됩니다. SQL Server를 시작할 때 SQL Server에 의해 수행되는 복구는 restartstartup 복구라고 합니다. 백업에서 복구하는 것은 일반적으로 디스크 오류로 인한 것입니다. 이런 유형의 복구를 미디어 복구라고 합니다.

다시 시작 복구는 자동으로 수행되며 항상 가장 최근의 상태로 복구합니다. 백업에서 복구하는 경우 DBA는 이전 시점의 상태로 복구하도록 선택할 수 있습니다. 여기에는 제한 사항이 있습니다. 자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오.

시작 복구는 SQL Server의 인스턴스를 시작할 때마다 자동으로 발생하고 인스턴스가 마지막으로 종료될 때 불완전했던 모든 트랜잭션을 롤백하는 것으로 이루어집니다. 백업에서 복구하는 경우 DBA는 이전 시점의 상태로 복구하도록 선택할 수 있습니다. 여기에는 제한 사항이 있습니다. 자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오. 위의 두 경우에 복구는 이 대상 시점에 따라 수행됩니다.

복구는 다음의 두 단계로 이루어집니다.

트랜잭션 로그에서 대상 시점을 찾을 때까지 모든 변경 내용을 다시 실행합니다.
다시 실행이 중지된 시점에 활성화되어 있었던 트랜잭션에 의해 수행된 모든 작업의 실행을 취소합니다.
SQL Server에서는 검사점을 사용하여 다시 시작 복구를 빨리 진행합니다. 검사점은 현재 버퍼 캐시에 있는 모든 수정된 데이터 페이지를 디스크에 기록합니다. 이렇게 하면 복구의 다시 실행 부분에 대한 기준이 만들어집니다. 검사점은 비용이 많이 들 수 있으므로 SQL Server는 자동으로 검사점을 관리하여 다시 시작하는 데 걸리는 시간을 최소화하면서 성능을 최대화합니다.

SQL Server 2000에서 성공적으로 완료한 쓰기 작업은 디스크에 저장되어야 합니다. 쓰기 캐싱 디스크 저장소를 사용하는 경우, 저장소 공급업체에 문의하여 캐시가 내결함성인지 확인합니다. 내결함성이란 캐시가 정전이나 운영자의 동작에 대해 영향을 받지 않는다는 것을 의미합니다. 사용 중인 캐시가 내결함성이 아니라면 사용하지 말아야 합니다.

논리 로그 표시
SQL Server 7.0에서는 모든 지정된 시점으로 복구하는 것이 가능했습니다. 하드웨어에 오류가 있는 경우 복구 프로세스는 아주 간단합니다. 그러나, 데이터베이스에 대한 다른 위험은 잘못된 데이터가 입력되거나 유효한 데이터가 사용자의 동작에 의해 파괴될 가능성이 있다는 것입니다. 이런 경우 사용자는 오류 트랜잭션을 시작할 시기를 결정해야 합니다. SQL Server 7.0의 경우, 이 작업을 하는 유일한 방법은 문제가 다시 일어날 때까지 데이터베이스 복사본으로 로그를 복원하는 것이었습니다. 그런 다음 오류가 발견된 바로 전 시점까지 프로덕션 이미지에 대한 복원을 실행할 수 있었습니다.

SQL Server 2000에서는 트랜잭션을 로그에 표시할 수 있습니다. 나중에 복원할 필요가 생기면 실제 시간이 아닌 실행 시간에 사용된 표시를 참조할 수 있습니다. 이렇게 하려면 명명된 BEGIN TRANSACTION 문과 WITH MARK [description] 절을 사용합니다. 표시는 msdb에 저장됩니다. 복구는 표시가 들어 있는 트랜잭션을 포함하거나 바로 앞에서 중지할 수 있습니다. 예를 들어, 일괄적으로 실행되고 여러 레코드를 변경하는 프로세스가 있으면 이 기능을 사용하여 잘못된 상황에서 프로세스가 실행되면 명령이 실행된 시점으로 데이터를 롤백할 수 있도록 합니다.

표시 이름은 고유하지 않아도 됩니다. 어느 트랜잭션이 필요한지 표시하려면 datetime 값을 지정합니다. 이에 대한 구문은 다음과 같습니다.

RESTORE LOG WITH [ STOPBEFOREMARK|STOPAFTERMARK ] = @TaggedTransaction AFTER @datetime

또한 분산 트랜잭션에서도 분산 표시라고 알려진 표시를 사용하여 여러 관련 데이터베이스를 트랜잭션상 일관성 있는 상태로 복구하도록 지원할 수 있습니다. 이러한 관련 데이터베이스는 SQL Server의 같거나 다른 인스턴스에 상주할 수 있습니다. 정기적으로 데이터베이스 집합에 걸쳐 분산 표시를 설정할 수 있습니다(예를 들어, 5분에 한 번씩). 데이터베이스 중 하나의 트랜잭션 로그가 손상되면 데이터베이스 집합을 이전 시점으로 복구해야 합니다. 분산 표시는 이 시점을 제공합니다. 분산 표시를 사용하면 여러 관련 데이터베이스에 대한 백업의 정확한 타이밍을 조절하려고 걱정할 필요가 없습니다. 자세한 내용은 SQL Server 2000 온라인 설명서의 "명명된 트랜잭션으로 복구"를 참조하십시오.

트랜잭션 로그 축소
SQL Server 7.0에서는 로그 축소 작업을 즉각적으로 실행할 수 없었습니다. 이 작업은 트랜잭션 로그가 다음에 백업되거나 잘릴 때까지 지연되었습니다. 이런 특성은 많은 SQL Server 7.0 사용자들을 혼란스럽게 했습니다. SQL Server 2000에서는 로그를 최대한 축소한 다음 로그 백업 후의 추가 축소가 가능한지 표시합니다. 이런 경우에는 로그 백업을 완료한 다음 축소 명령을 다시 실행합니다. 자세한 내용은 SQL Server 2000 온라인 설명서의 "트랜잭션 로그 축소"를 참조하십시오.

로그 크기는 현재 복구 모델과 응용 프로그램 디자인에 따라 달라집니다. 로그를 정기적으로 축소해야 하는 경우에는 원인에 대한 증상 이외에 다른 문제가 있는지 살펴보십시오. 축소 명령을 사용하여 정기적으로 로그를 유지 관리하는 데 주력하는 대신 로그를 채우는 원인이 무엇인지 살펴보아야 합니다.

복구 모델
SQL Server 2000에는 데이터 보호 계획을 활용하도록 복구 모델이 추가되었습니다. 이러한 복구 모델은 성능, 로그 공간 요구 사항 및 미디어(디스크) 오류에 대한 보호 사이의 보완 관계를 명확하게 합니다. 복구 모델에는 단순 복구, 전체 복구 및 대량 로그 등 세 가지가 있습니다.

복구 모델의 선택은 데이터베이스 사용과 가용성 요구 사항을 기준으로 하며, 적절한 백업과 복원 프로시저를 결정하는 데 도움이 됩니다. 복구 모델은 백업으로부터의 복구를 의미하는 미디어 복구에만 적용됩니다. 다시 시작 복구는 커밋된 모든 작업을 복구합니다. 자세한 내용은 SQL Server 2000 온라인 설명서의 "복구 모델 선택"을 참조하십시오.

사용자는 복구 모델 간에 손쉽게 전환할 수 있습니다. 예를 들어, 대용량 데이터베이스의 경우 전체 또는 대량 로그를 사용하거나 둘 다 사용할 수 있습니다. 대량 삽입과 인덱스 다시 작성으로 이루어진 데이터 로드를 처리하는 동안 낮에는 전체를 사용하고 밤에는 대량 로그를 사용할 수 있습니다. 또한 데이터 로드를 실행하고 전체 모드로 전환하는 동안 대량 로그로 전환하고, 트랜잭션 로그 백업을 실행할 수 있으며, 전체 데이터베이스 백업을 실행하지 않고도 해당 시점으로 복원할 수 있습니다. 이 기능을 사용하면 대량 처리를 보다 효과적으로 처리할 수 있습니다. 필요한 작업은 나중에 트랜잭션 로그 백업을 만드는 것뿐입니다.

복구 모델을 변경하려면 다음 구문을 사용합니다.

ALTER DATABASE SET RECOVERY RecoveryModel

자세한 내용은 SQL Server 2000 온라인 설명서의 "복구 모델 전환"을 참조하십시오.

단순 복구 모델
단순 복구 모델은 일반적으로 로그 공간을 더 적게 필요로 하지만 데이터나 로그 파일이 손상되는 경우 상당한 규모의 잠재적인 작업 손실을 초래합니다. 기본 복구에 필요한 이벤트만 기록됩니다. 단순 복구 모델을 사용하면 전체 데이터베이스와 차등 데이터베이스 백업만 사용할 수 있습니다. 오류가 발생하는 경우 마지막 백업 이후에 커밋된 모든 작업을 다시 실행해야 합니다. 이 모델은 관리하기는 가장 간단하지만 커밋된 작업의 손실을 허용하지 않는 중요한 업무를 다루는 응용 프로그램에는 적절하지 않습니다.

이 모델은 SQL Server 7.0 및 이전 버전에서의 검사점에서 로그 자름 옵션과 유사합니다.

전체 복구 모델
전체 복구 모델에서는 모든 것이 기록됩니다. 전체 복구 모델은 손상된 데이터 파일로부터의 작업 손실을 완전히 보호합니다. 트랜잭션 로그가 손상되면 가장 최근의 로그 백업 이후에 커밋된 작업이 손실되어 수동으로 다시 실행해야 합니다.

전체 복구 모델을 사용하는 경우라도 트랜잭션 로그에 대해 내결함성 디스크를 사용하여 데이터 손실을 막는 것이 중요합니다. 또한 전체 복구 모델을 사용하면 모든 특정 시점으로 복구할 수 있습니다.

대량 로그 복구 모델
대량 로그 복구 모델은 대량 작업 시 최상의 성능을 제공합니다. 또한 이러한 작업은 전체 복구 모델로 작업할 때보다 로그 공간을 덜 사용합니다. 예를 들어, 새 페이지를 할당하는 것은 기록되지만 페이지에 데이터를 삽입하는 것은 기록되지 않습니다. SQL Server 2000에서 대량 작업은 대량 로드(BCP 및 BULK INSERT, DTS 패키지 내에서 실행되는 경우 포함), SELECT INTO, CREATE INDEX, WRITETEXT, UPDATETEXT 등으로 이루어집니다.

전체 복구 모델과 비교할 때 대량 로그 복구 모델은 대량 작업에 대한 로깅을 최소화합니다. 로그가 손상되거나 가장 최근의 로그 백업 이후에 대량 작업이 발생하고, 마지막 로그 백업이 손실된 이후 데이터베이스를 변경하면 복구가 필요하다는 점을 기억하십시오.

이 모델은 특정 시점으로의 복구를 지원하지 않지만 대량 변경을 포함하는 트랜잭션 로그 백업의 끝으로 복구하는 것을 허용합니다. 대량 로그 복구 모델을 사용하여 만든 트랜잭션 로그 백업에는 대량 작업으로 수정된 익스텐트가 포함됩니다. 이 기능은 로그 전달에 대한 지원을 개선하는데, 이것은 사용자가 더 이상 대량 작업이 사용자의 백업을 무효화할 것이라고 걱정할 필요가 없기 때문입니다. SQL Server는 비트맵을 유지 관리하여 수정된 데이터 익스텐트를 추적는데, 이 데이터 익스텐트는 SQL Server가 변경 내용을 식별하는 프로세스를 최적화합니다.

개선된 백업 기능
일반적인 데이터 보호를 단순화하는 복구 모델의 도입 외에도 SQL Server 2000에서는 스냅샷 기술, 차등 백업 및 보안 같은 관리 효율성이 개선되었습니다.

트랜잭션 로그 백업 체인은 끊어지지 않습니다. SQL Server 7.0의 경우, 데이터베이스에 파일을 추가하는 것과 같은 특정 작업에서는 로그 체인이 끊어지므로 후속적인 전체 데이터베이스 백업이 요구되었습니다.
백업 작업은 응용 프로그램이나 다른 관리 작업과는 충돌하지 않습니다. 예를 들어, 백업은 인덱스 만들기와 대량 로드 같은 대량 작업과 동시에 발생할 수 있습니다.
로그 백업과 파일 백업은 동시에 발생할 수 있습니다.
SQL Server 2000에서는 시스템 동작에 상관 없이 무인 백업 작업도 지원됩니다.

SQL Server는 독립 하드웨어 및 소프트웨어 공급업체와 연계하여 스냅샷 백업과 복원 기술을 지원합니다. 스냅샷 백업은 백업을 수행하는 데 서버 리소스의 사용을 최소화하거나 배제합니다. 이것은 가용성이 매우 중요한 중간 크기에서 아주 큰 데이터베이스에 특히 유용합니다. 이 기술의 장점은 다음과 같습니다.

백업은 서버에 거의 아무런 영향을 미치지 않으면서 초단위로 측정되는 아주 짧은 시간에 만들어집니다.
데이터베이스를 빠르게 복원하는 데는 디스크 백업을 사용할 수 있습니다.
다른 호스트가 프로덕션 시스템에는 아무 영향을 주지 않고 백업을 만들 수 있습니다.
보고나 검사를 위해 프로덕션 데이터베이스의 복사본을 즉시 만들 수 있습니다.
스냅샷 백업과 복원은 이런 용도로 디자인된 SQL Server 2000의 기능을 사용하는 타사 하드웨어 및/또는 소프트웨어 공급업체와 공동으로 이루어집니다. 백업 기술을 사용하면 보통 미러링된 디스크 집합을 분할하여 백업되고 있는 데이터의 사본을 즉시 만들 수 있습니다. 복원 시에는 원본을 바로 사용할 수 있습니다. 원본으로 사용하는 디스크는 백그라운드에서 동기화되므로 거의 동시에 복원됩니다.

차등 데이터베이스 백업은 마지막 전체 백업 이후에 변경된 데이터 양과 비례하는 시간에 완성됩니다. 변경된 데이터가 적을수록 백업이 더 빨라집니다. SQL Server 2000에서는 비트맵을 사용하여 가장 최근의 데이터베이스 백업이나 파일 백업 이후에 수정된 데이터 익스텐트를 추적하여 이들의 위치를 효율적으로 찾을 수 있도록 합니다. 또한, SQL Server 2000에서는 파일 차등 백업이 지원됩니다.

백업은 복구 시와 같은 방법으로 작동하면서 가장 최근의 전체 백업 이후에 데이터베이스에서 수정된 변경 내용을 모아둡니다. 백업이 훨씬 빠르기는 하나 변경된 소량의 정보(특히 소량의 데이터만 변경된 대용량 데이터베이스의 경우)만을 기록합니다.

보안을 강화하기 위해 백업 미디어 및 백업 집합에 대해 암호 보호를 구현할 수 있습니다. 이렇게 하면 권한이 없는 사용자가 백업에 내용을 추가하거나 데이터베이스로 복원하는 것을 방지할 수 있습니다.

관리 개선
SQL Server 2000에서는 저장소 엔진의 몇 가지 관리 기능이 향상되었습니다.

데이터베이스 확인
DBCC는 데이터베이스 일관성 확인을 위한 CHECK 명령을 포함하여 다양한 관리 기능을 제공합니다.

SQL Server 7.0 및 SQL Server 2000을 사용해 본 결과 데이터베이스 불일치는 정상 작업 중에 데이터베이스 엔진이나 응용 프로그램에 의해 검색될 수도 있고 검색되지 않을 수도 있는 하드웨어 문제로 인해 생깁니다. 이것은 특히 자주 액세스되지 않는 데이터의 경우에 해당됩니다. 이러한 필요성에 부응하여 SQL Server 2000에서는 Physical_Only라는 검사 모드가 소개되는데, 이것은 대부분의 하드웨어로 인한 문제를 검색하기 위한 것입니다. 이것은 거의 디스크 스캔 속도 정도로 아주 빠르고 리소스를 많이 사용하지 않습니다.

SQL Server 7.0과 함께 시작된 SQL Server 저장소 엔진에서는 기본적인 구조적 개선으로 인해 기본 유지 관리 작업의 일부로 데이터베이스 확인을 실행할 필요가 없습니다. 그러나, Microsoft는 업무 지향 데이터 관리의 중요한 부분으로서 데이터베이스 확인 도구에 최선을 다하고 있습니다. Microsoft에서 추천하는 사항은 다음과 같습니다.

원본으로 사용하는 하드웨어(특히 디스크 하위 시스템)의 신뢰도에 따라 때때로 Physical_Only 검사를 실행합니다.
하드웨어나 소프트웨어 업그레이드 시 또는 원인에 상관 없이 문제가 있다고 느껴지는 경우 같은 중요한 시기에는 전체 데이터베이스 검사를 실행합니다.
Microsoft는 정기 점검의 일환으로 전체 검사를 실행하도록 권장하지는 않습니다.

또한 SQL Server 2000에는 데이터베이스 확인에 대한 중요한 기능 향상이 포함되어 있습니다.

기본적으로 검사는 완전히 온라인 상태에서 이루어집니다. 온라인 검사는 트랜잭션 작업 부하에 별로 영향을 미치지 않습니다. 이 영향은 시스템 로드, 하드웨어 구성 및 tempdb의 속도에 따라 달라집니다. Microsoft는 이 영향을 중간 OLTP 작업 부하(50% CPU)로 15에서 20%에서 측정했습니다. TABLOCK 옵션은 공유 테이블 잠금을 허용하도록 검사를 강제하기 위해 제공됩니다. 이 공유 테이블 잠금을 사용하면 검사를 더 빨리 실행할 수 있지만 업데이트는 할 수 없게 됩니다.
검사는 SQL Server의 인스턴스에 대해 사용자가 설정한 병렬 처리의 최대 한도로 제한된 대칭적 다중 프로세싱(SMP) 컴퓨터에서 병렬로 실행됩니다.
SQL Server 2000의 검사 명령은 SQL Server 7.0에서 소개된 복구 기능을 계속적으로 지원합니다. 어떤 경우에는 오프라인 복구가 백업에서 복원하는 것에 대한 대안을 제공하기도 합니다.

데이터베이스 상태 제어
SQL Server 2000에서는 Transact-SQL을 통해 데이터베이스 상태를 더 많이 제어할 수 있도록 하는 ALTER DATABASE 문에 대한 기능이 향상되었습니다. 이제 ALTER DATABASE 명령의 강력한 제어 기능을 통해 모든 데이터베이스 옵션을 수정할 수 있습니다. sp_dboption과 databaseproperty()는 향후 릴리스에서는 더 이상 업데이트되지 않을 것입니다. Transact-SQL 명령 sp_helpdb와 DatabasePropertyEx()는 사용자의 데이터베이스 상태에 대한 정보를 제공합니다.

다음은 데이터베이스 상태 옵션을 나열하는 테이블입니다.

옵션 종류 사용 가능한 설정
사용자 액세스 SINGLE_USER
RESTRICTED_USER
MULTI_USER
가용성 온라인
오프라인
업데이트 가능성 READ_ONLY
READ_WRITE

또한 SQL Server는 데이터베이스 내의 상태에 맞춰 복원, 복구, 주의 대상 같은 상태를 설정합니다. 데이터베이스 옵션은 ALTER DATABASE 문의 SET 절, sp_dboption 시스템 저장 프로시저 또는 경우에 따라 SQL Server 엔터프라이즈 관리자 등을 사용하여 설정할 수 있습니다.

데이터베이스 상태가 변경되면, 새로운 상태와 일치하지 않는 세션이 종료될 수 있고 해당 트랜잭션이 롤백되는 동안 데이터베이스 상태를 변경하는 세션이 연결된 채로 남아 있습니다. 세션 종료 옵션에 포함되는 것은 다음과 같습니다.

즉시 종료
지정된 시간 경과 후 종료
진행 중인 프로세스가 정상적으로 완료하도록 허용
작업을 검사하여 활성 사용자 세션이 발견되면 상태 변경 무시
다음은 구문의 두 가지 예제입니다.

alter database accting set read_only with rollback immediate alter database accting set single_user with rollback after 60 seconds

자세한 내용은 SQL Server 2000 온라인 설명서의 "데이터베이스 옵션 설정"을 참조하십시오.

시스템 프로세스 ID 및 작업 단위
프로세스를 중지하려고 할 때 도움이 되는 추가 관리 향상 한 가지는 KILL 명령입니다. KILL 명령의 기능이 향상되어 상태 피드백을 제공합니다. 현재 수행 중인 KILL 명령의 상태를 확인하려면 다음을 실행하십시오.

KILL SPID WITH STATUSONLY

다른 KILL 명령에 의해 중지되고 있는 시스템 프로세스 ID(SPID)를 중지하려고 하면 시스템에서는 같은 상태 정보를 반환합니다.

SQL Server 2000에서 MS DTC 트랜잭션은 관련된 연결이나 SPID 없이 존재할 수 있습니다. 그러므로, 연결은 완료할 작업 단위나 트랜잭션을 기다리는 동안 다른 프로세스에 사용할 수 있습니다. MS DTC 트랜잭션 관리자가 작업을 완료했다는 메시지를 보내면 사용자는 트랜잭션을 커밋하거나 롤백할 수 있습니다. 이것을 작업 단위(UOW)라고 하며, 트랜잭션을 위해 MS DTC가 사용하는 트랜잭션 식별자입니다. UOW에는 SPID가 없습니다.

자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오.

동적 조정
SQL Server 2000에서 사용 기반 성능 조정은 동적으로 관리되며, 수동 조절을 필요로 하거나 권장하지 않습니다. 정적 매개 변수는 제거되었으나 특정 리소스를 위해 관리 제어는 유지됩니다(예: SQL Server가 사용할 수 있는 메모리 용량에 대한 상위 제한 설정). 이 방법은 평균과 예상값을 기초로 수동으로 계산된 시스템보다 훨씬 정확하고 응답이 빠릅니다. 이 방법을 사용하면 데이터베이스 관리의 디자인 측면에만 집중할 수 있습니다. 일반적인 데이터베이스 시스템에서는 많은 양의 수동 관리와 조정이 필요합니다. 예를 들어, 사용에 맞춰 시스템을 조정하려면 DBA는 시스템에 최적의 이점을 제공한다고 생각되는 정적 설정을 선택하기 위해 오랫 동안 많은 양의 통계를 기록하면서 시스템을 모니터링해야 합니다. 그런 다음 DBA는 시스템을 다시 평가하여 새로운 설정은 어떤 효과를 갖는지 판단하고, 조정 프로세스를 다시 시작합니다.

SQL Server 2000에서는 저장소 엔진에 동적 알고리즘이 도입되었는데, 이것은 서버 사용을 활발하게 모니터링하고 내부적으로 설정을 조절합니다. SQL Server 2000의 동적 피드백과 분석은 설정을 절대 최적 값의 10% 이내로 유지하여(그림 3 참고), 시스템이 보다 잘 조정되고 적응력이 뛰어나도록 합니다.



데이터 저장소 구성 요소
SQL Server 2000은 Windows 2000 운영 체제와 함께 사용할 수 있는 모든 CPU를 통한 처리의 균형을 유지합니다. SQL Server의 전용 인스턴스를 실행하고 다른 응용 프로그램이 같은 리소스에 로드를 만들지 않는 경우, 모든 프로세서를 충분히 사용할 수 있도록 프로세서 관련 설정을 기본값으로 합니다. SQL Server는 쿼리, 인덱스 작성, DBCC 및 기타 작업에 대해 여러 프로세서에 걸친 병렬 처리의 장점을 활용할 수 있습니다.병렬 처리에 대한 자세한 내용은 SQL Server 2000 온라인 설명서의 "병렬도"를 참조하십시오.

SQL Server 2000 Standard Edition은 최대 4개의 프로세서와 2GB의 실제 메모리(RAM)를 지원할 수 있습니다. 엔터프라이즈 버전은 최대 32개의 프로세서와 64GB의 실제 메모리(RAM)를 지원하도록 확장할 수 있습니다.

SQL Server 인스턴스에 대한 메모리의 주요 소스를 메모리 풀이라고 합니다. SQL Server의 인스턴스에서 메모리를 사용하는 거의 모든 데이터 구조는 메모리 풀로부터 할당됩니다. 메모리 풀에서 할당된 개체의 예로는 최근에 읽은 데이터 페이지가 저장되는 버퍼 캐시와 최근의 실행 계획이 보관되는 프로시저 캐시 등이 있습니다.

메모리 풀 안에서의 할당은 매우 동적입니다. 성능을 최적화하기 위해 SQL Server는 여러 영역에 할당된 메모리 풀의 양을 지속적으로 조절합니다. 예를 들어, 저장된 실행 계획의 수가 적으면 데이터 캐시에서 사용할 수 있는 메모리의 양을 늘려 리소스 사용을 최적화도록 메모리 풀이 조절됩니다.

SQL Server 2000은 메모리를 사용하여 가능한 한 디스크 I/O를 최소화하도록 디자인되었습니다. 이렇게 하기 위해 SQL Server는 버퍼 캐시를 사용하여 최근에 참조된 데이터를 실제 메모리(RAM)에 보관하며, 여기에 있는 데이터를 다시 사용합니다. 디스크 I/O를 줄이고 데이터베이스 시스템 속도를 높이는 한 가지 잠재적인 방법은 SQL Server에 사용할 수 있는 실제 메모리(RAM)를 추가하는 것입니다.

일반적으로 메모리 설정은 조정할 필요가 없습니다. 그러나 상황에 따라 메모리 설정을 제어해야 할 수도 있습니다. 예를 들어, 같은 서버에 SQL Server의 여러 인스턴스를 실행하는 경우(특히 장애 조치 클러스터링을 사용하는 경우) 메모리에 특별한 주의를 기울일 필요가 있습니다. 또한 같은 서버에서 SQL Server 이외에 여러 응용 프로그램을 실행하는 경우에는 메모리 사용을 모니터링해야 합니다.



SQL Server 2000은 Windows 2000의 새로운 기능을 사용하여 3GB이상의 실제 메모리(RAM)를 주소 지정할 수 있습니다. 그림 4. 참고 SQL Server 2000 Enterprise Edition은 Windows 2000 Advanced Server 또는 Windows 2000 Datacenter Server가 허용하는 만큼의 메모리를 사용할 수 있습니다.

SQL Server 2000의 대용량 메모리 지원에 대한 자세한 내용은, SQL Server 2000 온라인 설명서의 "AWE 메모리 관리"를 참조하십시오.

파일, 파일 그룹 및 디스크
SQL Server는 데이터와 로그를 디스크 파일에 저장합니다. 기본 설치 시 데이터와 로그 파일은 기본적으로 서버 구성에 지정된 기본 위치에 만들어집니다. 그러나 성능과 관리 효율성을 높이기 위해 다음과 같은 몇 가지 기본 원칙을 적용할 수 있습니다.

가능한 한 여러 디스크, 채널 및 컨트롤러에 데이터를 분산합니다.
일반적으로, 개별 크기에 상관 없이 더 많은 디스크(스핀들)를 가질수록 그리고 컨트롤러와 채널에 더 빨리 액세스할수록 저장소 엔진은 더 빨리 데이터를 읽고 쓸 수 있습니다. 시스템 사용이 많아질수록 실제 드라이브의 다른 집합에 데이터 파일을 저장하여 로그 파일과 데이터 파일을 분리하는 것이 더욱 중요해집니다. 또한 tempdb의 사용이 변경되었으므로 이제는 tempdb를 대용량 디스크 집합(예를 들면, 데이터 파일과 함께 또는 디스크 집합에) 저장해야 합니다.
파일 그룹을 사용하여 사용자의 엔터프라이즈 데이터베이스를 보다 관리하기 쉽도록 합니다.
모든 데이터베이스는 하나의 기본 파일 그룹으로 시작합니다. SQL Server 2000은 추가 파일 그룹 없이도 효과적으로 작업할 수 있으므로 많은 시스템은 사용자 지정 파일 그룹을 추가할 필요가 없습니다. 그러나, 시스템이 확장함에 따라 파일 그룹을 추가로 사용하면 자격 있는 DBA가 수행하고 유지 관리하는 경우 보다 나은 관리 효율성이 제공됩니다.
SQL Server 2000에서 데이터베이스 안의 특정 파일 그룹을 읽기 전용으로 설정하면 해당 파일 그룹의 데이터는 변경할 수 없으나 사용 권한 같은 카탈로그 정보는 여전히 관리할 수 있습니다.

참고 SQL Server 2000에서 비동기 I/O의 수는 이제 데이터베이스 엔진 안에서 동적으로 관리되며, SQL Server 7.0의 경우처럼 사용한 파일 그룹이나 파일 수에는 영향을 받지 않습니다.

데이터베이스 디자인을 구현하거나 최적화하는 경우, 데이터베이스 관리자(데이터베이스 시스템 엔지니어)는 데이터베이스 저장소 구성 요소의 구성, 특히 실제 및 논리 디스크의 레이아웃과 여러 디스크에서의 데이터베이스 파일 배열 등을 고려해야 합니다.

결론
DBA의 입장에서는 성능에 대한 제어와 융통성이 증가하여 데이터베이스 시스템 관리에 대한 통합된 접근 방법으로 자신의 데이터베이스 기술과 경험을 데이터베이스 코드, 디자인 및 저장소 구성 요소를 관리하는 데에만 집중할 수 있게 되었습니다. SQL Server 2000 데이터베이스 엔진은 다양한 데이터베이스 구현에 대해 일반적인 확장성과 융통성을 제공합니다.

추천 자료
전문가와 전문적인 프로세스로 데이터베이스 시스템을 성공적으로 운영하는 데 대한 자세한 내용은 Microsoft Certified Database Administrator 프로그램 과 Microsoft Operations Framework 를 읽어보십시오.

SQL Server 2000 온라인 설명서는 SQL Server 2000에 들어 있습니다. 이 설명서는 http://www.microsoft.com/korea/sql/tech ··· EC%84%9C 온라인으로도 볼 수 있습니다.

하드웨어 및 SQL Server에 대한 자세한 내용은 SQL Server 2000 온라인 설명서의 대용량 서버에서 SQL Server 2000을 참조하십시오.

SQL Server 아키텍처 및 저장소 엔진에 대한 자세한 내용은 SQL Server 2000 온라인 설명서와 Kalen Delaney가 지은 Microsoft SQL Server 2000 내부 를 참조하십시오.

용량 계획 기술에 대한 자세한 내용은 Microsoft SQL Server 2000 관리자 안내서를 참조하십시오.

데이터베이스 관리자용 SQL Server에 대한 자세한 내용은 Microsoft SQL Server 웹 사이트와 MSDN 및 Microsoft TechNet에 있습니다.

개발자용 SQL Server에 대한 기술 정보는 MSDN SQL Server 홈 페이지에 있습니다.

[이 자료는 MSDN 라이브러리에서 가져왔습니다]

차주언 | SQL 컨설턴트

인덱스

번호 수칙 체크
1 적절한 인덱스가 걸려있는가? (I/O 가 많은 경우 실행 계획 재검사)
2 인덱스 튜닝마법사로 점검했는가?
3 상황 발생시 인덱스 채우기 비율을 조정하는가?

수칙1.적절한 인덱스가 걸려 있는가?

적절한 인덱스가 걸려있는지 인덱스 튜닝마법사로 확인할 수 있습니다. 또는 CTRL + K로 실행계획을 관찰 해도 됩니다.

인덱스를 만들어야 하는 장소
가. 참조키
나. 참조키가 아니더라도 join에 빈번히 사용되는 경우
다. select절에 자주 사용되는 칼럼
라. where,group by,order by절에 자주 사용되는 곳

수칙2.인덱스 튜닝마법사로 점검했는가?

인덱스란 책의 목차나 책 뒤쪽의 찾아보기와 매우 유사합니다. 예를 들면 40 메가의 데이터중 필요한 내용을 찾고자 한다면 40 메가를 모두 검색해야하지만 인덱스를 만들어 둔다면 인덱스만 읽음으로써 보다 적은 리소스에 사용만으로, 필요한 내용 검색을 끝낼 수 있습니다.

[따라하기 적절한 인덱스 자동 만들기]
1.다음과 같은 테이블을 예제 테이블을 pubs 데이터베이스에 만들어서 가상 Data 10 만개를 입력해 봅시다.




2.10 만개의 데이터가 입력됐으면 select 쿼리로 실험을 시작하도록 합니다. 먼저 총 I/O 가 얼마가 일어나는지 또 실행 계획은 무엇인지 알아보겠습니다. 단축키 CTRL + K 를 클릭하고 다음의 설정을 한후 쿼리를 실행합니다.




3. 데이터 한 개를 가져오기위해 테이블 전체를 검색하고 Data 페이지 6250 페이지를 읽었군요. 6250 페이지는 6250 * 8 다시말해 약 50 메가를 검색하고 있습니다.

4. 여기에서 인덱스를 만들어 보겠습니다.

create index idx on tb_test(id)

5.다시 쿼리를 실행하면 I/O 는 몇페이지가 나옵니까?

select * from tb_test where id = 1

6.인덱스를 제거하고 이번에는 자동 인덱스 추천을 한번 해보겠습니다.

drop index tb_test.idx

7.select 쿼리를 드래그 하여 선택 후 쿼리 메뉴에서 인덱스튜닝마법사(CTRL+I)를 선택합니다.



08.SQL쿼리분석기 선택 체크를 선택합니다.



09. 튜닝할 테이블만을 선택합니다.




10. 다음과 같은 인덱스가 권장 되었습니다.




11. 다음과 같이 변경 내용 적용과 함께 인덱스를 만드는데 사용한 소스도 저장합니다.




12. 마침을 클릭합니다. 인덱스도 훌륭히 적용된 것을 알 수 있습니다. 생성된 쿼리도 열어서 확인합니다.





13. 인덱스 결과도 확인합니다. 응용으로는 한번에 많은 쿼리를 선택한 후 인덱스 튜닝 마법사를 실행 할 수 있다는 것입니다.



[관련링크]
http://support.microsoft.com/default.as ··· 3B271509

위의 파일에 오류가 있는데 수정한 것은 다음과 같습니다.

use master go if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss80') and sysstat & 0xf = 4) drop procedure dbo.sp_blocker_pss80 GO create proc sp_blocker_pss80 (@fast int = 0) as set nocount on declare @spid varchar(6), @uid varchar(6), @blocked varchar(6) declare @tmpchar varchar(255) declare @time datetime select @time = getdate() declare @probclients table(spid smallint, blocked smallint, waittype binary(2), primary key (blocked,spid)) insert @probclients select spid, blocked, waittype from sysprocesses where blocked!=0 or waittype != 0x0000 if exists (select spid from @probclients) begin select @tmpchar='Start time: ' + convert(varchar(26), @time, 113) print @tmpchar insert @probclients select blocked, 0, 0x0000 from @probclients where blocked not in (select spid from @probclients) and blocked != 0 print ' ' if (@fast = 1) begin select spid, status, blocked, open_tran, waitresource, waittype, waittime, cmd, lastwaittype, cpu, physical_io, memusage,last_batch=convert(varchar(26), last_batch,113), login_time=convert(varchar(26), login_time,113), net_address, net_library,dbid, ecid, kpid, hostname,hostprocess, loginame,program_name, nt_domain, nt_username, uid, sid from master..sysprocesses where blocked!=0 or waittype != 0x0000 or spid in (select blocked from @probclients where blocked != 0) or spid in (select spid from @probclients where blocked != 0) select spid = convert (smallint, req_spid), ecid = convert (smallint, req_ecid), rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, Type = case rsc_type when 1 then 'NUL' when 2 then 'DB' when 3 then 'FIL' when 4 then 'IDX' when 5 then 'TAB' when 6 then 'PAG' when 7 then 'KEY' when 8 then 'EXT' when 9 then 'RID' when 10 then 'APP' end, Resource = substring (rsc_text, 1, 16), Mode = case req_mode + 1 when 1 then NULL when 2 then 'Sch-S' when 3 then 'Sch-M' when 4 then 'S' when 5 then 'U' when 6 then 'X' when 7 then 'IS' when 8 then 'IU' when 9 then 'IX' when 10 then 'SIU' when 11 then 'SIX' when 12 then 'UIX' when 13 then 'BU' when 14 then 'RangeS-S' when 15 then 'RangeS-U' when 16 then 'RangeIn-Null' when 17 then 'RangeIn-S' when 18 then 'RangeIn-U' when 19 then 'RangeIn-X' when 20 then 'RangeX-S' when 21 then 'RangeX-U' when 22 then 'RangeX-X'end, Status = case req_status when 1 then 'GRANT' when 2 then 'CNVT' when 3 then 'WAIT' end ,req_transactionID As TransID, req_transactionUOW As TransUOW from master.dbo.syslockinfo s, @probclients p where p.spid = s.req_spid end -- fast set else begin -- Fast not set select spid, status, blocked, open_tran, waitresource, waittype, waittime, cmd, lastwaittype, cpu, physical_io, memusage,last_batch=convert(varchar(26), last_batch,113), login_time=convert(varchar(26), login_time,113), net_address, net_library,dbid, ecid, kpid, hostname,hostprocess, loginame,program_name, nt_domain, nt_username, uid, sid from master..sysprocesses print '' print 'SPIDs at the head of blocking chains' select spid from @probclients where blocked = 0 and spid in (select blocked from @probclients where spid != 0) print '' select spid = convert (smallint, req_spid), ecid = convert (smallint, req_ecid), rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, Type = case rsc_type when 1 then 'NUL' when 2 then 'DB' when 3 then 'FIL' when 4 then 'IDX' when 5 then 'TAB' when 6 then 'PAG' when 7 then 'KEY' when 8 then 'EXT' when 9 then 'RID' when 10 then 'APP' end, Resource = substring (rsc_text, 1, 16), Mode = case req_mode + 1 when 1 then NULL when 2 then 'Sch-S' when 3 then 'Sch-M' when 4 then 'S' when 5 then 'U' when 6 then 'X' when 7 then 'IS' when 8 then 'IU' when 9 then 'IX' when 10 then 'SIU' when 11 then 'SIX' when 12 then 'UIX' when 13 then 'BU' when 14 then 'RangeS-S' when 15 then 'RangeS-U' when 16 then 'RangeIn-Null' when 17 then 'RangeIn-S' when 18 then 'RangeIn-U' when 19 then 'RangeIn-X' when 20 then 'RangeX-S' when 21 then 'RangeX-U' when 22 then 'RangeX-X'end, Status = case req_status when 1 then 'GRANT' when 2 then 'CNVT' when 3 then 'WAIT' end ,req_transactionID As TransID, req_transactionUOW As TransUOW from master.dbo.syslockinfo end -- Fast not set dbcc traceon(3604) Print '' Print '' Print '*********************************************************************' Print 'Print out DBCC Input buffer for all blocked or blocking spids.' Print 'Print out DBCC PSS info only for SPIDs at the head of blocking chains' Print '*********************************************************************' declare ibuffer cursor fast_forward for select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked from @probclients where (spid <> @@spid) and (blocked!=0 or waittype != 0x0000 or spid in (select blocked from @probclients where blocked != 0)) open ibuffer fetch next from ibuffer into @spid, @blocked while (@@fetch_status != -1) begin print '' print '' print 'DBCC INPUTBUFFER FOR SPID ' + @spid exec ('dbcc inputbuffer (' + @spid + ')') print '' if (@blocked = '0') -- if DBCC PSS is not required, comment the line above, remove the -- comment on the line below and run the stored procedure in fast -- mode -- if (@blocked = '0' and @fast = 0) begin print 'DBCC PSS FOR SPID ' + @spid exec ('dbcc pss (0, ' + @spid +')') print '' print '' end fetch next from ibuffer into @spid, @blocked end deallocate ibuffer if datediff(millisecond, @time, getdate()) > 1000 begin select @tmpchar='End time: ' + convert(varchar(26), getdate(), 113) print @tmpchar end dbcc traceoff(3604) end -- All go

[참고] 그러나 현업에선 프로 파일러가 UI부하 때문에 사용이 망설여 집니다. 그래서 proc 로 제작해서 사용하는 것을 권장합니다. 위의 시스템 프로시저를 제작한 후 사용해 봅니다. 직접 아래 쿼리를 수행하거나 결과를 파일로 만들어 저장할 수 있습니다.

-- checkblk.sql DBCC TRACEON (3604) GO WHILE 1=1 BEGIN -- EXEC sp_blocker_pss80 -- Or for fast mode EXEC sp_blocker_pss80 1 WAITFOR DELAY '00:00:15' END GO

실제 위의 파일을 저장한 곳에서 다음의 명령 프롬프트에서 이렇게 수행하면 됩니다.


osql -E -icheckblk.sql -ocheckblk.out -w2000

그 다음은 결과만 분석하면 됩니다. 락에 대기중인 쿼리가 딱 나와있습니다.

수칙3.상황 발생시 인덱스 채우기 비율을 조정하는가?

인덱스는 검색할 때 속도는 무척 우수합니다. 그러나 insert작업시 인덱스의 많은 변화가 요구되는 페이지 분할(Page Split) 발생할 수가 있어서 아예 여유공간을 비워두는 것이 좋습니다.

[따라하기]
01. 데이터베이스 유지관리 마법사 노드에서 마우스 오른쪽 클릭 후 새 유지관리 계획 마법사를 실행합니다.




02.인덱스를 정돈할 데이터베이스를 선택합니다




03.다음과 같이 인덱스 비우기 비율을 적당량(상황에따라)을 선택한 후 다음버튼을 클릭합니다.






04. 정기적으로 재정비 해주면 좋습니다.

제공 : DB포탈사이트 DBguide.net

출처명 : 한국마이크로소프트

Michelle A. Poolet

돈만 있다면, 인덱스된 뷰를 사용하여 쿼리를 좀 더 간단하게 작성할 수 있다.

SQL Server의 차기버전인, SQL Server 2005에는 생산성을 높여 줄 수 있는 많은 기능과 확장성이 포함되어 있으며, 특히, 데이터베이스 기반 웹 어플리케이션을 개발하는 경우라면 더욱 도움이 될 것이다. ( SQL Server 2005의 전체적인 개관에 대해서는 SQL Server 메거진의 2004년 5월호를 살펴보면 된다.) 하지만, Microsoft에서는 SQL Server 2005의 최종 릴리즈 시기를 내년 상반기로 연기하였고, 많은 소프트웨어 판매점들은 적어도 최종 릴리즈이후 약 1년정도는 신규 데이터베이스 시스템으로 이행하지 않을 것이다. 결국, 현재 사용자들은 향후 일정기간동안 좀 더 SQL Server2000의 기능을 활용하게 될 것이다.
필자는 데이터 모델링 전문가와 설계 분석가로 일하고 있다. 그래서, 필자는 본능적으로 테이블 스키마를 확인하고 이를 최적화한다. 필자의 컬럼 “Solutions By Design”을 정규적으로 읽는 독자라면, 데이터를 추가하거나 관리하기 위해, 또한 데이터에 대한 일관성을 보장하기 위해서, 테이블의 정규화가 필수적이라는 사실을 강력하게 주장한다는 것에 대해 잘 알고 있을 것이다. 정규화의 장점은 정규화되어 있는 테이블에서 데이터를 조회하기 위해 여러 개의 테이블을 조인해야 한다는 불편함보다 훨씬 더 가치있는 것이다. 또한, 잘 정규화된 데이터베이스로부터 데이터를 조회하거나, 요약보고서를 작성하기 위한 대량의 데이터를 집계작업을 수행하기 위해, 네 개 또는 다섯 개의 테이블을 조인해야 하는 작업의 부가적인 오버헤드를 제거할 수 있는 방법이 있다. 이를 위해, SQL Server엔진이 쿼리가 실행되는 시점에 매번 뷰를 동적으로 재구성하지 않도록 “실체화(materialize)”된 뷰를 만들 수 있다. 즉, 뷰가 가상 테이블이 아닌 실제 물리적인 테이블이 되는 것이다.
일반적으로 뷰는 파생된 가상 테이블이다. 뷰는 사용자를 제한할 필요가 있는 중요데이터나 민감한 데이터를 숨기기 위한 접근 통제의 목적으로 사용되기도 하고, 관련된 정보를 좀 더 가치있는 정보 컨텍스트 단위로 표현하여 데이터의 가시성을 향상시키기 위한 목적으로 사용된다. SQL Server에서는 뷰에 대한 “실체화(materialize)”를 지원하는데, 이는 뷰에 유일한 클러스터된 인덱스를 생성하는 것을 의미하며, 이러한 실체화된 뷰를 “인덱스된 뷰”라고 한다. 클러스터된 인덱스와 마찬가지로, 실체화된 뷰는 실제로 사용자 데이터를 저장하게 된다.
실체화된 뷰는 데이터베이스 세계에서는 새로운 개념이 아니다. 비록, 인덱스된 뷰가 SQL Server 2000에서부터 지원된 새로운 기능이기는 하지만, ORACLE이나 DB2와 같은, 기존 다른 데이터베이스 관리 시스템에서는 이미 오래 전부터 동일한 기능을 제공해왔다. 대용량 DBMS 공급자들은 데이터웨어하우스 시스템의 성능을 향상시키기 위해 실체화된 뷰를 개발했었다. 데이터웨어하우스 시스템을 구축하기 위해서는 기술적으로 여러가지 서로 다른 데이터 원본으로부터 하나의 대용량 데이터 저장소로 데이터를 통합하는 작업이 필요하다. 데이터 저장소에는 운영 어플리케이션에서 사용할 상세 데이터가 저장되기도 하고, 의사결정지원시스템에서 사용할 요약 데이터도 저장되기도 하며, 양쪽 모두가 동시에 저장되기도 한다. 데이터웨어하우스에서, 일반적으로 데이터는 몇 가지 차원(예를 들어, 시간, 지역, 품목 등)으로 요약되게 되며, OLAP 및 의사결정지원 어플리케이션을 통해 집계작업 처리를 처리하여 데이터 저장소에 저장하게 된다. 다음에서는 데이터웨어하우스 이외의 다른 환경에서 실체화된 뷰를 유용하게 사용하는 예제에 대해서 살펴보게 된다.

왜 실체화된 뷰를 사용하는가?

실체화된 뷰가 데이터웨어하우스의 쿼리성능향상을 위해 만들어졌다면, 왜 굳이 트랜잭션 처리 데이터베이스에 실체화된 뷰를 사용하려고 하는가에 대한 의구심을 들 수 있다. 트랜잭션 데이터베이스 설계를 하는 경우라면, 마치 스포츠카 설계자와 마찬가지로, 속도와 민첩함을 중점으로 하여 설계를 진행하게 되기 때문에, 인덱스와 같은 데이터베이스 개체를 최소화하게 되고, 결국은 성능상에 문제의 원인이 된다. 트랜잭션 처리 데이터베이스의 경우, 데이터 조회의 성능을 개선하기 위해서는 인덱스가 사용되지만, 대표적인 성공사례를 기반으로 생각하여 볼 때, 인덱스는 데이터를 삽입하거나 변경할 때 별도의 오버헤드를 발생시키기 때문에 반드시 필수적으로 필요한 경우에 한하여 생성하는 것을 권장한다. 추가적으로, 실체화된 뷰는 하나 또는 그 이상의 테이블의 복사본이라고 볼 수 있기 때문에, 데이터 저장소의 공간도 실제적으로 두 배가 소요되게 된다.
실체화된 뷰는 데이터에 매우 빠르게 접근할 수 있도록 해 준다. 일반적으로, 실체화된 뷰를 사용하여 성능상의 개선사항은 실체화된 뷰를 위해서 투자되는 비용, 예를 들어, 실체화된 뷰와 실제 테이블의 데이터를 동기화하기 위해 소요되는 여분의 디스크 공간 및 프로세서의 비용을 충분히 보상하고도 남는다. 하지만, 무조건 실체화된 뷰를 사용하여 얻은 성능상의 개선효과가 실체화된 뷰를 관리하기 위해 소요되는 디스크 공간 및 프로세서의 비용보다 더 큰 것은 아니며, 이는 상황에 따라 매우 달라질 가능성이 있다. 그래서, 실체화된 뷰를 사용할 것인지 여부를 결정하기 위해 테스트를 진행할 때, 고려할 사항을 살펴보기 위해 몇가지 예제 시나리오를 살펴보고자 한다. 인덱스된 뷰를 추가하면 데이터변경작업에 오버헤드가 발생하기 때문에, 인덱스된 뷰를 적용할 수 있는 가장 좋은 대상은 거의 정적으로 유지되다가 피크시간이 아닌 시간대에 데이터에 대한 추가 및 변경이 발생하는 테이블이 될 수 있다.
운영 데이터베이스의 데이터 요약 운영 데이터베이스의 데이터를 집계하는 작업을 할 때 시스템자원이 많이 소모된다는 것은 모두가 공감할 것이다. 이러한 집계작업에는 단순한 합계, 건수, 평균을 계산하는 것 이상의 복잡한 처리가 포함된다. SQL Server의 잠금관리자는 데이터에 대한 요청간에 균형을 유지해야 하며, 집계작업이 진행하는 동안 데이터에 대한 변경작업을 지연시킬 수 있어야 한다. 또한, 집계작업을 위한 쿼리가 실행될 때마다, 해당 레코드가 데이터 캐시에 존재하지 않는 경우에는, 하드 디스크로부터 데이터를 물리적으로 스캔하여 메모리로 올리는 작업을 수행해야 한다. 집계작업에 포함되는 데이터가 많아질수록, 좀 더 많은 물리적인 I/O가 필요하게 된다. SQL Server에는 레코드에 대한 처리작업이 완료되자마자 즉시 해당 데이터 페이지에 대한 잠금을 해제하는 것과 같은, 데이터요청에 대한 지연이 최소화될 수 있도록 하기 위한 많은 기술을 사용하고 있다. 하지만, 특히, 이미 시스템에 막대한 작업부하가 발생한 상태에서, 집계작업을 수행하는 쿼리를 실행하게 되면, 전체적인 성능저하의 원인이 될 수 있다는 것만은 확실하다.
목록 1에는 선적된 주문건에 대한 정보가 저장되어 있는, Northwind 데이터베이스의 Orders 테이블에 실체화된 요약 뷰를 생성하는 예제가 나타나 있다. 우편번호를 기준으로 주문에 대한 요약 보고서를 생성하는 것은 판매동향을 파악하기 위해 매우 유용한 정보를 제공해 줄 수 있다. 그림 1에는 목록 1의 코드를 실행시킨 결과집합의 일부가 나타나 있다. 이 요약보고서를 통해 Northwind Trader 사는 어느 우편번호 지역으로 대부분의 제품이 배송되었는지에 대해서 쉽게 분석할 수 있을 것이다.

목록 1: 요약보고서용 인덱스된 뷰 생성

USE Northwind GO /* 우편번호 기준으로 주문에 대한 요약정보를 보여주는 인덱스된 뷰 생성 */ CREATE VIEW Orders_By_Postal_Code (PostalCode, NumberOrdersShipped) WITH schemabinding AS SELECT ShipPostalCode, count_big(*) FROM dbo.Orders GROUP BY ShipPostalCode GO CREATE UNIQUE CLUSTERED INDEX iduc_Orders_By_Postal_Code ON Orders_By_Postal_Code(PostalCode) GO /* 사용공간 확인 */ sp_spaceused Orders_By_Postal_Code SELECT * FROM Orders_By_Postal_Code ORDER BY 2 DESC GO /* 만약, 위의 코드를 SQL Server 2000 Standard Edition에서 실행하게 되면, 다음과 같은 별도의 처리옵션을 부여하여, 쿼리 최적화기가 인덱스뷰를 사용하도록 강제해야 한다. */ -- SELECT * FROM Orders_By_Postal_Code WITH(NOEXPAND) -- ORDER BY 2 DESC -- GO



다중테이블 조인의 제거. 운영 데이터베이스환경에서는, 정보를 얻기 위해 많은 테이블을 조인해야 하는 상황이 종종 발생하게 된다. 예를 들어, Northwind 데이터베이스에서 지역별 영업사원보고서를 만들기 위해서는 네 개의 테이블을 조인해야 한다. 특정 지역에 대한 사원명부를 데이터베이스에서 쿼리하거나, 특정 지역에 대한 판매권역별 영업사원 정보를 조회하는 작업은 그리 재미있는 작업은 아니지만, 꼭 필요한 작업이다. 그림 2에 나타나 있는 ERD에서 확인할 수 있듯이, Northwind 데이터베이스와 같이 매우 잘 정규화된 데이터베이스에서는, SQL Server가 필요한 데이터를 반환하기 위해 여러 테이블간의 조인 연산을 하는 과정에서 쿼리가 지연되는 문제를, 실체화된 뷰를 사용함으로써 해결할 수 있다. 목록 2의 코드에는 네 개의 테이블을 조인한 결과에 대해 실체화된 뷰를 생성하는 예제가 나타나 있다. 그림 3에는 목록 2의 코드를 실행한 결과의 일부분이 나타나 있다.



목록 2: 네 개의 테이블을 조인하는 인덱스된 뷰

USE Northwind GO /* 네 개의 테이블을 조인하여 사원에 대한 실체화된 뷰를 생성 */ CREATE VIEW Employee_By_Region WITH schemabinding AS SELECT r.RegionDescription, t.TerritoryID, t.TerritoryDescription, e.LastName, e.FirstName, e.Title, e.EmployeeID FROM dbo.Employees e INNER JOIN dbo.EmployeeTerritories et ON e.EmployeeID = et.EmployeeID INNER JOIN dbo.Territories t ON et.TerritoryID = t.TerritoryID INNER JOIN dbo.Region r ON t.RegionID = r.RegionID GO CREATE UNIQUE CLUSTERED INDEX iduc_Employee_By_Region ON Employee_By_Region(TerritoryID, EmployeeID) GO /* 사용공간 확인 */ sp_spaceused Employee_By_Region SELECT * FROM Employee_By_Region GO /* 만약, 위의 코드를 SQL Server 2000 Standard Edition에서 실행하게 되면, 다음과 같은 별도의 처리옵션을 부여하여, 쿼리 최적화기가 인덱스뷰를 사용하도록 강제해야 한다. */ -- SELECT * FROM Orders_By_Postal_Code WITH(NOEXPAND) -- ORDER BY 2 DESC -- GO



계산된 컬럼을 분리하여 관리. 잘 정규화된 데이터베이스에서 준수하고 있는 원칙 중의 하나는 사용자 데이터로부터 파생된 계산된 데이터는 별도의 테이블로 저장하고 있다는 것이다. 계산된 데이터는 시스템에 매일매일 발생하는 데이터 처리건에서 집계된 데이터라 할 수 있으며, 2차적인 데이터 처리절차라고 할 수 있다. 만약, 데이터베이스에 누계정보나 합계정보를 계산하고자 하는 경우, 지속적인 재계산 작업으로 인한 오버헤드가 발생하기 때문에, 원본 데이터 테이블에 합계정보의 계산결과를 저장하지는 않을 것이다.
물론, 테이블에 계산된 컬럼을 생성할 수 있다. 하지만, 계산된 컬럼은 가상 컬럼이다. 일반적인 뷰와 같이, 계산된 결과값은 저장되지 않으며, 쿼리에 의해 해당 컬럼의 값이 요청될 때마다 SQL Server는 동적으로 해당 컬럼값을 계산하게 된다. 테이블에 행이 많지 않다면, 동적 재계산작업이 문제가 되지 않는다. 하지만, 테이블의 1GByte 이상의 대용량인 경우, 매우 심각한 성능상의 문제가 발생할 수 있다. 계산된 컬럼에 인덱스를 설정하여, 이를 데이터베이스에 영구적으로 저장하는 방법이 이러한 문제의 대안으로 사용될 수 있다.(좀 더 자세한 정보는 Brian Lawton의 “뒷뜰에 숨겨진 보물단지” 기사(InstantDoc ID 42264)를 참조한다.) 계산된 컬럼에 대한 요구조건은 시간이 경과함에 따라 변경될 수 있는 것이고, 테이블 구조도 변경될 수도 있다. 테이블에 많은 컬럼이 존재하고, 해당 컬럼내역을 어플리케이션에서 사용하고 있는 환경이라면, 테이블 구조를 변경하는 것은 문제가 된다. 테이블 구조를 변경하는 대신 계산된 데이터를 가지고 있는 뷰를 생성한 다음, 해당 뷰를 실체화화여 데이터베이스에 저장할 수 있다. 계산된 컬럼에 대한 계산처리 알고리즘이 변경된 경우에는 인덱스된 뷰만 삭제한 다음, 다시 만들면 되기 때문에, 계산된 컬럼이 포함된 테이블을 수정하는 것보다는 훨씬 관리하기가 쉽다.

목록 3: 계산된 컬럼을 처리하기 위한 인덱스된 뷰

USE Northwind GO /* Order_Total에 대해서 계산된 컬럼 기반 인덱스된 뷰를 생성 */ CREATE VIEW Order_Totals WITH schemabinding AS SELECT o.OrderID, o.OrderDate, GrandTotal = SUM((d.UnitPrice * d.Quantity) - d.Discount), NumberItems = count_big(*) FROM dbo.Orders o INNER JOIN dbo.[Order Details] d ON o.orderID = d.OrderID GROUP BY o.OrderID, o.OrderDate GO CREATE UNIQUE CLUSTERED INDEX iduc_OrderTotals ON Order_Totals(OrderID) GO /* 사용공간 확인 */ sp_spaceused Order_Totals SELECT * FROM Order_Totals GO /* 만약, 위의 코드를 SQL Server 2000 Standard Edition에서 실행하게 되면, 다음과 같은 별도의 처리옵션을 부여하여, 쿼리 최적화기가 인덱스뷰를 사용하도록 강제해야 한다. */ -- SELECT * FROM Order_Totals WITH(NOEXPAND) -- GO

목록 3과 같이, 상세 데이터를 요약하는 실체화된 뷰를 쉽게 설정할 수 있다. 목록 3의 코드는 Orders 테이블과 Order Details 테이블로부터 총계를 집계하여 계산된 컬럼 뷰를 생성하는 역할을 한다. 쿼리가 실행될 때마다 주문에 대한 총계정보를 매번 재계산하는 것보다는, Order_Totals라는 실체화된 뷰를 생성하여 해당 뷰의 데이터로 총계정보가 저장될 수 있도록 하는 것이 더 쉽게 총계정보를 관리하는 방법이 될 수 있다. 그림 4에는 계산된 요약정보의 일부가 나타나 있다.



로컬 웹 어플리케이션 지원. 웹기반 어플리케이션의 특성상 많은 오버헤드가 발생하게 된다. 단순히 웹 브라우저에 화면을 표시하는데에도 상당한 시간이 소요된다. 웹 사용자가 데이터베이스로 데이터 요청을 보내게 되면, 성능상 데이터베이스에 또 부가적인 지연현상의 원인이 될 수 있기 때문에, 결국 웹 어플리케이션을 사용불가능 상태로 만들 수도 있다. 웹 어플리케이션을 지원해야 하는 경우라면, 데이터베이스가 빠른 응답속도를 유지할 수 있도록 튜닝작업을 수행해야 한다. 웹 데이터베이스 개발 및 설계 관련서적에서는 언제나 데이터베이스를 정규화하는 것과 테이블에 인덱스를 생성하는 것이라는, 두 가지 측면에 대해 강조한다. SQL Server 쿼리 최적화기는 가능하다면 언제나 인덱스를 사용하기 때문에, 인덱스된 뷰는 데이터 조회측면에서는 매우 탁월한 선택이 될 수 있다. 웹 브라우저로부터 요청된 쿼리에 대해 인덱스된 뷰를 생성하게 되면, 웹 어플리케이션의 응답속도를 확실하게 개선할 수 있을 것이다.

실체화된 뷰 만들기

SQL Server 2000의 모든 Edition에서 모두 인덱스된 뷰를 생성할 수 있지만, Enterprise Edition과 Developer Edition의 쿼리 최적화기에서만 뷰에 설정된 인덱스를 활용하여 쿼리계획을 수립하게 된다. (인덱스된 뷰의 제약사항에 대한 좀 더 자세한 정보는 Microsoft 기사 "SQL Server 2000의 모든 에디션에서 인덱스된 뷰를 생성가능"( http://support.microsoft.com/default.aspx?scid=kb;[LN];270054)를 참조한다. Enterprise Edition과 Developer Edition을 제외한 나머지 에디션에 포함된 쿼리 최적화기에서는 WITH(NOEXPAND) 쿼리힌트를 사용하기 전까지는 인덱스된 뷰를 포함하여 쿼리 계획을 생성하지 못한다.
실체화된 뷰를 생성하는 절차를 살펴보기 위해, SQL Server 2000 Standard Editon과 Personal Edition에서는 다음과 같은 절차를 따라하면 된다.:

1. 엔터프라이즈 관리자를 실행한다.
2. Northwind 데이터베이스를 선택한다
3. Northwind 데이터베이스를 선택 후 보기 메뉴의 작업 창을 선택한다.
4. 작업 창에서, 테이블정보 탭을 선택한 다음, 조회되는 결과를 확인한다.
5. 쿼리 분석기를 실행시키고, Northwind 데이터베이스를 사용하도록 설정한다.
6. 이번호에 제공된 예제 코드 목록 중에서 아무 것이 사용하여 인덱스된 뷰를 만든다.
7. ALT-TAB 으로 엔터프라이즈 관리자로 이동한 다음, 작업 창 화면을 재갱신한다.

생성한 인덱스 뷰가 정확한 요구사항을 충족할 수 있도록 생성되었는지와 실제 데이터베이스에 존재하는 사용자 테이블에 목록화되었는지 확인하는 절차가 필요하다. 인덱스된 뷰는 가상이 아니라 영구적으로 존재하게 되며, 데이터베이스내에 실제적인 공간을 차지하게 되며, 실제 테이블의 데이터와 인덱스된 데이터가 동일한지 체크하기 위한 동기화작업이 지속적으로 이루어질 수 있도록 반복적인 처리과정이 필요하다.
SQL Server 2000 Standard Edition의 쿼리 최적화기에서는 사용자 쿼리에 특별한 지시어(FROM절에 WITH(NOEXPAND)옵션)을 사용하지 않는 한 인덱스된 뷰를 사용할 수 없기 때문에 자원사용의 문제를 해결할 수 없다. 만약, SQL Server2000 Enterprise(또는 Developer) Edition이 아닌 경우에 인덱스 뷰를 사용하기 위해서는, 강제적으로 쿼리 최적화기가 해당 뷰를 사용하도록 해야 한다.
맨 처음 예제에서 살펴본 일반적인 뷰를 생성하는 방법에 WITH SCHEMABINDING 이라는 표현식만 추가하면, 앞부분의 세 가지 예제에서 볼 수 있는 것과 같이 인덱스된 뷰를 생성할 수 있다. 스키마 바인딩은 기존 테이블의 스키마 또는 구조를 변경할 수 없도록 잠그는 역할을 하게 되며, 원본 테이블의 구조가 변경되어 인덱스된 뷰가 실제 테이블에 근거를 두지 못하는 상황이 발생하지 않도록 통제하는 역할을 한다. 인덱스된 뷰를 제거하지 않고서는 기존 테이블의 구조를 변경할 수 없다. 테이블의 스키마를 변경해야 하는 상황이라면, 먼저, 인덱스된 뷰를 삭제한 다음, 원본 테이블의 스키마를 변경하고 다시 인덱스된 뷰를 생성해야 한다.
뷰를 실체화하기 위한 다음 작업단계는 뷰에 유일한 식별자를 가지는 클러스터된 인덱스를 생성하는 것이다. 인덱스를 생성하기 위해서는, 뷰에 유일하게 식별할 수 있는 식별자를 가지고 있어야 한다. 바로 이러한 점 때문에, 인덱스된 뷰를 만들때는 데이터에 대한 이해가 선행되어야 한다.
목록 2의 코드에서, 필자는 뷰를 생성하기 위한 쿼리에 TerritoryDescription는 유일한 식별자의 역할을 할 수 없기 때문에 TerritoryDescription이 대신 TerritoryID를 포함시켰다. 코드에서는 TerritoryID와 EmployeeID를 조합하여 유일하게 식별할 수 있는 클러스터된 인덱스를 생성하였다. EmployeeID만으로는 뷰의 결과값에서 유일한 식별자가 되지 않는다. 또한 TerritoryDescription 컬럼 자체에 중복된 값이 포함되어 있기 때문에, 후보키로서의 역할을 할 수 없어서, EmployeeID와 TerritoryDescription 컬럼을 조합한다고 하더라도 인덱스를 생성하기 위한 유일한 식별자로서의 역할을 할 수 없다.

인덱스된 뷰 관리

뷰가 실체화되었는지를 확인하기 위해서는, 생성한 인덱스된 뷰를 매개변수로 하여 sp_spaceused 명령을 실행해 보면 된다. 그림 5를 보면, 명령을 실행한 결과가 어떻게 나타나는지 알 수 있다. 뷰를 실체화한 다음, 인덱스된 뷰에 클러스터되지 않은 부가 인덱스를 추가할 수 있다. 하지만, 트랜잭션 처리를 위한 데이터베이스라면, 삽입, 변경, 삭제 작업이 발생할 때마다 인덱스된 뷰를 유지하기 위해 추가적인 오버헤드가 발생하기 때문에 인덱스의 수를 늘리는 것에 대해서 신중하게 고려해야 한다. SQL Server는 실제 원본 테이블이 변경될 때마다 자동적으로 인덱스된 뷰의 컨텐츠와 동기화하게 되며, 이 과정에서 정확한 데이터의 일관성을 보장하기 위해 CPU 시간과 물리적인 I/O를 소비하게 된다.

<그림 5> sp_spaceused 저장 프로시저 실행결과
name rows reserved data index_size unused
--------------------- -------- ---------- --------- ----------- ---------
Employee_By_Region 49 32 KB 16 KB 16 KB 0 KB


모든 뷰를 실체화할 수는 없는 것이다. 뷰에 인덱스를 생성하기 위해서는, 많은 법칙과 규정을 준수해야만 하며, 관련된 정보로는 Kalen Delaney의 2000년 5월호 기사, “인덱스된 뷰의 소개”(InstantDoc ID 8410)을 참조한다.
인덱스된 뷰도 기타 일반적인 뷰를 관리하는 방법과 동일하게 관리하게 된다. Sp_help와 sp_helptext 저장프로시저를 사용하여, 뷰의 각 컬럼의 정의와, 뷰를 생성하는데 사용된 코드를 살펴볼 수 있다. 데이터베이스에서 인덱스된 뷰를 제거하기 위해서는, DROP VIEW 명령를 사용하면 된다. 뷰를 실체화하기 위해서 설정한 클러스터된 인덱스만을 삭제하기를 바란다면, DROP INDEX 명령을 사용하면 된다. 또한 DROP INDEX 명령은 테이블에서와 마찬가지로 클러스터된 인덱스를 기반으로 추가로 만들어진 클러스터되지 않은 인덱스를 제거하기 위해서도 동일하게 사용할 수 있다. 뷰를 변경할 필요가 있다면, ALTER VIEW 명령을 사용할 수 있지만, 뷰를 변경하게 되면 모든 인덱스는 삭제된다. 그렇기 때문에, 뷰를 변경한 경우에는 모든 인덱스를 새로 만들어 주어야 한다.

좀 더 빠른 쿼리속도, 상대적으로 느린 업데이트

데이터베이스 환경에서 행해지는 모든 일에는 반드시 장단점이 공존한다. 쿼리속도를 개선하기 위해서 인덱스된 뷰를 생성한 경우를 좀 더 자세히 살펴보게 되면, 삽입, 변경, 삭제작업의 성능에 악영향을 미치게 된다는 것을 알게 된다. 그렇기 때문에 운영환경시스템과 완벽하게 분리된 별도의 개발환경에서 각각의 장단점간의 영향력을 주의깊게 계획하고 테스트해 보아야 한다. 인덱스된 뷰가 성능에 미치는 영향에 대해서는 Itzik Ben-Gan의 2002년 12월호 기사 “인덱스된 뷰의 관점”기사에서 언급된 것과 같이, 데이터비교작업에서는 약 17배, 유일성을 가진 값을 찾는 Distinct 연산의 경우는 약 23배의 성능이 향상된다.
실체화된 뷰가 가장 큰 효과를 발휘할 수 있는 환경은 주로 읽기전용 작업을 수행하며 가끔씩만 데이터 변경이 발생하는 데이터웨어하우스와 의사결정지원시스템이라고 할 수 있다. 하지만, 일부 트랜잭션 처리 환경에서도 인덱스된 뷰가 매우 유용하게 사용될 수 있다. 예를 들어, 한 테이블이 매우 대규모이고(1 GByte를 초과하는 경우), 반복적인 집계작업이나 또 다른 대용량 테이블과 조인하는 작업이 필요한 경우라면, 이러한 양쪽 테이블을 원본으로 하는 실체화된 뷰를 사용하는 것도 고려하고 테스트해 볼 필요가 있다. 반면에 매우 대량의 삽입 및 변경작업이 발생하고, 데이터베이스에 대해 클러스터된 키값을 기준으로 하지 않고, 다른 임의 레코드를 조건으로 하는 조회작업이 많은 경우에는 실체화된 뷰를 사용하는 것이 바람직하지 않다. 뷰에 설정된 인덱스를 유지보수하기 위한 오버헤드는 분명히 데이터베이스 성능을 저하시키는 원인이 된다.
실체화된뷰는 매우 신중하고 주의깊게 사전 점검을 한 이후에 사용해야 하며, 대용량 쿼리의 성능을 개선하는데 매우 도움될 수 있다. 하지만, 인덱스된 뷰가 모든 성능관련 문제의 만병통치약은 아니다. 삽입 및 변경에 대한 성능 저하의 원인이 된다는 것에 대해서 고려하지 않고, 신중한 테스트 절차 없이 인덱스된 뷰를 사용하는 것은 바람직하지 않다. 하지만, 시기적절하게 잘 사용하기만 한다면, 인덱스된 뷰를 적용하기 위해 테스트과정에서 소요된 시간과 노력을, 실제 운영환경에서 모두 다 보상받고도 남을 만큼 뛰어난 성능개선을 얻을 수 있을 것이다.

출처 : Windows&.net[2004년도 7월호]

정원혁
필라넷 이사

중소기업에는 별도로 DBA가 없는 것이 보통이다. 그 중에 나은 개발자가 DBA로 승격되는 경우가 대부분이다. 하지만 DBA의 역할만 수행하는 것이 아니기에 부족함이 많다. 5년 개발 경력의 이수미씨와의 대화 주제는 효과적으로 SQL 서버를 운용하기 위해 무엇을 갖춰야 하는가에 대한 것이다. 이 글을 통해 자신의 모습은 어떠한가를 생각해보며 DB 전문가로 가는 발판을 만들어보기 바란다.

필자는 여행을 무척 좋아한다. 거의 20개국 정도를 가봤는데 대부분의 여행은 가고 오는 항공편만 확보해두고 계획 없이 떠나는 경우가 많았다. 필자는 그런 계획 없는 여행을 좋아한다. 하지만 홀로 떠나는 것이 아닌 여행, 즉 이수미씨가 동반자(혹은 손님이고 필자가 가이드)이기에 이번 여행은 약간의 계획이 필요했다.

어디로 여행을 떠날 것인가?

이수미씨는 의욕왕성한 경력 5년차의 개발자였다. 그래서 이번 여행에서 가고 싶은 곳이 무척 많았고, 여행 계획서는 방대했다. 하지만 급히 가서 사진만 찍고 다른 여행지로 떠나는 그런 여행보다는 좀 더 차분히 여행을 할 수 있도록 수정했다.
이수미씨의 현재 수준은 이제 막 DBA로 발걸음을 내딛는 상태로, 회사에서 일을 맡겨서 시작한 똑똑한(?) 개발자였다. 수미씨의 목표는 세계일주였지만 아직은 그럴 상황이 아니어서 우리는 세계 일주를 향한 걸음마로서 첫 해외여행을 목표로 했다. 특히 수미씨는 오라클을 쓰다가 SQL 서버로 넘어왔으며, 그래서 특히 SQL 서버가 가진 독특한 특성들에 대해 잘 모르고 있었다. 따라서 이런 부분에 초점을 맞췄다.
맨 처음 수미씨는 테이블 디자인부터 질문을 해왔지만, 이번 여행에서 테이블 디자인을 주로 다루는 팀도 있고 이것만 다루기에도 워낙 방대한 주제이기에 디자인에 대해서 특별히 문제가 있는지만 보고 넘어갔다. 그리고 현재 사용되고 있는 서버의 상황을 살펴보기로 했다. 여기서 발생되는 문제의 유형에 따라 다음 목적지를 변경할 수 있도록 했다. 하지만 흔히 발생하는 동일한 문제가 나올 것이라 예측했는데(실제로 이런 문제들이 나타났다), 그래서 그 다음으로 SQL 서버를 사용할 때 가장 흔히 부딪히는 문제들인 잠금, 차단, 클러스터 인덱스의 특징, 프로필러라는 매우 유용한 도구의 활용에 대해 다루는 것을 이번 여행의 목적지로 정했다.

가볍게 짚고 넘어가는 테이블 디자인

첫 스타트를 하려고 하자 수미씨는 무척 힘들어했다. 그래서 필자는 ‘문제 접근방법’에 대한 조언을 해줬다(<박스> 참조). 이런 상황에서 우리는 먼저 간단히 주로 사용되는 테이블에 대해서 디자인 이슈가 없는지 살펴보기로 했다. 회사에서 사용하는 가장 핵심이 되는 테이블은 다음의 스키마와 일부 데이터만 살펴봤다. 필자는 이에 대해 주요 테이블의 생성 스크립트와 BCP out 데이터를 보내달라고 했다.
우선 수미씨의 질문은 “왜 하필 bcp로 보내야 하는가”란 것이었다. “테이블 수가 몇 개 안 되어 쿼리 분석기에서 select 문을 써서 추출했다. 내가 아는 범위에서는 BCP로 추출해도 테이블을 하나씩 지정해 추출해야 하므로, 손에 익은 방법이 빠를 것 같아 select 문으로 추출했는데, 이 방법에 문제가 있는가?”라는 질문이었다.
BCP를 갖고 추출해 달라고 한 것은 스크립트화할 수 있기 때문이다. 손에 익은 select로 추출했는데 그렇게 하면 테이블이 많아질 때 자동화하지 못하고 수동으로 계속 작업을 해야 한다. 꼭 BCP를 쓰지 않더라도 SQL 서버의 DTS를 활용할 수도 있다. 하지만 이때도 가능한 패키지를 저장해 두고 똑같은 일이 반복될 때 시간을 줄일 수 있는 가능성을 남겨두어야 한다.
DBA가 가져야 하는 중요한 자세 중 하나는 자동화와 반복성을 가질 수 있는 방법의 개발이다. 자주 반복된 작업을 해야 하기 때문에 손에 익었다는 이유로 편한 방법을 쓰다 보면 나중에 그런 작업을 300번 반복해야 하는 경우가 발생하고, 그때의 생산성이란 이루 말할 수 없이 나빠진다. 뿐만 아니라 오류가 발생할 수도 있다는 것이 더 큰 문제다. 그래서 자동화된 스크립트를 생성해서 사용하고 필요에 맞게 고쳐 쓰는 것이 몸에 배야 한다고 생각한다. 수미씨의 회사 테이블에서 가장 많이 사용하는 테이블 스키마와 일부 데이터 그리고 인덱스는 <표 1>, <표 2>와 같다.

<표1>주요 테이블의 스키마
cafe_info
Cafe_ID(PK) int
Cafe_Name varchar
Cafe_Master varchar
Cafe_Class smallint
Cafe_Small_Content varchar
Cafe_Content varchar
Cafe_Main text
Cafe_InsertDay smalldatetime
Cafe_Permit bit
Theme_No int
Cafe_Status smallint
Group_No int
FilePath varchar
Ranking int
New bit
cafe_member
Member_Name varchar
wDate datetime
Years_old smallint
Address1 varchar
Address2 varchar
Address_Permit bit
PostNo char
Job_Name varchar
Job_Name_Permit bit
Introduce text
TelePhone_Permit bit
HandPhone_Permit bit
cafe_free_board
Cafe_ID int
Num int
Writer varchar
Subject varchar
Content text
Ref int
Re_Step int
Re_Level int
UserID varchar
wDate datetime
visited int

cafe_member_class
Member_Class smallint
Member_Class_Name varchar
cafe_group
Group_No int
Group_Name varchar
Group_Content varchar
Insert_Day smalldatetime
Group_OrderNo smallint



<표 2>인덱스
PK_Cafe_Group clustered, unique, primary key Group_No
Cafe_Free_Board_Index_1 nonclustered, unique Cafe_ID, Num(-)
Cafe_Free_Board_Index_2 nonclustered, unique Cafe_ID, Num(-), Ref, Re_Step, Re_Level
PK_Cafe_Free_Board clustered, unique, primary key Num
IX_Cafe_Info nonclustered Group_No
PK_Cafe_Info clustered, unique, primary key Cafe_ID
PK_Cafe_Member_Class clustered, unique, primary key Member_Class


아쉬운 부분은 인덱스

현재의 테이블들은 일단 대충 봐서 정규화 관점에서는 필자가 봤던 웬만한 중소 규모의 업체에서는 탁월하게 잘 된 테이블 디자인이라고 할 수 있다. 보통 중소 규모의 기업에서 테이블 디자인이란 것은 PK(기본 키)도 없거나 인덱스가 하나도 없거나 자료형은 모두 varchar이거나, 3 정규화 위반은 물론이요, 1 정규화 위반에 이르기까지 거의 엉망에 가까운 것이 보통이다. 이에 비하면 자료형도 적절하게 잘 사용하려고 한 노력이 보이며, 정규화도 무척이나 노력해서 한 흔적이 보인다. 특히 Bit, smallint, smalldatetime 등의 자료형을 적절하게 잘 썼다. 작은 자료형을 사용하는 것은 데이터가 늘어났을 때 성능에 영향을 미치는 요소가 되기 때문이다.
눈에 띄게 아쉬운 것은 인덱스 부분이었다. 특히 cafe_member는 아예 인덱스는 물론이고 PK가 없었는데 혹시 스크립트 생성 과정에서 실수한 것은 아닌지 한번 확인해 봤으나 확실히 없었다. 따라서 (Cafe_ID, Member_ID)를 PK로 잡기로 했다. 테이블에 PK가 없다는 건 정말 특별한 상황 아니면 대단히 중대한 실수다. 많은 사람들이 성능을 외치지만 정작 중요한 것은 그 이전의 기초다. PK가 없으면 중복데이터가 들어오게 되고, 그러면 나중에 그 중복 예외 처리를 하려고 프로그램을 짜는 게 복잡해지고 프로그램들은 결국 꼬이기 시작한다. FK(참조 키)가 없어도 마찬가지이다. 불행하게도 수미씨 회사의 테이블들은 FK가 없었다. 그럼 엉뚱한 member_id를 가진 데이터가 주문을 하는 등 문제가 생기고, 나중엔 결국 outer join을 사용해 엄청 성능이 느린 쿼리를 작성하게 된다.
그러니 아무리 강조해도 지나치지 않는 것이 데이터 무결성이다. 혹시 나중에 데이터 웨어하우징을 하거나 데이터 이전 작업을 해보면 실감하게 되니 지금부터 일찌감치 무결성 확보를 위한 노력을 기울여야 할 것 같다.

주요 테이블에 FK를 설정하라!

그래서 수미씨에게 주요 테이블에 FK를 설정해 보도록 숙제를 냈다. 시도해보면 바로 오류가 발생할 것이다. 거의 엉터리 데이터가 있을 거니까(아니라면 정말 축하할 일이지만). 이로 인해 개발자들이 아우성을 쳐도 미래를 위해 고집스럽게 FK와 check constraint와 같은 제약을 설정해 볼 것을 권장한다. 진행과정에서 역시나 PK를 거는 데 중복오류가 발생했다. 이런 경우 삭제는 어찌하는가 물어왔다. 물론 질문하곤 바로 답을 찾아내어 자문자답했다. 자문자답하는 동안 필자는 다음과 같은 방법을 제시했다. “몇 가지 삭제 방법이 있는데 일단 먼저 중복된 데이터를 찾아야 한다. 스스로 한번 풀어보라.”

“키 값에 대해 group by를 하고 그것의 개수(count(*))가 1보다 큰 것은 중복된 키 값이다.”

이렇게 중복된 데이터를 모두 찾아내서 지우면 된다. 중복된 것을 지우는 것도 양이 많다면 약간의 트릭을 쓸 수 있을 것이다.

- 중복데이터를 임시테이블에 넣고
- 최상위 한 행만 남기고 나머지는 지운 다음
- 원 테이블에서 모든 중복 행을 지우고
- 임시 테이블 데이터를 원 테이블에 넣는다.

서버에서는 무슨 일이 벌어지고 있을까?

테이블에 대한 검증이 끝나자 우리는 다음 목적지를 향해 떠났다. 실제 서버에서 어떤 일들이 벌어지고 있는지 ‘관찰(monitor)’해 보는 것이다. 현재 상황을 알아내는 것은 매우 중요하기 때문이다. DBA가 해야 하는 중요한 일은 성능을 향상시키는 것이지만, 이를 위해서 먼저 현재 상황을 알아야 하는 것이다. 따라서 우리는 프로필러라는 좋은 도구를 사용하기로 했다. 물론 윈도우의 성능을 모니터하기 위해서는 성능 카운터라는 좋은 도구가 있지만, 이번 여행에서는 제외시켰다. 너무나 방대하기 때문이다.

프로필러

그럼 이제 정말 이 테이블들에 인덱스가 제대로 사용되고 있는지 검증해보기 위해 프로필러를 사용할 줄 아는지 확인해 봤다. 안다고 해서 20MB 정도 분량이 될 때까지 필자가 주로 사용하는 템플릿을 보내주고 이를 사용해서 추적 데이터를 받아 보내주도록 했다. 그걸 갖고 인덱스가 제대로 위의 테이블에 만들어져 있는지 검증해 보도록 했다.
수미씨는 프로필러가 특정한 옵션을 주지 않았는데, 5MB씩 잘라 저장이 된다고 답해왔다. 또한 커뮤니티 관련 테이블(앞서 디자인에서 살펴본 주요 테이블)만 분석하려고 하니 따로 선택하는 옵션이 없어 전체 데이터베이스(정확히는 전체 서버)에 대한 추적으로 생성됐다고 하면서 부분 분석 옵션 기능이 있는지 물어왔다.
프로필러는 디폴트로 5MB가 되면 자동적으로 rollover가 된다. 그래서 처음 5MB가 차면 xxxx(제일 처음 준 파일이름).tr1, tr2, tr3 이런 식으로 파일명이 생성된다. 혹 실수로 추적을 중지하는 것을 잊으면 무제한의 파일이 쌓이게 되므로 주의해야 한다.
그래서 추적 중지 시간 설정이란 것이 필요하다. 또한 해당하는 테이블만 추적할 기능이 있기는 하지만, 임의의 쿼리(ad-hoc query)일 때만 가능하다. 프로시저라면 어렵다. 왜냐하면 프로시저 안에 테이블 이름이 포함되어 있고, 이를 프로필러가 알 수 있는 방법이 현재로는 없기 때문이다. 임의의 쿼리라면 프로필러에 필터라는 기능을 사용해 textdata에 필터를 걸면 된다. 이때는 와일드카드를 지원하므로, ‘%원하는테이블%’와 같이 설정하면 되지만 권장할 만한 방법은 아니다. 이를 필터하기 위해서 별도의 부하기 걸리기 때문이다. 차라리 추적을 받은 후 나중에 걸러내는 게 더 쓸만한 방법이다.

프로필러 분석1 - 차단

추적을 받아 봤더니 필자가 보낸 템플릿을 사용하지 않았다. 그래서 템플릿을 사용하는 이유에 대해 설명했다. 템플릿을 사용하는 이유는 추적 자료를 받고 나서 일관된 형식으로 보고 분석하고자 하는 목적도 있고, 또한 처음에 받을 때 불필요한 것들을 받지 않으려는 목적도 있다. 거듭 강조하지만 반복되는 일들에 대해 불필요한 ‘노가다’를 줄이는 것도 DBA 혹은 엔지니어들이 해야 할, 아니 모든 인생살이에서 해야 할 중요한 일이다.




<화면 1>을 보면 불필요하게 연결 속성, 로그인, 로그아웃, 애플리케이션 네임, 유저 네임 등이 잡혀 있다. 보통은 이런 것들이 필요 없다. 특별히 무슨 문제가 있어 일부러 그것들을 살펴보지 않는 한 공부를 위해서라도 필자가 보낸 템플릿을 사용해 한번 더 받아보도록 하자. 그 결과를 <화면 1>과 비교해보자. 항상 동일한 순서와 이벤트들이 캡처되며 따라서 이를 갖고 처리하는 다음 과정의 일들도 자동화될 확률이 높아진다.



<화면 2>를 보면 현재 exec sp_cursorfetch 180150000, 16, 4, 1이란 것들이 나타나는 것을 볼 수 있다. 이것은 클라이언트가 서버 측 커서를 사용하기 때문이다. 물론 장단점이 있다. ASP, VB 등의 프로그램에서 처음 연결 속성에서 이것을 정의해주게 되어 있는데, 디폴트로 서버 측 커서를 사용하게 된다(물론 클라이언트 속성에 따라 디폴트가 바뀌기도 한다). 꼭 그렇게 해야 하는 경우가 아니라면 불필요한 CPU 부하와 네트워크 부하를 걸기 때문에, 서버 측 커서를 클라이언트 측 커서로 바꿔야 한다. 추적 파일을 열어보면 50% 이상이 이들로 채워져 있음을 쉽게 발견할 수 있다. 레코드셋 페이징을 위해 커서를 사용한 부분인 것 같은데, 선언을 안 하면 디폴트로 adUseServer(서버 측 커서)가 선언이 되니 adUseClient(클라이언트 측 커서)로 선언을 변경해주면 된다. 또한 <화면 2>에서 선택된 라인을 보면 다음 쿼리가 수행됐다.

Select Cafe_ID, Num, Writer, Subject, WDate, Ref, Re_Step, Re_Level, Visited From Cafe_Free_Board_10 Where Cafe_ID = 324 Order By Ref Desc, Re_Level

앞에서 눈여겨 본 테이블은 아니지만 게시판이라는 것을 쉽게 알 수 있다. 자주 사용하는 테이블, 쿼리일 것이라고 추정한다. 그런데 특이한 것은 Reads가 0(페이지)인데 걸린 시간은 무려 1236ms, 즉 1.2초가 걸렸다. 정말 0인지는 확인해볼 필요가 있는데 추적 자료의 거의 모든 데이터가 0으로 나타나는 것으로 봐서 SQL 서버 추적에 문제가 있는 듯하다(가끔 이런 문제가 생기기도 한다). 어찌 되었건 단 하나의 값을 가져오는 쿼리가 이 정도의 시간이 걸린다는 것은 무지 느린 것이고(제대로 된 추적이라면), 읽기가 0이라는 것에서도 이 쿼리는 거의 10ms 이하가 걸려야 정상이라고 할 수 있다. 따라서 이 쿼리는 전형적인 차단(blocking) 현상이라고 할 수 있다. 차단을 피하기 위해서는 어찌 해야 할까? 이것이 게시판 테이블이라는 것을 염두에 두고 생각해보자.



<화면 3>과 비교해보면 확연히 차이가 난다. 즉, member, cafe_member 등의 테이블이다. 수미씨에게 걸린 시간이 거의 0 혹은 16ms 이하인 것과 비교해 보라고 주문했다. 그리고 <화면 4>는 비정상적으로 보이는 쿼리다. cafe_group 테이블에서 가져오는데 한 번에 다 가져오면 될 텐데 왜 저런 식으로 10번에 걸쳐 가져올까? 이런 쿼리가 자주 반복되는 것을 볼 수 있다. 뭔가 프로그램을 잘못 짠 듯하다. 문제되는 쿼리를 수미씨가 찾아보니 동호회 상단의 각 동에서 현재 위치를 표시해주기 위한 쿼리였다. 소스가 좀 이상하게 구성되어 있어 검토 후 수정하기로 했다.



수미씨는 새로 추적을 받았다. 지난 번에는 최대 파일 크기를 5MB로 설정하고 ‘파일 롤오버 사용’ 옵션을 사용했고, 이번에는 최대 파일 크기를 20MB로 설정하고 ‘파일 롤오버 사용’ 옵션을 사용하지 않았다. 무제한의 파일이 쌓이는 것을 방지하기 위해 추적 중지 시간도 설정했다. 추적 속성에 대해 좀 더 이해하기 위해 SQL 서버 온라인 설명서(BOOKS ONLINE)를 찾아봤다. 필자는 이에 대해 칭찬을 아끼지 않았다. 온라인 설명서는 매우 도움되는 가장 기본적인 참고서이다. 필자는 한때 “온라인 설명서를 첨부터 끝까지 다 읽어보리라”는 결심을 했는데 너무 빨리 새 버전이 나오니 어림도 없었다. SQL 서버 2005가 그나마 가장 오래 끌고 있는데, 요즘은 다른 일들로 바빠서 다 읽을 엄두는 못 내고 틈틈이 참고하고 있다. 뉴스 그룹에 올라오는 질문에 대한 답변의 상당 부분은 “readme.txt를 읽어봐라”, “온라인 설명서를 읽어봐라”이다. 그만큼 중요한 것이니 자주 살펴봐야 한다. 필자 역시 SQL 서버 서비스 팩 3를 설치할 때 문제가 생겨서 질문했는데, 답변이 “readme.txt를 읽어 봐라”였고 읽어보니 정말 있었다. 조금은 창피했다.

차단과 잠금 힌트

다음의 쿼리 구문의 블로킹 현상을 피하는 방법은 잠금 힌트를 사용하는 것이다.

Select Cafe_ID, Num, Writer, Subject, WDate, Ref, Re_Step, Re_Level, Visited From Cafe_Free_Board_10 Where Cafe_ID = 324 Order By Ref Desc, Re_Level

잠금 힌트를 사용하는 방법은 SELECT, INSERT, UPDATE, DELETE 문에서 테이블 이름 뒤에 잠금 힌트를 지정하는 것이다. 다음과 같은 방법이 가능하다.

◆ 방법 1 : 저장 프로시저의 상단에 다음의 구문을 선언하면 매 쿼리의 테이블마다 잠금 해제(read uncommitted)를 주지 않고 세션 전체에 영향을 준다.
Ex) set transaction isolation level readuncommitted
◆ 방법 2 : 차단당하는 쿼리문에 힌트(rad uncommitted)를 삽입한다.

Select Cafe_ID, Num, Writer, Subject, WDate, Ref, Re_Step, Re_Level, Visited From Cafe_Free_Board_10 with (readuncommitted) Where Cafe_ID = 324 Order By Ref Desc, Re_Level

하지만 <방법 2>를 쓸 때는 매 테이블마다 잠금 힌트를 써야 한다.

Select Cafe_ID, Num, Writer, Subject, WDate, Ref, Re_Step, Re_Level, Visited From Cafe_Free_Board_10 with (readuncommitted) join cafe_member with (readuncommitted) on … Where …

정확한 잠금 힌트의 사용은 트랜잭션의 수행 속도를 향상시킬 수 있다. 단 잠금 힌트는 잘 사용하면 참으로 좋지만 잘못 사용하면 역효과가 날 수도 있다. 즉 트랜잭션의 일관성을 깨지게 한다.
“NOLOCK과 ReadUncommited의 차이점은 무엇인가?”라는 질문을 흔히 한다. NOLOCK과 ReadUncommited는 차이가 없다. 과거에는 NOLOCK이라는 것만 존재하던 시절이 있었다. 이제는 ReadUncommited가 정식 문장이고 이것은 ANSI 호환이 되는 문장이므로 다른 데이터베이스 엔진에서도 사용되는 문장이다. 그래서 가능하면 ReadUncommited를 써야 한다.
말이 나온 김에 다음과 같은 문장은 어떤 결과를 가져올까? 실제로 어떤 사이트에서 실수로 작성된 문장에 이런 코드가 있었고, 웃음이 나오는 결과가 벌어졌다. 무엇이 문제일까?

Select Cafe_ID, Num, Writer, Subject, WDate, Ref, Re_Step, Re_Level, Visited From Cafe_Free_Board_10 NOLOCK Where Cafe_ID = 324 Order By Ref Desc, Re_Level

답을 아는 사람은 지금 빙긋이 웃을 것이다. 어떤 회사에서 실제로 이렇게 써 놓고는 왜 이게 차단이 될까 고민했다가 나중에 훈수를 둔 사람에 의해 발견하고는 다들 실소를 금치 못했다. 훈수 둘 때는 원래 잘 보이는 법이다. 이 답은 WITH (NOLOCK) 또는 (NOLOCK)이라고 했어야 하는데 그냥 NOLOCK이라고 했기 때문에 테이블의 별칭으로 인식했다. 참고로 SQL 서버 2005에서는 with가 없는 (NOLOCK)은 지원되지 않는다. 그러니 이젠 모든 코드에 with (NOLOCK)처럼 WITH를 꼭 붙여야 한다.

프로필러 분석2 - 인덱스

수미씨가 보내준 프로필러 자료를 테이블에 넣었다. 그리고 그걸 갖고 분석을 해봤다(긴 과정이라 줄여서 요약을 한다). 궁극적으로는 수미씨(뿐만 아니라 모든 DBA)도 이런 과정을 배워서 앞으로 혼자 튜닝을 해나가야 한다. 프로필러 자료를 프로필러에서 그대로 볼 수도 있지만 이것을 SQL 서버에 테이블로 저장해 분석할 수도 있다. 이렇게 하면 우리가 좋아하는 쿼리 문장으로 분석 작업을 할 수 있어서 훨씬 수월해진다. 이 상세한 과정은 생략한다. 해보면 그다지 어렵지 않다는 것을 금방 알게 되고, 각자의 분석 능력과 응용 능력에 따라 활용도는 달라진다.
쿼리 분석기에서 CPU를 가장 많이 차지한 것을 살펴봤다. 최고로 많이 걸린 것이 4초(4781ms)이고, 그 나머지는 300ms 이하니까 양호한 상태라고 할 수 있겠다. 그렇다고 방심할 수는 없다. 가장 문제가 되는 쿼리 두 개만 찾아봤다. Where 절이 어디에 걸리는지 잘 봐야 한다.

<리스트 1> 악성쿼리

유형 1
Select Cafe_ID, Num, Writer, Subject, WDate, Ref, Re_Step, Re_Level, Visited From Cafe_Free_Board Where Cafe_ID = 324 Order By Ref Desc, Re_Level

유형 2
Select Min(Num) From Cafe_Free_Board Where Ref > 11168 And Re_Step=1 And Cafe_ID = 60



<리스트 1>에서 두가지 유형의 쿼리가 수행되는 횟수를 찾아보니 전체 6만 6321건 중 6366번이나 수행됐다. 테이블에 추적 결과를 저장하면 이런 일이 편해진다. 이 쿼리에서 사용하는 테이블(Cafe Free_Board)에 지금은 다음과 같은 인덱스가 걸려 있다.

Cafe_Free_Board_Index_1 nonclustered, unique Cafe_ID, Num(-) Cafe_Free_Board_Index_2 nonclustered, unique Cafe_ID, Num(-), Ref, Re_Step, Re_Level PK_Cafe_Free_Board clustered, unique, primary key Num

우선 눈에 띄는 것은 index_1과 index_2가 중복되어 있다. Index_1은 index_2의 진 부분 집합이다. 중복된 것 중 하나는 지울 필요가 있다. 또한 이것 갖고는 <리스트 1>의 두 번째 유형을 충족시킬 수 없다. <리스트 1>에 대해서도 선택성이 나쁘면 도움이 안 될 듯하다. 넌클러스터 인덱스이기 때문이다. 클러스터 인덱스와 넌클러스터 인덱스의 상세한 내용에 대해서도 여기서는 다루지 않는다. 상세한 것들은 별도로 인덱스를 다루고 있는 수많은 온라인 세미나 자료, 서적 등을 참고하도록 하자.
실제 index_2를 사용하는 쿼리가 있는지 검사해 봤더니, 4% 정도의 쿼리가 사용을 했다. 하지만 컬럼 re_level은 사용하지 않았다. 반면에 추적에 잡힌 쿼리 중에는 Ref와 Cafe_ID를 사용해서 검색하는 쿼리가 존재했다. 이런 것들은 추적 파일을 집중적으로 분석해보면 알게 되는 결과이다.
다음 쿼리를 갖고 통계정보를 보았다. 통계란 SQL 서버가 컬럼에 어떤 데이터가 들어 있는지의 정보를 관리하는 것이다. 이것은 최적화기(optimizer)가 인덱스 결정을 내리고, 조인의 방법과 순서를 정하는 중요한 정보가 된다.

dbcc show_statistics (Cafe_Free_Board, Cafe_Free_Board_Index_1)

마지막 업데이트 전체 샘플
10 27 2002 1:00AM 368,968 368,968


불행하게도 지난 2002년 10월에 업데이트가 된 후로 전혀 업데이트가 안 됐다. 다시 이야기하면 이 인덱스는 사용하지 않는다는 의미다. 왜냐하면 SQL 서버는 자동으로 사용하는 인덱스나 통계를 업데이트하기 때문이다. 즉 index_2를 사용하기 때문에 중복되고 선택성도 상대적으로 덜 좋은 index_1은 사용하지 않는다는 의미이다. 전체 행은 약 40만 행 정도라는 것도 알 수 있다. 이것 역시 2002년 당시의 이야기이다. 또한 이 인덱스의 밀도는 다음과 같다.

밀도 길이 컬럼
0.010753 4 Cafe_ID
0.000003 8 Cafe_ID,Num


이 밀도를 기초로 이야기해 보면 하나의 cafe_ID를 찾으면 대략 1% 정도의 행을 찾아야 한다는 추정을 할 수 있다. 하지만 실제 통계 분포를 보면 어떤 cafe는 분포가 많고 어떤 것들은 적다. 많은 것은 20% 이상이다(분포 정보는 지면상 생략한다). 이 정도라면 넌클러스터 인덱스로 감당을 못할 것이라고 예측할 수 있다. 그러니 차라리 클러스터 인덱스를 만드는 게 나을 듯하다. 그러므로 num에 걸려있는 클러스터 인덱스보다는 cafe_id를 클러스터 인덱스로 만드는 것을 고려해 보자. 물론 클러스터 인덱스가 바뀌면서 틀림없이 cafe_id의 중간중간에 insert되는 행으로 인해 split으로 인한 오버 헤드와 조인이 걸릴 때 클러스터 인덱스보다 비효율적인 넌클러스터 인덱스를 사용해 조인을 처리해야 한다는 단점이 있기는 하다.
이제 성능 비교를 위해서 다음 쿼리와 같이 복사본을 만들도록 하자. 원본을 갖고 바로 적용하는 것도 방법이지만 온라인으로 사용 중이기 때문에, 그리고 성능 비교를 하기 위해서 별도의 복사본을 사용하도록 하자. 물론 테이블이 크다면 이렇게 하기도 힘들다. 그래서 사실 작은 크기의 테이블 유지가 필요하다.

select * into 임시Cafe_Free_Board from Cafe_Free_Board create unique index pk on 임시Cafe_Free_Board (num) CREATE CLUSTERED INDEX cx ON 임시Cafe_Free_Board ( cafe_id ) create index ix2 on 임시Cafe_Free_Board (Num DESC , Cafe_ID, Ref, Re_Step) create index ix3 on 임시Cafe_Free_Board (Re_Step, Cafe_ID, Ref)

또한 이것은 전형적인 웹 게시판이므로, 이에 대해 효과적으로 처리하는 몇 가지 알고리즘이 이미 나와 있다(필요하다면 www.en-core.com을 참고해 테이블 구조와 쿼리를 약간만 고치면 탁월한 성능을 얻을 수 있다). 사실 DBA는 이런 일을 추진할 능력과 권한이 있어야 한다. 많은 회사에서 DBA가 별 권한 없이 책임만 지는데, 서버의 성능 저하와 다운은 모두 DBA의 책임이므로 이 정도의 테이블 수정은 감행할 수 있는 추진력과 결단성이 필요하다.
결론적으로 성능 비교를 위해서 새로 생성한 테이블과 기존 테이블을 갖고 <리스트 1>의 쿼리를 수행해보도록 하자. 테이블 구조는 전혀 변경시키지 않았다. 인덱스만의 효과를 알아보기 위해 쿼리 문도 건드리지 않는다. 쿼리는 물론 새로 생성한 테이블 이름으로 바꿔서 수행해야 한다. 수행할 때는 다음과 같이 분석정보를 출력하도록 설정하고 수행할 쿼리를 GO로 구분해서 반복 수행해 결과를 비교해야 한다. 첫 번째 수행은 거의 캐시의 영향으로 느릴 수밖에 없기 때문에 그걸 그대로 믿으면 안 된다. 그래서 매번 캐시를 모두 비우고 테스트를 하든지 아니면 메모리에 올라온 상태에서 테스트를 해야 공정한 테스트가 된다.
<리스트 1>의 유형 1의 경우 8943ms는 5863ms가 되었고, 논리 읽기는 5082페이지에서 287페이지가 됐다. <리스트 1>의 유형 2의 경우는 44ms가 0ms, 즉 1ms 이하로 됐고, 논리 읽기는 471페이지에서 8페이지로 줄었다. 만족스런 결과다. 에이 기껏 3초? 기껏 44ms? 실제로 이런 작은 차이가 엄청난 성능의 차이를 가져온다. 필자는 기껏 30페이지 논리 읽기를 하는 원본 쿼리와 6페이지의 논리 읽기를 하는 수정본 쿼리를 스트레스트 테스트한 결과, 2000명 정도의 동시 접속에서 더 이상 접속이 안 되는 반면 아무 문제없이 성능을 발휘하는 사례도 경험한 바 있다. 따라서 저런 차이들에 대해 무시하면 나중에 많은 사용자, 많은 데이터가 됐을 때 문제가 생긴다.
Cafe_Free_Board가 Cafe_Free_Board_1부터 Cafe_Free_Board_10까지 적어도 10개 이상 되는 것 같은데, 나머지 테이블도 모두 적용해 보도록 했다. 적용된 후의 결과에 대해서는 이 글을 쓰고 있는 시점에서 확인할 수 없다. 아마 한달 후 쯤 적용되면 결과를 알 수 있을 듯. 그리고 그 때 프로필러로 다시 추적해보면 확실하게 성능이 향상된 것을 볼 수 있을 것이다.



“미지의 어떤 곳도 이제 도전할 수 있다”

이번 여행에서 우리는 어디로 떠날 것인지부터 논의하기 시작했고 그래서 명확하지 않은 것에 대해 구체화시켜가는 방법을 다뤘으며 프로필러를 이용해 모니터하고, 그래서 여행의 목적지를 어디로 잡을지 구체화시켰다. 실제 프로필러는 할 수 있는 기능이 엄청나다. 하지만 그보다 더 중요한 것은 이를 사용하는 사람이다. 궁극적으로는 SQL 서버의 최적화기가 동작하는 원리를 이해하고 이를 생각하며 이런 도구를 사용해 나가야 하지만, 첫 출발로 흥미를 갖고 모호한 개념을 구체화시키기에는 아주 좋은 도구이다.

<리스트 2> 성능 비교를 위한 출력
set statistics profile on go set statistics time on go set statistics io on go

원본 <리스트 1>의 유형 1의 결과
'Cafe_Free_Board' 테이블. 스캔 수 4, 논리적 읽기 수 5082, 물리적 읽기 수 0, 미리 읽기 수 8.
CPU 시간 = 377ms, 경과 시간 = 8943ms.

원본 <리스트 1>의 유형 2
'Cafe_Free_Board' 테이블. 스캔 수 1, 논리적 읽기 수 471, 물리적 읽기 수 0, 미리 읽기 수 0.
CPU 시간 = 44ms, 경과 시간 = 44ms.

튜닝 후 <리스트 1>의 유형 1의 결과
'Cafe_Free_Board' 테이블. 스캔 수 1, 논리적 읽기 수 287, 물리적 읽기 수 0, 미리 읽기 수 0.
SQL Server 실행 시간 : CPU 시간 = 63ms, 경과 시간 = 5863ms.

튜닝 후 <리스트 1>의 유형 2
'Cafe_Free_Board' 테이블. 스캔 수 1, 논리적 읽기 수 8, 물리적 읽기 수 0, 미리 읽기 수 0.
SQL Server 실행 시간 : CPU 시간 = 0ms, 경과 시간 = 0ms.

이런 과정에서 SQL 서버를 처음 접하는 사람들이 흔히 겪는 문제인 차단, 불필요한 쿼리, 클라이언트 커서, 잘못된 인덱스, 제약 없음 등의 문제들을 봤다. 빙산의 일각이라 할지라도 이들을 해결하는 방법도 살펴봤다. 지피지기(知彼知己), 정말 그렇다. 나를 먼저 알아야 하듯, SQL 서버에서는 현재 어떤 문제들이 벌어지고 있는지를 알아야 한다. 물론 방대한 양의 최적화기와 SQL 서버 아키텍처를 공부해야 하지만(전략), 초·중급 DBA들은 출발점으로는 현재 상황을 먼저 분석해야만 이를 근거로 자신이 무엇을 먼저 공부해야 하는지 전술을 세울 수 있다. 필자가 사용한 템플릿을 비롯한 나머지 자료는 ‘이달의 디스켓’으로 제공한다. 하지만 보안상의 이유로 스키마와 데이터 자료는 제외된다.
필자의 열흘간 해외 출장으로 수미씨에게 30일의 시간을 할애하지 못했다. 여행을 가고 싶은 곳은 많았지만 실제 다녀온 곳은 얼마 되지 않는다. 그건 누구나 갖는 생각이다. 하지만 그렇게 한번 여행을 하고 나면 앞으로 가고 싶은 새로운 곳에 대한 동경도 구체화되는 법이다. 이번 여행에서 정작 다녀야 할 곳을 못 다닌 곳도 있겠지만, 이 여행이 기초가 되어 이제 막 시작하는 이수미씨와 같은 DBA에게는 새로운 출발의 기회가 되기를 바란다.

이+달+의+디+스+켓

[ ‘막연할 때’의 문제 접근 방법 ]
뭔가 해야 하는데 막연하다. 이유가 뭘까? 한 번도 해본 적이 없어서 뭘 어찌해야 할지 모르기 때문이라고 생각한다. 또한 사람들이 어떤 결정을 내려야 할 때, 선뜻 결정을 내리지 못하고 우유부단한 이유는 정보 부족이 제일 큰 이유가 아닐까 싶다.
예를 들어 낯선 여행지, 사람의 인적이 드문 곳에서 길을 잃었다고 해보자. 필자는 사막 지역의 달려도달려도 주유소 하나 없는 곳에서 제대로 가는 건지 답답해한 적이 있었다. 해결책은 누군가를 만나서 물어보는 것이었다. 그 누군가는 물론 그 지역에 대해 잘 알고 있어야 한다. 모르는데 엉뚱하게 가르쳐 주는 것만큼 낭패가 없다. 이런 이유로 우리는 컨설팅을 받는다. 혹은 거창하게 컨설팅이라고 안 붙여도 이미 해본 사람들에게 조언을 얻거나 인터넷에서 검색하거나 책을 보거나 하는 이유가 바로 이것이다.
막연하다면 그냥 혼자서 끙끙 앓지 말고 주위에 물어보거나 정보를 더 구하면 될 듯하다. 그러나 때로는 무슨 일을 해야 하는지 명확지 않을 수도 있다. 그럴 때는 일을 지시한 사람과 더 의사소통을 해야 한다. 대충 이건가보다 하고 해서 맞으면 다행이지만(그럴 확률은 그다지 높지 않다), 아니라면 실컷 일하고도 결과는 없는, 혹은 더 나쁜 상황이 된다.
선뜻 결정을 내리지 못하는 것에 대해서도 생각해 보자. 예를 들어 전공을 결정할 때, 그 전공이 무엇인지 그 전공을 하고 났을 때의 진로는 어떻게 되는지 등의 정보가 아주 자세하다면, 전공에 대해 많은 고민을 하지 않고도 결정을 내릴 수 있다. 따라서 일을 할 때는 충분한 정보를 얻는 것이 중요하다고 할 수 있고, 제대로 의사소통을 해서 내가 무얼 해야 하는지 알고 시작하는 것이 중요하다.
문제가 생겼을 때 막연해지곤 한다. 이유는 거기에 대한 지식이 부족해서 정보가 부족해서라고 생각한다. 그래서 know-how와 함께 know-where가 중요하다. 인터넷을 검색해도 넘쳐나는 정보 중에 내가 찾는 것이 어떤 것인지 잘 모르니까. 그래서 우리는 이런저런 공부를 하는 것이다. 단순한 지식의 습득보다 중요한 것이 실제로 어떻게 문제를 해결하는지 어떻게 적용하는 것인지를 아는 것이다. 이번 여행도 그런 맥락에서 접근했으면 한다.

[ 압축을 해라! ]
프로필러는 압축을 하면 무진장 줄어드니 일단 압축을 하도록 하자. 필자가 이메일로 추적 결과를 보내달라고 하면 자주 압축이 안 된 어마어마한 양의 추적결과를 보내온다. 그래서 100MB의 용량을 보내려고 시도하는데, 압축률이 좋아서 압축하면 무척 작아진다. SQL 서버의 백업을 받은 백업 파일도 마찬가지다.

[ 차단과 잠금 옵션의 종류 ]
SQL 서버는 한 트랜잭션이 X(배타) 잠금을 걸었을 때, 다른 트랜잭션은 S(공유) 잠금을 걸 수 없다. 정확히 말하면, 오라클과 달리 SQL 서버에는 before image, after image라는 것이 현재 버전에서 존재하지 않는다. 따라서 트랜잭션이 진행되면 다른 트랜잭션은 기다리거나(차단당한다), 아니면 dirty read 즉 read uncommitted를 해야 한다. 은행 업무와 같이 특별히 트랜잭션 처리가 필요한 경우라면 몰라도 일반적인 게시판 업무나 웹 업무의 경우는 대부분 dirty read를 해도 된다. 따라서 SQL 서버에서는 가능한 디폴트 잠금 수준인 read committed 수준보다 잠금 수준을 낮춰서 dirty read를 허용할 수 있는 모든 상황에서 잠금 수준을 낮추는 것이 절대적으로 필요하다.

◆ 실제 사례 : 초기 화면이 뜨는 데 30초가 넘어 결국 timeout이 걸리던 유명한 사이트에서는 잠금 힌트의 설정만으로 모든 문제가 해결됐다.

출처 : 마이크로소프트웨어[2004년도 7월호]