5장 - SQL Server 데이터베이스 디자인

5장 - SQL Server 데이터베이스 디자인

주요 항목
소개
SQL Server 보안
SQL Server 성능 최적화
SQL Server 연합을 사용한 수평 확장
확장성을 위한 정보 기술 솔루션 디자인
가용성 향상을 위한 SQL Server 클러스터
요약

참조 아키텍처 가이드

요약

이 장에서는 데이터의 안전한 저장과 액세스, 응용 프로그램 요구에 맞는 데이터 액세스 성능, 예상 사용자 수를 충분히 지원하는 솔루션 확장성, 응용 프로그램의 가용성 요구 사항을 충족하는 데이터베이스와 같이 Microsoft® SQL Server 2000을 기반으로 데이터베이스 시스템을 디자인할 때 해결해야 할 문제에 대해 설명합니다.

소개

대부분의 응용 프로그램에서 데이터를 저장, 검색 및 조작해야 하기 때문에 응용 프로그램 인프라를 디자인할 때의 주된 요소는 바로 데이터 관리 방식입니다. Microsoft® SQL Server 2000은 관계형 데이터를 위해 안전하고 확장 가능한 데이터 저장소 서비스와 다양한 관리 기능 및 고가용성 지원 기능을 제공합니다. 하지만 SQL Server가 "자동으로 최적화"되더라도 Microsoft 인터넷 데이터 센터 솔루션만의 보안, 성능, 확장성 및 가용성 요구 사항을 충족하려면 해당 응용 프로그램에서 몇 가지 사항을 고려해야 합니다. 이 장에서는 이러한 고려 사항에 대해 살펴보고 인터넷 데이터 센터 아키텍처용 SQL Server 기반 솔루션의 디자인 전략에 대해 논의하고자 합니다.

대상 독자

이 장은 안전하고 성능이 우수한 SQL Server 기반 솔루션을 계획하거나 구현하는 작업에 참여하는 모든 이를 대상으로 하며, 특히 다음 데이터베이스 전문가에게 유용합니다.

데이터베이스 관리자
데이터베이스 응용 프로그램 개발자

디자인 시 고려 사항

이 장에 설명된 전략은 모든 응용 프로그램에 적용할 수 있는 전략으로, 응용 프로그램의 성능과 확장성을 최적화하는 데 필요한 투자 영역을 제시하는 일반적인 전략에 지나지 않습니다. 각각의 응용 프로그램마다 수준이 다르므로 성능을 최적화하는 특정 구성 집합을 제시할 수 없습니다. 그 대신, 이 장에 설명된 지침을 사용자 환경과 요구 사항에 맞게 적용해야 합니다.

인적 자원 요구 사항

안전한 고성능/고가용성 데이터 그룹을 디자인하는 데 필요한 인적 자원은 다음과 같습니다.

데이터베이스 시스템 엔지니어(데이터베이스 관리자 및 데이터베이스 개발자)
공유 디스크/네트워크 저장소 관리자
네트워크 관리자
응용 프로그램 구조 설계자

데이터 그룹을 구축하는 데 필요한 인적 자원은 다음과 같습니다.

데이터베이스 시스템 엔지니어 데이터베이스 서버, 데이터베이스 개체 및 데이터베이스 코드를 구축합니다.
응용 프로그램 구조 설계자 분산 응용 프로그램을 디자인합니다.
개발자 데이터 종속 라우팅과 데이터 액세스에 필요한 코드를 작성합니다.
하드웨어 기술자 하드웨어를 조립합니다.

하드웨어 요구 사항

대용량 데이터 그룹에 적용되는 하드웨어 요구 사항은 다음과 같습니다.

서버 당 8개 이상의 CPU를 권장합니다.
필수 요건은 아니지만 서버 간 시스템 영역 네트워크(SAN)를 강력히 권장합니다.

소프트웨어 요구 사항

인터넷 데이터 센터 아키텍처의 확장성에 필요한 소프트웨어는 다음과 같습니다.

Microsoft Windows® 2000 Advanced Server 또는 Windows 2000 Datacenter Server(권장)
SQL Server 2000 Enterprise Edition

시스템 요구 사항

서버와 구성 요소는 Windows 2000 운영 체제와의 호환성에 대한 인증을 받아야 합니다. 제조업체에서 인증한 서버는 다음 웹 사이트에 나와 있는 Windows 2000 하드웨어 호환성 목록(HCL)에 추가됩니다.
http://www.microsoft.com/hcl/

데이터 그룹을 설치하기 전에 데이터베이스 서버와 VLAN 사이의 대역폭에 제한이 없음을 확인해야 합니다.

SQL Server 보안

인터넷 데이터 센터 아키텍처에서는 데이터베이스에 사용자 신용 카드 번호나 암호와 같은 중요한 데이터를 저장하기 때문에 데이터베이스 서버의 데이터를 반드시 보호해야 합니다. 이 절에서는 인터넷 데이터 센터 환경에 설치된 SQL Server 데이터베이스를 보호하는 방법에 대해 설명합니다.

인증 모드

SQL Server 2000은 두 가지 방식의 사용자 인증, 즉 Windows 통합 인증과 SQL Server 로그인을 지원합니다. 기본적으로 SQL Server 2000은 Windows 인증만 지원하지만, 인증 모드를 혼합 인증 모드로 설정하여 Windows 인증과 SQL Server 로그인을 둘 다 지원하도록 서버를 구성할 수 있습니다.

Windows 인증 모드는 암호 암호화, 감사, 보안 정책(예: 최소 암호 길이, 암호 만료)에 대한 Windows 2000 지원 기능을 사용할 수 있기 때문에 일반적으로 혼합 인증 모드보다 보안성이 뛰어납니다. 혼합 모드는 역호환성 용도로 제공되거나 Windows 98 또는 Windows Millennium Edition 운영 체제에 SQL Server 2000을 설치한 경우에만 제공됩니다. 대부분의 응용 프로그램에서는 SQL Server 2000을 Windows 인증 모드로 구성하여 높은 수준의 보안을 제공하는 것이 좋지만, Microsoft BizTalk Server 2000과 같은 일부 응용 프로그램에는 SQL Server 로그인이 필요합니다. 이러한 응용 프로그램을 사용하려면 혼합 인증 모드를 지원하도록 SQL Server를 구성해야 합니다.

시스템 관리 계정

인터넷 데이터 센터 아키텍처 디자인에서는 Windows 그룹 등록을 통해 모든 SQL Server 관리자에게 SQL Server에 대한 액세스 권한을 부여하고 이 그룹을 sysadmin서버 역할의 구성원으로 지정하도록 권장합니다. 하지만 이 방법에는 문제가 따르는데 그것은 Windows 관리자가 해당 Windows 그룹에 사용자를 추가할 수 있기 때문에 누구든지 SQL Server 2000에 대한 sysadmin권한을 부여받을 수 있다 것입니다. Windows 관리자가 SQL Server에 대한 sysadmin 액세스 권한을 할당하지 못하도록 웹 사이트를 디자인하려면 sysadmin의 역할에 개별 Windows 계정만 할당해야 합니다.

어떤 경우든지 인터넷 데이터 센터 아키텍처 디자인에서는 시스템 관리자(SA) SQL Server 로그인 대신에 데이터베이스 백업이나 로그인 관리와 같은 특정 관리 기능의 역할을 사용하여 일상적인 관리 작업을 수행하도록 권장합니다. SA 계정에는 알아내기 힘들고 안전하게 보관해 두었다가 응급 시에만 액세스할 수 있는 암호를 할당해야 합니다.

인터넷 데이터 센터 아키텍처 디자인에서 SQL Server 2000을 Windows 인증 모드로 실행하면 신뢰할 수 있는 연결만 허용되기 때문에 SA계정으로 로그온할 수 없습니다. 하지만 앞서 설명한 것처럼 BizTalk Server와 같은 특정 응용 프로그램에는 표준 SQL Server 로그인에 대한 지원이 필요하기 때문에 이러한 응용 프로그램이 인터넷 데이터 센터 환경에 통합되어 있으면 혼합 인증을 사용해야 합니다.

참고: 인터넷 데이터 센터 아키텍처 디자인에서는 SQL Server 2000을 Windows 인증 모드로 실행하고 있을 때 SA 계정으로 로그온할 수 없어도 SA 암호를 할당하도록 권장하는데, 이는 레지스트리를 약간만 변경해도 보안 모드가 Windows 인증 모드에서 혼합 모드로 변경될 수 있기 때문입니다. 기본 설치에서처럼 SA 암호가 비어 있으면 침입자나 Windows 관리자가 서버에 무단으로 액세스할 수 있습니다. 이러한 공격을 줄이는 방법에 대한 자세한 내용은 이 문서의 뒷부분에 설명된 "레지스트리 고려 사항"을 참조하십시오.

서비스 계정 고려 사항

SQL Server 2000에서 실행하는 세 가지 Windows 서비스는 MSSQLServer, SQLServerAgent 및 Microsoft Search 서비스가 있습니다.

로컬 시스템 계정, 로컬 사용자 계정 또는 도메인 사용자 계정을 사용하도록 SQL Server(MSSQLServer라고도 함)와 SQLServerAgent 서비스를 구성할 수 있습니다. 이 때 사용되는 계정은 SQL Server 2000에 필요한 기능에 따라 선택되며, 두 서비스 모두 같은 Windows 사용자 계정을 사용할 수 있습니다. SQL Server 실행 서버가 복제용으로 구성되어 있지 않고 네트워크 리소스에 액세스하지 않아도 될 경우에는 로컬 시스템 계정을 사용할 수 있습니다.
Microsoft Search 서비스는 전체 텍스트 검색 기능을 제공합니다. 인터넷 데이터 센터 아키텍처에서 Microsoft Search 서비스는 항상 로컬 시스템 계정을 사용하도록 구성됩니다.

SQL Server 2000 작업을 제대로 수행하려면 다음 항목에 대한 사용 권한을 로컬 시스템 계정으로 설정해야 합니다. 이러한 사용 권한은 설치 시 자동으로 할당됩니다.

SQL Server 디렉터리(기본값: \Program Files\Microsoft SQL Server\MSSQL)에 대한 모든 권한
모든 .mdf, .ndf, .ldf 데이터베이스 파일에 대한 모든 권한
다음 위치와 그 아래에 있는 레지스트리 키에 대한 모든 권한

HKEY_LOCAL_MACHINE \Software
Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE \System
CurrentControlset\Services\MSSQLServer

명명된 인스턴스의 경우

HKEY_LOCAL_MACHINE \Software
Microsoft\Microsoft SQL Server\InstanceName
HKEY_LOCAL_MACHINE \System
CurrentControlset\Services\MSSQL$InstanceName

로컬 사용자 계정에는 로컬 시스템 계정과 같은 요구 사항뿐 아니라 설치 프로그램이 기본적으로 부여하는 다음 요구 사항도 추가로 적용됩니다.

사용자 계정에는 서비스로 로그온 사용 권한을 부여해야 합니다.

도메인 사용자 계정 옵션은 다중 서버 환경에서 융통성을 최대한 높이면서 관리 오버헤드를 줄입니다. 도메인 사용자 계정을 사용할 때만 가능한 기능 중 몇 가지는 다음과 같습니다.

복제
네트워크 드라이브를 통한 백업 및 복원
원격 데이터 원본을 포함하는 유형이 다른 조인 수행
SQL Server Agent 메일 기능 및 SQLMail

파일 시스템

인터넷 데이터 센터 아키텍처 디자인에서는 모든 데이터베이스의 데이터와 로그 파일에 NTFS 파일 사용 권한을 적용하도록 권장합니다. SQL Server 2000에 사용되는 사용자 계정에는 데이터베이스 파일에 대한 "모든 권한" 사용 권한이 있어야 합니다.

실행 파일과 동적 연결 라이브러리(DLL)를 비롯한 모든 SQL Server 2000 파일은 사용자가 조작할 수 없게 구성됩니다. 이러한 파일에 대한 사용 권한은 SQL Server에 사용되는 사용자 계정, 관리자 그룹, 로컬 시스템 계정에 대한 "모든 권한" 사용 권한을 허용합니다. 다른 사용 권한은 설정되지 않습니다.

레지스트리 고려 사항

실제 서버에 대한 로그온 권한이 있는 사용자의 보안 공격으로부터 SQL Server 2000 설치를 보호하려면 SQL Server 2000을 구성하는 데 사용되는 레지스트리 키에서 다음 Windows 사용 권한을 설정하는 것이 좋습니다. 모든 키는 다음 위치 아래에서 보호됩니다.

HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \MSSQLSERVER(기본 인스턴스의 경우)
HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \MICROSOFT SQL SERVER\INSTANCENAME(명명된 인스턴스의 경우)

이 키에서 모든 사람 그룹 사용 권한을 제거하고 관리자 그룹, 로컬 시스템 계정 또는 SQL Server 서비스 계정에 대한 모든 권한 사용 권한을 추가해야 합니다. 설치 프로그램은 설치 과정에서 선택한 서비스 계정에 대해 이 작업을 자동으로 수행합니다.

SQL Server 관리자가 Windows 관리자의 SQL Server 액세스를 차단하고자 하는 경우에는 레지스트리 키에서 사용 권한을 설정하는 작업이 특히 중요합니다. 또한 이 경우에는 SQL Server 관리자가 레지스트리 키의 소유권을 가져오고 관리자 그룹의 사용 권한을 제거해야 합니다. 그런 다음 SQL Server 서비스 계정에 모든 권한 사용 권한을 추가해야 합니다. 그러면 SQL Server 관리자가 Windows 관리자의 액세스를 차단하지는 못해도 Windows 관리자가 보안을 위반한 시기는 알 수 있습니다. Windows 관리자는 언제든지 소유권을 가져올 수는 있지만 할당할 수는 없습니다. Windows 관리자의 SQL Server 액세스에 대한 자세한 내용은 이 문서의 앞부분에 설명된 "시스템 관리 계정"을 참조하십시오.

감사 고려 사항

SQL Server 2000에서는 Windows 이벤트 로그를 통해 서버 로그온을 감사할 수 있습니다. SQL 엔터프라이즈 관리자나 xp_loginconfig 확장 저장 프로시저를 사용하여 감사 수준을 설정할 수 있습니다.

설정 가능한 감사 수준은 다음과 같습니다.

없음. 감사 정보를 로그에 기록하지 않습니다.
성공. 성공한 로그온만 로그에 기록합니다.
실패. 실패한 로그온만 로그에 기록합니다.
모두. 성공한 로그온과 실패한 로그온을 모두 로그에 기록합니다.

실패 설정은 권장되는 최소 감사 설정입니다. 보안이 특히 요구되는 경우에는 성공한 로그온과 실패한 로그온을 비롯하여 모든 로그온을 감사해야 합니다. 감사 정보는 SQL Server 2000의 오류 로그에 기록됩니다.

백업 및 복원 고려 사항

SQL Server 데이터를 백업하는 방법 중 하나는 SQL Server 2000을 사용하여 데이터 파일을 백업한 다음 Windows 2000 백업 프로그램을 사용하여 해당 파일을 필요한 미디어에 백업하는 것입니다. 이 방법을 사용할 때는 데이터 자체가 암호화되지 않기 때문에 백업 미디어를 물리적으로 보호한 상태로 다른 장소에 보관해야 합니다. 백업 데이터 파일은 일반 사용자가 액세스하지 못하게 디렉터리 사용 권한을 설정한 상태로 NTFS 파티션에 넣어야 합니다.

Windows 게스트 계정

SQL Server 2000을 Windows 인증 모드로 실행할 때는 모든 클라이언트 인증이 Windows에 따라 수행됩니다. Windows 게스트 계정은 보안 문제를 야기할 수 있으므로 사용할 수 없게 설정해야 합니다.

물리적 액세스 고려 사항

물리적 액세스는 최대한 억제해야 합니다. 승인되지 않은 물리적 액세스의 위험 중 하나는 침입자가 플로피 디스크로 서버를 시작한 다음 Windows 2000 파일 시스템에 액세스하는 것입니다. 업무상 중요한 프로덕션 데이터베이스 서버는 물리적으로 보호해야 합니다.

데이터베이스 계정 별칭 지정

SQL Server 2000은 역호환성 용도로 데이터베이스 내의 사용자 계정 별칭 지정을 지원합니다. 따라서 여러 로그인에서 하나의 데이터베이스 사용자 계정을 공유할 수 있기 때문에 사용자 수가 많아도 데이터베이스 개체 사용 권한을 관리하기가 쉽습니다. SQL Server 2000 데이터베이스 솔루션에서는 별칭을 지정할 필요 없이 역할을 사용하는 것이 좋습니다. 역할은 별칭과 비슷한 관리 이점을 제공하지만 별칭보다 훨씬 강력합니다.

Kerberos 및 위임

Kerberos는 Windows 2000 네트워크의 주 인증 메커니즘이고, 위임은 여러 컴퓨터와 응용 프로그램에 보안 자격 증명을 전달하는 기능입니다. 컴퓨터 간의 각 홉에서는 사용자의 보안 자격 증명이 유지됩니다. SQL Server 2000은 위임된 Kerberos 티켓을 받아들이는 기능, Windows 2000 도메인 컨트롤러 및 Windows 2000 Active Directory 서비스를 사용하여 받아들인 티켓을 재위임하는 기능 등 Kerberos를 완벽하게 지원합니다(Windows 2000 운영 체제에서 실행하는 경우).

네트워크 소통량 암호화

SQL Server 2000은 네트워크를 통해 클라이언트와 서버 시스템 사이를 이동하는 데이터와 기타 네트워크 소통량에 대한 암호화를 지원합니다. 암호화 수준은 SQL Server용으로 설치한 인증서의 암호화 기능과 클라이언트/서버의 암호화 기능에 따라 달라집니다.

SQL Server용으로 선택한 인증서를 SQLServer1.Redmond.Microsoft.com과 같이 정규화된 도메인 이름 시스템(DNS) 서버 이름의 형태로 서버 이름에 할당해야 합니다. 인증서는 서버를 인증하는 데 유효해야 합니다. 인증서를 설치하려면 SQL Server 서비스 계정으로 SQL Server에 로그온하고, 내부 인증 기관이나 신뢰할 수 있는 서드 파티 공급자가 제공하는 인증서를 구한 다음, 해당 인증서를 가져온 서버의 기본 위치에서 설치해야 합니다.

파일 시스템 암호화

SQL Server 2000 데이터 파일은 Windows 2000 파일 시스템 암호화(EFS)로 보호할 수 있습니다. 데이터 파일은 SQL Server의 서비스 계정을 사용하여 암호화됩니다.

이 서비스 계정을 변경해야 할 경우에는 파일의 암호를 해독하고 SQL Server 서비스의 서비스 계정을 변경한 다음 새 서비스 계정으로 해당 파일을 다시 암호화해야 합니다. 이러한 변경 작업 중 하나라도 수행하지 않으면 이전 서비스 계정의 자격 증명으로 암호화된 파일의 암호를 해독할 수 없기 때문에 SQL Server를 시작하지 못할 수 있습니다.

SQL Server 성능 최적화

SQL Server 2000의 성능에 영향을 주는 주된 요소에는 다음 네 가지가 있습니다.

SQL Server 2000 구성
물리적 데이터 모델
논리적 데이터 모델
SQL 문

이 절에서는 위의 네 요소 각각에 대해 논의하고 SQL Server 응용 프로그램의 성능을 최적화할 수 있는 가장 좋은 방법에 대해 살펴봅니다.

SQL Server 2000 구성

SQL Server 응용 프로그램의 성능을 가장 확실하게 향상시키는 방법 중 하나는 서버 자체의 구성을 최적화하는 것입니다. 다방면에서 SQL Server 2000은 작업과 리소스 사용률에 따라 구성 설정을 동적으로 조정하는 "자동 조정 서버"로 설명될 수 있습니다. 하지만 응용 프로그램의 성능을 크게 향상시킬 수 있는 설치 및 구성 관련 결정 사항이 매우 많습니다.

디스크 시스템

프로덕션 SQL Server 2000 데이터베이스는 일반적으로 내부 디스크에 설치하지 말아야 합니다. 각 데이터베이스를 자체 외부 디스크 하위 시스템에 설치하면 데이터 읽기/쓰기 작업의 성능을 향상시킬 수 있습니다. 설치한 디스크 수가 많을수록 오류가 발생할 확률이 높기 때문에 하드웨어 오류로 인한 데이터 손실을 막으려면 모든 환경을 RAID(Redundant Array of Inexpensive Disks) 시스템에서 실행해야 합니다. 그러면 내결함성이 충분히 제공되고 하드웨어 오류로 인한 데이터 손실 위험이 줄어듭니다.

Windows 2000에서 지원하는 세 가지 RAID 수준은 다음과 같습니다.

RAID 0. 실제 드라이브에 데이터를 고르게 분산합니다. 각각의 분산된 볼륨은 스트라이프 집합이나 저장소라고 합니다. RAID 0을 사용하면 읽기/쓰기 성능은 크게 향상될 수 있지만 하드웨어 오류에 대한 보호 기능은 제공되지 않습니다.
RAID 1. 디스크 미러링이나 디스크 중복화(각 디스크마다 전용 컨트롤러가 있는 경우)라고도 하며 드라이브 두 개에 데이터를 씁니다. 미러 집합의 각 파티션에 정확히 동일한 데이터 복제본을 만들어 하드웨어 오류를 방지합니다. 설치 과정에서 데이터를 읽는 미러를 선택할 수 있으면 입/출력 채널 사용을 조정하여 성능을 향상시킬 수 있지만, 이는 일반적인 방법이 아닙니다.
RAID 5. 패리티 있는 디스크 스트라이핑이라고도 하며 RAID 0과 같은 방식으로 셋 이상의 디스크에 데이터를 고르게 분산하지만, 디스크에 쓰여진 데이터에 대한 복구 정보를 계산하여 내결함성을 구현합니다. 이러한 복구 정보, 즉 패리티는 모든 디스크에 고르게 분산되기 때문에 하나의 디스크에 오류가 발생한 경우 스프라이프 집합의 다른 디스크에서 패리티 정보를 참조하여 오류가 발생한 데이터를 다시 만들 수 있습니다. RAID 5를 사용하면 단일 디스크 드라이브 오류에 대한 보호 기능은 제공되지만 쓰기 성능은 저하됩니다.

데이터베이스 환경에서 가장 많이 사용하는 RAID 수준은 RAID 0과 1을 조합한 RAID 10 또는 RAID 0/1입니다.

성능 향상을 위해서는 다음 SQL Server 파일을 여러 저장소 집합에 분산해야 합니다.

트랜잭션 로그 파일
Tempdb 파일
데이터 파일
인덱스 파일

빠른 쓰기 입/출력 속도로 인해 성능이 저하될 수 있기 때문에 트랜잭션 로그 파일과 tempdb 파일은 RAID 5가 아닌 RAID 0/1에 저장하는 것이 좋습니다. 마찬가지로 데이터 파일과 인덱스 파일도 RAID 0/1 저장소 집합에 저장하는 것이 좋지만, 쓰기 작업이 적은 응용 프로그램인 경우에는 RAID 5에 저장해도 됩니다.

데이터베이스 파일과 저장소 집합의 수를 계산하려면 응용 프로그램의 저장 및 액세스 특성을 고려해야 합니다.

참고: 저장소 구성 방식은 저장소 솔루션마다 다릅니다. 자세한 내용은 해당 인프라의 저장소 솔루션을 참조하십시오.

RAID 수준과 SQL Server에 대한 자세한 내용은 다음 웹 사이트의 MSDN 문서인 Microsoft SQL Server 7.0 Performance Tuning Guide(저자: Henry Lau)를 참조하십시오.
http://msdn.microsoft.com/LIbrary/en-us/dnsql7/html/msdn_sql7perftune.asp

메모리

기본적으로 SQL Server 2000은 서버에서 사용할 수 있는 양만큼 실제 메모리를 할당하지만, 과도한 페이징을 방지하기 위해 충분한 리소스를 운영 체제 몫으로 남겨둡니다. 할당된 메모리는 실행 코드뿐 아니라 SQL Server 메모리 풀에도 사용됩니다. 메모리 풀은 다음 영역으로 구성됩니다.

시스템 수준 데이터 구조
로그 캐시
프로시저 캐시
연결 컨텍스트
버퍼 캐시(데이터 캐시라고도 함)

위의 각 영역에 필요한 메모리 크기는 기본 메모리 구성에 따라 자동으로 결정됩니다. SQL Server는 위의 각 영역에 필요한 메모리를 자동/동적으로 할당하거나 할당 해제하여 각 영역의 성능을 최적화합니다.

다음 매개 변수를 사용하여 할당된 메모리를 수동으로 구성할 수 있습니다.
매개 변수
기능
max server memory (mb)
SQL에서 할당하는 최대 메모리량
min server memory (mb)
SQL에서 할당하는 최소 메모리량
min memory per query (KB)
각 쿼리별로 할당되는 최소 메모리량
index create memory (KB)
인덱스를 만드는 동안 정렬 작업에 사용되는 메모리량

기본 메모리 옵션은 일반적으로 변경하지 않는 것이 좋지만, 컴퓨터에서 BizTalk Server와 같은 다른 응용 프로그램을 함께 실행하고 있으면 max server memory 매개 변수를 설정하여 SQL Server에서 할당하는 메모리의 양을 제한할 수 있습니다. 반대로 min server memory 매개 변수를 0이 아닌 값으로 설정하여 데이터 캐시와 같이 SQL에 필요한 (모든) 메모리를 다른 응용 프로그램에서 사용하지 못하게 만들 수 있습니다.

SQL Server 2000은 각 쿼리에 대해 적절한 양의 메모리를 할당합니다. 특히, 해싱이나 정렬 작업이 많은 쿼리에는 많은 양의 메모리를 할당합니다. 따라서 디스크에서 해싱이나 정렬 작업을 수행하지 않는 것이 좋습니다.

쿼리 과정 중 디스크에 수행되는 정렬 작업을 검색하려면 해당 컴퓨터에 대해서만 쿼리를 실행하고 tempdb 파일이 있는 디스크나 RAID 배열에 대한 입/출력 작업을 찾아보십시오. 이러한 작업을 찾아보려면 미리 정의된 입/출력 작업 카운터를 사용하는 Windows 2000 성능 모니터를 사용하십시오. 디스크에 대한 정렬 작업이 수행되고 있다는 쿼리 결과가 나오면 min memory per query 매개 변수를 사용하여 메모리를 추가로 할당할 수 있습니다. min memory per query 매개 변수의 값을 계산하는 절차는 다음과 같습니다.

컴퓨터의 총 메모리를 확인합니다.
현재 컴퓨터에서 실행되고 있는 Microsoft Windows NT®와 다른 응용 프로그램의 메모리를 뺍니다.
메모리 풀에 할당할 메모리를 뺍니다.
동시에 실행할 쿼리 횟수로 나머지 메모리를 나눕니다.

참고: 일반적으로 SQL Server 2000이 메모리의 나머지 모든 구성 요소를 고려하여 각 쿼리의 메모리를 동적으로 할당하기 때문에 이 옵션은 변경하지 않는 것이 좋습니다.

다른 모든 매개 변수와 마찬가지로 index create memory도 자동으로 구성됩니다. 필요한 정렬 작업 때문에 인덱스 만들기 작업이 어렵다고 예상되면 이 값을 높이십시오.

위의 매개 변수를 설정하려면 다음과 같이 sp_configure 저장 프로시저를 사용하여 show advanced options를 활성화해야 합니다.

sp_configure "show advanced options", 1 go

그런 다음 원하는 매개 변수 값을 설정하십시오. 새 값을 적용하려면 RECONFIGURE를 실행해야 합니다.

이제는 다음과 같이 sp_configure 시스템 저장 프로시저를 사용하여 구성 옵션을 설정할 수 있습니다.

sp_configure "min server memory", 32 go

메모리 구성에 대한 자세한 내용은 다음 리소스를 참조하십시오.

SQL Server 온라인 설명서의 "구성 옵션 사양"
Microsoft SQL Server 2000 Performance Tuning Technical Reference

입/출력 작업

입/출력 작업은 가능한 줄여야 합니다. SQL Server 2000에서는 다음 세 경우에 입/출력 작업이 발생합니다.

로깅으로 인한 트랜잭션 로그 쓰기
데이터 캐시에서 수행할 수 없는 요청으로 인한 데이터나 인덱스 파일 읽기/쓰기
디스크 상의 데이터 정렬이나 정기 검사점 작업과 같은 SQL Server 시스템 동작으로 인한 기타 디스크 작업

다음 두 매개 변수는 입/출력 성능에 영향을 줄 수 있습니다.
매개 변수
기능
recovery interval (min)
SQL Server에서 데이터베이스를 복구하는 데 걸리는 최소 시간(분)
max async io
파일 당 해결되지 않은 최대 입/출력 요청 수

recovery interval 매개 변수를 사용하여 검사점 간격에 영향을 줄 수 있습니다. SQL Server 2000을 설치하면 recovery interval 매개 변수가 1분 정도로 설정됩니다. 즉, SQL Server는 기본적으로 복구 시간을 1분 정도로 유지하도록 검사점 수를 계산합니다. 따라서 캐시 플러싱으로 인한 입/출력 작업을 줄이려면 recovery interval 매개 변수의 값을 높여야 하지만, 복구 시간이 늘어난다는 단점이 있습니다.

recovery interval 매개 변수의 값을 높이는 방법 외에 SQL Server에서 Windows 2000과 디스크 컨트롤러로 보내는 비동기 입/출력 요청을 사용하여 입/출력 성능을 향상시킬 수 있습니다. 기본적으로 SQL Server는 파일 당 최대 32개의 해결되지 않은 입/출력 요청을 보내는데, 이는 일반 디스크 컨트롤러에 허용되는 개수입니다. 지능형 컨트롤러에서는 파일 당 33개 이상의 요청을 처리하여 디스크 헤드의 움직임을 줄일 수 있습니다. 하지만 max async io의 값을 높이면 상한선을 넘어선 후 성능이 저하됩니다.

일반적으로 recovery interval 매개 변수의 값을 높이면 전체 복구 시간이 늘어나기 때문에 recovery interval 매개 변수는 변경하지 않는 것이 좋습니다. 하지만 소규모 트랜잭션이 많은 응용 프로그램에서와 같이 검사점 작업으로 인해 입/출력 작업이 많이 발생했다는 시스템 모니터링 결과가 나오면 recovery interval 매개 변수의 값을 높여 성능을 어느 정도 향상시킬 수 있습니다.

위의 매개 변수는 다음과 같이 sp_configure 시스템 저장 프로시저를 사용하여 구성할 수 있습니다.

비동기 입/출력을 조작하는 방법은 다음과 같습니다.

sp_configure "max async IO", <value> Go Reconfigure Go

검사점 간격을 조작하는 방법은 다음과 같습니다.

sp_configure "recovery interval", <value> Go Reconfigure go

입/출력 작업 관리에 대한 자세한 내용은 다음 리소스를 참조하십시오.

SQL Server 온라인 설명서의 "복구 성능"
Deploying Microsoft SQL Server 7.0: "Chapter 5, Hardware Selections and Configurations"

SMP 조정

CPU가 둘 이상이고 모든 프로세서가 하나의 대용량 메모리 영역(RAM)에 액세스하는 시스템을 대칭적 다중 프로세서(SMP) 컴퓨터라고 합니다. SMP는 확장성이 뛰어난 최신 기술입니다. SMP 시스템이 있는 경우, SQL Server 2000은 다중 스레드를 사용하여 여러 CPU에 작업을 분산합니다. 예를 들어, 병렬 쿼리 옵션을 사용하면 SQL Server 2000이 다중 스레드를 사용하여 테이블을 스캔할 수 있게 특정 쿼리를 분리할 수 있습니다. 각 스레드마다 다른 CPU를 사용하여 병렬로 스레드가 처리됩니다. 올바르게 조정된 데이터베이스 인스턴스의 캐시 적중률은 일반적으로 98% 이상이며, 캐시 적중률이 98%보다 낮으면 불충분한 CPU 성능으로 인해 병목 현상이 발생할 수 있습니다. 또한 단일 CPU의 클럭 속도나 CPU 수가 전체 성능에 큰 영향을 줄 수 있습니다.

SMP 동작을 제어하는 데 사용할 수 있는 구성 매개 변수는 다음과 같습니다.
매개 변수
기능
affinity mask
프로세서와 스레드 간의 연결
max worker threads
SQL Server에 사용할 수 있는 작업자 스레드 수
max degree of paralleLIsm
병렬 계획 실행에서 사용할 프로세서 수
cost of threshold for paralleLIsm
병렬 계획을 만들고 실행하기 위한 임계값

affinity mask 매개 변수를 사용하면 SQL Server 2000 스레드에서 사용하지 않도록 특정 프로세서를 제외할 수 있습니다. 따라서 각 스레드마다 다른 프로세서에 호핑하는 동작이 줄어들거나 없어지는 추가 효과가 발생하여 프로세서 캐시의 효과를 높일 수 있습니다. 기본값인 0을 사용하면 SQL Server 2000에서 사용 가능한 CPU를 모두 사용합니다. 예를 들어, 컴퓨터에서 다른 응용 프로그램이 실행되고 있으면 SQL Server 2000 스레드에 특정 프로세서를 지정하여 사용할 수 있습니다.

max worker threads 매개 변수의 기본값인 255는 대부분의 응용 프로그램에 허용되는 값입니다. 동시 연결 수가 256개 이상이면 SQL Server 2000이 스레드 풀링을 사용합니다. 즉, max worker threads 매개 변수의 값을 높이지 않아도 사용 가능한 다음 작업자 스레드에서 요청을 처리할 수 있습니다.

max degree of paralleLIsm 매개 변수는 단일 쿼리에 할당되는 스레드 수를 결정합니다. 이는 쿼리의 병렬 처리 수준이라고도 합니다. max degree of paralleLIsm 매개 변수는 사용 가능한 CPU 수를 고려하기 때문에 affinity mask 매개 변수 사용에 따른 제한을 받습니다.

병렬 쿼리를 사용하면 SQL Server 2000에서 이 쿼리의 분리 비용과 중간 결과의 재결합 비용을 고려해야 합니다. SQL Server 2000은 쿼리를 비병렬로 실행하는 데 드는 예상 비용과 cost of threshold for paralleLIsm 매개 변수에 지정된 값을 비교한 다음 예상 비용이 더 높으면 SMP 시스템에 대한 쿼리를 병렬로 실행합니다. 즉, cost of threshold for paralleLIsm 매개 변수는 SQL Server 2000이 별다른 성능 이익을 얻을 수 없는 간단한 쿼리를 병렬로 실행하지 못하게 합니다.

위의 매개 변수 중에서 affinity mask 매개 변수를 제외한 나머지 매개 변수는 변경하지 않는 것이 좋습니다. affinity mask 매개 변수는 해당 컴퓨터에 다른 응용 프로그램이 실행 중이거나 엄청난 작업 부하가 있는 경우에만 변경해야 합니다. 또한 이 경우에는 CPU 사용률을 프로세스별로 모니터링하여 SQL Server 2000 스레드를 사용률이 낮거나 전혀 사용되지 않는 CPU에 지정해야 합니다. CPU 사용률을 모니터링하려면 Windows 2000 성능 모니터를 사용하십시오. 프로세서 개체에서 % User Time 카운터를 선택하고 모든 프로세서를 선택하십시오.

위의 매개 변수를 설정하려면 sp_configure 저장 프로시저를 사용하여 show advanced options을 1로 설정하십시오.

또한 엔터프라이즈 관리자를 사용하여 affinity mask, max degree of paralleLIsm 및 cost threshold for paralleLIsm 매개 변수를 설정할 수 있습니다.

SMP 서버의 쿼리 처리에 대한 자세한 내용은 다음 리소스를 참조하십시오.

MS SQL Server 7.0 쿼리 프로세서(저자: Goetz Graefe, Jim Ewel, Cesar GaLIndo-Legaria)
병렬 쿼리: SQL Server 온라인 설명서의 "Max Degree of ParalleLIsm 옵션" 및 "Cost Threshold for ParalleLIsm 옵션"
프로세서에 스레드 할당: SQL Server 온라인 설명서의 "Affinity Mask 옵션"
작업자 스레드 수: SQL Server 온라인 설명서의 "Max Worker Threads 옵션"

물리적 데이터 모델

성능에 영향을 줄 수 있는 또 다른 요소는 데이터베이스의 물리적 데이터 모델입니다. 데이터베이스 개체의 실제 저장소를 디자인하면서 선택하는 사항은 데이터베이스 성능에 커다란 영향을 줄 수 있습니다.

물리적 데이터 모델의 개념

물리적 데이터 모델에는 응용 프로그램의 구성 요소를 변경하지 않고도 수정할 수 있는 데이터베이스 모델의 모든 디자인 요소가 포함됩니다. 예를 들어, 특정 열에서 새 인덱스를 만든 후 SQL 문을 수정할 필요가 없습니다.

SQL Server 2000은 파일 그룹을 사용하여 여러 디스크에 테이블이나 인덱스 데이터를 분산합니다. 테이블이나 인덱스와 같은 데이터베이스 개체는 하나의 파일 그룹에 속합니다. 파일 그룹에는 하나 이상의 실제 파일이 있으며, 둘 이상의 파일이 있는 파일 그룹을 만들 때는 파일 크기에 비례하여 각 파일의 공간을 할당하는 비례 채우기 전략이 사용됩니다. 예를 들어, file1의 빈 공간이 600MB이고 file2의 빈 공간이 100MB이면 file1에는 여섯 개의 페이지 익스텐트가 할당되고 file2에는 한 개의 페이지 익스텐트가 할당됩니다.

응용 프로그램의 데이터 모델은 주로 단일 데이터베이스에 저장됩니다. 일반적으로 데이터는 데이터베이스에 속해 있는 파일과 파일 그룹을 사용하여 배치됩니다. 다음 경우에는 분리된 데이터베이스나 분리된 인스턴스가 대개 단일 응용 프로그램에만 사용됩니다.

테이블의 액세스 방식이 전혀 다른 경우. 예를 들어, 일부는 응용 프로그램의 OLTP 부분에 속해 있는 테이블이고 나머지는 문제 보고용 테이블인 경우입니다.
서버 인스턴스 간의 로드 균형 조정 트랜잭션을 통해 확장성이 향상되는 경우. 이 솔루션은 파일과 파일 그룹을 사용한 데이터 배치가 충분하지 않을 때 사용할 수 있으며, 간혹 분산 분할된 뷰를 통해 서버 간의 일관성 있는 데이터 뷰를 제공하기도 합니다.
SQL Server 2000 클러스터 솔루션을 사용하는 고가용성 솔루션이 필요한 경우

파일과 파일 그룹을 사용하여 단일 데이터베이스 내에서 데이터를 분산할 때는 다음 사항을 고려해야 합니다.

만들 파일 그룹 수
만들 데이터 파일 수
파일 그룹 당 데이터 파일 수
어떤 저장소 집합이나 디스크에 어떤 데이터 파일을 배치해야 하는가?
어떤 파일 그룹에 어떤 데이터베이스 개체(테이블, 인덱스 등)를 배치해야 하는가?

최적의 구성은 응용 프로그램의 액세스 특성에 따라 결정됩니다. 액세스 방식을 분석하려면 표준적인 사용자 동작이나 중요한 사용자 동작을 보여주는 사용 사례를 살펴보고, 여기서 얻은 정보를 통해 얼마나 많은 파일 그룹과 데이터 및 인덱스 파일을 만들어야 하는지 결정한 다음, 어떤 파일 그룹에 어떤 데이터베이스 개체(테이블, 인덱스, 프로시저, 함수 등)를 배치해야 하는지 결정하십시오.

병렬 테이블 스캔, 병렬 인덱스 스캔 또는 병렬 조인 작업과 같은 SQL Server 2000 병렬 옵션을 사용하려면 파일과 파일 그룹을 추가로 만드는 것이 좋습니다. SQL Server 2000은 드라이브 문자를 사용하여 병렬 실행이 가능한 입/출력 요청을 선택합니다. 기본적으로 SQL Server 2000 응용 프로그램 데이터베이스에는 주 파일 그룹이라는 하나의 파일 그룹만 있는데, 여기에는 데이터베이스 시스템 테이블이 저장됩니다. 각 파일 그룹마다 하나 이상의 파일이 있으며, 각각의 추가 데이터 또는 인덱스 파일마다 다른 드라이브(다른 드라이브 문자)나 다른 RAID 배열을 사용해야 합니다. 결론적으로 각 요청이 다른 디스크나 스트라이프에서 수행되면 저장소 하드웨어가 병렬 쿼리 요청을 최대로 지원합니다.

이 작업에 적용되는 지침은 다음과 같습니다.

파일 그룹을 사용하여 특정 실제 디스크에 개체를 배치합니다.
추가 파일에 사용할 보조 파일 그룹을 하나 이상 만든 다음 이를 기본 파일 그룹으로 설정합니다. 주 파일 그룹은 시스템 테이블을 저장하는 용도로만 사용합니다.
일반적으로 디스크 당 데이터 파일 수는 하나뿐이어야 합니다.
파일 그룹을 사용하여 가능한 많은 디스크에 데이터를 분산합니다.
응용 프로그램에서 자주 액세스하는 데이터는 전용 디스크에 배치합니다.
조인에 함께 사용할 데이터를 여러 파일에 분산합니다.
인덱스를 사용하여 직접 액세스되는 스캔으로부터 순차적 스캔을 분리합니다.
테이블 데이터로부터 클러스터되지 않은 인덱스를 분리합니다.
행 분할을 사용하는 경우에는 각 파티션마다 다른 저장소 집합이나 디스크에 배치합니다.
데이터베이스 테이블의 액세스 방식이 전혀 다른 경우(읽기 중심 대 쓰기 중심)에는 각기 다른 데이터베이스를 사용합니다.

기존 응용 프로그램 데이터베이스에 파일 그룹과 추가 파일을 추가하려면 ALTER DATABASE 명령을 사용하십시오.

create a second file group for the Northwind database alter database Northwind add file group secondary add one data file to the new file group alter database Northwind add file ( name = 'logical_filename', filename = 'c:\Program Files\Microsoft SQL Server\MSSQL\ DATA\physical_filename.NDF', SIZE= required_size ) to file group secondary

그런 다음 파일 그룹의 이름을 지정하여 새 파일 그룹에 새 데이터베이스 개체를 배치하십시오.

create table table_name() on secondary

SQL Server 2000 엔터프라이즈 관리자를 사용하여 위의 작업을 수행할 수도 있습니다. 파일 그룹과 파일을 추가하려면 데이터베이스 속성 아래에서 SQL Server 엔터프라이즈 관리자를 사용하십시오.

파일 및 파일 그룹 사용에 대한 자세한 내용은 다음 리소스를 참조하십시오.

Deploying Microsoft SQL Server 7.0, Notes from the field: 212ff 페이지
SQL Server 온라인 설명서의 "파일 및 파일 그룹"
SQL Server Magazine 2000년 9월호(http://www.sqlmag.com)의 "Querying Distributed Partitioned Views"(저자: Kalen Delaney, Itzik Ben-Gan)

데이터 배치 및 인덱싱

SQL Server 2000에서 데이터에 액세스하는 방법에는 두 가지 방법, 즉 전체 테이블을 순차적으로 스캔하는 방법과 인덱스를 사용하여 필요한 데이터가 있는 페이지를 직접 찾는 방법이 있습니다. B-트리 구조의 순서에 따라 인덱싱된 열을 포함하는 보조 중복 데이터 구조의 형태로 인덱스를 정의하면 성능 측면에서 도움이 됩니다. 순차적 스캔 방법을 사용하면 모든 테이블 행을 실제 저장소의 순서에 따라 읽습니다. 또한 인덱스를 사용하여 선택 기준에 맞는 테이블 행에만 액세스하는 것도 가능합니다. SQL Server 2000에서 사용하는 두 인덱스 전략은 다음과 같습니다.

클러스터되지 않은 인덱스
클러스터된 인덱스

두 인덱스 전략 간의 주된 차이는 클러스터된 인덱스의 경우 테이블 데이터를 정렬하여 클러스터된 인덱스의 순서에 따라 저장한다는 점입니다. 클러스터된 인덱스는 테이블 데이터와 인덱스 잎 수준 데이터를 함께 저장하여 성능을 향상시킵니다. 따라서 SQL Server 2000은 클러스터된 인덱스 잎 노드의 항목을 읽을 때마다 테이블의 데이터 행을 동시에 읽습니다. 테이블 당 클러스터된 인덱스는 한 번에 하나씩만 허용되며, 테이블 데이터의 클러스터된 인덱스를 여러 파일 그룹으로 분리할 수는 없습니다.

그 반면, 클러스터되지 않은 인덱스는 잎 수준에서 고유 식별자(rowid)만 저장합니다. 결론적으로 클러스터되지 않은 인덱스의 경우 테이블 데이터를 검색하려면 추가 입/출력이 필요합니다.

클러스터된 인덱스와 클러스터되지 않은 인덱스가 같은 테이블에 있으면 클러스터된 인덱스의 단점이 명확해집니다. 클러스터되지 않은 인덱스는 테이블 행을 직접 가리키지 않지만 클러스터된 인덱스 값을 테이블 행에 대한 포인터로 사용합니다. 따라서 클러스터되지 않은 인덱스를 통과하는 읽기 작업은 클러스터되지 않은 인덱스의 B-트리를 내려가서 잎 수준의 값을 찾은 다음 클러스터된 인덱스의 B-트리를 내려가야 합니다. 또한 클러스터된 인덱스 열을 업데이트하면 업데이트된 행이 클러스터된 인덱스의 정렬 순서에 따라 배치되므로 행이 이동합니다.

응용 프로그램의 읽기/쓰기 특정 작업에 유용하도록 인덱스 수를 최소화하여 인덱스 계획을 세우십시오. 인덱스 수가 너무 많으면 실행 계획안을 만들고 평가하는 시간이 너무 늘어날 뿐 아니라 쓰기 성능도 저하됩니다. WHERE 조건자의 제한을 받으면서 선택도가 높은 쿼리의 열에서는 클러스터되지 않은 인덱스를 만들어야 합니다.

다음 경우에는 클러스터된 인덱스가 작업 속도를 높입니다.

정렬된 결과나 정렬된 중간 결과를 필요로 하는 쿼리(예: 조인 작업)
하나 이상의 고정된 간격을 반입하는 쿼리. 이러한 쿼리의 WHERE 절에는 대개 BETWEEN 조건자가 포함됩니다. 클러스터된 인덱스로 정렬된 테이블 행은 클러스터된 인덱스의 정렬 순서에 따라 배치됩니다. 결론적으로 쿼리는 입/출력 읽기 횟수를 최소화하는 값 범위를 선택합니다.

클러스터된 인덱스는 다른 인덱스의 작업 속도를 저하시킵니다. 하나의 테이블에서 클러스터된 인덱스와 클러스터되지 않은 인덱스를 함께 사용할 때는 다음 사항에 주의해야 합니다.

클러스터되지 않은 인덱스에 대한 액세스와 통과 횟수가 많으면 실제 행을 검색하기 위해 클러스터된 인덱스도 매번 통과됩니다.
대규모 열에서 클러스터된 인덱스를 만들거나 둘 이상의 열이 있는 인덱스를 만들면 B-트리 인덱스 구조가 복잡해집니다. 데이터에 빠르게 액세스하려면 클러스터된 인덱스를 가능한 간단하게 유지해야 합니다.
자주 업데이트되는 열에서 클러스터된 인덱스를 사용하면 대규모 페이지 오버플로가 발생하여 다시 인덱싱해야 할 수도 있습니다.

인덱스가 필요하면 항상 클러스터되지 않은 인덱스를 먼저 사용하십시오. WHERE 조건자와 SELECT 목록의 열을 조사하십시오. WHERE 조건자의 모든 열과 SELECT 목록의 열이 인덱스에 포함되어 있으면 포함 인덱스 작업이 가능합니다. 단, 복합 인덱스가 사용될 때는 WHERE 조건자에서 가장 왼쪽에 있는 인덱스 열의 값을 지정해야 한다는 전제 조건이 따릅니다. 그렇지 않으면 SQL Server 2000이 이 인덱스를 고려하지 않습니다.

쿼리 분석기를 사용하여 자신이 정의한 인덱스가 특정 쿼리에 사용되는지 모니터링하십시오. 평가할 쿼리를 쿼리 입력 창에 복사하고 "예상 실행 계획 표시" 바로 가기를 누르거나 CtrL-L을 누르십시오. 그러면 쿼리 실행 계획을 그래픽 형식으로 보여주는 창이 나타납니다. 쿼리가 실제로 실행되지 않지만 리소스 요구 사항에 대한 예상치가 표시됩니다.

예를 들어, Customer 테이블과 Orders 테이블 간의 관계를 마스터와 세부 사항으로 지정하여 고객 주문을 저장하는 응용 프로그램을 가정해 봅니다. 다음 SQL 문 예제에서는 고객 ID가 2020인 고객의 주문을 선택합니다.

select ct.cust_name, od.ord_item_id, od.ord_it_name from customer ct, orders od where ct.cust_id = 2020 and ct.cust_id = od.cust_id

그림 1과 같이 쿼리 분석기의 쿼리 창을 사용하여 인덱스가 적절히 사용되는지 추적할 수 있습니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 1 인덱스를 사용하지 않는 쿼리 실행 계획

그림 1의 그래픽 출력은 테이블 스캔 두 개와 중첩 루프 조인 작업 한 개를 차례로 보여줍니다. 그림 2와 같이 WHERE 조건자의 제한을 지원하는 인덱스를 두 개 만들어 이 쿼리의 성능을 향상시킬 수 있습니다.

create unique clustered index idx_cust_pk on customer(cust_id) create unique clustered index idx_ord_pk on orders(cust_id, ord_item_id)

그림 2와 같이 쿼리 분석기를 한번 더 사용하여 쿼리에서 새 인덱스를 사용하도록 만들 수 있습니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 2 인덱스를 사용하는 쿼리 실행 계획

이제는 두 인덱스가 모두 쿼리를 지원하는 데 사용됩니다.

인덱스와 쿼리 실행 계획에 대한 자세한 내용은 다음 리소스를 참조하십시오.

Deploying Microsoft SQL Server 7.0, "Planning Indexes": 234ff 페이지
SQL Server 온라인 설명서의 "SQL 쿼리 분석기를 사용하여 실행 계획을 그래픽으로 표시"

페이지의 빈 공간 최적화 및 페이지 분리

데이터는 두 데이터베이스 개체, 즉 테이블과 인덱스에 저장됩니다. SQL Server 2000 테이블의 데이터는 순차적으로 정렬되지 않은 메모리 구조나 정렬된 메모리 구조에 저장됩니다. 순차적으로 정렬되지 않은 메모리 구조는 힙 테이블이라고 하며 정렬된 메모리 구조는 클러스터된 테이블이라고 합니다. 두 메모리 구조 간의 주된 차이는 힙 테이블의 경우 행이 무작위 순서로 저장되고 페이지만 순차적으로 연결되는 반면, 클러스터된 테이블의 경우 행이 클러스터된 인덱스의 열에 따라 정렬된다는 점입니다. 따라서 새 행의 위치는 인덱스 값을 사용하여 삽입 과정에서 계산된 다음 새 행이 해당 페이지에 저장됩니다. 기본적으로 각각의 데이터와 잎 수준 인덱스 페이지는 완전히 채워집니다. 나중에 테이블과 그 인덱스가 커지면 페이지 분리와 B-트리 균형 재조정의 위험을 감수해야 합니다. 이는 테이블이 원래 값보다 긴 행으로 업데이트되는 경우에도 마찬가지입니다. 페이지 분리는 입/출력 성능을 저하시킬 수 있습니다. 페이지 분리를 방지하려면 다음 매개 변수를 사용하십시오.
매개 변수
기능
Fillfactor
각 페이지에 남겨둘 빈 공간
Pad_index
중간 수준의 인덱스에서 각 페이지(노드)에 열어둘 공간을 지정합니다.

fillfactor 매개 변수는 테이블 데이터 페이지와 인덱스 잎 수준 페이지에 영향을 주는 반면, pad_index 매개 변수는 B-트리 인덱스의 잎 수준(노드) 이외의 것에 영향을 줍니다. fillfactor 매개 변수는 항상 pad_index 매개 변수와 함께 사용해야 하는데, 이는 pad_index 매개 변수에서 fillfactor 값을 사용하기 때문입니다.

입/출력 성능을 최적화하려면 페이지의 빈 공간을 최소화하여 전체 데이터베이스 크기를 줄이는 작업과 fillfactor를 적용하여 페이지 분리를 방지하는 작업 간의 균형을 조정해야 합니다.

데이터 웨어하우스 응용 프로그램과 같은 읽기 전용 응용 프로그램에서는 기본값을 변경하지 않는 것이 좋습니다. 데이터 웨어하우스 응용 프로그램은 점진적으로 로드됩니다. 즉, 기본 키 값이 낮은 값에서 높은 값의 순서로 삽입되면서 페이지 분리를 방지합니다.

인덱스, 클러스터된 테이블 또는 원래 행보다 긴 행으로의 업데이트와 같이 정렬된 메모리 구조 내에 삽입이 예상될 때는 fillfactor와 pad_index를 100% 미만의 값으로 낮추는 것이 좋습니다.

fillfactor 옵션은 CREATE table이나 CREATE INDEX 문에, pad_index 옵션은 CREATE INDEX 문에 사용됩니다.

다음 T-SQL 문으로 테이블을 만듭니다.

CREATE table(.) [WITH FILLFACTOR = value] [ON file_group]
다음 T-SQL 문으로 클러스터되지 않은 인덱스를 만듭니다.
CREATE [UNIQUE] INDEX index_name ON table_name (column_name,..) [PAD_INDEX] [WITH FILLFACTOR = value] [ON file_group]

다음 T-SQL 문으로 클러스터된 인덱스를 만듭니다.

CREATE [UNIQUE] CLUSTERED INDEX index_name ON table_name (column_name,..) [PAD_INDEX] [WITH FILLFACTOR = value] [ON file_group]

각각의 PRIMARY KEY 제약 조건마다 기본값을 갖는 클러스터된 고유 인덱스를 만든다는 점에 유의하십시오. fillfactor 매개 변수와 같이 기본값 이외의 값은 PRIMARY KEY 제약 조건을 만들 때 지정해야 합니다.

페이지 분리를 모니터링하려면 Windows 2000 성능 모니터를 사용하고 Access Method 개체에서 Page SpLItting 카운터를 선택하십시오.

쿼리 창에서 다음 명령문을 사용하여 테이블과 인덱스 조각화를 자세히 표시하십시오. 키 표시기는 스캔 밀도를, 100 미만의 값은 조각화 비율을 나타냅니다.

DBCC SHOWCONTIG (table_name) [WITH ALL_INDEXES]

잎 수준의 조각화는 DBCC INDEXDEFRAG 문을 사용하여 온라인으로 해결할 수 있습니다. 단, 이 재구성 방법은 다음 오프라인 방법보다 효과적이지 않습니다.

페이지 분리를 해결하려면 다음과 같이 DROP_EXISTING 절을 사용하여 인덱스를 다시 만드십시오.

CREATE [UNIQUE] [CLUSTERED] INDEX index_name ON table_name (column_name,..) [DROP_EXISTING] [PAD_INDEX] [WITH FILLFACTOR = value] [ON file_group]

예를 들어, 고객의 주문을 수집하는 응용 프로그램을 가정해 보면 주문이 다음 테이블에 삽입됩니다.

CREATE table orders ( customer_id int not null, item_id int not null, order_date datetime not null, amount int, .)

이제, 이 테이블에 대해 쿼리 분석을 실행하니 읽기 성능을 향상시키려면 열(customer_id, item_id)에 고유하지 않은 클러스터된 인덱스를 정의해야 된다는 결과를 얻었다고 가정해 봅니다. 이 경우에는 새 삽입이 발생해도 customer_id와 item_id가 순차적으로 증가하지 않기 때문에 페이지 분리가 발생할 수 있습니다. 결론적으로 SQL Server 2000는 클러스터된 인덱스의 정의된 정렬 순서에 따라 새 행을 삽입해야 합니다. 페이지 분리를 방지하기 위해 fillfactor와 pad_index 매개 변수의 값을 100 미만으로 지정하여 클러스터된 인덱스와 테이블을 만들 수 있습니다. fillfactor 매개 변수에 대해 기본적으로 가정할 사항은 이 응용 프로그램에서 일정한 시간 간격 내에 일정한 수의 주문을 내는 활성 고객과 품목의 비율입니다. 이 비율이 낮을수록 fillfactor 매개 변수의 값이 낮아집니다.

다음 CREATE INDEX 문을 사용하여 fillfactor가 75%인 인덱스를 만들 수 있습니다.

CREATE CLUSTERED INDEX idx_Orders ON orders (customer_id, item_id) DROP_EXISTING PAD_INDEX WITH FILLFACTOR = 75

참고: 테이블 페이지에서 페이지 분리를 모니터링하는 경우에는 테이블 데이터를 내보낸 후 테이블을 삭제하고, 새 테이블을 만든 다음 테이블 데이터를 다시 가져와야 합니다. 테이블 데이터를 가져왔으면 새 테이블에서 인덱스를 만드십시오.

인덱스 및 페이지 분리 관리에 대한 자세한 내용은 다음 리소스를 참조하십시오.

SQL Server 온라인 설명서의 "힙 인덱스" 및 "클러스터된 인덱스"
SQL Server 온라인 설명서의 "CREATE INDEX" 및 "CREATE table"
SQL Server 온라인 설명서의 "DBCC SHOWCONTIG"
Deploying Microsoft SQL Server 7.0, "Planning Indexes": 234ff 페이지

논리적 데이터 모델

데이터베이스의 논리적 모델은 테이블과 관계를 사용하여 데이터를 논리적으로 구성하는 방법을 결정합니다. 이 디자인 역시 응용 프로그램의 성능에 커다란 영향을 줄 수 있습니다.

분할

분할은 테이블이나 인덱스 데이터를 분리하는 방법입니다. 데이터 행은 각기 다른 물리적 테이블과 인덱스에 배치됩니다. 데이터베이스의 데이터는 두 방법, 즉 열 분할이나 행 분할 중 하나로 분할할 수 있습니다. 이 절에서는 다음 고객-주문 데이터 예제를 사용하여 두 방법을 모두 살펴 볼 것입니다.
ord_id
ord_item_id
ord_it_name
amount
unit_price
cust_id
cust_name
cust_first
country
1
5567
Cement
10.9
$120.0
A1230
Miller
Justus
Germany
2
9876
Concrete
4.5
$60.50
B2345
Schulz
Bob
USA
3
3654
Blocks
7
$12.60
C5679
Vogt
Martin
USA
4
1211
Pipes
3
$10.0
A1230
Miller
Justus
Germany
5
5567
Cement
12.5
$60.50
C5679
Vogt
Martin
USA
6
4655
Slump
9
$5.55
A1230
Miller
Justus
Germany
7
1211
Pipes
133
$10.0
A1230
Miller
Justus
Germany

열 분할

데이터베이스 디자인에서 가장 일반적인 분할 방법은 대개 데이터베이스 정규화의 결과로 나타나는 열 분할입니다. 정규화는 의미가 다른 개체를 분리하는 과정으로, 이 과정을 거치면 반복 그룹이나 NULL 값이 제거되기 때문에 데이터가 더욱 간단하게 저장됩니다. 데이터베이스를 정규화하여 전체 테이블 스캔에 대한 입/출력 로드는 낮추고 데이터베이스 적중률은 높일 수 있습니다. 정규화의 유일한 단점은 추가 조인을 수행해야 하기 때문에 두 파티션 모두의 열이 필요한 경우 CPU 사용률과 디스크 입/출력 작업이 늘어난다는 점입니다. 이 경우에는 조인에 참여하는 테이블의 인스턴스를 검색하는 SQL 문의 경과 시간을 모니터링하여 어떤 데이터 모델이 더 빠른지 결정해야 합니다.

위의 고객-주문 데이터는 Orders 테이블과 Customers 테이블에 데이터를 분리하는 방식으로 정규화할 수 있습니다. 두 테이블을 조인하려면 Orders 테이블에 cust_id라는 외래 키 열을 추가해야 합니다.

Orders
ord_id
ord_item_id
ord_it_name
amount
unit_price
cust_id
1
5567
Cement
10.9
$120.0
A1230
2
9876
Concrete
4.5
$60.50
B2345
3
3654
Blocks
7
$12.60
C5679
4
1211
Pipes
3
$10.0
A1230
5
5567
Cement
12.5
$60.50
C5679
6
4655
Aggregate
9
$5.55
A1230
7
1211
Pipes
133
$10.0
A1230

Customers
cust_id
cust_name
cust_first
country
A1230
Miller
Justus
Germany
B2345
Schulz
Bob
USA
C5679
Vogt
Martin
USA

열 분할은 논리적 테이블 수를 늘리기 때문에 응용 프로그램 디자인에 영향을 줍니다. 일정 한도까지는 뷰를 사용하여 클라이언트 응용 프로그램에서 논리적 모델에 대한 변경 사항을 추상화할 수 있습니다. 하지만 뷰를 사용하여 데이터를 수정한 경우 여러 원본 테이블에 수정 사항을 적용하려면 INSTEAD-OF 트리거와 같은 추가 코드가 필요할 수 있습니다.

정규화는 다양한 성능상 이점을 제공합니다. 완전히 정규화된 데이터베이스에서는 데이터가 한 번만 존재하기 때문에 데이터베이스 테이블의 데이터 양이 더 적고 단일 테이블의 데이터를 포함하는 입/출력 작업의 속도가 일반적으로 더 빠릅니다. 데이터베이스에서 중복 데이터를 제거하여 얻을 수 있는 또 다른 이점은 모든 수정 작업(삽입, 업데이트, 삭제)이 한 위치에서만 이루어진다는 점입니다. 예를 들어, 주문 테이블에 고객 이름을 저장할 때 해당 고객이 두 번 이상 주문한 경우 cust-name 열에 대한 모든 업데이트 작업을 여러 번에 걸쳐 수행해야 하지만, 완전히 정규화된 데이터베이스 스키마에서는 모든 업데이트 작업을 한 번에 수행할 수 있습니다.

그 반면, 정규화에도 몇 가지 성능상 단점이 있으며 그 중에서 조인하는 테이블에 가장 큰 영향을 미칩니다. 따라서 완전히 정규화된 데이터베이스 스키마가 최적의 성능을 제공하지 못하는 경우도 있는데, 이는 여러 테이블을 조인하는 쿼리나 대량의 데이터를 계산하는 쿼리의 경우에 특히 그렇습니다. 이런 경우에는 데이터베이스를 비정규화하여 성능을 향상시켜야 합니다.

비정규화 과정에서 사용할 수 있는 기술은 다음과 같습니다.

복제된 열
계산된 값

복제된 열

조인하는 테이블이 시스템 성능을 저하시킬 수 있는데, 이는 가장 많은 시간을 필요로 하는 데이터베이스 작업 중 하나가 바로 조인 작업이기 때문입니다. 조인 작업으로 인해 병목 현상이 발생하면 참조되는 테이블의 열을 복제하여 외래 키를 제거해야 합니다. 예를 들어, 다음 T-SQL은 Customers 테이블과 Orders 테이블 간의 관계가 일반적인 마스터와 세부 사항 관계임을 보여줍니다.

create table customer( cust_id int NOT NULL, cust_name varchar(20), cust_first varchar(20), country varchar(20)) create table orders( ord_id int NOT NULL, ord_item_id int, ord_it_name varchar(20), amount int NOT NULL, unit_price money, cust_id int)

다음 제약 조건을 적용합니다.

alter table customer add primary key (cust_id) alter table orders add primary key (ord_id) alter table orders add foreign key (cust_id) references customer(cust_id)

다음 쿼리는 가격이 매겨진 주문의 합계를 고객과 품목별로 검색합니다.

select ct.cust_name, ct.cust_first, ct.country, od.ord_it_name, sum(od.amount * od.unit_price) from customer ct, orders od where ct.cust_id = od.cust_id group by ct.cust_name, ct.cust_first, ct.country, od.ord_it_name

결과적으로 조인 작업이 발생합니다.

Orders 테이블에서 SELECT 목록의 열과 Customers 테이블의 기본 키를 복제하면 조인 작업을 방지하는 데 도움이 됩니다.

create table orders_customers( ord_id int NOT NULL, ord_item_id int, ord_it_name varchar(20), amount int NOT NULL, unit_price money, cust_id int, cust_id int NOT NULL, cust_name varchar(20), cust_first varchar(20), country varchar(20)};

새 테이블에 대하여 동일한 쿼리는 다음과 같습니다.

select oc.cust_name, oc.cust_first,oc.country,oc.ord_it_name, sum(oc.amount * oc.unit_price) from orders_customers oc group by oc.cust_name, oc.cust_first, oc.country, oc.ord_it_name

비정규화의 단점은 새 주문을 생성하고 삽입할 때마다 해당하는 고객 데이터도 삽입해야 한다는 점입니다. 또한 고객 이름이 변경되어 고객 데이터를 업데이트해야 할 때도 해당하는 모든 고객 행을 업데이트해야 합니다. 그렇지 않으면 데이터베이스의 일관성이 없어집니다. 마지막으로, 삭제된 고객이 있으면 Orders 테이블에서 삭제하거나 해당 항목을 NULL로 설정해야 합니다.

계산된 값

완전히 정규화된 데이터베이스 스키마는 합계나 평균과 같은 계산된 데이터를 저장하지 않습니다. 그 대신, 이러한 집계 데이터는 모든 세부 데이터를 사용하여 실행 시간에 계산됩니다. 쿼리 측면에서 보면 위의 예제와 같이 집계 데이터를 실행 시간에 생성하는 것보다 계산된 값을 테이블에 저장하는 것이 일반적으로 낫습니다. 단, 데이터 중복을 제어하는 추가 기능을 구현해야 한다는 단점이 있습니다.

예를 들어, $10,000 이상 주문한 고객의 정보를 Orders 테이블에 반입하려면 다음과 같이 쿼리를 확장해야 합니다.

select ct.cust_id, od.ord_it_name, sum(od.sale) from customer ct, orders od where ct.cust_id = od.cust_id and(od. amount * od.unit_price) > 10.000 group by ct.cust_id, od.ord_it_name;

이 경우에는 SQL Server 2000이 새 제한을 지원하는 인덱스를 사용할 수 없는데, 이는 새 제한이 두 열의 식(함수)으로 계산되기 때문입니다. 이러한 종류의 제한을 지원하려면 원래의 Orders 테이블에 sale이라는 계산된 열을 추가하십시오. sale 열의 값은 unit_price * amount로 계산됩니다.

Alter table orders add sale AS amount * unit_price

이렇게 하면 계산이 테이블의 일부로 정의되지만, 식은 그림 3의 쿼리 실행 계획과 같이 여전히 실행 시간에 계산됩니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 3 실행 시간에 계산되는 계산된 열

Compute Scalar 노드를 살펴보면 sale 열이 데이터베이스에 영구적으로 저장되는 것이 아니라 실행 시간에 계산된다는 것을 알 수 있습니다. 계산된 열은 SELECT 목록, WHERE 절 및 ORDER BY 절에서 사용할 수 있습니다.

SELECT 성능을 추가로 향상하려면 계산된 열에서 인덱스를 만드십시오. 이 함수 기반 인덱스는 데이터베이스에 함수 결과(sale = amount * unit_price)를 저장합니다. 따라서 쿼리 엔진이 이 액세스 경로를 고려할 수 있습니다. 계산된 열을 인덱싱하려면 해당 열이 확정적이어야 한다는 전제 조건이 따릅니다. 이에 대한 자세한 내용은 SQL Server 온라인 설명서의 "확정적 함수 및 비확정적 함수"를 참조하십시오. 필요한 인덱스를 만드는 명령문은 다음과 같습니다.

create index idx_sale on orders(sale)

그림 4에서 실행 계획은 매출 데이터를 검색할 때 인덱스가 어떻게 사용되는지 보여줍니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 4 인덱싱된 계산된 열

이 쿼리 계획의 노드 수는 이전 쿼리 계획의 노드 수와 같습니다. 그렇지만 이 쿼리 계획에서는 쿼리 엔진이 $10,000보다 많은 매출을 지정하는 WHERE 조건자의 제한을 사용할 수 있기 때문에 응답 시간이 늘어납니다. SQL Server는 이전 실행 계획처럼 Orders 테이블의 모든 행을 일일이 스캔하여 제한에 맞지 않는 행을 모두 제거하는 것이 아니라 새 인덱스인 idx_sale을 사용하여 직접 반입합니다.

일반적으로 데이터베이스 스키마 비정규화는 가능한 사용하지 않는 것이 좋은데, 이는 다음과 같은 몇 가지 단점이 있기 때문입니다.

비정규화를 거치면 데이터베이스 디자인이 크게 수정됩니다.
예상치 못한 업데이트 문제를 방지하는 전략을 구현해야 합니다.

데이터베이스 스키마의 비정규화는 앞 장에 설명된 방법으로 원하는 성능을 얻을 수 없는 경우에만 적용하는 것이 좋습니다.

모든 비휘발성 열, 즉 값이 거의 수정되지 않는 열은 대개 복제하기에 좋은 대상입니다. 휘발성 열을 복제해야 할 경우에는 해당하는 트리거를 만들어 데이터 무결성을 유지하는 것이 좋습니다.

계산된 값은 데이터베이스에 저장되는 것이 아니라 사용자가 선택할 때마다 매번 계산되므로, 원본으로 사용하는 열에 대한 삽입이나 업데이트가 발생할 때 이를 업데이트할 필요가 없습니다.

비정규화 과정은 단계별로 진행하고 세심하게 계획 및 실행해야 합니다. 원하는 성능을 얻을 수 없는 경우에만 비정규화를 실행하여 데이터베이스 성능을 향상하십시오.

정규화 및 비정규화에 대한 자세한 내용은 다음 리소스를 참조하십시오.

Conceptual Database DesignAn Entity-Relationship Approach(저자: Carlo Batini, Stefano Ceri, Shamkant Navathe)
Database ModeLIng and Design(저자: Toby J. Teorey)
A Practical Guide to Logical Data ModeLIng(저자: George Tillmann)
SQL Server 온라인 설명서의 "계산된 열에 인덱스 만들기"
SQL Server 온라인 설명서의 "확정적 함수 및 비확정적 함수"

행 분할

행 분할은 파티션이라는 여러 테이블에 다수의 테이블 행을 분산하는 방법입니다. 테이블의 구조는 모두 같습니다. 즉, 열 이름과 데이터 형식이 모두 같습니다. 행 분할에는 각각의 테이블 행마다 다른 파티션을 사용하도록 만드는 분할 기준(예: 시간 열)이 포함됩니다. 각 파티션마다 일정한 범위의 값을 포함합니다. 예를 들어, 12개월을 각각 다른 파티션으로 분리할 수 있습니다. 성능 측면에서 테이블을 행 분할하는 주된 이유에는 다음 두 가지가 있습니다.

단일 데이터베이스에서 여러 실제 파일 그룹에 데이터를 분산함으로써 리소스 경합을 최소화합니다.
여러 서버에 데이터를 분산함으로써 데이터베이스를 수평 확장하여 동시성을 향상합니다.

ord_id 열을 분할 기준으로 사용함으로써 orders_customers 테이블을 네 파티션으로 분리하여 고객 주문 데이터를 행 분할할 수 있습니다.

파티션 Orders_customers_part1
ord_id
ord_item_id
ord_it_name
amount
Unit_price
cust_id
cust_name
cust_first
country
1
5567
Cement
10.9
$120.0
A1230
Miller
Justus
Germany
2
9876
Concrete
4.5
$60.50
B2345
Schulz
Bob
USA

파티션 Orders_customers_part2
ord_id
ord_item_id
ord_it_name
amount
Unit_price
cust_id
cust_name
cust_first
country
3
3654
Blocks
7
$12.60
C5679
Vogt
Martin
USA
4
1211
Pipes
3
$10.0
A1230
Miller
Justus
Germany

파티션 Orders_customers_part3
ord_id
ord_item_id
ord_it_name
amount
Unit_price
cust_id
cust_name
cust_first
country
5
5567
Cement
12.5
$60.50
C5679
Vogt
Martin
USA
6
4655
Slump
9
$5.55
A1230
Miller
Justus
Germany

파티션 Orders_customers_part4
ord_id
ord_item_id
ord_it_name
amount
Unit_price
cust_id
cust_name
cust_first
country
7
1211
Pipes
133
$10.0
A1230
Miller
Justus
Germany

분할 기준을 강제로 실행하기 위한 CHECK 제약 조건을 사용합니다. 예를 들어, 위의 테이블에 대한 CREATE table 문은 다음과 같습니다.

CREATE table Partition_Orders_customers_Part1 (ord_id integer primary key CONStrAINT ckPartition1 CHECK(order_id > 1 AND ord_id < 3), ord_item_id integer, ord_it_name varchar(20), amount double, Unit_price money, Cust_id char(5), Cust_name varchar(20), Cust_first varchar(20), Country varchar(20)) CREATE table Partition_Orders_customers_Part2 (ord_id integer primary key CONStrAINT ckPartition2 CHECK(order_id > 2 AND ord_id < 5), ord_item_id integer, ord_it_name varchar(20), amount double, Unit_price money, Cust_id char(5), Cust_name varchar(20), Cust_first varchar(20), Country varchar(20)) CREATE table Partition_Orders_customers_Part3 (ord_id integer primary key CONStrAINT ckPartition3 CHECK(order_id > 4 AND ord_id < 7), ord_item_id integer, ord_it_name varchar(20), amount double, Unit_price money, Cust_id char(5), Cust_name varchar(20), Cust_first varchar(20), Country varchar(20)) CREATE table Partition_Orders_customers_Part4 (ord_id integer primary key CONStrAINT ckPartition4 CHECK(order_id > 6), ord_item_id integer, ord_it_name varchar(20), amount double, Unit_price money, Cust_id char(5), Cust_name varchar(20), Cust_first varchar(20), Country varchar(20))

분할된 테이블에서 데이터를 검색하려면 UNION 연산자로 모든 테이블을 조합하는 뷰를 사용하십시오. SQL Server 2000은 응용 프로그램 디자인을 투명하게 유지하는 분할된 뷰, 즉 SQL 문의 구문에 영향을 주지 않는 분할된 뷰를 지원합니다. 다른 열을 사용하여 분할을 적용하거나 데이터 증가로 인해 기존 파티션 집합에 새 파티션을 추가할 때와 같이 분할 기준을 변경해야 하는 경우에도 항상 SQL 문의 구문은 성능 향상을 위해 그대로 유지됩니다. DBA는 분할된 뷰를 수정하기만 하면 됩니다.

분할된 뷰에는 다음 두 종류가 있습니다.

로컬 분할된 뷰: 모든 파티션이 하나의 SQL Server 2000 인스턴스에 상주합니다.
분산 분할된 뷰: 파티션이 여러 SQL Server 2000 인스턴스(서버 연합)에 분산됩니다. 즉, 분할된 뷰를 구성하는 각각의 구성원 테이블(하나의 파티션)마다 다른 구성원 서버에 저장될 수 있습니다.

로컬 분할된 뷰를 만드는 절차는 다음과 같습니다.

CREATE table 문을 사용하여 분할된 테이블을 필요한 개수만큼 생성합니다.
분할 기준을 포함하는 열에 CHECK 제약 조건을 적용합니다.
다음과 같이 모든 파티션에 UNION ALL 연산자를 사용하여 뷰를 만듭니다.

CREATE VIEW customer_orders AS SELECT * FROM Partition_orders_customer_part1 UNION ALL SELECT * FROM Partition_orders_customer_part2 UNION ALL SELECT * FROM Partition_orders_customer_part3 UNION ALL SELECT * FROM Partition_orders_customer_part4

참고: 분산 분할된 뷰를 사용하여 연합 서버 솔루션을 디자인하는 방법은 다음 절인 "연합 SQL Server를 사용한 수평 확장"을 참조하십시오.

다음 경우에는 행 분할을 사용하는 것이 좋습니다.

인덱스에서 요청을 지원할 수 없는 경우. 예를 들어, 요청된 범위가 너무 넓을 때 하나 또는 소수의 파티션에서만 요청을 수행할 수 있으면 전체 테이블을 스캔하는 것보다 소수의 파티션을 스캔하여 입/출력 횟수를 줄일 수 있습니다. 이는 쿼리의 WHERE 조건자에 분할 CHECK 제약 조건의 열이 포함되어 있으면 SQL Server 2000의 쿼리 엔진은 요청을 수행하는 파티션만 고려하기 때문입니다.
병렬 SQL Server 요청을 수행하는 경우. 병렬 쿼리 스캔과 같은 SQL Server 2000 병렬 쿼리 실행은 하드웨어에서 지원할 수 있습니다. 파티션을 각기 다른 디스크나 심지어 다른 SQL Server 2000 인스턴스에도 배치할 수 있습니다.
인덱싱된 테이블에 쓰기 작업이 많은 경우
직접 액세스 방식 또는 인덱스를 사용하는 SQL 쿼리인 경우. 이 경우에는 B-트리의 수준 수가 줄어드는 이점이 있습니다. 인덱싱된 파티션은 완전히 인덱싱된 테이블보다 수준 수가 적은 B-트리를 생성합니다. 수준 수가 적은 B-트리일수록 인덱스 쓰기 작업으로 인해 페이지 분리가 발생해도 균형 재조정 속도가 빨라지고 SELECT 쿼리의 통과 시간이 줄어듭니다.

또한 파티션을 올바르게 디자인하면 DBA가 데이터베이스를 관리하는 데 도움이 됩니다. DBA는 일반적인 테이블 수준 작업을 파티션 수준에서 모두 수행할 수 있습니다. 예를 들어, 시간 기준에 따라 만든 파티션은 기록 데이터 내보내기/가져오기를 지원합니다. 새 파티션을 추가하여 새 데이터에 필요한 공간을 할당하고, 기존 파티션을 삭제하여 기존 데이터를 제거할 수 있습니다.

행 분할은 쿼리 엔진이 관련 데이터를 포함하는 것으로 알려진 파티션에 대한 액세스를 제한하는 경우에만 사용하는 것이 좋습니다. 다른 테이블과의 조인과 같이 복잡한 쿼리에서 로컬 분할된 뷰나 분산 분할된 뷰를 사용하면 쿼리 엔진이 모든 파티션을 포함하는 다른 계획을 사용할 수 있습니다. 이를 피하려면 관련 데이터를 행 분할하여 파티션 간 조인이 최소화되도록 논리적 데이터베이스 스키마를 신중하게 디자인해야 합니다.

행 분할에 대한 자세한 내용은 다음 리소스를 참조하십시오.

SQL Server 온라인 설명서의 "분할된 뷰 사용"
SQL Server 온라인 설명서의 "분할된 뷰 만들기"
SQL Server 온라인 설명서의 "파티션 디자인"

인덱싱된 뷰

SQL Server 2000의 인덱싱된 뷰 기능을 사용하면 뷰의 SELECT 문에 지정된 데이터 집합을 데이터베이스에 영구적으로 저장할 수 있습니다. 이를 "뷰 구체화"라고 합니다. 뷰 컨텐트의 이러한 중복 저장은 추가 디스크 사용을 통해 특정 쿼리 속도를 높일 수 있는 이점을 제공합니다. 뷰나 기본 테이블을 참조하는 SQL 문을 사용하면 인덱싱된 뷰의 형태로 저장되는 데이터로 이루어진 미리 집계된 데이터 집합에서 데이터를 검색할 수 있습니다. 즉, 쿼리가 원본으로 사용하는 테이블만 처리하더라도 쿼리 엔진이 인덱싱된 뷰를 고려합니다. 기본 테이블에서 삽입과 업데이트 작업을 하면 인덱싱된 뷰의 저장소 집합에 자동으로 적용됩니다.

인덱싱된 뷰는 표준 뷰에 고유 클러스터된 인덱스를 적용하여 만들어집니다. 그런 다음 지정된 데이터 집합이 클러스터나 정렬된 메모리 구조의 형태로 저장됩니다. 클러스터된 인덱스를 만든 후에는 저장소 집합에 다른 클러스터되지 않은 인덱스를 적용할 수 있습니다.

다음 경우에는 인덱싱된 뷰를 사용하여 응답 시간을 크게 줄일 수 있습니다.

읽기 중심 응용 프로그램인 경우. 쓰기 작업이 많은 환경에서는 인덱싱된 뷰를 사용하지 않는 것이 좋습니다. 그 까닭은 원본 테이블에 삽입 또는 업데이트 작업을 하면 인덱싱된 뷰에도 추가 업데이트 작업을 해야 하기 때문입니다.
SELECT 문이 여러 테이블의 합계 또는 기타 계산 등 집계 결과 집합을 검색하는 경우. 이 경우에는 미리 집계된 결과 집합을 인덱싱된 뷰의 형태로 저장하여 읽기 속도를 크게 높일 수 있습니다.

인덱싱된 뷰를 만드는 절차는 다음과 같습니다.

SCHEMABINDING 옵션을 사용하여 뷰를 만듭니다. 이 옵션을 사용하면 뷰 구체화에 참여하는 원본 개체의 스키마 변경이 허용되지 않습니다. 이 옵션을 사용하지 않고는 어떤 뷰에서도 인덱스를 만들 수 없습니다.
뷰에서 고유 클러스터된 인덱스를 만듭니다. 뷰는 이 시점에서 구체화됩니다.
뷰에서 클러스터되지 않은 다른 인덱스를 만듭니다.

인덱싱된 뷰에 대한 자세한 내용은 다음 리소스를 참조하십시오.

SQL Server 온라인 설명서의 "SQL 뷰"
SQL Server 온라인 설명서의 "인덱스 뷰 만들기"
SQL Server 온라인 설명서의 "뷰에서 인덱스 사용"
SQL Server 온라인 설명서의 "뷰의 인덱스 확인"
SQL Server 온라인 설명서의 "인덱스된 뷰 디자인"
SQL Server 온라인 설명서의 "계산된 열에 인덱스 만들기"
SQL Server Magazine 2000년 5월호의 "Introducing Indexed Views"(저자: Kalen Delaney)

SQL 문

이 절에는 최적의 성능을 얻을 수 있게 T-SQL 문을 코딩하는 방법에 대한 지침이 수록되어 있습니다.

레코드 지향적 SQL과 집합 지향적 SQL의 비교

레코드 지향적 데이터베이스 액세스는 응용 프로그램에서 데이터베이스 서버로 각 행에 대한 요청을 보내는 것을 뜻합니다. 최악의 경우, 요청은 요청된 행을 검색하는 하나의 SQL 문과 같습니다. 데이터베이스는 응용 프로그램에 필요한 행을 제공하는 "더미" 액세스 연산자로 사용됩니다. 행 해석에 필요한 논리는 클라이언트 계층에서 구현됩니다.

집합 지향적 데이터베이스 액세스는 응용 프로그램의 요청이 한 번에 일정한 개수의 데이터베이스 행을 반환하는 것을 뜻합니다. 이 액세스 방법을 사용하면 데이터베이스의 논리를 레코드 처리에 포함할 수 있습니다. 가장 극단적인 경우에는 다른 계층을 포함하지 않은 채 데이터베이스가 전체 요청을 수행합니다.

집합 지향적 액세스 방법의 장점은 레코드 지향적 액세스 방법보다 클라이언트와 데이터베이스 간의 통신 횟수가 적다는 점입니다. 서버에서 레코드를 처리할 수 있으므로 클라이언트에 레코드를 전송할 필요가 없습니다. 또한 캐싱 메커니즘이나 인덱싱 전략과 같은 데이터베이스 서버 기능을 모두 사용하여 성능을 향상할 수 있습니다. 집합 지향적 쿼리를 사용할 때의 단점은 데이터베이스가 공유 리소스이고 사용자 고유의 처리를 항상 최소로 유지해야 하기 때문에 확장성이 줄어들 수 있다는 점입니다.

T-SQL 문은 가능한 집합 지향적이 되도록 코딩하는 것이 좋습니다. 즉, 가능하면 IFELSE 흐름 제어 문을 SQL Server 2000 CASE 함수로 바꾸어야 합니다. 그러면 전체 작업이 서버쪽 컨텍스트에서 처리되고 데이터베이스 서버의 지능형 기능이 사용되기 때문에 성능상 이득이 큽니다.

예를 들어, 고객을 수입별로 분류하는 응용 프로그램을 가정해 보면 다음 Revenue 테이블에 고객의 실제 수입이 저장됩니다.

Create table revenue( Cust_id int NOT NULL PRIMARY KEY, Tot_sale money, Cust_type varchar(2))

고객의 주문은 다음 Orders 테이블에 저장됩니다.

create table orders( ord_id int NOT NULL PRIMARY KEY, ord_item_id int, ord_it_name varchar(20), amount int NOT NULL, unit_price money, sale AS (amount * unit_price), cust_id int}

각 고객의 수입은 ABC 분석 방법론에 따라 식별되고 분류됩니다. "A" 고객의 수입은 $10,000를 초과하고 "B" 고객의 수입은 $1,000와 $10,000 사이에 있으며 "C" 고객의 수입은 $1,000 미만입니다. Revenue 테이블에 이 정보를 삽입합니다.

첫번째 솔루션에서 고객 수가 100명이라고 가정해 보면 cust_id가 1에서 100까지 증가합니다. 이 경우, 단일 SELECT 문이 있는 다음 코드를 사용할 수 있습니다.

CREATE PROCEDURE classify_cust AS DECLARE @h_ci int, @h_tot_sale money, @h_cust_type varchar(2) SET @h_ci =0 while @h_ci <= 100 SET @h_ci = @h_ci + 1 BEGIN Select sum(sale) AS sales INTO h_tot_sale From orders Where cust_id = @h_ci IF @h_tot_sale <= 1000 SET @h_cust_type = 'C' IF @h_tot_sale > 1000 and @h_tot_sale < 10000 SET @h_cust_type = 'B' ELSE SET @h_cust_type = 'A' insert into revenue(cust_id, tot_sale, cust_type) values (@h_ci, @h_tot_sale, @h_cust_type) END

이 프로시저 내에서 Orders 테이블의 각 레코드는 개별적으로 처리됩니다. 단일 SELECT 문인 SELECT INTO 를 사용하십시오. 이 SELECT 문은 각 레코드별로 처리됩니다. 데이터베이스는 응용 프로그램에 필요한 행을 제공하는 더미 액세스 연산자로 사용됩니다. 행 해석에 필요한 전체 지능 업무는 호스트(클라이언트) 컨텍스트에서 담당합니다.

커서를 사용하고 커서에서 데이터를 반입하는 것이 더 좋을 수 있습니다. 이전 솔루션의 장점은 SELECT 문이 한 번만 처리된다는 점입니다. 결과 집합은 커서에 저장됩니다. 그런 다음 나중에 호스트 변수의 컨텍스트에서 추가로 처리할 수 있게 커서에서 각 행이 반입됩니다.

CREATE PROCEDURE classify_cust AS DECLARE @h_ci int, @h_tot_sale money, @h_cust_type varchar(2) DECLARE cust_sale CURSOR FOR Select cust_id, sum(sale) AS sales From orders group by cust_id OPEN cust_sale FETCH NEXT FROM cust_sale INTO @h_ci, @h_tot_sale WHILE @@FETCH_STATUS = 0 BEGIN IF @h_tot_sale <= 1000 SET @h_cust_type = 'C' IF @h_tot_sale > 1000 and @h_tot_sale < 10000 SET @h_cust_type = 'B' ELSE SET @h_cust_type = 'A' insert into revenue(cust_id, tot_sale, cust_type) values (@h_ci, @h_tot_sale, @h_cust_type) FETCH NEXT FROM cust_sale INTO @h_ci, @h_tot_sale END CLOSE cust_sale DEALLOCATE cust_sale

이 솔루션에서는 IF ELSE 구문과 INSERT 문을 차례로 사용하여 Revenue 테이블에서 고객을 분류할 수 있게 호스트(클라이언트) 변수(h_ci, h_tot_sale, h_cust_type)에 각각의 단일 레코드를 반입하는 방식으로 단일 레코드를 계속 처리합니다.

SQL Server 2000은 각각의 단일 레코드를 호스트쪽으로 처리하지 않고도 이 반복 작업을 완벽하게 수행할 수 있습니다. 따라서 T-SQL 문의 SELECT 목록 내에서 사용할 수 있는 집합 연산자인 CASE를 사용하십시오.

insert into revenue(cust_id, tot_sale, cust_type) Select cust_id, sum(sale) AS tot_sale, cust_type = CASE WHEN sum(sale) <= 1000 THEN 'C' WHEN sum(sale) > 1000 and sum(sale) < 10000 THEN 'B' WHEN sum(sale) >= 10000 THEN 'A' END From orders group by cust_id

이제는 하나의 T-SQL 문에서 전체 프로시저가 쓰여집니다. 전체 처리는 데이터베이스 서버 내에서 완벽하게 수행됩니다. 따라서 클라이언트와 데이터베이스 서버 간의 통신 횟수가 최소로 줄어듭니다. 서버는 첫번째와 두번째 예제의 행 형식 처리와 반대되는 수량 지향적 처리를 수행합니다.

다음 데이터베이스 예제는 여러 액세스 방식의 응답 시간을 보여줍니다.

경과 시간
CPU 시간(밀리초)
입/출력 횟수
솔루션 1
14200
4317
53103
솔루션 2
12916
4126
57602
솔루션 3
1170
100
4314

transact-SQL에 대한 자세한 내용은 다음 리소스를 참조하십시오.

SQL Server 온라인 설명서의 "CASE"
The Guru's Guide to transact-SQL(저자: Ken Henderson)

집합 차이

SQL은 비직교 언어, 즉 여러 SQL 문을 사용하여 동일한 결과 집합을 얻을 수 있는 언어입니다. 일반적으로 검색할 데이터 양, 테이블 행 수, 물리적 데이터 모델과 같이 쿼리 프로세서가 명령문의 구문을 분석하는 방식에 차이가 있기 때문에 각 변형마다 다른 성능상 단점이 있을 수 있습니다. 어떤 방식이 나은지에 대한 일반 권장 사항을 제시할 수는 없지만, 여러 방법을 사용하여 동일한 결과 집합을 검색할 수 있다는 점을 알아두어야 합니다.

이 절에서는 동일한 데이터 집합을 검색하는 솔루션 세 개를 소개합니다. 이 절의 예제는 이전 절인 "레코드 지향적 SQL과 집합 지향적 SQL의 비교"에 사용된 예제를 확장한 것으로, Revenue 테이블에 입력되지 않은 주문을 보고자 합니다.

고객의 실제 수입은 다음 테이블에 저장됩니다.

Create table revenue( Cust_id int NOT NULL PRIMARY KEY, Tot_sale money, Cust_type varchar(2))

고객의 주문은 다음 Orders 테이블에 저장됩니다.

create table orders( ord_id int NOT NULL PRIMARY KEY, ord_item_id int, ord_it_name varchar(20), amount int NOT NULL, unit_price money, sale AS (amount * unit_price), cust_id int}

사용자 요청에 맞는 집합 차이를 검색할 수 있는 솔루션에는 다음 세 가지가 있습니다.

상관되지 않은 하위 쿼리
상관 하위 쿼리
외부 조인

상관되지 않은 하위 쿼리

이 하위 쿼리는 내부와 외부 SELECT 문 사이에 참조 지점이 없기 때문에 상관되지 않습니다.

select od.ord_id, od.ord_it_name, od.amount from orders od where cust_id NOT IN (select cust_id from revenue)

따라서 그림 5와 같이 항상 내부 SELECT 문이 먼저 평가된 다음 그 결과가 외부 SELECT 문에 전송됩니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 5 상관되지 않은 하위 쿼리의 쿼리 실행 계획

Orders 테이블과 Revenue 테이블 간의 차이를 가져오기 위해 쿼리 엔진이 Revenue 테이블의 cust_id 열에 있는 데이터를 임시 테이블에 저장합니다. 그런 다음 (외부) 조인을 수행하는 중첩 루프 조인으로 인해 이 데이터가 Orders 테이블의 기본 키에 있는 각 항목에 대해 스캔됩니다.

상관 하위 쿼리

이 하위 쿼리의 외부 SELECT 문에는 반복 참조나 데이터별 행이 포함되어 있습니다.

select od.ord_id, od.ord_it_name, od.amount from orders od where NOT EXISTS (select rv.cust_id from orders od, revenue rv where od.cust_id = rv.cust_id)

따라서 상관 하위 쿼리는 그림 6과 같이 외부 SELECT 문의 행별 열 값으로 제공되고 처리되어야 합니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 6 상관 하위 쿼리의 쿼리 실행 계획

이 경우, 쿼리 엔진은 하위 쿼리의 Revenue 테이블과 Orders 테이블을 (내부) 조인하고 그 결과로 발생하는 cust_id 데이터를 임시 테이블에 저장합니다. 그리고 중첩 루프 조인이 필요한 외부 조인 작업을 수행하여 집합 차이를 결정합니다.

외부 조인

JOIN 조건이 있는 SELECT 쿼리를 사용하여 동일한 결과 집합을 검색할 수 있습니다.

SELECT od.ord_id, od.ord_it_name, od.amount FROM orders od LEFT OUTER JOIN revenue rv ON od.cust_id = rv.cust_id WHERE rv.cust_id is NULL

이에 대한 내용은 그림 7에 나와 있습니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 7 외부 조인의 쿼리 실행 계획

이 외부 조인 실행 계획은 필터 노드를 제외하고 상관되지 않은 하위 쿼리의 실행 계획과 비슷합니다. 필터 노드는 WHERE 조건자에 지정될 행을 결정하는 데 필요합니다.

인덱스를 사용하여 임시 테이블 생성 방지

지금까지 의미는 같지만 구문이 다른 세 SQL 문을 살펴보았는데, 세 SQL 문 모두 동일한 행 집합을 검색합니다. 상관되지 않은 하위 쿼리, 상관 하위 쿼리 및 외부 조인 솔루션은 각기 다른 실행 계획을 보여줍니다. 어떤 솔루션이 더 빠른지는 해당 환경(실제 데이터 양, 현재 로드 등)에 따라 달라집니다.

세 실행 계획 모두에는 쿼리 엔진이 임시 테이블에 데이터 행을 저장한다는 한 가지 공통점이 있는데, 이 임시 테이블은 tempdb 데이터베이스에 저장되고 쿼리 작동 기간에만 존재합니다. 데이터 양에 따라서는 임시 테이블로 인해 쿼리 경과 시간이 늘어날 수 있습니다. 이 문제는 임시 테이블을 사용하지 않고 실행 계획을 만드는 다음과 같은 인덱싱 전략을 통해 방지할 수 있습니다.

Orders 테이블에서 기본 키를 삭제합니다.
다음 옵션을 사용하여 새 기본 키를 만듭니다.

alter table orders add CONStrAINT pk_nc primary key NONCLUSTERED (ord_id);

다음과 같이 외래 키에 따라 테이블 데이터를 정렬합니다.

create clustered index idx_orders on orders(cust_id)

이 실행 계획에 대한 내용은 그림 8에 나와 있습니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 8 클러스터된 인덱스를 사용하는 쿼리 실행 계획

새 실행 계획에서는 임시 테이블을 사용하지 않습니다. 쿼리 엔진이 두 테이블 모두의 데이터가 조인 기준의 정렬 순서에 따라 저장된다는 점을 고려하기 때문에 중첩 루프 조인 알고리즘이 병합 조인으로 대체됩니다. SQL Server 2000은 행을 순차적으로 검색한 다음 이를 병합하여 외부 조인 작업을 수행하기만 하면 됩니다.

이 예제에서 알 수 있듯이, SQL 문을 작성하는 방법은 대개 많이 있습니다. 여러 SQL 문을 각각 평가한 다음 사용자의 요구에 가장 적절한 SQL 문을 구현하는 것이 좋습니다. 이 경우, 임시 테이블을 사용하면 성능 병목 현상이 발생할 수 있습니다. 이를 방지하는 방법은 조인에 사용된 키에 클러스터된 인덱스를 만드는 것뿐입니다.

조인 및 하위 쿼리 사용에 대한 자세한 내용은 다음 리소스를 참조하십시오.

Hash Joins and Hash Teams in Microsoft SQL Server(저자: Graefe, Bunker, Cooper)
Understanding the New SQL: A Complete Guide(저자: Simon Melton)
SQL Server 온라인 설명서의 "외부 조인 만들기"

데이터 정렬

이 절에서는 정렬된 결과 집합이 필요한 경우 성능을 높이는 데 도움이 되는 정보를 제공합니다. 정렬된 행은 ORDER BY 절을 사용하여 엄격하게 처리됩니다. DISTINCT 또는 UNION 연산자를 사용하려면 값을 순서대로 정렬하여 중복 값을 제거해야 합니다.

정렬 순서가 지정되어 있지 않을 때는 일반적으로 디스크 상의 물리적 위치와 쿼리 엔진에서 선택한 액세스 전략 순서에 따라 행이 선택됩니다.

조인 작업과 데이터 정렬을 둘 다 처리하는 데 너무 많은 시간이 소모될 수 있으므로, 액세스 계획에서 정렬 작업을 피하도록 조정 목표를 세워야 합니다. 정렬이 필요한 쿼리를 다시 작성하는 것은 좋지 않지만, 정렬이 필요한 쿼리에 맞는 저장소 구조를 찾아서 정렬을 불필요하게 만드는 것은 좋습니다.

이 예제에서는 주문량에 따라 정렬된 고객 주문을 분석하고자 합니다. 기본적으로 행은 오름차순으로 선택됩니다.

select ord_id, cust_id, ord_it_name, amount from orders where cust_id = 'value' order by amount

정렬 열이 있는 인덱스

고객 주문은 다음 Orders 테이블에 저장됩니다.

create table orders( ord_id int NOT NULL, ord_item_id int, ord_it_name varchar(20), amount int NOT NULL, unit_price money, sale AS (amount * unit_price), cust_id int) alter table orders add primary key NONCLUSTERED (ord_id) create index idx_ord1 on orders(cust_id)

SQL Server 2000은 그림 9의 실행 계획을 사용하여 결과 집합을 결정합니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 9 데이터를 정렬하는 실행 계획

액세스 계획에서는 세 일반 노드, 즉 WHERE 조건자에 지정될 값을 결정하는 인덱스 스캔, SELECT 목록에 지정될 데이터를 검색하는 책갈피 조회, ORDER BY 절에 따라 결과를 정렬하는 정렬 노드를 보여줍니다.

최적화 프로그램은 사용 가능한 물리적 액세스 옵션과 기타 기준에 따라 최적의 정렬 방법을 결정합니다. 이제는 정렬된 물리적 액세스 사용의 효과를 조사할 수 있습니다. 여기에 제시된 조정 전략은 SQL 문을 처리하기 위한 리소스의 사용을 줄이는 것입니다. 실행 계획에서는 SQL 문을 처리하는 데 필요한 세 단계를 보여줍니다. 이제는 세 단계를 실행하지 않고 성능 향상을 실현할 수 있습니다.

Create index idx_ord2 on orders (cust_id, amount)

이 경우, cust_id의 주어진 값(cust_id = 'value')은 amount 열을 기준으로 정렬됩니다. 이 정보가 있으면 그림 10과 같이 최적화 프로그램이 인덱스를 사용하여 적중률을 설정합니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 10 데이터를 정렬하지 않는 실행 계획

이 실행 계획에서 분명히 알 수 있듯이, 최적화 프로그램은 추가 정렬 없이 적중률을 설정합니다. 즉, 최적화 프로그램이 사용자의 추가 수동 작업 없이 idx_ord2 인덱스의 물리적 순서를 인식합니다. 실행 계획에는 다음 두 노드가 남아 있습니다.

클러스터되지 않은 인덱스인 idx_ord2는 WHERE 조건자의 쿼리 조건을 충족하면서 적중률을 결정합니다.
SELECT 목록의 나머지 열은 책갈피 조회가 가리키는 기본 테이블에 액세스하는 방식으로 검색할 수 있습니다.

인덱스 전용 액세스

이전 예제에 나와 있는 쿼리에 대해 또 다른 조정 최적화를 구현할 수 있습니다. 식 목록에 포함되는 열은 ord_id, cust_id, ord_it_name 및 amount뿐입니다. 이 열은 다음 인덱스에 중복해서 저장됩니다.

Create index idx_ord3 on orders(cust_id, amount, ord_id, ord_it_name);

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 11 포함 인덱스를 사용하는 실행 계획

이 실행 계획에서는 결과 집합이 idx_ord3 인덱스에서 완전히 검색되고 기본 테이블 정렬과 액세스가 제거됩니다. 이를 포함 인덱스 작업 또는 인덱스 전용 액세스라고 합니다.

클러스터된 인덱스 액세스

포함 인덱스 작업을 대신하는 것으로는, 필요한 정렬 순서에 따라 데이터 행을 포함하는 클러스터된 테이블이 있습니다.

Create unique clustered index idx_ord4 on orders(cust_id, amount, ord_id);

새 인덱스에 모든 열을 포함할 필요가 없는데, 이는 그림 12와 같이 SQL Server 2000이 클러스터된 인덱스 트리의 잎 수준에서 전체 행을 검색하기 때문입니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 12 클러스터된 인덱스를 사용하는 실행 계획

이 실행 계획에서 쿼리는 클러스터된 인덱스에 대한 액세스를 통해서만 다시 충족되고, 실제 데이터 페이지에서 반환된 행의 정렬을 통해서는 다시 충족되지 않습니다. 포함 인덱스 작업을 통해 얻어진 것과 비슷한 "단일 행" 실행 계획이 얻어지지만, 이 개념은 포함 인덱스 작업보다 확장된 개념으로 다음과 같은 두가지 장점이 있습니다.

여기서 선택한 저장소 구조를 사용하면 idx_ord4 인덱스 항목과 그에 대응하는 Orders 기본 테이블 행을 동시에 읽고 선택할 수 있습니다.
식 목록의 모든 열을 인덱스에 중복해서 저장할 필요가 없습니다.

데이터 정렬에 대한 자세한 내용은 다음 리소스를 참조하십시오.

SQL Server Magazine 1999년 7월호(http://www.sqlmag.com)의 "New Features for Query Processing"(저자: Dusan Petkovic, Christian Unterreitmeier)

병목 현상 식별

성능 병목 현상을 찾으려면 우선 가장 큰 로드가 발생하는 중요한 함수나 명령문을 식별해야 합니다. 지정된 시간 창 내에서 작업 빈도, 우선 순위, 요청된 응답 시간, 로드 분포와 관련된 특성 값을 정의하십시오. 이러한 함수의 디자인을 최적화해야만 응용 프로그램의 성능을 향상시킬 수 있습니다.

지원 질문

성능을 측정하는 데 필요한 사용 사례를 수집하십시오. 다음 질문뿐 아니라 특정 상황에서의 중요한 요소 관련 질문도 성능 측정에 유용합니다.

가장 자주 수행되는 작업은 무엇입니까(예: 최종 사용자의 호출, 인터페이스 호출, 시스템 호출 등)?
경과 시간과 관련된 가장 중요한 기능은 무엇입니까(예: 여러 테이블에서 단일 최종 사용자 대화를 채우는 데이터를 선택하는 기능)?
가장 중요한 기능(응용 프로그램의 주된 작업)은 무엇입니까?
가장 많은 리소스를 사용하는 기능(구현된 알고리즘과 관련된 매우 복잡한 기능)은 무엇입니까?

데이터베이스에 대해 실행할 수 있도록 각 함수나 명령문을 하나의 스크립트에 수집하십시오. 예를 들어, 함수는 단일 SQL 문, SQL 문 그룹, T-SQL 프로시저 또는 데이터베이스에 T-SQL 문을 보내 데이터를 검색하는 코드의 일부일 수 있습니다. 사용자의 조정 작업을 테스트하고 측정하려면 수집된 스크립트를 여러 개발 단계에서 여러 번 실행하여 성능을 확인해야 합니다.

모니터링 환경 설정

지금까지 구성, 물리적 및 논리적 데이터 모델, SQL 문과 같이 SQL Server 2000에서 최적화할 수 있는 다양한 요소에 대해 살펴보았습니다. 이 절에서는 SQL Server 2000의 도구를 사용하여 성능을 측정하는 방법에 대해 간략히 설명합니다.

전체 시스템 성능은 일반적으로 특정 사용 사례나 테스트 사례의 경과 시간으로 정의할 수 있습니다. 경과 시간은 전체 사용 사례를 완료하는 데 걸리는 시간으로, 사용 사례가 완료될 때까지 사용자가 기다려야 하는 시간이기도 합니다. 성능 측면에서 테스트 사례의 경과 시간은 다음과 같이 정의됩니다.

경과 시간 = CPU 시간 + 측정된 입/출력

이는 전체 시간을 정확히 계산할 수 있는 수학 공식이 아니지만, 선택한 테스트 사례가 CPU 중심 사례인지 아니면 입/출력 중심 사례인지 결정하는 데 유용하며 사용자의 추가 조정 작업에 필요한 지침을 제공합니다.

SQL Server 2000 응용 프로그램을 모니터링하고 추적하려면 다음 세 도구를 사용하십시오.

SQL 성능 모니터
SQL 프로필러
SQL 쿼리 분석기

SQL 성능 모니터

SQL 성능 모니터를 사용하여 CPU 작업, 입/출력 작업 및 메모리 사용을 측정하십시오.

CPU 사용률을 측정하려면 테스트 사례를 실행하여 해당 작업이 수행되고 있는지 확인하고, CPU 사용률을 모니터링하려면 아래의 미리 정의된 카운터를 사용하십시오.

Processor 개체의 % user-time 카운터

창에 나타나는 숫자를 지정하여 모든 프로세서를 선택해야 합니다. 이 카운터는 초 당 프로세서 사용률을 제공합니다.

프로세서 사용률이 80% 이상으로 유지되면 CPU 병목 현상이 발생한 것이므로 테스트 사례를 자세히 분석해야 합니다. 병목 현상을 초래하는 원인 중 몇 가지는 다음과 같습니다.

SQL 문의 개수와 형식
조인 작업의 횟수와 형식
정렬 작업의 횟수

식별된 쿼리를 자세히 분석하려면 SQL 프로필러 및/또는 SQL 쿼리 분석기를 사용하십시오.

아래의 미리 정의된 카운터는 디스크 작업을 모니터링하는 데 유용합니다.

Physical disk 개체의 disk transfers/sec 카운터

대규모 디스크 작업에서 입/출력 병목 현상을 초래하는 원인은 다음과 같습니다.

물리적 데이터 모델 디자인(예: 데이터와 인덱스 파일의 개수)
RAID 구성(예: 저장소 집합의 개수)
논리적 데이터 모델(예: 분할)
SQL 문(예: 트랜잭션 디자인)

데이터 캐시는 SQL Server 2000에서 가장 중요한 요소입니다. 아래의 미리 정의된 카운터는 디스크가 아닌 데이터 캐시에서 데이터 페이지를 찾는 비율을 측정하도록 예약됩니다.

Buffer Manager 개체의 Buffer Cache Hit Ratio 카운터

캐시 적중률은 95% 이상이 좋습니다. 모니터링된 값이 아주 낮으면 다음 작업을 수행하십시오.

SQL Server 2000에서 max server memory 매개 변수의 구성을 확인합니다.
메모리를 추가합니다.

SQL 프로필러

중요한 SQL 문을 찾고 사용자의 조정 작업으로 인한 영향을 측정하려면 SQL 프로필러를 사용하십시오. SQL 프로필러를 사용하면 단일 SQL 문을 실행하는 데 걸리는 시간을 명확히 모니터링할 수 있습니다. 중요한 SQL 문을 찾으려면 Duration, Reads, Writes 및 CPU 열을 집중적으로 살펴보십시오.

SQL 쿼리 분석기

쿼리 실행 계획을 보고 사용자의 작업을 살펴보려면 SQL 쿼리 분석기를 사용하십시오. 자세한 예제는 "집합 차이"와 "데이터 정렬"을 참조하십시오.

SQL Server 연합을 사용한 수평 확장

응용 프로그램이 최적화되고 하드웨어를 적절한 수준으로 수직 확장하면 SQL Server 2000이 놀라운 성능을 발휘하지만, 완전히 최적화된 데이터베이스와 응용 프로그램이 단일 서버에 대해 일반적으로 예상되는 것보다 높은 데이터베이스 로드를 지원하면 수평 확장을 하는 것이 좋습니다. 수평 확장은 개별 트랜잭션의 처리량을 늘리는 것이 개별 트랜잭션의 속도를 최대로 높이는 것보다 중시되는 시스템에 확장성을 제공합니다. SQL Server 2000을 사용하여 수평 확장을 하면 대규모 웹 또는 엔터프라이즈 시스템의 막대한 처리 로드와 성장 요구 사항을 지원할 수 있습니다.

데이터베이스 확장성 문제

응용 프로그램을 최대한 활용하고자 할 때는 틀림없이 응용 프로그램의 대량 작업 부하를 지원하는 방법과 속도를 향상시키는 방법을 고려할 것입니다. 하지만 확장성만이 응용 프로그램의 전체 성능을 구성하는 요소라는 점을 알아야 합니다. 요구 사항의 우선 순위를 부여하고 시스템 요구 사항과 시스템 지원 담당자에 맞게 다른 요구 사항의 균형을 조정해야 합니다. 데이터베이스의 성능을 논할 때는 데이터베이스 전문가가 처리량, 응답 시간 및 가용성 측면에서 성공을 측정하는 것이 보통입니다.

처리량은 데이터베이스에서 동시에 처리할 수 있는 트랜잭션 수로, 대개 초 당 트랜잭션 수로 측정됩니다. 단, \Processor(_Total)\% Processor Time과 관련하여 \SQLServer:SQL Statistics\Batch Requests/sec를 추적하면 처리량을 더욱 정확하게 측정할 수 있습니다. 이 두 카운터는 동시에 높아지거나 낮아집니다. 실제 응답 시간은 서버에서 쿼리 당 경과 시간으로 측정할 수 있습니다.

감지된 응답 시간은 사용자가 요청한 시간과 그 결과를 받은 시간 사이의 간격으로, 시스템의 모든 부분을 포함합니다. 일반적으로 확장성 높은 방법에서는 처리량 극대화를 목표로 삼지만, 항상 디자인이 응답 시간에 미치는 영향을 고려해야 합니다. 확장 가능한 데이터베이스 응용 프로그램이라면 예상된 동시 사용자 수에 필요한 처리량을 지원함과 동시에 모든 사용자 요청에 적합한 응답 시간을 제공해야 합니다.

수평 확장을 선택하기 전에 응용 프로그램 디자인을 신중하게 고려해야 합니다. 수평 확장을 하면 시스템의 디자인과 관리가 훨씬 복잡해집니다. 따라서 수평 확장은 확장성과 성능이 이미 높은 데이터베이스를 확장하는 최후 수단으로서 선택해야 합니다. 분산 시스템을 처리하는 경우가 아니면 대부분 수평 확장이 아닌 수직 확장을 해야 합니다.

다음 목록에는 시스템 수평 확장을 통해 얻을 수 있는 이익, 수평 확장된 시스템이 현재 시스템인지, 새 시스템인지 아니면 다른 데이터베이스 플랫폼에서 마이그레이션된 시스템인지 결정하는 데 사용할 수 있는 몇 가지 초기 디자인 고려 사항이 나와 있습니다.

대용량의 동시 트랜잭션을 지원하려고 합니까? 수평 확장은 대규모 다중 프로세서 서버의 용량을 초과하는 데이터베이스 로드를 지원해야 할 경우에 가장 많이 선택됩니다. 트랜잭션 용량은 실제적인 문제가 아니라 데이터베이스 관리 시스템(DBMS)의 전체 로드와 관련된 문제입니다.

동시성 문제는 응용 프로그램 디자인 문제로 인해 자주 발생하지만 디스크 입/출력 제한으로 인해서도 발생할 수 있습니다. 이 문제는 물리적 디스크 추가, 더 많거나 더 나은 배열-컨트롤러 채널 추가, 여러 대규모 디스크 집합에서 파일 그룹 사용, 메모리 양을 크게 늘려 더욱 큰 캐시를 통한 물리적 입/출력 감소 등의 방법을 통해 해결할 수 있습니다. 표준 솔루션을 적용하면 동시성이 증가하기 때문에 이 문제 하나만으로는 시스템을 연합 시나리오의 대상으로 삼을 수 없습니다.

중요하게 고려해야 할 요소는 처리 용량 요구 사항입니다. 수평 확장을 하는 주된 원인은 사용자 요구 사항이 가장 큰 대칭적 다중 프로세싱(SMP) 하드웨어의 프로세스 용량을 초과하기 때문입니다.

개별 쿼리의 속도보다 트랜잭션 처리량이 중요합니까? 응용 프로그램에서 원격 서버의 파티션에 있는 데이터를 자주 요청하는 경우에는 이 질문이 중요합니다. 결과 집합을 만들기 위해 여러 서버에 있는 데이터를 얼마나 자주 수집해야 합니까? 요청에 맞는 데이터를 모두 수집하기 위한 원격 서버 호출 횟수가 적을수록 성능이 향상됩니다.

전체 결과 집합을 만들기 위한 원격 데이터 수집 횟수가 많을수록 성능 문제가 커집니다. 하지만 쿼리를 가끔씩만 사용하거나 해당 결과 집합과 관련된 데이터의 대부분을 포함하는 서버로 향하도록 쿼리의 방향을 지정할 수 있는 경우에는 이 질문이 그다지 중요하지 않습니다. 예를 들어, 어떤 정보 집합에 대한 쿼리 횟수가 많으면 데이터와 동일한 노드에서 쿼리를 실행하도록 구성하여 해당 노드에서 가능한 많은 관련 정보를 로컬로 사용할 수 있게 합니다.

관련 분할된 데이터를 연합 서버 한 대에 저장(데이터 병치라고 함)한 다음 들어오는 쿼리의 방향을 해당 데이터가 있는 서버로 향하도록 지정할 수 있습니까? 수평 확장을 선택하기 전에 중요하게 고려해야 할 사항은 데이터베이스 분할에 따른 데이터 구성 방식과 데이터 액세스 방식입니다.

성능 고려 사항 중 하나인 데이터 위치는 원격 데이터가 아닌 로컬 데이터를 사용하여 쿼리를 완료할 수 있는 비율을 측정한 것입니다. 데이터 위치의 비율이 높을수록 성능이 향상됩니다.

서버 간 데이터 이동량은 얼마나 많습니까? 이 질문에 대한 답은 행별 바이트 수와 행 수를 곱하여 얻을 수 있습니다. 행 집합 당 수천 킬로바이트에 달하는 대용량 데이터를 자주 전송하는 경우에는 이 성능 고려 사항이 디자인에 영향을 주므로 더 적합한 데이터 분할 방법이나 더 나은 데이터 액세스 처리 방법을 구하십시오.

다른 모든 데이터베이스 요소와 마찬가지로 올바른 데이터 분할 방법은 사용률에 따라 결정됩니다. 자주 사용되는 쿼리의 경우에는 포함된 데이터를 조사하고 요청되는 데이터 양과 비교하여 포함된 데이터가 얼마나 자주 실행되는지 예상하십시오. 관련 데이터를 병치할 수 없으면 원격 서버에서 요청될 것으로 예상하는 데이터 양을 계산하십시오. 포함된 행 집합이 수천 킬로바이트에 달할 정도로 크고 그 요청 빈도가 높으면 데이터를 다르게 구성하십시오. 행 집합은 크지만 쿼리를 가끔씩만 실행할 경우에는 데이터를 다르게 구성하여 전체 시스템의 만족할 만한 성능을 얻을 수 있습니다.

포함된 데이터의 크기는 얼마나 됩니까? 단순히 가장 큰 테이블을 분할해야 한다고 생각하기 쉽습니다. 가장 큰 테이블을 분할해야 한다고 여기는 회사는 주로 5KB 이상인 10억개 이상의 좁은 행 또는 2천만개 이상의 넓은 행으로 된 큰 테이블을 보유합니다. 하지만 분할을 수평 확장 솔루션의 일부로 사용할 경우에는 가장 큰 테이블이 테라바이트 범위에 있더라도 단순히 가장 큰 테이블을 분할하지 말고 데이터 액세스 방식을 토대로 분할해야 합니다. 일부 분할 가능한 테이블은 커지는 경향이 있지만, 크기 하나만은 결정적인 요소가 될 수 없습니다.

데이터 크기는 서버 간에 교환되는 데이터 양을 가리키는 요소이기도 합니다. 극단적인 경우에는 대용량이 문제가 될 수 있지만, 연결된 서버는 요청된 행의 바이트 크기에 따라 트랜잭션 당 수백개나 수천개의 행 교환을 쉽게 지원할 수 있습니다. 대용량 데이터 교환은 순수 원격 소통량으로 인해 응답 시간이 허용 불가능한 수준으로 저하되는 경우에만 문제가 됩니다.

연합 디자인 시 고려 사항

서버 연합은 특정 응용 프로그램에 대한 데이터가 분산되어 있는 SQL Server 실행 컴퓨터로 이루어진 그룹으로, 처리 로드를 공유하는 서버가 여러 대 포함되어 있는 클러스터된 웹 그룹과 여러 측면에서 비슷합니다. 하지만 SQL Server 연합과 로드 균형 조정 클러스터 사이에는 한 가지 근본적인 차이가 있습니다. 로드 균형 조정 클러스터에서는 서버를 서로 정확히 복제한 것이므로 클러스터의 구성원이 모든 사용자 요청을 처리할 수 있지만, SQL Server 연합에서는 각 서버마다 데이터의 하위 집합이 있으므로 해당 데이터가 있는 서버에서만 특정 데이터에 대한 요청을 처리할 수 있습니다.

참고: "클러스터"가 서버 그룹을 지칭하는 데 자주 사용되므로 "연합"과 장애 조치 클러스터의 구별에 유의하십시오. 두 구성은 함께 사용할 수는 있지만 완전히 다른 기술입니다.

수평 확장 솔루션을 디자인하려면 세심한 계획과 분석이 필요합니다. 여기에 적용할 수 있는 기술 몇 가지는 다음과 같습니다.

데이터 종속 라우팅
분산 분할된 뷰
사용자 정의 분할 방법(예: 해시)
복제
메시지 대기열(MSMQ)
위에 나열된 기술의 조합

사용자 요구 사항에 따라 위에 나열된 기술을 여러 가지로 조합해서 사용할 수 있지만, 일반적인 연합 서버 시나리오에서는 분산 분할된 뷰와 함께 데이터 종속 라우팅 또는 몇 가지 형태의 복제를 조합하여 사용합니다. 하지만 이 디자인이 반드시 필요한 것이 아니므로 사용자 환경에 따라 위에 나열된 기술 중 하나만 사용할 수 있습니다.

연합 디자인의 첫번째 단계는 데이터 분할 방법을 결정하는 것입니다. 제대로 작동하는 파티션을 만들려면 쿼리를 올바른 데이터 파티션 방향으로 지정할 수 있는 전용 키 값으로 파티션을 만들어야 합니다. 예를 들어, 전자 상거래 솔루션에서는 그림 13과 같이 여러 서버에 고객 프로필 데이터(이름, 주소, 전자 메일 주소, 전화 번호 등)를 분할해야 합니다.

rag0513

그림 13 SQL Server 연합

이 예에서 SQLServer1은 1000과 1999 사이의 고객, SQLServer2는 2000과 2999 사이의 고객, SQLServer3은 3000과 3999 사이의 고객에 대한 모든 고객 프로필 레코드를 포함합니다. 아무 키를 사용하여 데이터를 분할할 수 있지만, 여러 서버에 데이터를 사용률별로 고르게 분산하는 키를 사용해야 합니다. 단, 데이터를 행 개수가 아닌 사용률별로 분산해야 한다는 점에 유의하십시오. 응용 프로그램 디자인 때문에 데이터를 사용률별로 분산할 수 없으면 좀 더 임의로 분산하는 해시 기술을 통해 분할을 시도할 수 있습니다. 해시 파티션은 이 문서의 뒷부분에서 설명됩니다.

물론 일부 응용 프로그램에서는 둘 이상의 파티션에서 데이터를 검색해야 할 수도 있는데, 이러한 액세스 방식을 사용하려면 원본 테이블을 추상화해야 합니다.

클라이언트 응용 프로그램에서 파티션을 추상화하는 방법 중 하나는 각각의 연합 서버마다 분산 분할된 뷰(DPV)를 만드는 것입니다. 이 뷰는 로컬 서버와 원격 서버에 분산된 데이터를 조합하여 분할 테이블의 모든 데이터를 통합된 단일 뷰의 상태로 제공합니다. 각 연합 서버의 뷰가 동일하면 클라이언트 응용 프로그램이 어떤 서버에 연결되었는지에 상관없이 분할된 테이블의 모든 데이터를 사용할 수 있습니다. 그림 14에 분산 분할된 뷰가 나와 있습니다.

rag0514

그림 14 분산 분할된 뷰

이 예에서는 pv_CustomerProfiles이라는 분산 분할된 뷰가 모든 연합 서버에서 정의됩니다. 이 뷰를 사용하면 고객 데이터에 대한 단일 통합 뷰를 제공하는 데이터를 원본 테이블에서 검색할 수 있습니다. SQL Server 2000은 디자인 요구 사항이 충족된 경우 뷰를 통해 여러 서버의 데이터를 수정할 수 있도록 분산 분할된 뷰의 업데이트 기능을 지원합니다(SQL Server 온라인 설명서 참조). 또한 SQL Server 2000 쿼리 엔진은 분산 분할된 뷰에서 쿼리를 지능적으로 평가하여 불필요한 원격 서버 쿼리를 방지할 수 있습니다.

하지만 분산 분할된 뷰의 주된 목적은 연합 서버를 개념적으로 이해하기 쉽게 추상화 계층을 만드는 것입니다. 분산 분할된 뷰가 효과적이라도 한 가지 단점을 갖습니다. 즉, 연합 서버 중 한 대라도 사용할 수 없게 되면 쿼리 계획이 다시 컴파일된 후(예: 뷰에 대한 쿼리가 드물어 캐시에 저장되지 않은 경우) 또는 사용할 수 없는 파티션의 데이터가 쿼리된 후 오류 메시지가 반환됩니다.

이러한 단점 때문에 관리 작업을 단순화해야 하는 경우에는 DPV를 보류하고 응용 프로그램 디자인에서 이를 대부분 무시한 다음 이 문서의 뒷부분에 설명된 데이터 종속 라우팅이나 사용자 정의 분할로 대체할 수 있습니다.

데이터 파티션(파티션 구성원이라고도 함)은 큰 데이터 집합의 하위 계층을 나타내며 항상 분할 키로 인덱싱되고 쿼리된다는 점을 제외하고 일반 테이블과 비슷합니다. 파티션 구성원 테이블이 분산 분할된 뷰에 포함되는 경우, 키는 특수 분할 요구 사항을 충족해야 하며 CHECK 제약 조건의 적용을 받습니다. 자세한 내용은 SQL Server 온라인 설명서를 참조하십시오.

디자인하는 시스템을 명확히 관리할 수 있어야 합니다. 연합 서버를 관리하는 것은 이미 복잡하기 때문에 불필요한 관리 문제를 초래하여 더 복잡하게 해서는 안됩니다. 관리 복잡성 비용은 추가 인력 작업 시간과 기술 수준에 대한 지원 비용으로 측정할 수 있습니다. 또한 문제의 복잡성이 지원 기술이나 지원 시간을 초과하면 시스템 정지 시간이 발생합니다. 회사의 담당자 그룹은 예상 수입 손실, 작업 시간 손실, 고객 손실 및 기타 무형 비용(예: 고객을 대상으로 하는 응용 프로그램인 경우 전문업체로서의 명성 손상)의 관점에서 단위 시간 당 정지 시간의 비용을 평가할 수 있어야 합니다. 따라서 연합 디자인을 수행하기 전에 우수한 디자인, 올바른 테스트 계획 및 높은 지원 수준을 준비해야 합니다.

이러한 사항을 염두에 두고 여러 파티션에 데이터를 분산하거나 다시 분산하는 방법을 신중하게 계획하십시오. 둘 이상의 테이블을 분할하고 데이터를 병치하여 자주 조인하도록 할 수 있습니다. 또한 트랜잭션 로그 표시를 백업에 사용하지 않을 경우에는 트랜잭션 측면에서 일관성이 있어야 하는 데이터를 병치하려고도 할 수 있습니다. 이 경우, 각각의 분할된 구역은 그것이 데이터베이스이든 단순 파일 그룹(서버 한 대에 둘 이상의 파티션을 배치한 경우)이든 관계 없이 완벽한 단위가 됩니다.

일부 분할 방법에서는 서버 한 대나 데이터베이스 하나에 둘 이상의 파티션이 있을 수 있습니다. 이 경우에는 각 파티션마다 다른 파일 그룹에 배치하여 개념을 단순화할 수 있지만, 이것이 반드시 필요한 것은 아닙니다. 파일 그룹을 복원해도 전체 데이터베이스는 트랜잭션 측면에서 일관성을 유지합니다. 따라서 처음에는 동일한 데이터베이스 내의 파티션을 파일 그룹별로 분리할 때 얻을 수 있는 장점이 개념의 단순성뿐인 것처럼 보일 수 있습니다.

하지만 파일 그룹을 사용할 때 얻을 수 있는 중요한 장점은 바로 복원 속도입니다. 전체 데이터베이스가 아닌 하나의 데이터베이스 구역만 복원해야 하는 경우에는 정지 시간이 크게 줄어듭니다. 데이터베이스는 복원된 파일 그룹과 나머지 데이터베이스 사이의 일관성을 복구해야 하기 때문에 모든 관련 데이터가 파일 그룹에 있으면 복구 속도가 훨씬 빨라집니다. 또한 이 방법으로 분리하면 나중에 데이터를 이동하려고 할 때 데이터가 이미 통합되어 있기 때문에 분석 작업이 줄어듭니다. 마지막으로 fn_virtualfilestats를 사용하여 입/출력을 파일 그룹별로 모니터링할 수 있습니다.

데이터 분할

분할을 통해 이익을 얻을 수 있는 데이터베이스 응용 프로그램이 무엇인지, 데이터베이스 응용 프로그램을 분할하는 데 가장 적합한 키가 무엇인지에 대해 신중하게 고려해야 합니다. 일반적으로 가장 자주 액세스되는 테이블을 분할해야 하지만, 가장 많은 데이터를 포함하는 테이블이라고 해서 가장 자주 액세스되는 것이 아닙니다. 분할 키를 선택할 때는 숫자 키로 분할하는 것이 문자 기반 키로 분할하는 것보다 나은 성능을 얻을 수 있다는 점에 유의하십시오. 적합한 키를 찾을 수 없으면 새 분할 키 열을 만들거나 해싱 알고리즘을 사용하여 기존 필드 중 하나를 기초로 데이터를 분할해야 합니다.

디자인 단계에서는 각 서버마다 작업이 고르게 분산되도록 데이터를 분할하고 있는지 확인해야 합니다. 그러기 위해서는 데이터를 값별로 분산하는 것이 아니라 사용률별로 분산해야 합니다. 즉, 파티션이 고르지 않으며 경우에 따라 파티션마다 행 수에 큰 차이가 있다고 예상해야 합니다.

지금부터 잘못된 예를 통해 비교해 봅시다. 첫번째 파티션은 1과 999 사이의 주문을, 두번째 파티션은 1000과 1999 사이의 주문을, 세번째 파티션은 2000과 2999 사이의 주문을 포함하도록 Orders 테이블을 OrderID(ID 열)별로 분할했다고 가정해 보면 이것이 "고른 분산"이더라도 두 가지의 직접적인 문제가 발생합니다. 첫번째 문제는 OrderID가 순차적으로 추가되기 때문에 세번째 파티션만 증가할 것이고 각 파티션의 키 값 범위를 늘리려면 데이터를 다시 분할해야 한다는 점입니다. 두번째 문제는 다른 주문보다 가장 최근 주문에 대한 쿼리가 많기 때문에 대부분의 작업이 세번째 파티션에 집중된다는 점입니다.

따라서 파티션을 디자인하기 전에 데이터가 쿼리되는 방식을 조사하고 시스템이 증가하는 방식을 이해해야 합니다. 결과적으로 각 파티션의 데이터 양이 액세스 빈도에 기초해서만 분산되기 때문에 분산된 데이터의 행 수는 고르지 않습니다.

연합 서버의 하드웨어 구성은 물리적으로 같지 않아도 됩니다. 파티션을 균등하게 사용하기 때문에 프로세서와 메모리는 동일해야 하지만, 한 파티션의 데이터 양이 다른 파티션보다 훨씬 큰 경우에는 새 서버에 또 다른 파티션을 만드는 것이 아니라 해당 서버에 디스크를 추가하여 이를 보정할 수 있습니다. 특정 파티션의 사용률이 높아서 그 데이터가 다른 파티션의 데이터보다 자주 액세스되고 처리량 병목 현상이 발생하는 경우에는 서버를 균등하게 사용하도록 데이터를 다시 분산하거나 또 다른 연합 서버를 추가해야 합니다. 데이터의 위치를 유연하게 변경할 수 있도록 디자인해야 합니다.

대부분의 데이터베이스 작업에는 조인, 키 제약 조건 또는 트리거를 사용하여 관련 데이터에 액세스하는 작업이 포함되며, 이러한 액세스 작업에 원격 서버 호출이 요구되면 분산 분할의 이점이 없어집니다. 따라서 자주 액세스되는 데이터를 식별한 후에는 전략적 측면에서 같은 서버에 병치하기에 가장 좋은 관련 데이터가 무엇인지 결정해야 합니다. 데이터를 여러 서버에 분할하는 경우 관련 데이터를 처리하는 방법은 다음과 같습니다.

대칭 파티션

병치. 표준적인 방법은 원래의 분할된 테이블을 토대로 외래 키 테이블을 분할하는 것입니다. 물론 이 방법은 원래의 테이블을 토대로 외래 키 테이블을 성공적으로 분할할 수 있는 경우에만 사용할 수 있습니다. 예를 들어, 고객이 각 제품에 대한 검토 의견을 추가할 수 있는 전자 상거래 사이트를 가정해 봅시다. 검토 의견은 Productreviews라는 테이블에 저장되고 이 정보는 CustomerID 필드의 고객 프로필 파티션 구성원 테이블과 연관될 수 있습니다. 각 검토 의견은 고객 한 명과 연관되기 때문에 Productreviews 테이블을 성공적으로 분할할 수 있고 각 제품에 대한 검토 의견은 해당 의견을 입력한 고객의 레코드와 같은 서버에 놓입니다.

비대칭 파티션

비정규화. 관련 데이터 중에는 원래의 분할된 테이블을 토대로 분할하기가 어려운 데이터도 있습니다. 예를 들어, 사용자가 선택할 수 있는 배달 방법(예: 일반 배달, 속달 또는 야간 배달)을 나열하는 DeLIveryMethods라는 테이블이 전자 상거래 데이터베이스에 포함되어 있다고 가정해 봅시다. 이 경우, 고객은 CustomerProfiles 테이블의 PreferredDeLIveryMethod 필드를 DeLIveryMethods 테이블의 외래 키로 포함시켜 자신의 프로필에서 특정 배달 방법을 기본 배달 방법으로 선택할 수 있습니다.

여러 고객이 같은 배달 방법을 기본 배달 방법으로 선택할 가능성이 높기 때문에 제공 데이터 중 일부를 CustomerProfiles 테이블의 추가 열로 복제하여 데이터베이스를 비정규화하는 것이 좋습니다. 그러면 자주 액세스되는 DeLIveryMethod 데이터(예: 배달 방법의 명칭 및 비용)를 고객별로 검색할 수 있기 때문에 DeLIveryMethods 테이블에 조인하지 않아도 됩니다.

또한 DeLIveryMethods 테이블에 데이터가 추가되는 빈도와 DeLIveryMethods 테이블의 복제된 열이 업데이트되는 빈도를 고려해야 합니다. 복제된 데이터의 무결성을 유지하기 위해 디자인에 한 트랜잭션의 관련 데이터를 모두 업데이트하는 작업을 포함하면 해당 업데이트 작업이 완료될 때까지 테이블이 잠깁니다. 이를 프로그래밍 방식으로 해결하는 방법에는 여러 가지가 있지만, 확장성을 향상하기 위해 무엇을 버리고 무엇을 취할 것인지에 대해 신중하게 결정해야 합니다. 무제한에 가까운 트랜잭션 수를 지원하는 기능을 버리고 트랜잭션 당 응답 시간을 약간 줄일 수 있지만, 결함 있는 디자인을 통해 응답 시간을 크게 줄이는 것은 바람직하지 않습니다.

복제. 비정규화를 통해 원격 서버 호출을 줄일 수 있지만, 이렇게 하면 데이터 무결성을 유지하기가 더욱 복잡해질 수 있습니다. 이 문제를 해결하는 방법 중 하나는 SQL Server 복제를 사용하여 연합 서버에 같은 데이터를 분산하는 것입니다. 예를 들어, 모든 연합 서버에서 전체 배달 방법 목록을 검색할 수 있도록 세 서버 모두에 DeLIveryMethods 테이블을 복제하면 배달 방법 데이터를 비정규화하는 것과 관련된 무결성 문제를 방지할 수 있습니다. 이 때 데이터 무결성이 유지되는 이유는 배달 방법 정보에 대한 업데이트가 복제되기 때문입니다.

하지만 복제를 사용하려면 일부 CPU와 메모리 리소스를 희생해야 하는데, 작업량이 많은 시스템에서는 그 대가가 너무 큽니다. 이러한 시스템이 디자인에 포함되어 있으면 그림 15와 같이 분할 가능한 테이블은 해당 연합 서버에 걸쳐 분할하고 나머지 테이블은 모두 저장하는 시나리오를 테스트해야 합니다. 이 때 나머지 테이블 중에서 저장할 수 없는 테이블은 단일 서버에 함께 복제해야 합니다.

rag0515

그림 15 단일 서버에 공통 데이터 저장

이 디자인을 사용하면 분산 분할된 뷰를 통해 분할된 데이터를 검색할 수 있지만, 다른 모든 데이터에는 단일 공통 위치가 제공됩니다. 이러한 위치는 연합 구성원 중 하나이거나 별도의 추가 서버일 수 있습니다.

복제를 대신하는 다른 방법. 복제는 서버의 프로세서, 메모리 및 디스크를 소모합니다. 이러한 추가 로드를 허용할 수 있게 서버를 디자인했고 DBA에게 충분한 복제 관리 경험이 있으면 걱정할 필요가 없지만, 그렇지 않으면 INSTEAD-OF 트리거를 사용하여 모든 복제된 데이터를 업데이트하는 것이 좋습니다. 예를 들어, 모든 연합 서버에 포함시켜야 하는 조회 테이블이 있을 때 이를 지속적으로 업데이트하지 않으려면 INSTEAD-OF 트리거를 사용하는 것이 더 간단하면서도 효과적입니다. INSTEAD-OF 트리거는 조회 테이블의 모든 복사본에 만들어지며 각 복사본(트리거가 있는 복사본 포함)에 데이터 변경 사항을 적용합니다.

사용 가능한 방법 비교

연합 구성에서 각 부분의 역할을 개별적으로 이해하면 모든 부분을 하나의 그룹으로 구현하는 방법을 선택하는 데 유용합니다.

데이터 종속 라우팅(DDR)은 코드와 조회 테이블 또는 해시 키를 사용하여 적절한 파티션을 향하도록 요청의 방향을 지정하는 기술입니다.

분산 분할된 뷰(DPV)를 사용하면 각 테이블을 개별적으로 참조하지 않고도 뷰를 통해 파티션에 간단히 액세스할 수 있습니다.

해시 파티션은 데이터를 포함하는 서버를 향하도록 방향을 지정한다는 측면에서 DDR과 매우 비슷합니다.

결정을 내리는 데 중요한 요소는 네트워크 대역폭과 서버 간 쿼리이며, 이는 유추를 통해 손쉽게 설명할 수 있습니다. 서버 간 연결을 인도라고 가정해 봅니다. 서버가 고속 네트워크로 연결되면 인도는 넓어지고, 표준 네트워크로 연결되면 인도는 좁아집니다. 보행자를 100K의 데이터라고 가정합니다. 따라서 쿼리 볼륨이 너무 커지기 전까지는 결과 수가 적은 쿼리(적은 보행자)를 수용하는 것이 쉽습니다. 대규모 결과 집합을 포함하는 요청을 하는 것은 인도에서 함께 걷는 보행자의 규모가 대형이라는 것을 나타냅니다. 보행자가 자신의 목적지에 도착하는 속도는 그와 동시에 인도를 걷고 있는 다른 보행자 수에 따라 달라진다는 것은 쉽게 알 수 있습니다. 연합 서버 간에 고속 네트워크가 필요한 이유가 바로 이것입니다.

여러 서버의 데이터를 요청하는 쿼리가 정기적으로 발생할 것으로 예상되면 DDR과 DPV를 조합하거나 해시 분할과 DPV를 조합해서 사용하는 편이 낫습니다. 또한 여러 서버의 결과를 프로그래밍 방식으로 제어할 수 있지만, 이렇게 하면 발생하는 쿼리 유형에 따라 더 빨리 복잡해 질 수 있습니다. 같은 서버에 있는 여러 파티션에 대해 쿼리하는 경우 명확한 성능 상의 손실은 없다는 것을 참고하십시오.

분산 분할된 뷰 구현

연합 솔루션을 만들려면 각각의 구성원 서버에서 아래에 설명된 몇 가지 기본 구성 작업을 수행해야 합니다.

서버 설치

서버 연합을 설치하려면 다음 작업을 수행해야 합니다.

연결된 서버 정의 추가

각각의 연합 서버마다 다른 서버에 대한 연결된 서버 정의가 있어야 합니다. 연결된 서버 정의는 sp_addLInkedserver 시스템 저장 프로시저나 SQL Server 엔터프라이즈 관리자로 추가할 수 있습니다.

참고: 이 시점에서 서버 간에 필요한 보안 유형을 고려해야 합니다. 또한 시스템에 새 사용자를 추가할 방법과 필요한 기타 보안 정책도 고려해야 합니다. 단순히 원격 서버의 SA 계정에 모든 사용자를 매핑하는 것은 분명히 좋지 않습니다.

예를 들어, 다음 transact-SQL을 실행하여 SQLServer2와 SQLServer3을 SQLServer1의 연결된 서버로 추가할 수 있습니다.

EXEC sp_addLInkedserver @server = 'SQLServer2', @provider = 'SQLOLEDB' @datasrc = 'SQLServer2' EXEC sp_addLInkedserver @server = 'SQLServer3', @provider = 'SQLOLEDB' @datasrc = 'SQLServer3'

@server 매개 변수는 원격 서버의 내부 이름을 지정하는 데 사용됩니다. 컴퓨터의 실제 NetBIOS 이름이 아닌 이름을 사용할 수 있지만, 서버 연합을 구성할 때는 실제 컴퓨터 이름을 사용하는 것이 좋습니다. 그러면 로컬 서버의 NetBIOS 이름으로 로컬 테이블을 참조할 수 있기 때문에 모든 구성원 서버에서 분할된 뷰의 테이블 참조를 일관성 있게 유지할 수 있습니다. @provider 매개 변수는 원격 서버에 연결하는 데 사용되는 데이터 액세스 공급자를 지정하고, @datasrc 매개 변수는 원격 서버의 NetBIOS 이름을 지정합니다. sp_addLInkedserver 저장 프로시저는 SQL Server 데이터베이스가 아닌 데이터베이스를 연결할 수 있는 추가 매개 변수를 지원합니다. SQL Server 데이터 원본을 연결할 때는 이 추가 매개 변수가 필요하지 않습니다.

이와 비슷한 명령문을 SQLServer2에서 실행하여 SQLServer1과 SQLServer3을 연결하고, SQLServer3에서 실행하여 SQLServer1과 SQLServer2를 연결해야 합니다.

Lazy Schema VaLIdation 설정

sp_serveroption을 통해 구성되는 이 서버 옵션을 설정하면 원격 파티션 구성원 테이블의 데이터가 실제로 필요한 경우에만 쿼리 프로세서가 연결된 테이블에 대한 메타 데이터를 요청합니다. 이 옵션은 각각의 연합 서버마다 설정해야 합니다.

참고: 모든 파티션이 로컬 서버의 동일한 데이터베이스 내에 있으면 이 옵션을 설정하지 말아야 합니다. 이런 상황은 향후 확장이 예상되는 파티션을 디자인하고 있지만 아직 연합을 구현하지 않는 경우에 발생할 수 있습니다.

Lazy Schema VaLIdation 옵션을 설정하려면 각각의 연합 서버에서 다음 명령문을 실행하십시오.

EXEC sp_serveroption @@servername, 'Lazy Schema VaLIdation', true

데이터베이스 개체

서버 설치를 완료했으면 분할된 데이터베이스에 필요한 데이터베이스 개체를 만들어야 합니다.

파티션 구성원 테이블 만들기

분할된 데이터를 포함할 구성원 테이블은 연합 서버에서 만들어야 합니다. 이 테이블을 만드는 데 사용되는 테이블 스키마는 데이터 분할에 사용되는 제약 조건만 제외하고 모든 연합 서버에서 동일해야 합니다. 제약 조건은 각 테이블마다 상주할 서버에 대한 적절한 데이터만 포함할 수 있게 분할 키 열에서 정의해야 합니다. 이 제약 조건은 쿼리 엔진이 분산 분할된 뷰에서 쿼리 실행 계획을 세우는 데 사용됩니다.

예를 들어, SQLServer1에서 다음 CREATE table 문을 실행하여 1000과 1999 사이의 고객에 대한 테이블을 만들 수 있습니다.

CREATE table CustomerProfiles ( CustomerID INTEGER CONStrAINT ck_Profiles CHECK(CustomerID BETWEEN 1000 AND 1999), FirstName Varchar(20), EMail Varchar(70) CONStrAINT PK_CProfiles PRIMARY KEY(CustomerID) )

SQLServer2에서 다음 CREATE table 문을 실행할 수 있습니다.

CREATE table CustomerProfiles ( CustomerID INTEGER CONStrAINT ck_Profiles CHECK(CustomerID BETWEEN 2000 AND 2999), FirstName Varchar(20), EMail Varchar(70) CONStrAINT PK_CProfiles PRIMARY KEY(CustomerID) )

SQLServer3에서 다음 명령문을 실행하여 집합을 완성할 수 있습니다.

CREATE table CustomerProfiles ( CustomerID INTEGER CONStrAINT ck_Profiles CHECK(CustomerID BETWEEN 3000 AND 3999), FirstName Varchar(20), EMail Varchar(70) CONStrAINT PK_CProfiles PRIMARY KEY(CustomerID) )

중요: 테이블이 만들어질 때 모든 SET 옵션은 각 서버마다 같아야 합니다. 그렇지 않으면 DPV를 업데이트할 수 없습니다.

예제에서 각 테이블의 이름이 같다는 것을 참고하십시오. 각 구성원 테이블의 이름을 동일하게 하면 모든 구성원 서버에서 구성원 테이블에 액세스하는 데 사용되는 명령문이 일관성 있게 유지됩니다.

하지만 나중에 파티션을 여러 서버로 분리할 작정으로 현재 단일 데이터베이스에서 분할 가능한 응용 프로그램을 디자인하고 있으면 각 파티션 구성원 테이블을 서로 다른 이름으로 명명해야 합니다. 이 경우에는 키 범위와 관련이 없는 이름을 선택하십시오. 예를 들어, 세번째 테이블을 CustomerProfiles_3000_3999로 명명한 경우 나중에 서버 간 사용률을 고르게 분산시키기 위해 세번째 테이블을 다시 분할하면 이 이름이 틀려지게 됩니다.

각각의 연합 서버에서 DPV 만들기

마지막으로 각 구성원 서버마다 분할된 뷰를 만들어야 합니다. 구성원 테이블과 연결된 서버 정의가 모든 구성원 서버에 만들어진 후에야 이 단계를 실행한다는 것은 의미가 없고 오히려 서버 연합을 만드는 스크립팅이 처음보다 복잡해 집니다. 각 구성원 서버마다 분할된 뷰를 만들려면 구성원 테이블을 만드는 스크립트를 실행하고 연결된 서버를 정의하고 각 서버에 Lazy Schema VaLIdation 옵션을 설정한 다음, 각 서버에 분할된 뷰를 만드는 별도의 스크립트를 실행해야 합니다.

분할된 뷰는 UNION 연산자(ALL 옵션을 지정하면 SQL Server가 결과 집합에서 중복 행을 제거하지 못함)를 사용하여 통합된 구성원 테이블 각각에 대한 SELECT 문으로 이루어집니다. 예를 들어, SQLServer1에서 다음 명령문으로 CustomerProfiles 뷰를 정의할 수 있습니다.

CREATE VIEW pv_Customers AS SELECT CustomerID, FirstName, EMail FROM SQLServer1.FederatedRetail.dbo.CustomerProfiles UNION ALL SELECT CustomerID, FirstName, EMail FROM SQLServer2.FederatedRetail.dbo.CustomerProfiles UNION ALL SELECT CustomerID, FirstName, EMail FROM SQLServer3.FederatedRetail.dbo.CustomerProfiles

SQLServer2와 SQLServer3에서 이와 비슷한 명령문을 실행하여 모든 고객 프로필 레코드를 포함하는 뷰인 pv_CustomerProfiles를 각각의 연합 서버에 포함시킬 수 있습니다.

DPV 쿼리

각 서버마다 뷰가 있기 때문에 서버에 있는 테이블인 것처럼 뷰를 쿼리할 수 있습니다. 따라서 저장 프로시저를 액세스에 사용하는 경우에는 다음과 같이 고객 프로필 레코드를 CustomerID별로 검색하는 저장 프로시저를 만들 수 있습니다.

CREATE PROC getCustomerProfile (@CustomerID Int) AS SELECT CustomerID, FirstName, EMail FROM pv_Customers WHERE CustomerID = @CustomerID

데이터가 CustomerID별로 분할되기 때문에 모든 파티션이 검색되는 현상을 피하려면 테이블을 검색할 때마다 해당 열을 포함시켜야 합니다. 예를 들어, 다음 프로시저를 만들면 모든 파티션이 검색되므로 효율적이지 않습니다.

CREATE PROC getCustomerByEMail (@Email Varchar (70) ) AS SELECT CustomerID, FirstName, EMail FROM pv_Customers WHERE EMail = @EMail

CustomerID를 포함시키면 보다 효율적으로 쿼리할 수 있습니다.

쿼리와 제약 조건을 토대로 가능하면 컴파일 시간에 중복 파티션이 제거되고, 그렇지 않으면 실행 시간에 시작 필터나 동적 필터라는 메커니즘을 통해 중복 파티션이 제거됩니다. 시작 필터는 쿼리의 매개 변수에 대한 조건으로, 파티션에 액세스해야 하는지 여부를 나타냅니다. 시작 필터를 로컬로 평가하는 비용은 원격 서버에서 쿼리를 실행한 다음 기준에 맞는 행이 없음을 발견하는 비용보다 훨씬 적습니다.

분할된 쿼리의 실행을 살펴보려면 시작 필터 계획을 보여주는 실행 계획 출력을 조사하십시오. 그러면 모든 파티션이 계획에서 승인되지만 시작 필터가 파티션 당 사용 가능한 값 범위를 등록한다는 점을 알 수 있습니다. "set statistics i/o on"과 "set statistics showplan on" 옵션을 사용하면 더 자세한 내용을 알 수 있습니다.

테이블을 쿼리하면 아래의 실행 계획 출력이 얻어집니다. 쿼리가 실행될 때, 시작 식은 중복 파티션을 동적으로 제거합니다. 계획을 세울 때 키 값이 알려져 있지 않은 경우, SQL Server는 어떤 구성원 테이블이 액세스되는지 제어하는 조건 논리가 있는 실행 계획을 세웁니다. 이 실행 계획에는 입력 매개 변수 값을 토대로 어떤 구성원 테이블이 액세스되는지 제어하는 조건 논리인 시작 필터가 있습니다. 그래픽 형식의 쿼리 분석기 뷰에서는 표시된 모든 원격 파티션이 승인된다는 점을 알 수 있습니다. 불필요한 파티션은 제공된 매개 변수를 토대로 실행 시간에 실행 계획에서 제거되고, 통계 프로필을 살펴보면 한 개의 파티션만 실제로 쿼리된다는 점을 알 수 있습니다.

SET STATISTICS IO ON SET STATISTICS PROFILE ON EXEC getCustomerProfile 1020

통계 입/출력은 CustomerProfiles 테이블이 스캔되었음을 보여줍니다.
통계 입/출력 결과
table 'CustomerProfiles'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0

통계 프로필은 첫번째 파티션의 CustomerProfiles 테이블에 대한 실행이 1회 수행되었고 기본 키(이 예의 경우 PK_CProfiles)에 클러스터된 인덱스가 사용되었음을 보여줍니다.
통계 프로필 결과
실행 횟수
명령문 텍스트
1
SELECT * FROM pv_Customers
WHERE CustomerID = @CustomerID
1
Concatenation
1
Filter(WHERE:(STARTUP EXPR([@CustomerID]<=1999 AND [@CustomerID]>=1000)))
1
Clustered Index Seek(OBJECT:([FederatedRetail].[dbo].[CustomerProfiles].[PK__CProfiles), SEEK:([CustomerProfiles].[CustomerID]=[@CustomerID]) ORDERED FORWARD)
1
Filter(WHERE:(STARTUP EXPR([@CustomerID]<=2999 AND [@CustomerID]>=2000)))
0
Remote Query(SOURCE:(Server2), QUERY:(SELECT Col1015,Col1014,Col1013 FROM (SELECT Tbl1003."CustomerID" Col1013,Tbl1003."FirstName" Col1014,Tbl1003."EMail" Col1015 FROM "FederatedRetail"."dbo"."CustomerProfiles" Tbl1003) Qry1016 WHERE Col1013
1
Filter(WHERE:(STARTUP EXPR([@CustomerID]<=3999 AND [@CustomerID]>=3000)))
0
Remote Query(SOURCE:(Server3), QUERY:(SELECT Col1020,Col1019,Col1018 FROM (SELECT Tbl1008."CustomerID" Col1018,Tbl1008."FirstName" Col1019,Tbl1008."EMail" Col1020 FROM "FederatedRetail"."dbo"."CustomerProfiles" Tbl1008) Qry1021 WHERE Col1018

쿼리와 실행 계획을 분석하는 작업은 데이터 분할을 준비하는 데 있어 중요한 부분입니다. 기존 데이터베이스를 분할할 때는 쿼리에 감사 코드를 몇 개 추가하거나 SQL 프로필러를 실행하여 사용되는 쿼리, 액세스되는 데이터 및 수행되는 빈도에 대한 기본 정보를 파악하는 것이 좋습니다. 이 기본 정보는 최적의 데이터 분산 방법을 결정하는 데 유용합니다.

DPV 업데이트

업데이트 가능한 분할된 뷰를 만들려면 SQL Server 2000 온라인 설명서의 업데이트할 수 있는 분할된 뷰 요구 사항을 충족해야 합니다. DPV를 통해 파티션을 업데이트할 때는 다음 예제와 같이 SET XACT_ABORT ON을 사용해야 합니다.

CREATE PROC ChangeEmail (@CustomerID Int, @NewEMail varchar(50)) AS SET XACT_ABORT ON BEGIN trAN UPDATE pv_Customers SET Email = @NewEMail WHERE CustomerID = @CustomerID IF (@@error <> 0) GOTO ErHand COMMIT trAN RETURN 0 ErHand: If @@trancount > 0 BEGIN ROLLBACK trAN RETURN -1 END

분산 분할된 뷰를 사용하지 않고 파티션을 업데이트할 경우에는 파티션 구성원 테이블과 직접 상호 작용할 수 있기 때문에 XACT_ABORT 설정이 필요하지 않습니다.

업데이트할 수 있는 분할된 뷰 요구 사항을 충족하지 않는 테이블은 INSTEAD-OF 트리거로 업데이트할 수 있습니다. 이 트리거는 뷰에 실행되는 INSERT, UPDATE 또는 DELETE 문 대신에 호출되며, 데이터를 조사하고 올바른 파티션을 결정한 다음 해당 파티션 구성원 테이블에서 필요한 동작을 수행하는 코드를 포함합니다. 단, INSTEAD-OF 트리거를 사용하는 뷰에서는 실제 분할된 뷰의 실행 계획과 같이 효율적인 실행 계획이 세워지지 않는 것이 보통입니다.

데이터 종속 라우팅(DDR) 구현

다른 종류의 시스템과 마찬가지로 응용 프로그램 디자인이 성능에 커다란 영향을 줍니다. 데이터 종속 라우팅을 사용하면 라우팅 정보를 통해 해당 서버로 직접 이동할 수 있기 때문에 SQL Server 실행 서버 간의 소통량을 줄일 수 있습니다. 이 방법에서는 대상 데이터의 위치를 확인하는 코드를 사용한 다음 해당 서버를 향하도록 연결 경로를 지정합니다. 데이터는 분산 분할된 뷰의 데이터처럼 어려움 없이 배치됩니다. 데이터 종속 라우팅과 분산 분할된 뷰의 차이는 응용 프로그램에서 데이터를 사용할 수 있게 된 후 해당 데이터가 이동하는 방법에 대한 정보입니다. 데이터 종속 라우팅은 연결된 서버 연결을 사용하지 않고 해당 서버에 데이터 요청을 직접 보냅니다. 분할된 테이블을 포함하는 데이터베이스의 이름과 구조가 같으면 데이터 종속 라우팅을 사용하는 것이 가장 좋습니다. 라우팅 테이블의 차이에 대해 설명할 수 있지만, 이렇게 하면 디자인이 더 복잡해집니다.

그림 16은 중간 계층에서 데이터 종속 라우팅 정보를 사용하는 연합 서버 솔루션을 보여줍니다.

rag0516

그림 16 데이터 종속 라우팅 논리

표준 연합 서버 디자인에서는 분산 분할된 뷰와 데이터 종속 라우팅을 둘 다 사용하여 성능을 극대화합니다. 이를 수행하는 간단한 방법은 응용 프로그램이 어떤 서버의 데이터를 요청할 것인지에 대한 정보를 포함하는 라우팅 테이블을 만드는 것입니다. 라우팅 테이블을 넣는 위치에 대한 결정이 중요합니다. SQL Server에 라우팅 테이블을 넣으면 필요할 때마다 손쉽게 라우팅 테이블을 업데이트할 수 있으므로 관리하기가 쉽습니다. 하지만 분할된 테이블의 데이터를 검색할 때마다 SQL Server의 라우팅 테이블을 검색할 필요가 없도록 응용 프로그램을 디자인해야 합니다. 각 응용 프로그램 서버의 첫번째 요청에서는 SQL Server 데이터베이스에 있는 라우팅 정보를 검색하여 캐시(예: IIS의 응용 프로그램 수준 레코드 집합 또는 COM+ 공유 속성 관리자의 배열)에 저장할 수 있습니다. 그 이후의 모든 요청에서는 캐시에 저장된 데이터를 사용하여 특정 레코드 검색을 위한 연결에 가장 적절한 서버를 판별합니다.

라우팅 테이블을 사용하는 방법 대신에 분할 열 기반의 사용자 지정 해싱 알고리즘을 사용하여 올바른 파티션이나 서버에 데이터를 보낼 수도 있습니다. 이 방법을 사용하면 알고리즘만 변경해도 파티션 수를 변경할 수 있기 때문에 재분할 작업이 수월해집니다.

여기서 중요하게 고려해야 할 사항은 데이터 이동을 통해 사용률의 균형을 조정하여 데이터 파티션을 가장 손쉽게 관리할 수 있는 방법이 무엇인지 결정하는 것입니다. 이를 결정하지 못하면 커다란 문제가 발생할 수 있습니다.

라우팅 테이블을 사용하는 경우에는 적어도 특정 데이터에 대한 쿼리를 실행하기에 가장 적절한 서버를 결정하는 데 필요한 정보가 테이블에 포함되어야 합니다. 매번 다른 라우팅 규칙(예: 유지 관리 일정을 수용하는 라우팅 규칙)을 적용할 수 있게 해주는 추가 열을 포함시키거나 더 복잡한 라우팅 논리를 사용할 수 있습니다. 예를 들어, 이 장에 설명된 전자 상거래 솔루션 예제에서 요청을 적절히 라우팅하는 데 필요한 논리는 다음과 같습니다.
Lower Range
Upper Range
Column Name
table Name
Server Instance
Begin Date
End Date
Active Flag
0
10
Category
Products_1
SQLServer1
01 jan 2001

Y
11
20
Category
Products_2
SQLServer2
01 jan 2001

Y
21
30
Category
Products_3
SQLServer3
01 jan 2001

Y

위의 테이블은 단지 예에 불과하며, 필요한 모든 정보를 사용하여 테이블을 디자인하고 해당 응용 프로그램에 적합한 형태로 저장할 수 있습니다. 다른 서버에 제어를 양도해야 하거나 유지 관리 창 없이 다시 분할하는 경우에는 Begin Date와 End Date 열에서 정지 및 장애 조치에 대한 예정을 세우는 것이 약간 유연합니다. 서버 정지를 감지하여 보고하도록 오류 처리 코드를 추가하는 것이 좋습니다. 또한 적어도 분할된 데이터와 관련된 모든 테이블에 감사 열을 추가하십시오.

복잡한 라우팅 논리가 필요하더라도 성능을 저하시키지 않으려면 라우팅 논리를 비교적 간단하게 유지해야 합니다. 분명한 사실은 라우팅 논리의 성능을 신중하게 테스트하여 해당 라우팅 논리로 인한 오버헤드를 원격 쿼리로 인한 오버헤드보다 낮추어야 한다는 점입니다!

참고: 여기서는 연합 서버에 대해서만 설명하지만, 모든 응용 프로그램에서 DDR을 사용하여 기본 데이터 원본을 사용할 수 없는 경우 다른 데이터 원본에 프로그래밍 방식으로 연결할 수 있습니다.

ASP 응용 프로그램에서 라우팅 정보 캐싱

다음과 같이 global.asa 스크립트에서 라우팅 테이블을 포함하는 서버에 대한 연결 문자열과 함께 응용 프로그램 수준 변수를 초기화하여 ASP 응용 프로그램에서 라우팅 정보를 캐싱할 수 있습니다.

<SCRIPT LANGUAGE=VBScript RUNAT=Server> Sub AppLIcation_OnStart Set AppLIcation("rsRouting") = Nothing AppLIcation("CONN_StrING") = _ "PROVIDER = SQLOLEDB;DATA SOURCE=(local);" & _ "INITIAL CATALOG=FederatedRetail;" & _ "USER ID=retailsite;PASSWORD=password;" End Sub </SCRIPT>

응용 프로그램 수준 변수는 ASP 스크립트에서 검사되며 특정 사용자의 프로필 데이터를 요청해야 합니다. 응용 프로그램 수준 변수가 라우팅 정보를 포함하지 않으면 라우팅 테이블을 포함하는 레코드 집합이 검색되고 해당 연결이 중단됩니다. 이렇게 검색된 레코드 집합은 그 이후의 모든 데이터 요청에서 사용할 수 있습니다. 제품 데이터를 검색하는 데 사용된 ASP 코드는 다음 예제와 비슷한데, 여기서 인증된 사용자의 CustomerID는 서버에 쿠키 형태로 전달됩니다.

>& Dim Lgn Dim rs Dim strConn Dim strInstance Lgn = Request.Cookies("LoginID") 'Add routing information to cache if it is not already there If AppLIcation("rsRouting") Is Nothing Then Set rs = server.CreateObject("ADODB.Recordset") rs.CursorLocation = 3 rs.Open "SELECT * FROM routing", AppLIcation("CONN_StrING"), 3, 4 Set rs.ActiveConnection = Nothing Set AppLIcation("rsRouting") = rs End If 'Construct correct connection string from cached routing table Dim conFound AppLIcation("rsRouting").MoveFirst conFound=False Do Until AppLIcation("rsRouting").EOF OR ConFound If AppLIcation("rsRouting").fields("LowerRange").Value <= Cint(Lgn) And AppLIcation("rsRouting").fields("UpperRange").Value >= Cint(Lgn) Then conFound = true strInstance = AppLIcation("rsRouting").fields("ServerInstance").value strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=" & strInstance & _ ";INITIAL CATALOG=FederatedRetail;" & _ "USER ID=retailsite;PASSWORD=password;" 'Note that standard security is used because the IUsr_X 'user is a local account (i.e. not a domain member) End If AppLIcation("rsRouting").MoveNext Loop 'Get Profile Data Dim rsCustProfile Dim cmd Set cmd = Server.CreateObject("ADODB.Command") With cmd .ActiveConnection = strConn .CommandText = "GetCustomerProfile" .CommandType = 4 .Parameters.Append(cmd.CreateParameter("LoginID", 3, 1, 4, Lgn)) End With Set rsCustProfile = cmd.Execute Response.Write "Hello " & rsCustProfile.fields("FirstName").value Response.Write " Your email address is " & _ rsCustProfile.Fields("EMail").Value %<

COM+ 응용 프로그램에서 라우팅 정보 캐싱

대부분 전자 상거래 응용 프로그램에서 물리적 데이터 액세스는 ASP 페이지에서 직접 수행되는 것이 아니라 구성 요소의 코드에서 수행됩니다. 이런 경우에는 응용 프로그램 구성 요소에서 다시 사용할 수 있도록 라우팅 정보를 캐싱해야 합니다. COM+ 공유 속성 관리자(SPM)는 이러한 용도에 사용할 수 있습니다. SPM은 COM+ 응용 프로그램에서 변형 값을 저장하는 데 사용되는 개체입니다. SPM으로는 레코드 집합과 같은 개체 변수를 저장할 수 없기 때문에 라우팅 테이블을 배열로 변환해야 하는데, 이러한 변환은 다음 코드 예제와 같이 레코드 집합 개체의 Getrows 메서드를 사용하여 수행할 수 있습니다.

PubLIc Function getCustomer(ByVal intLoginID As Integer) As ADODB.Recordset Dim SPM As COMSVCSLIb.SharedPropertyGroupManager Dim SPG As COMSVCSLIb.SharedPropertyGroup Dim SP As COMSVCSLIb.SharedProperty Dim bExists As Boolean Dim strConn Dim strInstance Set SPM = New COMSVCSLIb.SharedPropertyGroupManager Set SPG = SPM.CreatePropertyGroup("RoutingProps", LockSetGet, Process, bExists) Set SP = SPG.CreateProperty("routingtable", bExists) 'Add Routing table to cache if it is not already there If Not bExists Then Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.CursorLocation = adUseCLIent rs.Open "SELECT * FROM routing", CONN_StrING, adOpenStatic, adLockBatchOptimistic Set rs.ActiveConnection = Nothing SP.Value = rs.Getrows End If 'Get Correct connection string cached routing table Dim aRouting aRouting = SP.Value Dim i As Integer For i = 0 To UBound(aRouting) If aRouting(0, i) <= intLoginID And aRouting(1, i) >= intLoginID Then strInstance = aRouting(4, i) strConn = "PROVIDER = SQLOLEDB;DATA SOURCE=" & _ strInstance & _ ";INITIAL CATALOG=FederatedRetail;" & _ "INTEGRATED SECURITY=sspi;" 'Note that integrated security can be used because 'the COM+ appLIcation can be assigned a domain account Exit For End If Next i 'Get Profile Data Dim cmd As ADODB.Command Dim rsCustomer As ADODB.Recordset Set cmd = New ADODB.Command With cmd .ActiveConnection = strConn .CommandText = "GetCustomerProfile" .CommandType = adCmdStoredProc .Parameters.Append cmd.CreateParameter("LoginID", adInteger, adParamInput, 4, intLoginID) End With Set rsCustomer = cmd.Execute Set getCustomer = rsCustomer End Function

해시 분할 구현

해시 분할은 키 계산을 통해 레코드가 저장되는 서버를 판별하는 해싱 알고리즘이 데이터 선택용 키의 기반으로 사용된다는 점을 제외하고 데이터 종속 라우팅과 비슷합니다. 다음 예제에서 고객은 웹 사이트에 로그인하여 자신의 사용자 프로필을 활성화할 수 있어야 합니다. 분할 키에 사용할 필드로는 전자 메일 필드를 선택하는 것이 가장 좋지만, 전자 메일 필드는 길이가 변하는 문자 필드이므로 대부분의 경우에는 숫자 값을 분할에 사용하는 것이 좋습니다. 또한 손쉬운 유지 관리를 위해서는 동적 값을 기반으로 파티션을 디자인해야 합니다.

해시와 라우팅 테이블을 조합하여 정수 키의 부족이나 다중 열 키의 사용을 손쉽게 보정할 수 있지만, 이 장에서는 라우팅 테이블 없이 해시를 사용하는 방법을 보여주는 다음 예제를 사용합니다.

해시를 분할에 사용하는 방법에는 여러 가지가 있으며 여기에 설명된 방법은 단지 한가지 예에 불과합니다. 이 예제에서는 연합 서버 수를 모듈러스(Modulus)로 사용하여 서버를 식별하고 서버에 매핑하며 연합 서버에서 라우팅 테이블의 필요성을 제거합니다(라우팅 테이블을 포함시키면 관리 효율성을 높일 수는 있음). 모듈러스 3을 사용하여 세 데이터 위치를 처리하는 방법을 보려면 다음 코드 조각을 실행한 다음 100개의 행이 테이블 변수에서 만들어지고 해시가 각 행에 1~3의 숫자를 할당하는 것을 확인합니다. 이 예제에서는 서버가 세 대이므로 모듈러스가 3으로 설정됩니다. 단, 이해를 돕기 위해 해시 결과에 1이 추가되므로 서버와 파티션의 번호가 0~2가 아닌 1~3으로 지정됩니다.

set nocount on declare @Locations smalLInt, @counter tinyint, @current tinyint select @current = 1, @counter = 100, @Locations = 3 declare @tab table (modulos int) while @counter >= @current begin insert @tab SELECT (@current % @Locations) + 1 select @current = @current + 1 end select [Server assigned followed by total locations] = modulos from @tab group by modulos compute count (modulos)

이 전제 조건은 다음 함수에 적용되며, 다음 함수 예제에서 전자 메일 별칭의 처음과 마지막 문자(@ 기호의 앞에 오는 부분)에 대한 ASCII 값을 기반으로 해시 값이 할당됩니다. 예제는 다음과 같습니다.

create function dbo.udf_hashmail2 (@EMail varchar(100), @GroupServerCount int) returns table as return (select [Server or Group ID] = (ascii(left(@EMail, 1)) % @GroupServerCount) + 1 , [Partition ID] = (ascii( substring( @EMail, charindex('@', @EMail) - 1, 1)) % @GroupServerCount) + 1 ) GO -- This statement shows how this function could be executed: declare @email varchar(100), @gl_servercount smalLInt select @gl_servercount = 3, @email = 'plato@msn.com' select [Server or Group ID], [Partition ID] from dbo.udf_hashmail2 (@email, @gl_servercount)

따라서 새 고객 프로필이 만들어질 때 해시 키가 데이터 삽입 위치를 결정하고, 고객이 시스템에 로그인하여 자신의 전자 메일 주소를 입력할 때 해당 레코드를 찾는 해시 키가 결정됩니다.

이것이 올바르게 작동하는 데 필요한 마지막 부분은 서버 이름과 파티션 이름입니다. DDR에서처럼 이 방법과 조회 테이블을 조합할 수 있습니다. 그렇지 않으면 이름은 같게 하고 식별할 수 있는 숫자만 달리하여 각 서버를 명명할 수 있습니다. 마찬가지로 각 파티션도 이름은 동일하고 접미사의 숫자만 다르게 지정할 수 있습니다(예: MyServer001, MyServer002 등). 서버 내의 파티션도 이와 비슷하게 이름을 지정할 수 있습니다. 응용 프로그램이 로드될 때, 파티션 위치(이 예제의 경우 서버와 테이블)가 생성되고 이 정보가 캐시에 저장됩니다. 다음 코드 예제는 T-SQL에서 이 이름을 생성하는 방법을 보여줍니다.

create function dbo.udf_get_locname (@hashkey smalLInt, @locname varchar(30) ) returns table as return (select [Location of Data] = @locname+ substring( '00'+ convert(varchar(3), @hashkey), len(@hashkey), 3 ) ) GO -- This statement shows how this function could be executed: declare @hashkey smalLInt, @locname varchar(30) select @hashkey = 3 ,@locname = 'MyServer' select [Location of Data] from dbo.udf_get_locname (@hashkey, @locname)

일반적으로 사용자는 서버에 연결하지 않고도 이 정보를 얻을 수 있기를 바라겠지만, 이미 서버에 연결된 상태라면 이 정보가 필요할 때마다 T-SQL 버전을 사용하는 것이 좋습니다. DBA가 보고 시스템에 필요한 데이터를 추출하거나 레코드를 찾아야 할 때는 특히 그렇습니다.

다음 코드는 Microsoft Visual Basic®에서 특정 전자 메일 주소와 관련된 올바른 서버와 파티션을 결정하는 중간 계층 구성 요소를 작성하는 방법을 보여줍니다.

PubLIc Function HashPartition(ByVal strEMail As String) As String intNumServers = 3 strBaseServerName = "Server00" strBasetableName = "table00" 'First, assign a hash values using the EMail address Dim intServerID As Integer Dim intPartitionID As Integer intServerID = (Asc(strEMail) Mod intNumServers) + 1 intPartitionID = (Asc(Mid$(strEMail, InStr(strEMail, "@") - 1)) _ Mod intNumServers) + 1 'Now assign the server and table names Dim strServerName As String Dim strtableName As String strServerName = strBaseServerName & CStr(intServerID) strtableName = strBasetableName & CStr(intPartitionID) 'Return the hashed values as XML Dim xmlHashData As String xmlHashData = "<hashdata>" xmlHashData = xmlHashData & "<servername>" & strServerName & _ "</servername>" xmlHashData = xmlHashData & "<tablename>" & strtableName & _ "</tablename>" xmlHashData = xmlHashData & "</hashdata>" HashPartition = xmlHashData End Function

이 경우, 서버와 파티션은 예측 가능한 방식으로 명명되고, 해시 데이터는 다음 형식의 XML 문자열로 반환됩니다.

<hashdata> <servername>Server002</servername> <tablename>table001</tablename> </hashdata>

여기에 나와 있는 논리는 중간 계층이나 데이터 계층에서 효과적으로 처리할 수 있지만, 관리 효율성과 융통성 그리고 담당자가 회사 업무 표준 내에서 명확히 지원할 수 있는 사항을 기준으로 논리 저장 위치를 선택해야 합니다. 일반적으로 처리 논리는 모듈러스와 서버 둘 다에 저장되며, 파티션 이름은 데이터베이스에 저장되지만 응용 프로그램 계층에서 캐싱됩니다.

해시 분할은 서버나 파티션을 추가하는 경우 모듈러스 조정이 간편할 때 가장 효과적입니다. 여기에 나와 있는 코드에 또 다른 변수를 추가하여 서버 수와 파티션 수를 다르게 만들 수 있지만, 서버 당 파티션 수가 달라지면 데이터 위치를 확인하기 위해 DDR 라우팅 테이블과 비슷한 테이블이 필요합니다.

모듈러스, 서버 및 파티션 이름에 대한 정보는 전역 변수에 저장해야 하며, 나중에 변경해야 할 이름은 하드코딩하지 않아야 합니다. DDR과 해시 분할을 함께 사용하는 경우에는 서버 이름과 파티션 이름 그리고 현재 모듈러스와 새 모듈러스를 라우팅 테이블에 저장할 수 있습니다. 이 요구 사항은 서버 이름과 파티션 이름이 예측 가능한 방식을 따르지 않는 경우에만 적용됩니다.

자동 조정 시스템

해시 분할이나 DPV를 사용하여 응용 프로그램을 연합 서버에서 추상화된 상태로 유지할 수 있지만, 시스템을 전체 응용 프로그램에 걸쳐 분할된 상태로 디자인하면 데이터 분산의 변화에 반응하여 고유 프로세스를 조정하도록 중간 계층이나 데이터 계층에 기능을 넣을 수 있다는 커다란 장점이 제공됩니다.

예를 들어, 서비스 중단 없이 파티션 사이에서 데이터를 이동할 수 있게 해주는 프로시저와 코드를 구현하는 경우를 가정해 봅시다. 이를 수행하는 방법 중 하나는 파티션 구성원 테이블에 status(값: current, relocate, delete), NewGroupServerID 및 NewPartitionID 열을 추가하는 것입니다. 응용 프로그램은 프로필이 검색될 때마다 레코드의 상태를 검사하여 다른 파티션으로 이동할 수 있다는 플래그가 붙어 있는지 확인합니다. 다른 파티션으로 이동할 수 있다는 플래그가 붙어 있는 경우, 응용 프로그램은 같은 세션에서 레코드가 다시 검색될 경우 해당 레코드의 위치를 다시 검사할 수 있게 해주는 논리를 포함해야 합니다.

또한 DBA는 데이터베이스 서버에 모니터링 기능을 추가하여 작업 수준이 높은 파티션을 감지해야 합니다. DBA는 한 파티션의 사용률이 높은 경우 재분할을 초기화하는 자동 프로세스를 만들기보다 높은 사용률이 감지되었음을 알리는 경고를 만들어야 합니다. 그래야 DBA가 충분한 정보를 가지고 데이터를 재분할하는 방법과 프로세스를 예약된 시간에 초기화하는 방법을 결정할 수 있습니다.

복제된 데이터 업데이트

각 연합 서버마다 동일해야 하는 테이블이 있을 수 있습니다. 복제를 사용하지 않는 경우 각 서버의 테이블마다 변경 사항을 업데이트하려면, 간단하게 INSTEAD-OF 트리거를 사용할 수 있습니다. 다음은 이를 수행하는 방법의 예로, 여기서는 이해를 돕기 위해 오류 처리 부분을 생략했습니다.

-- table that must be copied to each server CREATE table ProfileOptions ( [ProfileOptionID] int not null, [SettingGroupID] int not null, [Description] char(50) not null, [ActiveFlag] char(1) not null default('Y') ) GO CREATE trIGGER IO_trig_I_ProfileOptionRepl on ProfileOptions INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON IF (NOT EXISTS (SELECT p.[ProfileOptionID] FROM [Server001].[DB1].[dbo].[ProfileOptions] p JOIN [inserted] i ON p.[SettingGroupID] = i.[SettingGroupID] AND p.[Description] = i.[Description]) ) INSERT INTO [Server001].[DB1].[dbo].[ProfileOptions] SELECT [ProfileOptionID], [SettingGroupID], [Description], case when [ActiveFlag] not in ('Y', 'N') then 'Y' else [ActiveFlag] end FROM [inserted] IF (NOT EXISTS (SELECT p.[ProfileOptionID] FROM [Server002].[DB2].[dbo].[ProfileOptions] p JOIN [inserted] i ON p.[SettingGroupID] = i.[SettingGroupID] AND p.[Description] = i.[Description]) ) INSERT INTO [Server002].[DB2].[dbo].[ProfileOptions] SELECT [ProfileOptionID], [SettingGroupID], [Description], case when [ActiveFlag] not in ('Y', 'N') then 'Y' else [ActiveFlag] end FROM [inserted] IF (NOT EXISTS (SELECT p.[ProfileOptionID] FROM [Server003].[DB3].[dbo].[ProfileOptions] p JOIN [inserted] i ON p.[SettingGroupID] = i.[SettingGroupID] AND p.[Description] = i.[Description]) ) INSERT INTO [Server003].[DB3].[dbo].[ProfileOptions] SELECT [ProfileOptionID], [SettingGroupID], [Description], case when [ActiveFlag] not in ('Y', 'N') then 'Y' else [ActiveFlag] end FROM [inserted] END GO CREATE trIGGER IO_trig_U_ProfileOptionRepl on ProfileOptions INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON UPDATE p SET p.[Description] = isnull( i.[Description], p.[Description]) , p.[ActiveFlag] = case when i.[ActiveFlag] not in ('Y','N') then 'Y' else i.[ActiveFlag] end FROM [Server001].[DB1].[dbo].[ProfileOptions] p INNER JOIN [inserted] i ON p.[ProfileOptionID] = i.[ProfileOptionID] AND p.[SettingGroupID] = i.[SettingGroupID] UPDATE p SET p.[Description] = isnull( i.[Description], p.[Description]) , p.[ActiveFlag] = case when i.[ActiveFlag] not in ('Y','N') then 'Y' else i.[ActiveFlag] end FROM [Server002].[DB2].[dbo].[ProfileOptions] p INNER JOIN [inserted] i ON p.[ProfileOptionID] = i.[ProfileOptionID] AND p.[SettingGroupID] = i.[SettingGroupID] UPDATE p SET p.[Description] = isnull( i.[Description], p.[Description]) , p.[ActiveFlag] = case when i.[ActiveFlag] not in ('Y','N') then 'Y' else i.[ActiveFlag] end FROM [Server003].[DB3].[dbo].[ProfileOptions] p INNER JOIN [inserted] i ON p.[ProfileOptionID] = i.[ProfileOptionID] AND p.[SettingGroupID] = i.[SettingGroupID] END GO CREATE trIGGER IO_trig_D_ProfileOptionRepl on ProfileOptions INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON IF EXISTS (select [ProfileOptionID] from [deleted]) UPDATE p SET [ActiveFlag] = 'N' FROM [Server001].[DB1].[dbo].[ProfileOptions] p JOIN [deleted] d ON p.ProfileOptionID = d.ProfileOptionID AND p.SettingGroupID = d.SettingGroupID IF EXISTS (select [ProfileOptionID] from [deleted]) UPDATE p SET [ActiveFlag] = 'N' FROM [Server002].[DB2].[dbo].[ProfileOptions] p JOIN [deleted] d ON p.ProfileOptionID = d.ProfileOptionID AND p.SettingGroupID = d.SettingGroupID IF EXISTS (select [ProfileOptionID] from [deleted]) UPDATE p SET [ActiveFlag] = 'N' FROM [Server003].[DB3].[dbo].[ProfileOptions] p JOIN [deleted] d ON p.ProfileOptionID = d.ProfileOptionID AND p.SettingGroupID = d.SettingGroupID END GO

개발 및 테스트 환경

개발 환경을 설정하는 옵션에는 다음 두 가지가 있습니다.

각각의 파티션 구성원 테이블마다 이름이 다르면 모든 파티션을 하나의 데이터베이스에 조합하거나 같은 인스턴스의 다른 데이터베이스에 넣을 수 있습니다. 이것은 가장 간단한 방법으로, 분할 가능한 시스템을 디자인하고 있는 경우 일반 디자인보다 넓게 확장하여 급격한 사용률 증가에 대비할 수 있기 때문에 매우 효과적입니다.
또는 단일 컴퓨터에서 각 파티션에 대한 SQL Server 인스턴스를 만들 수 있습니다. 이 방법은 연결된 서버라는 차이만 제외하고 프로덕션 환경과 거의 비슷한 시뮬레이션입니다. 별도의 서버에 분할된 데이터베이스를 넣으면 실제 서버 너머로 이동하지 않아도 될 때의 인증 수행 방식에 약간의 차이가 있기 때문에 연결과 관련된 보안 문제가 발생할 수 있습니다.

참고: 실제 같은 서버에서 실행되는 여러 인스턴스의 파티션으로 이루어진 프로덕션 시스템을 실행할 수 있지만, 실제적인 이익을 얻을 수 없으므로 권장되지 않습니다. SQL Server에서 리소스 간의 로드 균형을 조정하도록 만들면 이 파티션이 같은 시스템의 모든 부분이기 때문에 서버 간의 리소스를 더 잘 격리할 수 있습니다. 하지만 한 대의 서버 내에서 분할해야 하는 경우에는 하나의 인스턴스에서 분할한 다음 앞부분의 "연합 디자인 시 고려 사항"에 설명된 것처럼 여러 파일 그룹에 파티션을 분산하십시오.

분할 가능한 시스템을 디자인하고 있으면 해당 시스템을 즉시 연합 서버로 구현할 것인지 여부와 상관 없이, 같은 서버의 각 인스턴스가 아닌 연합 서버의 개별 서버에서 실제 테스트 스크립트를 사용하여 철저히 테스트해야 합니다. 그러면 프로덕션 환경에 시스템을 배치하기 전에 나머지 문제를 찾아 해결할 수 있습니다.

테스트 과정에서 반드시 필요한 단계는 시스템을 유지 관리하는 방법을 배우는 것입니다. 새 시스템의 경우에는 충분한 데이터를 만들어서 몇 가지 장기 테스트를 실행하고 다양한 종류의 오류를 시뮬레이션하여 오류가 발생한 시스템을 복구하는 작업을 해야 합니다. 그러면 프로덕션 환경에 대한 지침을 제공하는 실무 문서를 만드는 데 도움이 됩니다.

테스트 스크립트를 실행하는 동안에는 다음 작업을 수행하여 각 동작의 소요 시간을 비롯한 발견 사항을 문서화해야 합니다.

성능 카운터와 프로필러 추적을 수집하여 기준을 파악하고 변경 사항이 생긴 후 성능을 비교할 수 있게 데이터를 보관합니다.
작업을 파티션별로 모니터링합니다.
단일 파티션과 다중 파티션을 백업하고 복원합니다.
서버 간 동기화가 해제된 데이터를 복원합니다(해당하는 경우).
테스트 과정에서 일반적인 정기 유지 관리 작업이 서버에 어떤 영향을 주는지 확인합니다.
주 전원의 정전을 초래하지 않는 상태로 분할된 개체에 대한 개체 변경을 구현합니다.
최소 정지 시간으로 데이터를 다시 분할합니다.
수정하려면, 즉 파티션을 변경, 추가, 제거하거나 새 서버를 향하도록 파티션의 방향을 지정하려면 라우팅 테이블을 업데이트합니다.
재난 복구 계획을 테스트하면 실제적인 문제가 발생해도 프로덕션 환경에서 해당 계획을 테스트할 필요가 없습니다. 따라서 다음 테스트를 미리 수행하는 것이 좋습니다.
응용 프로그램이 실행되는 동안 파티션을 제거했다가 다시 복원하고, 해당 응용 프로그램이 어떻게 반응했는지 기록합니다.
잘못된 데이터를 사용하여 라우팅 테이블(별도의 개체인 경우)을 무효로 만들고 어떤 동작이 발생하는지 살펴봅니다.
클러스터를 사용하고 있으면 클러스터를 장애 조치하고 어떤 동작이 발생하는지 살펴봅니다. 먼저 하나의 클러스터만 장애 조치한 다음 모든 클러스터를 장애 조치하여 최악의 경우를 테스트합니다. 모든 클러스터가 올바르게 작동하면 원래의 장애 없는 상태로 서버를 복구합니다. 전체 과정과 발견 사항을 세밀하게 기록합니다.
원격 서버 사이트가 있으면 원격 사이트에 대한 장애 조치/복구 단계를 거쳐야 합니다.

위와 같은 종류의 테스트를 완료하고 발견 사항을 문서화했으면 프로덕션 환경에서 시스템을 지원할 준비가 제대로 된 것입니다.

파티션 유지 관리

기존의 대규모 시스템을 성공적으로 분할하려면 대개 일련의 개선 작업이 필요합니다. 각 단계에서 분할용으로 선택되는 테이블은 대개 해당 시점에서 가장 높은 성능상 이점을 제공하는 테이블입니다. 데이터 사용률이 시간에 따라 변하기 때문에 데이터를 계속 효과적으로 사용하려면 파티션을 때때로 조정해야 합니다.

테이블 간의 로드 균형 조정이나 재분할은 지속적인 노력을 필요로 하는 작업이지만, 아직은 이를 자동으로 수행할 수 있는 방법이 없습니다. 파티션을 계획할 때 계산이 틀리면 전체 테이블을 다시 분할해야 할 수 있습니다. 예를 들어, Orders 테이블을 순차적 주문 번호별로 분할하면 해당 그룹 내의 마지막 파티션이 가장 커질 뿐 아니라 가장 자주 쿼리되고, Orders 테이블을 지역별로 분할하면 한 지역이 나머지 다른 지역보다 빠르게 커지거나 자주 쿼리됩니다. 두 경우 모두 이 데이터 중 일부를 이동하여 로드 균형을 다시 조정해야 합니다. 로드 균형 재조정을 자주 수행하면 이동하는 데이터의 양이 점점 작아지기 때문에 이 작업을 수행하는 게 그다지 어렵지 않습니다.

이를 수행하는 가장 직접적인 방법은 파티션 사이에서 데이터를 이동하는 동안에는 임시로 해당 제약 조건을 사용하지 않도록 설정했다가 데이터 이동 작업이 완료될 때 해당 제약 조건을 다시 만드는 것입니다. 파티션을 조정하는 동안만이라도 응용 프로그램에서 데이터 종속 라우팅을 전용으로 사용할 수 있으면 정지 시간을 염려할 필요가 없습니다. 먼저 데이터를 이동하고 라우팅 테이블을 업데이트하고 특정 파티션 관련 정보를 포함하는 데이터 종속 라우팅 관련 개체 또는 COM 개체를 업데이트한 다음 데이터 종속 라우팅 정보의 캐시를 새로 고치십시오. 그런 다음 마지막으로 특정 파티션에 없어야 하는 행을 삭제해야 하는데, 이는 해당 행이 더 나은 위치로 이동했기 때문입니다.

새 파티션을 추가하는 경우에는 위의 작업과 거의 비슷한 작업을 수행한 다음 새 파티션을 인식해야 하는 분산 분할된 뷰나 기타 SQL 프로시저, 함수 또는 작업(특히 백업 작업)을 변경해야 합니다.

분할된 테이블은 파티션을 유지 관리해야 한다는 점을 제외하고 다른 테이블과 같습니다. 각각의 연합 서버와 데이터베이스마다 따로 유지 관리해야 합니다. 인덱스 유지 관리와 연합 서버/데이터베이스 백업은 여전히 필요합니다. 가끔씩 DBCC 검사를 수행하여 가능한 완벽하게 유지 관리해야 합니다. 연합 내 유지 관리를 통해 얻을 수 있는 장점 중 하나는 모든 파티션에 대한 유지 관리를 동시에 수행할 수 있다는 것인데, 이는 각 파티션이 독립된 데이터베이스이고 각 파티션마다 별도의 유지 관리가 실행되기 때문입니다. 또한 DBCC와 인덱스 만들기 과정에서 병렬 처리를 통한 이점이 각 파티션마다 제공되는데, 이는 Enterprise Edition의 특징입니다.

재난 복구 및 분할

다음 절에서는 분할된 데이터베이스에 대한 최적의 백업 후 복원 전략을 수립하는 데 도움이 되는 팁을 제공합니다.

분할된 데이터베이스 백업 및 복원

SQL Server 2000에서는 구성원 서버 간의 백업을 조정할 필요가 없습니다. 구성원 서버 간의 트랜잭션 일관성을 유지할 필요가 없으면 다른 구성원 데이터베이스의 상태와 관계 없이 각 데이터베이스마다 따로 백업할 수 있습니다. 이 방법을 사용하면 동기화 오버헤드가 최소화되어 트랜잭션 처리에 가장 적은 영향을 줍니다. 또 다른 옵션은 모든 데이터베이스를 동시에 백업하는 것입니다. 하지만 이 옵션을 사용하면 확장이 불가능하기 때문에 관리하기가 너무 어렵고 백업 장치를 공유할 수 없습니다.

모든 파티션을 동일한 시점으로 백업하여 트랜잭션 일관성을 유지해야 한다면 SQL Server 2000의 트랜잭션 로그에 명명된 표시를 사용하여 이 요구 사항을 충족할 수 있습니다. 이 특수 표시를 사용하면 분할된 뷰를 사용하여 데이터베이스를 명명된 시점으로 복원할 수 있습니다. 이름 표시를 사용하면 구성원 테이블이 있는 모든 데이터베이스를 같은 시점까지 동기화할 수 있습니다. 이렇게 하려면 파티션에 속해 있는 각 데이터베이스의 복구 모드를 전체 복구 모드로 설정해야 합니다.

백업 후 복원 작업을 수행하는 동안에는 분산 분할된 뷰와 데이터 종속 라우팅을 사용하는 분할된 데이터베이스를 동기화된 상태로 유지해야 합니다. 데이터 종속 라우팅은 항상 코드로 제어되는 분할이기 때문에 표시된 백업과 같은 방법을 사용할 수 있습니다. 코딩이나 중간 계층 기술을 사용하면 트랜잭션을 현재 상태로 유지하고 트랜잭션 손실을 막을 수 있습니다.

또한 데이터 종속 라우팅에서는 로그 표시를 활용할 수 있습니다. 하지만 분할된 뷰와 달리, 분할된 관점에서 각 서버가 완전히 서로 독립되기 때문에 데이터 동기화가 해제될 수 있습니다. 즉, 트랜잭션 로그에 표시하도록 서버가 연결되어 있더라도 이론적으로는 각 서버가 응용 프로그램 코드에만 연결됩니다.

데이터 종속 라우팅을 사용하는 분할된 데이터베이스를 복원하려면 손상된 서버가 양호했던 시점의 마지막 표시까지 각 데이터베이스를 롤백하여 모든 서버를 동기화해야 합니다. 롤백을 거치면 트랜잭션이 손실될 수 있는데, 이는 메시지 대기열이나 COM+와 같은 SQL Server 이외의 또 다른 기술을 사용하거나 XML로 트랜잭션 상태를 저장하는 코드를 사용하여 막을 수 있습니다. 이러한 기술을 선택하는 경우에는 손실된 데이터를 복구하기 전에 해당 기술을 신중하게 테스트하십시오.

트랜잭션 표시에 대한 자세한 내용은 SQL Server 온라인 설명서의 "관련 데이터베이스의 백업 및 복구" 및 "명명된 트랜잭션 복구"를 참조하십시오.

가용성 향상을 위한 SQL Server 클러스터

Windows 클러스터와 SQL Server 장애 조치 클러스터를 사용하여 SQL Server 가용성을 향상할 수 있습니다. SQL Server 2000에서는 이전 버전보다 쉽게 SQL Server 클러스터를 설치, 구성 및 유지 관리할 수 있습니다. 또한 4노드 환경이 지원되고 다중 인스턴스 장애 조치 환경에 대한 지원 기능도 향상되었습니다.

클러스터링 순서도

다음 순서도는 효과적인 클러스터된 SQL Server 구현을 결정할 때 수반되는 디자인 과정을 보여줍니다.

현재 사용하는 브라우저가 인라인 프레임을 지원하지 않을 경우 여기를 누르면 새 창에서 볼 수 있습니다.

그림 17 클러스터된 SQL Server 구현의 디자인 과정

클러스터 아키텍처

Windows 2000과 SQL Server 2000은 아무것도 공유하지 않는 클러스터 모델을 지원합니다. 즉, 클러스터의 각 노드는 고유 리소스와 운영 체제를 사용하는 독립형 컴퓨터입니다. 각 노드는 고유 리소스를 관리하고 공유하지 않는 데이터 서비스를 제공합니다. 노드 오류가 발생한 경우, 해당 노드에서 실행되는 디스크와 서비스는 오류가 발생하지 않은 다른 노드로 장애 조치되거나 다시 시작되지만, 특정 시간에 특정 디스크와 서비스 집합 하나는 노드 하나에서만 관리됩니다.

rag0518

그림 18 클러스터 네트워크 연결

SQL Server 2000의 장애 조치 클러스터를 구성하는 방법은 두 가지로, 단일 인스턴스 장애 조치(능동/수동) 구성이나 다중 인스턴스 장애 조치(능동/능동) 구성을 사용할 수 있습니다.

단일 인스턴스 장애 조치 구성

단일 인스턴스 장애 조치 구성에서 클러스터는 단일 SQL Server 인스턴스를 실행합니다. 주 서버에서 오류가 발생하면 클러스터 내의 다른 서버에서 동일한 인스턴스를 실행할 수 있습니다. 이 구성에서는 두 서버가 마스터 데이터베이스와 사용자 데이터베이스 집합을 공유합니다.

다중 인스턴스 장애 조치 구성

다중 인스턴스 장애 조치 구성은 현재 인터넷 데이터 센터 디자인에 사용되지 않습니다. 이 구성에서는 두 능동 노드가 각각 고유의 SQL Server 인스턴스를 실행합니다. 각각의 SQL Server 인스턴스는 전체 서비스를 다르게 설치한 것이며 각기 따로 관리, 업그레이드 및 중단될 수 있습니다.

다중 인스턴스 장애 조치 구성을 구현하려면 다음 작업을 수행해야 합니다.

클러스터에서 둘 이상의 SQL Server 인스턴스를 설치합니다.
특정 노드에서 주 서버로 실행되도록 각 인스턴스를 구성합니다.

rag0519

그림 19 다중 인스턴스 장애 조치 구성

상호 참조 빈도가 높은 데이터베이스는 같은 SQL Server 인스턴스에 배치해야 합니다. 다음은 다른 인스턴스에 배치해야 하는 데이터베이스의 예입니다.

카탈로그 데이터베이스와 응용 프로그램 데이터베이스용 Microsoft Commerce Server 2000
XLANG과 스케줄러 데이터베이스용 Microsoft BizTalk Server

다중 인스턴스 장애 조치 구성을 구현하기 전에 각각의 데이터베이스 응용 프로그램에서 예상 로드를 평가하고 장애 조치가 발생할 경우 단일 노드에서 조합된 로드를 처리할 수 있는지 확인해야 합니다. 그렇지 않으면 단일 인스턴스 장애 조치 모드 클러스터를 두 개 사용하는 방안을 강구해야 합니다.

가용성 및 연합 서버

연합 서버는 장애 조치 기능을 제공하지 않습니다. 각각의 연합 서버는 여전히 독립적인 SQL Server이지만, 장애 조치 클러스터나 로그 전달과 같이 연합에서 전체적으로 구현되는 기술을 사용하는 경우에는 단일 서버로 처리되어야 합니다.

예를 들어, 장애 조치 클러스터에서 각 파티션을 전용 SQL Server 장애 조치 클러스터에 있는 별도의 가상 서버로 만들 수 있습니다. 데이터베이스 파티션이 세 개인 경우, 독립된 장애 조치 클러스터를 세 개 만들려면 서버 여섯 대가 필요하고 이로써 연합에서 장애 조치 기능을 사용할 수 있습니다. 이 때 응용 프로그램에서 몇 가지 작업을 추가로 수행해야 하는데, 이는 DDR을 처리할 뿐 아니라 클러스터를 인식하는 코드를 포함시켜야 하기 때문입니다. 이 방법은 그림 20에 나와 있습니다.

rag0520

그림 20 각 파티션에 대한 클러스터

데이터베이스가 가상 서버 세 대로 분할된 경우, 가장 좋은 솔루션은 그림 21과 같이 네 대 이상의 서버로 Windows 2000 DataCenter를 사용하는 N+1 시나리오를 만드는 것입니다.

rag0521

그림 21 4노드 클러스터

클러스터와 함께 또는 클러스터 대신에 로그 전달을 사용하여 웜 대기 서버를 만들 수 있습니다. 이 서버는 모든 분할된 데이터베이스를 호스팅하는 한 대의 대형 서버(고유 데이터베이스 이름이 있는 경우)이거나 일대일 비율의 분할된 서버와 로그 전달 서버일 수 있습니다.

요약

지금까지 안전하고 성능이 우수하며 확장 가능하고 가용성 높은 데이터 저장소를 제공하도록 SQL Server를 구성하는 방법에 대해 살펴보았습니다.

데이터베이스 솔루션을 디자인할 때는 응용 프로그램의 특수 요구 사항을 고려해야 합니다. 이는 보안 설정과 서버 구성 옵션을 선택하는 데 영향을 줍니다. 프로덕션 환경에서 SQL Server는 일반적으로 신중한 네트워크, 파일 시스템, 레지스트리, 실제 보안 측정값을 올바르게 배치한 상태로 Windows 인증을 사용해야 합니다.

응용 프로그램 디자인에서는 적절한 인덱스를 통합해야 하며, 논리 및 실제 디자인에서는 특정 데이터 액세스 요구 사항에 맞는 최적의 성능을 보장해야 합니다.

SQL Server 솔루션을 수평 확장하기 전에 분산 데이터베이스의 응용 프로그램 디자인과 함축된 관리 효율성을 고려해야 합니다. 또한 데이터 종속 라우팅 테이블이나 해싱과 같이 가장 적절한 데이터 라우팅 솔루션을 식별해야 합니다.

Windows 클러스터는 가용성을 향상하는 데 권장되는 방법입니다. 지원해야 할 데이터베이스 수와 각 데이터베이스의 예상 로드를 세심하게 평가한 다음 단일 인스턴스 장애 조치 구성과 다중 인스턴스 장애 조치 구성 중 하나를 적절히 선택해야 합니다.

이 문서에 포함된 정보는 문서를 발행할 때 논의된 문제들에 대한 Microsoft Corporation의 당시 관점을 나타냅니다. Microsoft는 변화하는 시장 환경에 대처해야 하므로 이를 Microsoft 측의 책임으로 해석해서는 안되며 발행일 이후 소개된 어떠한 정보에 대해서도 Microsoft는 그 정확성을 보증하지 않습니다.

이 문서는 오직 정보를 제공하기 위한 것입니다. MICROSOFT는 이 문서의 정보에 대하여 명시적으로나 암시적으로 어떠한 보증도 하지 않습니다.

Microsoft, Windows, Active Directory, BizTalk Server, Windows NT는 미국 또는 기타 국가에서 Microsoft Corporation의 등록 상표 또는 상표입니다.

여기에 인용된 실제 회사와 제품 이름은 해당 소유자의 상표일 수 있습니다.

1001

최종 수정일 : 2002년 1월 22일