SQL Q&A: Best Practices Analyzer, Multi-Core Processors, and More

아티클
08/24/2016

SQL Q&ABest Practices Analyzer, 다중 코어 프로세서 외

편집자: Nancy Michell

Q: 스키마 정규화 테이블/뷰의 사용과 관련된 BPA(Best Practices Analyzer) 규칙에 대해 몇 가지 질문이 있습니다. BPA 설명서에 따르면 이 규칙은 임시 테이블에 대해서는 스키마 정규화를 검사하지 않습니다. BPA 보고서 플래그가 저장 프로시저 내에 만들어진 임시 테이블을 참조하는데, 이 임시 테이블을 정규화해야 합니까? 정규화해야 한다면 어떤 스키마를 사용해야 합니까? 저는 임시 테이블도 다른 테이블과 마찬가지로 해당 소유자에 대해 정규화해야 한다고 생각합니다.

A: SQL Server™ 2005에서는 사용자와 스키마가 분리되어 이전 버전의 SQL Server에서 테이블/뷰 참조를 스키마로 정규화해야 하는 BPA 권장 사항이 더 이상 적용되지 않습니다. SQL Server 2000에서는 서로 다른 기본 스키마를 사용하는 여러 사용자가 쿼리 계획을 다시 사용하려면 스키마 정규화가 필요했습니다. 정규화하지 않고도 dbo 개체를 사용할 수는 있었지만 SQL Server에서 먼저 개체의 기본 스키마를 검사해야 했으므로 쿼리 계획을 다시 사용할 수가 없었습니다. 이제 사용자와 스키마가 분리되어 여러 사용자가 기본 스키마를 공유할 수 있으며, 대개의 경우 모든 데이터베이스 사용자가 자신의 기본 스키마에 있는 개체에 액세스할 수 있습니다. 따라서 정규화되지 않은 테이블 및 뷰 이름이 포함된 임시 계획을 여러 사용자가 정상적으로 공유하고 다시 사용할 수 있습니다. 저장 프로시저 내 정규화되지 않은 테이블 및 뷰 이름의 경우에는 처음부터 이러한 문제가 발생하지 않으므로 이 규칙이 더욱 더 필요하지 않습니다. SQL Server 2005의 BPA에는 T-SQL 구문 분석기가 없으므로 이와 같은 규칙이 포함되지 않습니다. 대신 적절하지 않은 구성 및 보안 설정이 있는지 검사합니다.

Q: SQL Server 2005 SP1에서 이상한 동작이 발생합니다. 한 테이블에서 레코드를 삭제하고 바로 삽입하려 하면 삭제 트랜잭션이 커밋될 때까지 테이블이 잠겨 삽입할 수 없습니다. 왜 차단되는지 이유를 알 수 있을까요?

A: 예. 그림 1의 DMV(동적 관리 뷰)를 실행하여 잠금과 차단의 원인을 알아낼 수 있습니다.

Figure 1 차단 및 잠금 찾기

-- script to show blocking and locks
SELECT 
  t1.request_session_id AS spid, 
  t1.resource_type AS type, 
  t1.resource_database_id AS dbid, 
  (case resource_type 
   WHEN ‘OBJECT’ THEN object_name(t1.resource_associated_entity_id) 
   WHEN ‘DATABASE’ THEN ‘ ‘ 
   ELSE (SELECT object_name(object_id) 
      FROM sys.partitions 
      WHERE hobt_id=resource_associated_entity_id) 
  END) AS objname, 
  t1.resource_description AS description, 
  t1.request_mode AS mode, 
  t1.request_status AS status,
   t2.blocking_session_id
FROM sys.dm_tran_locks AS t1 LEFT OUTER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address 
GO

Q: 하이퍼스레딩 및 듀얼 코어 기술 외에도 CPU 공급업체에서는 더 많은 코어(4중, 8중 등)가 장착된 프로세서를 출시하기 시작했습니다. SQL Server 2005 Standard Edition 배포를 지원하기 위해 다중 코어 프로세서가 여러 개 장착된 새 서버를 구입하는 것을 고려 중입니다. 그런데 Standard Edition에서는 CPU가 4개로 제한되므로 4중 코어 프로세서를 사용할 경우 한 개의 실제 CPU만 사용할 수 있는 것인지 궁금합니다.

A: 라이선스 및 CPU 버전 지원을 위해 SQL Server에서는 프로세서의 코어 수에 관계없이 실제 소켓/CPU의 수만 고려합니다. 따라서 SQL Server 2005 Standard Edition에서 CPU를 최대 4개까지 지원한다는 것은 각 CPU에 포함된 코어 수에 관계없이 4개의 실제 CPU 소켓을 지원한다는 의미입니다. 예를 들어 각각 네 개의 코어가 포함된 실제 CPU가 네 개 있을 경우 Standard Edition 배포 환경에서는 16개의 논리적 CPU를 사용하게 됩니다. 또한 16개의 코어/논리적 CPU를 사용하더라도 16개의 코어가 아니라 4개의 실제 CPU에 대해서만 비용을 지불하면 라이선스를 얻을 수 있습니다. SQL Server 및 다중 코어에 대한 자세한 내용은 microsoft.com/sql/howtobuy/multicore.mspx를 참조하십시오.

Q:새 데이터베이스 서버를 구입하려고 합니다. 64비트를 사용해야 할까요, 아니면 검증된 기존의 32비트 버전을 고수해야 할까요?

A. 상황에 따라 다릅니다. 이는 공급업체들이 하드웨어를 x86에서 x64로 바꾸면서 자주 제기되고 있는 질문입니다. 이 질문에 앞서 데이터베이스 서버의 메모리 부담이 어느 정도나 될지를 고려해야 합니다. 하드웨어의 총 사용 기간(일반적으로 3년) 동안 SQL Server 인스턴스에서 사용하는 메모리의 양이 3GB 미만이면 32비트 x86이 적절합니다. 단, 이 경우 도메인 컨트롤러, DNS 서버, 응용 프로그램 서버, 웹 서버, 메일 서버 등에서도 환경이 동일해야 합니다. SQL Server 인스턴스의 메모리 사용량이 16GB를 초과하거나 각 서버(또는 클러스터)에 여러 개의 인스턴스가 있는 경우에는 64비트 기술로 전환하는 것을 진지하게 고려해 보아야 합니다. 프로세서가 8개를 넘으면 IA64가 표준 권장 사항입니다. 다만 x64 4중 및 8중 코어 CPU가 대중화되기 시작함에 따라 IA64 대신 x64를 도입하는 데 대한 비용 측면의 논란은 있을 수 있습니다.

x64(또는 IA64)로 전환할 때 개발, 테스트 및 운영 환경에 동일한 아키텍처를 배포하는 비용은 초기 예상 비용에 포함하지 않을 수도 있습니다. 그러나 하드웨어 총 사용 기간이 절반 정도가 지날 때까지 이전 기술을 계속 사용해야 할지를 검토해 보아야 합니다. 18개월 정도 되면 순전히 x86 하드웨어에만 의존한다는 것이 거의 불가능할 것입니다. 당장은 현재대로 유지하고 1, 2년 후에 업그레이드하는 경우 완전 교체해야 하므로 비용이 많이 들 것입니다. 지금 64비트 하드웨어로 전환한다면 향후 몇 년간 선택의 폭이 넓어질 것입니다.

따라서 새 하드웨어를 구입할 때, 코어 응용 프로그램 릴리스 주기가 시작될 때 또는 SQL Server 2000에서 SQL Server 2005로 업그레이드할 때가 64비트로 전환하기에 좋은 시기입니다.

Q: 두 서버 간에 로그가 전달되도록 설정해 두었는데, 주말에 주 서버에서 하드웨어 문제가 발생하여 로그 전달이 중지되었습니다. 로그 전달을 모니터링하기 위해 돌아왔을 때는 몇 시간 동안 시스템 작동이 중단되었음에도 불구하고 LS_backup_dbname이 제대로 실행되고 있었습니다.

그러나 보조 서버에서 복사 작업은 제대로 수행되는 것처럼 보였지만 복원 작업은 파일을 건너뛰어 결국 계속해서 실패하고 있었습니다. 복원 작업이 실행되기는 하지만 모든 파일을 건너뛰고 결국 실패하는 것입니다. 그 결과 마지막 복원 이후 지금까지 849분이 흘러갔습니다. 과거에 이와 비슷한 상황에서는 단순히 로그 전달 구성을 다시 시작했었습니다. 다시 말해 로그 전달을 해제하고 다시 설정하여 새 백업을 만들어 보조 서버에 복원하고 로그 전달을 다시 시작했습니다. 이 시나리오를 보다 효율적으로 관리할 수 있는 방법이 있습니까?

A: 백업 파일이 없어서 발생한 결과인 것 같습니다. 아시겠지만 로그 전달의 백업, 복사 및 복원 작업은 개별적으로 실행됩니다. 복사 위치의 이전 백업 파일은 지정된 일정에 따라 제거됩니다. 복원 작업이 실행되지 않은 기간이 있었다면 지정된 일정을 초과하여 백업 파일이 없어졌을 수 있습니다. 로그 전달 복원 작업에는 여러 가지 작업 조건으로 발생한 다양한 실패 유형에서 복구하려고 하는 논리가 사용됩니다. 기본적으로 이 논리는 오류가 발생할 경우 파일을 검색하여 올바른 백업 파일을 찾으려고 합니다. 올바른 백업 파일이 없다면 무언가로 인해 필요한 파일이 제거되었거나 로그 전달의 백업 부분이 다시 구성되었기 때문입니다.

이 문제를 해결하는 한 가지 방법은 다른 백업 작업에서 우연히 만들어진 트랜잭션 로그 백업을 통해 수동으로 복원하는 것입니다. 파일을 단순히 로그 전달 복사 위치로 복사할 때의 문제는 로그 전달에서 인식하는 이름 규칙에 있습니다. 그러나 수동으로 해당 지점에서 복원하고 나면 정상 복원 작업이 다시 시작되어 계속됩니다.

Q: T-SQL을 사용하여 실제 메모리의 총 크기와 사용 가능한 크기를 알고 싶습니다. 이 정보를 쉽게 구할 수 있는 방법이 있습니까?

A: 그림 2의 쿼리를 실행하십시오. 그러면 원하는 데이터를 얻을 수 있습니다.

Figure 2 메모리 정보 보기

With VASummary(Size,Reserved,Free) AS 
(SELECT 
  Size = VaDump.Size, 
  Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) 
  WHEN 0 THEN 0 ELSE 1 END), 
  Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) 
  WHEN 0 THEN 1 ELSE 0 END) 
FROM 
( 
  SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) 
    AS Size, 
    region_allocation_base_address AS Base 
  FROM sys.dm_os_virtual_address_dump 
  WHERE region_allocation_base_address <> 0x0 
  GROUP BY region_allocation_base_address 
  UNION 
  SELECT CONVERT(VARBINARY, region_size_in_bytes), 
    region_allocation_base_address 
  FROM sys.dm_os_virtual_address_dump 
  WHERE region_allocation_base_address = 0x0 
) 
AS VaDump 
GROUP BY Size)
 
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS INT)/1024 
    AS [Max free size, KB] 
FROM VASummary 
WHERE Free <> 0

Q: 응용 프로그램에서 데이터베이스를 쉽게 만들 수 있도록 sa(시스템 관리자) 암호를 대칭 키로 암호화된 파일에 저장하고 싶습니다. 가능합니까?

A: 먼저 응용 프로그램에서 sa를 사용해서는 안 된다는 것을 말씀드리고 싶습니다. 또한 SQL Server 인증 대신 Windows 인증을 사용해야 합니다.

응용 프로그램에서 많은 권한이 필요한 작업을 반드시 수행해야 하는 경우에는 먼저 작업을 수행하는 데 필요한 최소 권한을 정의한 다음 해당 권한이 포함된 데이터베이스 역할을 정의합니다. 그런 다음 사용자에게 해당 역할을 할당한 후 EXECUTE AS 절을 사용하여 해당 기능을 수행할 저장 프로시저를 만듭니다. Execute As 절에 대한 설명은 msdn2.microsoft.com/ms188354.aspx를 참조하십시오.

해당 계정이 sa 계정인지의 여부는 제쳐두고 Windows 인증을 항상 사용해야 한다는 점을 감안하면 암호를 사용할 응용 프로그램에서만 암호에 액세스할 수 있고 권한이 없는 사용자나 응용 프로그램에서는 액세스할 수 없도록 SQL Server 로그인 계정의 암호를 안전하게 저장할 수 있는 방법이 있느냐를 먼저 질문해야 합니다.

암호를 암호화하는 것까지는 좋습니다. 그러나 이는 암호를 보호하는 것에서 암호화 키를 보호하는 것으로 문제가 옮겨진 것일 뿐입니다.

해당 응용 프로그램이 Microsoft® .NET Framework 기반 응용 프로그램일 경우 엔터프라이즈 라이브러리(msdn2.microsoft.com/aa480453.aspx)와 MSDN®의 패턴 및 사용 방법 섹션의 권장 사항(msdn.microsoft.com/practices)을 살펴보십시오. 엔터프라이즈 라이브러리에는 구성 블록, 암호화 블록, 데이터 액세스 블록 등 유용한 몇 가지 응용 프로그램 블록이 포함되어 있습니다. 이러한 블록은 적어도 암호를 안전하게 저장하는 데 도움이 되며 더 나아가 전반적인 데이터 액세스를 관리하는 데도 유용합니다.

.NET Framework를 사용하지 않을 경우에는 엔터프라이즈 라이브러리가 적절하지 않으므로 CryptoAPI(msdn2.microsoft.com/aa380255.aspx)에 익숙해져야 합니다. 여기에는 OS와 함께 배포되며 사용자 이름 또는 컴퓨터에 암호화 키를 연결하여 키 관리를 단순화하는 데 사용할 수 있는 핵심 암호화 기능이 포함되어 있습니다. 암호를 암호화하고 나면 키 관리가 중요해집니다.

Q: SQL Server 2000 2노드 클러스터 서버(64비트 하드웨어의 활성/활성)를 동일한 포리스트 내의 도메인 A에서 도메인 B로 이동하려고 합니다. SQL Server 데이터베이스는 SAN에 있습니다. Active Directory® 아키텍처에서는 두 개의 자식 도메인(도메인 A와 도메인 B)이 있는 빈 루트 도메인이 필요하며, 도메인 B에 모든 인프라 구성 요소를 넣으려고 합니다. 실행 환경은 Windows Server® 2003 Enterprise이고 Windows 2000을 실행하는 추가 도메인 컨트롤러가 있습니다.

SQL Server 클러스터 서버의 도메인 구성원 자격을 도메인 A에서 도메인 B로 변경하는 것이 가능합니까?

A: SQL Server 초보자는 "SQL Server 2000 장애 조치 클러스터의 도메인을 변경하는 방법" 및 "한 도메인에서 다른 도메인으로 Windows 클러스터 서버를 이동하는 방법"을 읽어 보십시오. 다중 인스턴스 클러스터에도 동일한 프로세스가 적용됩니다. 다중 인스턴스 클러스터의 경우 필요에 따라 각 인스턴스에 대해 단계를 반복해야 한다는 점만 다릅니다. 클러스터의 도메인 변경에 따르는 복잡함을 감안하면 절대로 권장하고 싶지 않지만 여러 차례 이러한 작업이 수행된 사례가 있습니다. 유일하게 선택할 수 있는 다른 방법은 질문자가 제시한 대로 새 인스턴스를 병렬로 다시 빌드한 다음 데이터를 마이그레이션하는 것입니다.

프로젝트마다 성격이 다르며 실제 도메인 수준의 변경 작업(보안 고려, 계정 마이그레이션, sid 매핑 등)을 수행하는 데는 매우 다양한 도구가 사용됩니다. 대개 이러한 부분이 실제 변경보다 더 어렵습니다.

기술적 자문을 주신 Microsoft IT 전문가: Sunil Agarwal, Laurent Banon, Steve Bloom, Chad Boyd, Matt Burr, Shaun Cox, Cindy Gross, Bobby Gulati, Matt Hollingsworth, Arnost Kobylka, Mikhail Shir, Fernando Pessoa Sousa, Stephen Strong, Ramu Veeraraghavan

추가 리소스