• 아티클

SQL Q+A임시 테이블, 64비트 프로세싱, 미러링 및 기타

편집자: Nancy Michell

임시 테이블

Q: 경합 문제를 피하기 위해 임시 데이터베이스인 tempdb를 사용하는 것이 항상 좋은 방법은 아니라고 들었습니다. 맞습니까? 여러 테이블에 분산되어 있는 데이터를 검색하여 이 데이터를 수정해야 하기 때문에 이 방법을 사용해 왔습니다. 그런데 뷰를 사용해서는 이 작업을 손쉽게 할 수 없습니다.

A: tempdb를 과도하게 사용하면 경합이 발생할 수 있습니다. 그러나 기술 자료 문서(support.microsoft.com/kb/328551)에 설명되어 있는 것처럼 수많은 임시 테이블을 모두 동시에 만들고 삭제하는 수백 개의 SPID(서버 프로세스 식별자)를 가져오는 경우와 같이 드문 경우에만 대개 경합 문제가 발생합니다.

임시 테이블(tempdb에 있을 수도 있고 없을 수도 있음)을 사용하는 것이 실제로는 적절한 해결 방법이 될 수 있습니다. 중요한 점은 임시 테이블에서 데이터를 가져올 때 얻는 성능 향상이 애초에 테이블을 작성하는 데 따른 오버헤드를 능가하는지 여부입니다.

이 방법을 배제하기 전에 먼저 임시 테이블의 크기, 수명, 사용 빈도 및 특히 활성 인스턴스 수를 고려해야 합니다. tempdb 경합은 하드웨어와 관련이 있기 때문에 서버의 물리적 특성도 중요합니다.

수명이 짧은 소규모 테이블이 많이 필요한 경우 임시 테이블보다 다소 이점이 있는 테이블 변수를 사용해 볼 수 있습니다. 테이블 변수와 임시 테이블 간의 차이점에 대한 자세한 내용은 다음 질문에 대한 답변을 참조하십시오. 테이블 변수에 대한 자세한 내용은 "INF: 질문과 대답 - SQL Server 2000 - 테이블 변수"를 참조하십시오.

질문하신 분의 경우 모든 데이터베이스 쿼리가 임시 테이블 만들기 절차를 거쳐야 한다면 원래 데이터 모델이 잘못된 것은 아닌지 살펴보는 것도 좋습니다.

Q: 테이블 변수란 무엇이며 성능은 어떻습니까? 임시 테이블 대신 실제로 사용할 수 있습니까?

A: A: 임시 테이블과 테이블 변수는 기본 기능 측면에서 동일하며 실제로 양쪽 모두 tempdb 데이터베이스에서 임시 테이블로 만들어집니다. 그러나 적은 수의 행만을 삽입하는 경우는 테이블 변수가 성능 면에서 훨씬 뛰어납니다. 테이블 변수는 통계나 인덱스를 유지 관리하지 않으므로 오버헤드가 적게 발생하기 때문입니다. 테이블의 내용이 데이터베이스 엔진에서 데이터 캐시에 유지하는 8KB 크기의 데이터베이스 페이지를 초과하지 않는다면 테이블 변수를 사용하는 것이 좋습니다.

SQL Server™ 온라인 설명서에 따르면,

  • 테이블 변수는 로컬 변수처럼 동작합니다. 또한 함수, 저장 프로시저 또는 이들이 선언되어 있는 일괄 처리의 범위가 잘 정의되어 있습니다. 이 범위 내에서는 테이블 변수를 정규 테이블처럼 사용할 수 있습니다.
  • 테이블 변수는 함수, 저장 프로시저 또는 이들이 정의되어 있는 일괄 처리가 종료될 때 자동으로 정리됩니다.
  • 저장 프로시저에 사용되는 테이블 변수는 임시 테이블을 사용할 때보다 저장 프로시저 다시 컴파일이 적게 발생합니다.
  • 테이블 변수에는 잠금 및 로깅 리소스가 덜 필요합니다.
  • 경우에 따라 상태가 유지되지 않더라도 성능이 향상될 수 있습니다.

미러링

Q: 고가용성을 지원하는 SQL Server 2005 미러링을 사용하려고 합니다. 그러면 시스템이 자동 장애 조치(Failover)를 지원하는 고가용성 모드로 실행될 것인데 리소스를 충분히 활용하기 위해 다른 응용 프로그램도 미러 서버에서 실행하고 싶습니다. 미러 서버에서 지원하는 두 개의 데이터베이스는 주 데이터베이스에 대한 미러로 사용되는 프로덕션 DB와 독립 실행형 데이터베이스로 사용되는 준비 DB입니다. 또한 미러 서버에서는 두 가지 유형의 클라이언트도 지원합니다. 하나는 미러링 장애 조치를 사용하여 프로덕션 DB에 액세스하도록 구성되고 다른 하나는 준비 DB에 직접 연결됩니다.

또한 몇 개의 SSIS(SQL Server Integration Services) 패키지는 미러 서버에서 실행되며 프로덕션 DB와 준비 DB에 액세스하여 로컬에 있는 준비 DB의 데이터를 주 서버에서 실행되는 프로덕션 DB로 전송합니다. SSIS 패키지는 장애 조치 중에 로컬 준비 DB의 데이터를 로컬 프로덕션 DB로 전송합니다(장애 조치). 이러한 구성과 관련하여 주의해야 할 사항이 있습니까?

A: 매우 일반적인 관점에서 볼 때 이러한 유형의 구성은 장애 조치 후 새로운 주 서버에게 지나치게 많은 권한을 부여할 수 있으므로 권장하지 않습니다.

기능적인 측면에서 미러링 관련 문제가 발생하지는 않겠지만 이 방법을 계속 사용하려는 경우에는 철저히 테스트해야 합니다. 테스트는 장애 조치 후 표준 구성에서 부하가 가장 많이 발생하는 시점에 수행해야 합니다. 이때 최대 부하는 현재 최대 부하 기준이 아닌 향후 최대 부하에 대한 예측 값이어야 합니다. 미래의 비즈니스 요구 사항을 감안하지 않을 경우 부하가 최대 용량을 초과하면 시스템이 중단됩니다.

테스트를 수행하면 느린 응용 프로그램 응답 시간, 만족스럽지 못한 처리량, 시간 제한 오류 또는 미러 서버의 매우 높은 다시 실행 큐(실제 다시 실행 비율에 따라 장애 조치 시간이 예기치 않게 길어질 수 있음) 등으로 나타날 수 있는 네트워크, 디스크 및 CPU 병목 현상을 잘 파악할 수 있습니다.

또한 장애 조치 중 문제가 있는 주 서버와 실행 중인 패키지 간의 연결은 끊깁니다. 그러나 장애 조치가 완료되어 새로운 주 서버가 데이터베이스를 사용할 수 있게 되면 패키지가 새로운 주 서버에 다시 연결될 수 있어야 합니다. 물론 이는 패키지가 연결 다시 시도 메커니즘을 사용하여 만들어졌다는 것과 "끊긴" 연결 조건에서의 복구와 같이 연결 오류를 원활하게 처리한다는 것을 전제로 합니다.

SQL Server 및 64비트 OS

Q: 64비트 버전의 Windows Server® 운영 체제를 실행하는 경우 32비트 SQL Server 2000 대신 SQL Server 2000 Enterprise Edition(64비트)을 사용해야 합니까?

A: SQL Server 2000을 놓고 볼 때 유일하게 지원되는 네이티브 64비트 아키텍처는 Intel의 ia64입니다. 따라서 SQL Server 2000의 64비트 지원에 관한 내용이 언급될 경우 이는 ia64를 의미합니다. 네이티브 64비트 ia64에서 유일하게 지원되는 SQL Server 2000 버전은 Enterprise Edition입니다. ia64에서 기본적으로 64비트 응용 프로그램으로 실행되거나 ia64 Windows® on Windows(WOW, 32비트 응용 프로그램을 실행할 수 있는 OS 하위 시스템)에서 실행되는 Standard Edition SQL Server 2000은 없습니다.

AMD x64 컴퓨터의 경우 SQL Server 2000과 관련하여 두 개의 옵션을 사용할 수 있습니다. 그 중 하나는 32비트 운영 체제를 실행하는 것인데 이 경우 SQL Server 2000의 모든 버전과 모든 서비스 팩(SP)을 실행할 수 있습니다. 다른 한 옵션으로 WOW에서의 SQL Server 2000(모든 버전) SP4 및 64비트 운영 체제를 실행할 수 있습니다. 이러한 환경에서 실행되는 SQL Server 2000은 네이티브 64비트 응용 프로그램이 아닙니다. WOW에서 32비트 모드로 실행되므로 전혀 64비트와 관련이 없습니다.

좀 더 자세히 설명하면 ia64 WOW와 x64 WOW는 완전히 다른 하위 시스템입니다. ia64에서 WOW는 제한된 가상 주소 공간뿐만 아니라 실제로 기계 명령도 에뮬레이트해야 합니다. ia64 기계 명령은 x86과는 완전히 다릅니다. 이것은 완전히 다른 시스템입니다. SQL Server는 ia64 WOW에서 실행되는 구성 요소(SQL Server 2000 또는 SQL Server 2005)를 지원하지 않습니다.

그러나 x64 WOW에서는 상황이 달라집니다. x86과 x64 시스템 아키텍처는 매우 유사합니다. 명령 집합이 동일(또는 거의 일치)하므로 32비트 가상 주소 공간만 에뮬레이트해야 하며 이에 대한 하드웨어 지원을 사용할 수 있습니다. 따라서 SQL Server 2000 SP4 및 SQL Server 2005의 모든 버전과 같이 x64 WOW를 지원하는 SQL Server 구성 요소는 많이 있습니다.

SQL Server 2005로 업그레이드

Q: SQL Server 2000에서 SQL Server 2005로 업그레이드하려고 합니다. SQL 코드를 수정하는 것 외에 응용 프로그램에서 변경해야 할 사항은 무엇입니까? 예를 들어 Microsoft® .NET Framework 클라이언트를 사용하여 SQL Server 2005에 연결하는 경우 사용 중인 데스크톱 클라이언트를 Windows XP SP2로 업그레이드해야 합니까?

현재 Windows XP SP1과 Visual Basic® 6.0을 비롯하여 .NET을 사용하는 몇 가지 응용 프로그램을 실행하고 있습니다. SQL Server Native Client, .NET Framework 2.0 및 Windows XP SP2가 반드시 필요합니까? 다시 말해 업그레이드를 위한 전반적인 최소 클라이언트 요구 사항은 무엇입니까? 그리고 SQL Server 2005 미러링을 사용하려면 클라이언트 요구 사항이 달라집니까?

A: 미러링을 최대한 활용하려면 SQL Native Client(OleDb 또는 ODBC용) 또는 ADO.NET 2.0 SqlClient를 사용해야 합니다. 그리고 주 서버와 미러 서버를 모두 참조할 수 있도록 연결 문자열을 변경해야 합니다.

그러나 클라이언트가 미러 서버를 인식할 수 있게 하려면 SQL Native Client 또는 .NET Framework 2.0만 있으면 됩니다. 이전 버전의 클라이언트의 경우 연결은 할 수 있지만 주 서버가 다운되면 미러 서버로의 연결을 자동으로 시도하지는 않습니다.

클라이언트를 변경하지 않고 클라이언트와 서버 간에 BIG-IP 스위치를 추가하여 장애 조치 시 수동으로 클라이언트를 다른 시스템에 연결할 수 있습니다. 또는 응용 프로그램의 코드를 변경하여 주 서버로의 연결이 실패한 경우 미러 서버로 연결을 시도하도록 할 수 있습니다.

시스템 요구 사항은 Windows Installer 3.0, Microsoft Windows XP SP1 이상, Microsoft Windows 2000 SP4 이상 또는 Microsoft Windows Server 2003입니다. 요구 사항에 대한 자세한 내용은 "Using ADO with SQL Native Client(영문); "Updating an Application to SQL Native Client from MDAC(영문)" 및 "System Requirements for SQL Native Client(영문)"를 참조하십시오.

SQL Server 2000 메모리 부족

Q: Windows Server 2003 SP1에 설치된 SQL Server 2000 Enterprise Edition에서 메모리 부족 예외가 발생합니다. 그림 1은 로그에 기록된 오류입니다.

Figure 1 메모리 부족 오류

2006-06-23 14:41:40.72 spid77    WARNING:  Failed to reserve contiguous memory of Size= 24641536.
2006-06-23 14:41:40.85 spid77    Buffer Distribution:  Stolen=4800 Free=1744 Procedures=39391
                                Inram=0 Dirty=90621 Kept=0
                                I/O=0, Latched=99, Other=3063345
2006-06-23 14:41:40.85 spid77    Buffer Counts:  Commited=3200000 Target=3200000 Hashed=3154065
                                InternalReservation=587 ExternalReservation=0 Min Free=1024 Visible= 173320
2006-06-23 14:41:40.85 spid77    Procedure Cache:  TotalProcs=9555 TotalPages=39391 InUsePages=1031
2006-06-23 14:41:40.85 spid77    Dynamic Memory Manager:  Stolen=44191 OS Reserved=7648 
                                OS Committed=7619
                                OS In Use=7603
                                Query Plan=39088 Optimizer=0
                                General=4183
                                Utilities=140 Connection=7651
2006-06-23 14:41:40.85 spid77    Global Memory Objects:  Resource=1617 Locks=313 
                                SQLCache=1836 Replication=204
                                LockBytes=2 ServerGlobal=42
                                Xact=63
2006-06-23 14:41:40.85 spid77    Query Memory Manager:  Grants=0 Waiting=0 Maximum=94533 Available=94533
2006-06-23 14:41:40.88 spid77    Error: 17803, Severity: 20, State: 12
2006-06-23 14:41:40.88 spid77    Insufficient memory available..

서버 RAM은 32GB입니다. 이상하게도 이 문제는 무작위로 발생합니다. 이 오류가 발생한 저장 프로시저를 20번 실행하면 그 중 한두 번만 문제가 발생합니다.

서버의 성능 카운터에는 사용 가능한 메모리가 충분하다고 표시됩니다. 기록기가 디스크로의 페이징을 신속하게 수행하지 않는 문제일까요? 저장 프로시저를 실행할 때 프로세스에서 이렇게 많은 양의 메모리를 요구하는 이유는 무엇입니까? SELECT 문의 개수 때문일까요? 아니면 임시 테이블 때문일까요?

A: 이 문제는 컴퓨터의 실제 메모리 양과 관련이 없습니다. boot.ini 설정에 따라 응용 프로그램은 2GB ~ 3GB의 가상 주소 공간을 갖습니다. 가상 주소 공간은 32비트 시스템에서는 매우 중요합니다. 기본적으로 버퍼 풀은 384MB의 가상 주소 공간을 제외한 나머지 모두를 사용합니다. 이 384MB는 너무 커서 버퍼 풀에서 처리할 수 없거나 버퍼 풀의 할당 방법과 호환되지 않는 구성 요소(타사 xprocs, 연결된 서버, COM 구성 요소)의 할당과 스레드 스택을 위해 남겨집니다.

경고에는 약 23MB를 할당하지 못했다고 표시되어 있습니다. 이 크기를 성공적으로 할당할 수 있는지 여부는 384MB 블록 내에 있는 다른 할당의 수와 위치에 따라 결정됩니다.

시스템에 로드된 타사 xprocs를 일부 제거하여 sp_oacreate 또는 연결된 서버를 통해 COM 구성 요소가 사용되었는지 확인해 볼 수도 있습니다.

저장 프로시저에서 FOR XML 절을 사용함으로 인해 SQL 저장 프로시저를 실행할 때 프로세스에서 그와 같이 많은 양의 메모리를 요구할 수도 있습니다. 그러나 보다 자세한 정보가 필요하다면 Process\sqlservr\Virtual Bytes에서 사용 가능한 가상 주소 공간이 얼마나 있는지 확인할 수 있습니다. 또한 가장 큰 가상 주소 블록 크기를 확인하는 VMStat(Jeffrey Richter가 집필한 Programming Applications for Microsoft Windows의 CD에 포함되어 있음)이라는 도구를 사용할 수 있습니다. 기록기의 속도가 느린 것은 이 할당과는 관련이 없으며 이 할당에 사용되는 메모리 영역은 버퍼 풀에는 없습니다.

기술적 자문을 주신 Microsoft IT 전문가: Ramon Arjona, Stephen Borg, Sandu Chirica, Robert Djabarov, Guillaume Fourrat, Osamu Hirayama, Alejandro Mihanovich, Maxwell Myrick, Uttam Parui, Shashi Ramaka, Gavin Sharpe, Vijay Sirohi, Jimmie Thompson, Madhusudhanan Vadlamaani, Jian Wang 및 Dave Wickert

© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다..