SQL Q&A: Rebuilding Indexes, Disk Queue Length, and More

아티클
08/24/2016

SQL Q&A인덱스 다시 작성, 디스크 큐 길이 외

편집자: Nancy Michell

Q: SQL Server 인덱스는 어떻게 다시 작성됩니까? DBCC DBREINDEX 명령이 디스크 공간을 모두 사용하는 이유와 이 명령이 실패해도 디스크 공간이 해제되지 않는 이유를 알고 싶습니다. 현재 데이터베이스 크기는 90GB이고 가장 큰 테이블은 70GB입니다.

사용 가능한 공간을 10% 남겨두고 DBCC DBREINDEX 명령을 실행하면 사용 가능한 디스크 공간이 모두 사용되고 명령이 실패합니다. 처음에는 데이터베이스 파일이 90GB였는데 DBCC 명령이 실패하면 데이터베이스 파일이 160GB로 늘어나서 증가된 70GB가 해제되지 않습니다. 따라서 이 공간을 다시 확보하기 위해 수동으로 데이터베이스를 여러 번 축소해야 합니다.

이 명령이 제대로 작동해도 데이터베이스 파일의 크기가 160GB가 되고 몇 시간 후 자동 축소를 통해 공간이 자동으로 해제됩니다. DBREINDEX 대신 조각 모음을 수행하면 공간 소모를 줄일 수 있을까요? 클러스터형 인덱스는 없으며 단순 복구 모드를 사용하고 있습니다.

A: 쉽게 말해서 새 인덱스 복사본을 만든 다음 이전 인덱스를 삭제하면 인덱스가 다시 작성됩니다. 따라서 잠깐 동안은 인덱스 복사본이 두 개가 되는 것입니다. 새 인덱스를 만들기 위해서는 원본 인덱스를 만들 때만큼의 데이터베이스 파일 공간이 필요하며, 다시 작성에서 정렬 작업이 필요할 경우에는 정렬을 위해 인덱스 크기의 20%가 추가로 필요합니다.

따라서 최악의 경우 인덱스를 다시 작성하는 데 원래 인덱스 공간의 1.2배가 필요하게 됩니다. 데이터베이스 파일에 사용 가능한 공간이 충분하지 않을 경우 작업이 진행되면 파일 크기가 늘어나야 합니다. 따라서 자동 증가 기능이 설정되어 있지 않거나 디스크 볼륨의 공간이 충분하지 않을 경우 사용 가능한 공간이 부족하여 다시 작성 작업이 실패하게 됩니다.

다시 작성 작업의 실패 여부와 관계없이 작업이 완료된 후 데이터베이스 파일에 할당된 추가 공간이 해제되지 않습니다. 이 공간은 정규 데이터베이스 작업에 사용될 것입니다.

수동으로든 자동으로든 축소를 실행하면 알고리즘 작동 방식 때문에 거의 인덱스 조각화가 발생합니다. 자세한 내용은 SQL Server 저장소 엔진을 참조하십시오. 정규 작업을 위해 데이터베이스에 공간이 필요할 경우에 자동 축소를 수행하면 자동 증가-자동 축소-자동 증가-자동 축소 순환에 빠져 조각화 및 성능에 악영향을 주므로 특히 성능이 저하될 수 있습니다.

DBCC INDEXDEFRAG(SQL Server™ 2005의 경우 ALTER INDEX ... REORGANIZE)를 사용하면 데이터베이스 파일 공간을 추가로 소모하지 않는다는 이점이 있지만 인덱스 다시 작성보다 시간이 더 오래 걸리며 트랜잭션 로깅이 훨씬 더 많이 생성됩니다. DBCC INDEXDEFRAG는 사용 중인 복구 모드와 상관없이 항상 완전 로그되지만, 단순 복구 모드에서 인덱스 다시 작성은 대량 로그됩니다. 각 방법에는 여러 가지 장단점이 있는데 이에 대해서는 SQL Server 인덱스 조각 모음을 위한 최선의 방법 백서에 자세히 설명되어 있습니다.

조각화 해결 방법을 결정하기 전에 먼저 조각화를 해결할지 여부를 결정하십시오. 인덱스가 사용되는 작업 유형에 따라 조각화가 성능에 별 영향을 주지 않을 수 있으므로 조각화를 해결하는 것이 리소스 낭비가 될 수 있습니다. 자세한 내용은 위 백서를 참조하십시오.

결론: 현재 환경에 가장 적합한 조각화 제거 방법을 선택하고 조각화를 제거하면 쿼리 성능에 도움이 됩니다.

Q: 두 SQL Server 2005 인스턴스 간의 데이터베이스 미러링을 성공적으로 구성했습니다. 응용 프로그램은 SQL Server 로그인을 사용하여 SQL Server에 연결되어 있으며 ADO 및 SQL Native Client를 사용하여 작성되었습니다. 연결 문자열과 연결 설정에는 적절한 장애 조치 파트너를 비롯하여 올바른 정보가 지정되어 있습니다. 또한 주 서버의 것과 동일한 모든 로그인을 미러 서버에도 만들었습니다. 데이터베이스 오류를 테스트하면 미러 서버 인스턴스가 성공적으로 주 서버 역할을 수행하며 이 SQL Server 인스턴스에서 모든 사항이 올바르게 작동하는 것으로 보입니다. Windows® 로그인을 사용하여 미러 서버 인스턴스에 연결할 수도 있습니다. 그런데 응용 프로그램이 다음과 같은 오류와 함께 다시 연결되지 않습니다.

Cannot open database "<db name>" requested by the login. The login failed.

로그인이 새로운 주(원래 미러) 데이터베이스의 사용자와 연결되어 있지 않은 것 같습니다. sp_change_users_login을 실행하여 데이터베이스의 로그인과 사용자를 동기화했는데 여러 명의 분리된 사용자를 수정했다는 메시지가 표시되었습니다. 그런 다음 응용 프로그램이 새로운 주 서버에 성공적으로 다시 연결되었습니다. 장애 조치를 여러 번 시도해 봤는데 그때마다 로그인과 사용자 간의 연결이 손실됩니다.

이러한 문제 없이 미러링 설정을 구성하는 방법이 있습니까?

A: 예. 이 문제는 로그인의 이름은 같지만 각 서버의 SQL Server 로그인에 대한 SID(보안 식별자)가 일치하지 않기 때문에 발생합니다. Windows/도메인 사용자/그룹 로그인에 대한 SID는 사용자/그룹의 도메인 SID에 따라 만들어지므로 사용자/그룹이 어떤 SQL Server 서버에 추가되어도 해당 사용자/그룹에 대해 동일하기 때문에 Windows/도메인 사용자/그룹 로그인에서는 문제가 되지 않습니다.

sp_change_users_login 동기화 단계를 수행하지 않기 위해서는 미러 서버에 주 서버의 것과 이름뿐 아니라 SID도 동일한 SQL Server 로그인을 만들어야 합니다. 미러 서버에 로그인을 만들 때는 다음과 같이 CREATE LOGIN 문에 SID를 지정하면 됩니다.

CREATE LOGIN <loginname> WITH PASSWORD = <password>, 
SID = <sid for 
same login on principal server>,...

sys.sql_logins 카탈로그 뷰를 쿼리하면 주 서버에서 각 로그인에 대한 SID를 검색할 수 있습니다. 지정된 서버에 각 SQL Server/Windows 로그인에 대한 실제 CREATE LOGIN 문을 생성하는 쿼리의 예가 그림 1에 나와 있습니다.

Figure 1 CREATE LOGIN 문 생성

select 'create login [' + p.name + '] ' + 
case when p.type in('U','G') then 'from windows ' else '' end + 
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + 
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + 
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'

Q: 평균 디스크 큐 길이는 어떻게 계산해야 합니까? 예를 들어 RAID 01 구성으로 SAN(Storage Area Network)에 별도의 실제 스핀들 20개가 있는 경우 평균 디스크 큐 길이는 어떻게 계산합니까? 평균 디스크 큐 길이/20입니까 아니면 평균 디스크 큐 길이/2입니까?

A: 우선 SAN 환경에서 평균 디스크 큐 길이를 계산하기 전에 디스크 대기 시간을 확인해야 합니다. 그러나 대기 시간은 어떤 것을 계산하려고 하는지에 따라 달라지는데 그 이유는 잠시 후에 알게 될 것입니다.

성능 모니터에서 이 카운터에 대한 설명은 "Avg. Disk Queue Length는 샘플 간격 동안 선택된 디스크에 대해 큐에 있는 읽기 및 쓰기 요청의 평균 수입니다."라고 나옵니다. 이 카운터는 실제 디스크 또는 논리적 디스크 카운터이므로 기본 저장소가 운영 체제에 제공되는 방식에 따라 값이 달라집니다.

지금부터 질문 내용에 대해 살펴보겠습니다. RAID 01 구성으로 20개의 스핀들을 사용하고 있는데 이는 스핀들이 스트라이프된 후 미러링되었거나 01 또는 10을 읽는 방식에 따라 미러링된 후 스트라이프되었음을 의미합니다. 스토리지 배열에서 중요한 점은 스트라이프 집합에는 10개의 스핀들이 있다는 것입니다.

그러나 현재는 스트라이프 크기, 쓰기 크기, 실행할 I/O의 종류(읽기, 쓰기, 순차 또는 임의)와 같은 필수 정보가 없습니다.

따라서 일단 누락된 정보를 무시하고 계산해 보겠습니다. 평균 디스크 큐 길이가 10일 경우 OS에서는 10개의 I/O를 디스크 배열 큐에 배정합니다. 이론적으로는 스트라이프에 있는 10개의 미러링된 집합 각각에 대해 I/O가 한 개씩 있거나 한 디스크에 대해 10개의 I/O가 모두 있을 수 있습니다. 그러나 이 중 어떤 것이 해당되는지는 확인할 수 없습니다.

이번에는 누락된 정보를 포함하여 스트라이프 크기는 64KB이고 쓰기 크기는 8KB이며, 전체적으로 순차 쓰기를 수행한다고 가정해 보겠습니다. 이는 SQL Server 저장소 작업의 일반적인 시나리오입니다. 이 경우 전체 I/O 중 8개는 첫 번째 디스크에 배정되고 다음 두 개의 I/O는 다음 디스크에 배정될 수 있습니다. 따라서 이 시나리오에서 디스크당 디스크 큐 길이는 첫 번째 디스크는 8, 두 번째 디스크는 2, 배열에 있는 나머지 8개의 디스크는 0이 됩니다.

이제 이론적 스트라이프 크기는 8KB로, 쓰기 블록 크기는 64KB로 변경하고 디스크 큐 길이는 10으로 유지해 보겠습니다. 이 경우 각 64KB 블록은 8개 디스크에 분산되므로 한 개의 I/O가 8개의 디스크에 쓰여지고 큐에 있는 10개의 I/O는 배열에 있는 10개의 디스크에 대해 80개의 디스크 쓰기로 분산됩니다. 따라서 배열의 디스크당 디스크 큐 길이를 계산해 보면 배열의 각 디스크에 대해 8이 됩니다

현실적인 관점에서 또 다른 불확실성도 감안해 보겠습니다. 대부분의 경우 SAN 저장소는 서버에 연결될 때 서버에 있는 한 개 이상의 HBA를 사용하거나, 여러 개의 파이버를 사용하여 HBA를 SAN에 연결하거나, SAN의 프런트 엔드에 있는 여러 포트를 사용하거나, 서버와 SAN 간 패브릭에 있는 여러 유형의 파이버를 사용합니다. 이제 SAN 내에 있는 버스의 내부 아키텍처와 SAN 프런트에 있는 포트에 디스크가 연결되는 방식을 참작할 수 있습니다.

성능 모니터에 보고된 큐가 있다면 이는 대기 시간이 높거나, OS에서 디스크 큐 길이를 측정하는 위치와 디스크 표면 사이의 어느 지점에 큐가 있음을 나타냅니다. 이런 이유 때문에 대기 시간을 확인한 다음 평균 디스크 큐 길이가 아닌 이 카운터에 따라 결정을 내려야 합니다.

Q: 트랜잭션 복제를 사용하고 있으며 구독자의 테이블에서 여러 개의 행이 수동으로 변경된 것을 알고 있습니다. 따라서 게시자가 구독자에 더 이상 존재하지 않는 행을 업데이트하려고 하면 오류가 표시됩니다.

전체 스냅숏을 다시 적용하는 대신 복제를 통해 해당하는 한 개의 게시자 테이블만 다시 초기화할 수 있는 방법이 있는지 알고 싶습니다. tablediff 함수를 사용하면 제가 원하는 작업을 할 수 있을 것 같아 살펴봤지만 이 함수가 복제에 어떤 영향이 있을지 의문입니다.

예를 들어 tablediff 함수가 게시자 테이블의 스냅숏을 제때에 만들어 이를 구독자의 해당 테이블과 비교합니까? 데이터의 일관성을 보장하기 위해 복제를 중지하고 tablediff 유틸리티를 사용해야 합니까? 이 밖에도 알아야 할 사항이 있습니까?

A: 우선 tablediff는 게시 테이블 또는 구독 테이블의 리터럴 스냅숏을 만들지 않습니다. 이러한 시나리오를 위해 살펴볼 수 있는 두 가지 선택 사항이 있습니다.

첫 번째 방법은 일시적으로 복제를 중지하고 유틸리티를 실행하는 것입니다. 사용자가 데이터를 수정하는 것이 걱정되면 -sourcelocked 및 -destinationlocked 매개 변수를 사용하면 됩니다. 이 두 매개 변수는 유틸리티가 실행되는 동안 두 테이블에 배타적 잠금을 설정합니다. 이 방법이 적절하지 않으면 복제가 실행 중인 동안 -rc 및 -ri 매개 변수를 확인하는 방법이 있습니다. 이 방법은 tablediff를 한 번 실행한 후 오류가 발생할 때 이를 연속으로 실행하여 복제 전파가 지연될 경우 발생할 수 있는 오류를 방지합니다. 그러나 이 방법을 사용할 경우 복제 지연 정도에 따라 구독자에서 변경된 행 일부를 감지하지 못할 수도 있다는 점에 유의하십시오.

기술적 자문을 주신 Microsoft IT 전문가: Sunil Agarwal, Chad Boyd, David Browne, Gilles Comeau, Emmanuel Dreux, Amanda Foote, Matt Hollingsworth, Paul Mestemaker, Uttam Parui, Paul Randal, Dennis Tighe, Steven Wort.

추가 리소스