SQL Q&A: CPU Bottlenecks, Restoring and Moving Databases, and More

아티클
08/24/2016

SQL Q&ACPU 병목 현상, 데이터베이스 복원과 이동 및 추가 정보

편집자: Nancy Michell

Q: SQL Server™의 CPU 사용량이 갑자기 늘어나기 시작했습니다. 새로운 사용자를 추가하거나 하드웨어가 다운되거나 새 테이블이 만들어진 것도 아닌데요. 왜 그런 것이지요?

팁: 인덱스 생성 중 액세스

시간이 많이 걸리는 대형 테이블에 대한 인덱스 생성 작업을 수행하려고 하는데 이 프로세스를 수행하는 동안 데이터를 사용해야 하는 경우가 있습니다. 어떻게 하면 두 가지 목표를 모두 달성할 수 있을까요?

클러스터형 인덱스를 만들거나 삭제하거나 다시 만들 때마다 SQL Server에서는 테이블에 대해 스키마 수정(SCH-M) 잠금을 설정하여 작업이 수행되는 동안 어떤 사용자도 기본 데이터에 액세스할 수 없게 만듭니다. 테이블에 대한 클러스터형 인덱스를 만드는 경우가 여기에 해당합니다. 반면 열에 대한 비클러스터형 인덱스를 만들 때는 SQL Server에서 테이블에 대한 공유(S) 잠금을 설정합니다. 이때도 기본 테이블의 데이터를 업데이트할 수는 없지만 SELECT 문을 실행하여 데이터를 읽을 수는 있습니다.

클러스터형 인덱스를 만드는 동안 테이블을 읽어야 하는 경우에는 테이블에 대한 인덱스를 만들어 온라인 작업으로 전환할 수 있습니다. 이 작업을 수행하는 명령은 다음과 같습니다.

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

테이블에 대해 온라인 인덱스 작업을 사용하는 경우에도 SQL Server에서 클러스터형 인덱스에 대해 SCH-M 잠금을 설정하고 기본 테이블에 대한 비클러스터형 인덱스에 대해서는 공유(S) 잠금을 설정합니다. 하지만 인덱스 작업이 시작될 때와 끝날 때만 이러한 잠금을 설정하므로 그 시간이 매우 짧습니다. 따라서 이 옵션을 사용하면 인덱스를 생성하는 동안 기본 테이블을 보다 쉽게 쿼리하고 테이블에 액세스할 수 있습니다. 온라인 인덱스 생성/작업은 SQL Server 2005 Enterprise Edition에서만 사용할 수 있습니다.

A: 서버의 로드가 추가되거나 어떤 것도 변경되지 않았는데 예기치 않게 갑자기 CPU 병목 현상이 발생하는 경우 근본적으로 몇 가지 이유가 있을 수 있지만 일반적으로는 쿼리 계획이 최적이 아니거나 SQL 서버 구성에 문제가 있거나 응용 프로그램/데이터베이스의 설계가 적절하지 않거나 하드웨어 리소스가 부족한 데서 원인을 찾을 수 있습니다.

이와 같은 상황이 발생하면 가장 먼저 서버가 CPU 바인딩 서버인지 확인하고 그럴 경우 로컬 SQL Server 시스템에서 CPU를 가장 많이 사용하는 문을 알아내야 합니다. 서버가 CPU 바인딩 서버인지는 성능 모니터를 실행하고 PROCESSOR:% PROCESSOR TIME 카운터를 보고 확인할 수 있습니다. CPU당 사용된 시간 값이 75%와 같거나 그 이상인 경우 CPU 병목 현상이 있음을 나타냅니다.

SYS.DM_OS_SCHEDULERS라는 시스템 DMV(동적 관리 뷰)를 쿼리하여 실행 가능 작업의 값을 확인함으로써 SQL Server 스케줄러도 모니터링해야 합니다. 0이 아닌 값은 작업이 실행되려면 얼마 동안 대기해야 함을 나타냅니다. 이 카운터의 값이 높은 경우 이것도 CPU 병목 현상이 있음을 나타냅니다.

다음 쿼리를 사용하여 스케줄러를 모두 표시하고 실행 가능 작업의 수를 검토할 수 있습니다.

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

CPU를 많이 사용하는 상위 50개의 SQL 문을 보려면 그림 1에 나오는 쿼리를 사용합니다.

Figure 1 CPU를 많이 사용하는 상위 50개 항목

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

Q: 테이프에서 SQL Server 2005 데이터베이스를 복구해야 했습니다. 복원을 수행한 후 데이터베이스에 대해 사용자에게 부여되어 있었던 대부분의 사용 권한이 없어졌습니다. 복원 중 무엇이 잘못되었는지 알 수 있을까요? 데이터에는 문제가 없는데 사용 권한이 엉망이 되었습니다.

A: 아마도 마스터 데이터베이스를 동일한 시점으로 복원하지 않았고 사용자 데이터베이스의 로그인 ID가 현재 마스터 데이터베이스와 일치하지 않기 때문일 것입니다. 이들을 동기화해야 합니다. "데이터베이스 이동 시 유용한 도움말을 제공하는 리소스" 추가 기사에는 데이터베이스를 복원하거나 이동할 때 발생하는 문제의 원인을 찾는 데 도움이 되는 리소스가 나와 있습니다.

Q: SQL Server 2005 SP1의 OpenXML을 통해 XML 데이터를 여러 조각으로 나눠 관계형 테이블로 가져오는 저장 프로시저를 사용하여 응용 프로그램을 하나 개발했습니다. XML 문서의 크기는 큰 것이 5KB이고 평균 크기는 2.5KB입니다. 저장 프로시저는 병렬로 최대 50번까지 호출됩니다.

그런데 심각한 잠금 경합 문제가 발생했습니다. OpenXML 때문인 것 같은데,. 어떻게 해야 할까요?

A: OpenXML은 노드를 이용할 때보다 단일 스레드에서 데이터를 여러 조각으로 나누거나 변환하는 속도가 빠르지만 일반적으로 노드를 이용할 경우, 특히 노드를 병렬로 사용할 경우 확장 성능이 보다 우수합니다. 그럼에도 불구하고 OpenXML을 사용하려는 경우에는 다음 지침에 따라 전반적인 OpenXML 성능을 개선해야 합니다.

앞에서 언급한 방법, 즉 동일한 행 패턴을 사용하여 OpenXML을 다섯 번 호출하는 방법 대신 행 패턴이 동일한 모든 데이터를 임시 테이블에 추출한 다음 임시 테이블에서 데이터를 선택하도록 해야 합니다. 가능하면 일찍 sp_xml_removedocument를 사용하여 메모리를 해제해 보십시오. * 및 //와 같은 와일드카드는 되도록 사용하지 않는 것도 좋은 방법입니다. 명시적 경로를 제공하면 쿼리 성능이 향상됩니다.

Q: 서버에서 DBCC SHRINKFILE을 실행하면 속도가 정말로 느립니다. 다중 프로세서 시스템을 사용하면 성능이 좋아질까요? 이 문제를 해결하려면 어떻게 해야 할까요?

A: DBCC SHRINKFILE은 단일 스레드에서 수행되는 작업이므로 여러 개의 CPU를 사용하지 않습니다. DBCC SHRINKFILE은 한 번에 한 페이지씩 파일의 맨 뒤에서 파일의 맨 앞으로 페이지를 이동합니다. 또한 소위 말하는 축소는 대부분의 경우에는 조각 모음을 수행하는 것이 아니라 논리적 조각화를 늘립니다.

축소 성능을 개선하는 방법 중 하나는 클러스터형 인덱스에서 페이지를 이동하는 것입니다. 힙이 여러 개 있고 힙에 비클러스터형 인덱스가 많은 경우 클러스터형 인덱스에 비해 속도가 눈에 띄게 느려집니다.

또한 LOB(Large Object Blob) 데이터의 경우 LOB 데이터의 루트를 찾기 위해 행 내부 데이터를 읽어야 하므로 페이지 이동 속도가 느립니다.

대부분의 인덱스/테이블 내용은 파일의 끝에 상주하므로 파일의 맨 앞으로 이동하기 위해 인덱스를 다시 만들 수 있습니다. 인덱스를 다시 만드는 작업의 경우 여러 개의 CPU를 사용하므로 bulk_logged 모드에서 작업을 수행할 경우 사용되는 로그 공간을 줄일 수 있습니다. 그런 다음 축소를 실행하면 속도가 훨씬 빠릅니다.

축소 작업에 대한 자세한 내용을 보려면 blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspx와 blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx의 항목부터 읽어보십시오.

데이터베이스 이동 시 유용한 도움말을 제공하는 리소스

팁: 포트 변경

SQL Server에 친숙한 사용자라면 누구나 SQL Server의 기본 수신 포트 번호가 1433이라는 것을 잘 알 것입니다. SQL Server의 명명된 인스턴스는 동적 포트를 사용하도록 구성되지만, 즉 SQL Server 인스턴스가 시작될 때마다 사용 가능한 포트 중 아무 것이나 선택할 수 있지만 기본 SQL Server 인스턴스는 항상 포트 1433에서 수신합니다. 따라서 기본 포트에서 수신하는 서버에 보안 대비책이 충분히 마련되어 있지 않으면 서버가 위험에 노출될 수 있습니다. 기본 포트를 변경하여 공격을 막을 수 있습니다. 방법은 다음과 같습니다.

SQL Server 구성 관리자를 열고 SQL Server 2005 네트워크 구성과 프로토콜을 차례로 확장합니다. TCP/IP를 두 번 클릭합니다. 그러면 아래의 차트에 TCP/IP 속성 및 해당되는 기능 목록이 표시됩니다. 차트를 참조하여 적절하게 설정합니다.

SQL Server 데이터베이스 엔진의 경우 한 IP 주소가 여러 포트에서 수신할 수 있으므로 사용할 포트를 1433,1500,1501 형식과 같이 쉼표로 구분하여 지정합니다. 단일 IP 주소가 여러 포트에서 수신하도록 구성하려면 TCP/IP 속성 대화 상자의 프로토콜 탭에서 '모두 수신합니다.' 매개 변수도 아니요로 설정해야 합니다.

이제 각 주소를 마우스 오른쪽 단추로 클릭하고 속성을 클릭하여 구성할 IP 주소를 확인합니다. TCP 동적 포트 대화 상자에 데이터베이스 엔진이 동적 포트에서 수신하고 있음을 나타내는 0이 있으면 0을 삭제합니다. IP 속성 영역의 TCP 포트 상자에서 이 IP 주소가 수신하도록 할 포트 번호를 입력하고 확인을 클릭합니다. 콘솔 창에서 SQL Server 2005 서비스를 클릭하고 세부 정보 창에서 SQL Server(<instance name>)를 마우스 오른쪽 단추로 클릭한 다음 다시 시작을 클릭하여 SQL Server를 중지한 후 다시 시작합니다.

특정 포트에서 수신하도록 SQL Server를 구성한 후에는 세 가지 방법 중 하나로 클라이언트가 해당 포트에 연결하도록 설정할 수 있습니다. 서버에서 SQL Server Browser 서비스를 실행하여 이름을 기준으로 데이터베이스 엔진 인스턴스에 연결하거나, 포트 번호를 지정하여 클라이언트에 별칭을 만들거나, 사용자 지정 연결 문자열을 사용하여 연결하도록 클라이언트를 프로그래밍할 수 있습니다.

속성	설명
활성	SQL Server가 지정된 포트에서 수신하고 있음을 나타냅니다. IPAll에는 사용할 수 없습니다.
사용	이 연결을 사용하거나 사용하지 않도록 설정합니다. IPAll에는 사용할 수 없습니다.
IP 주소	이 연결에 사용된 IP 주소를 보거나 변경합니다. 컴퓨터에 사용된 IP 주소와 IP 루프백 주소 127.0.0.1을 표시합니다. IPAll에는 사용할 수 없습니다.
TCP 동적 포트	동적 포트를 사용하지 않는 경우에는 비어 있습니다. 동적 포트를 사용하려면 0으로 설정합니다.
TCP 포트	SQL Server가 수신하는 포트를 보거나 변경합니다. 기본적으로 기본 인스턴스는 포트 1433에서 수신합니다. 이 필드에는 최대 2047자까지 입력할 수 있습니다.

이 달의 질문과 답변 문서를 작성하는 데 도움을 주신 분들: Chad Boyd, Cindy Gross, John Hadden, Saleem Hakani, Stephen Jiang, Mahesh Nayak, Paul Randal과 Wayne Yu

추가 리소스