내보내기(0) 인쇄
모두 확장

Windows Azure SQL 데이터베이스 및 SQL Server -- 성능과 확장성 비교 및 대조

업데이트 날짜: 2014년 1월

저자: Conor Cunningham, Tobias Ternström, Silvano Coriani, Ewan Fairweather

참여자: Ralph Squillace, Karthika Raman

SQL Server와 Windows Azure SQL 데이터베이스(줄여서 SQL 데이터베이스, 이전의 SQL Azure)는 중요하고도 많은 유사점이 있지만 같지는 않으며, 차이가 크지 않 다고 해도 이러한 차이점은 SQL Server와 비교해 볼 때 SQL 데이터베이스에서 응용 프로그램의 수행 방식에 영향을 줍니다. 따라서 플랫폼마다 응용 프로그램 아키텍처 및 성능 평가 기술도 다릅니다.

이 문서에서는 이러한 성능 차이와 그 이유를 설명하고, 프로덕션 SQL 데이터베이스에 대한 성능 문제 해결 경험에 따른 고객의 실용 노하우에 대해 알아봅니다. 또한 SQL 데이터베이스에서는 사용할 수 없는 일반적인 SQL Server 성능 평가 기법을 검토해 봅니다. 고성능의 대규모 Windows Azure 응용 프로그램의 디자인에 대한 보다 광범위한 설명은 Windows Azure 클라우드 서비스에서 대규모 서비스를 디자인하는 방법에 대한 모범 사례를 참조하십시오.

SQL Server는 온-프레미스로 실행될 뿐만 아니라 Windows Azure 가상 컴퓨터에서도 실행될 수 있습니다. 이 문서에서 설명하는 비교와 방법은 온-프레미스 환경에서 실행되든 Windows Azure 가상 컴퓨터에서 실행되든 간에 SQL Server에 적용됩니다. 그러나 SQL Server가 Windows Azure 가상 컴퓨터에서 실행되는 경우 성능 면에서 몇 가지 사항을 추가로 고려해야 합니다. 이에 대한 자세한 내용은 Windows Azure 가상 컴퓨터의 SQL Server에 대한 성능 지침 문서를 참조하십시오.

Windows Azure SQL 데이터베이스는 Premium Edition을 통해 데이터베이스의 리소스를 예약하는 기능을 제공합니다. 자세한 내용은 Windows Azure SQL 데이터베이스의 Premium Edition 지침을 참조하십시오.

이 문서는 다음과 같이 구성됩니다.

첫째, 몇 가지 표준 응용 프로그램 시나리오와 그러한 시나리오에서 일반적으로 사용하는 응용 프로그램 패턴 및 접근법에 대해 설명합니다.

둘째, SQL 데이터베이스에 대한 고객의 일반적인 기대 사항을 설명하고 그러한 기대로 인해 올바르지 않은 방향으로 접근할 수 있는 상황을 살펴봅니다.

셋째, 공유 환경에서 우수한 성능의 전형적인 패턴에 초점을 맞추면서 특히 SQL 데이터베이스의 몇 가지 성능 패턴에 대해 좀 더 심도 있게 들어가 봅니다.

넷째, 관계형 데이터베이스를 위한 몇 가지 일반적인 성능 평가 기법을 개략적으로 설명합니다.

다섯째, SQL 데이터베이스가 제공하는 기능이 SQL Server와 다른 점을 고려하여 SQL 데이터베이스 인스턴스의 성능을 제대로 평가하기 위한 몇 가지 기법을 설명합니다.

여섯째, 부록에서는 SQL 데이터베이스 인스턴스를 평가하고 문제를 해결하는 데 도움이 될 수 있는 몇 가지 SQL 스크립트를 제공합니다.

성능 분석과 최적화를 다룰 때는 다양한 아키텍처와 기술적 요구 사항으로 인해 서로 다른 응용 프로그램 패턴에 맞는 접근 방식이 필요합니다. 이러한 차이로 인해 성능 특성을 측정하고 파악하기 위한 일반적인 모범 사례와 기법을 정의하기가 어려울 수 있습니다. 하지만 응용 프로그램을 분류하고 일반적인 지침을 작성하는 데 사용할 수 있는 몇 가지 공통된 패턴이 있습니다. 이 섹션에서는 현재 응용 프로그램의 Windows Azure SQL 데이터베이스에서 볼 수 있는 몇 가지 일반적인 패턴을 설명합니다.

이 그룹에는 SaaS(Software-as-a-Service) 제공 모델을 수용하는 전통적인 비즈니스 응용 프로그램(회계, CRM, ERP 등)이 포함되며 이들은 새로운 비즈니스 모델의 구축이나 특정 신규 고객 부문과 관련된 경우가 많습니다. 기존의 소프트웨어 개발 업체들이 새로운 소프트웨어 제공 모델로 전환하면서 겪는 가장 큰 문제는 기존의 코드 베이스와 직원의 기술 및 접근법입니다. SaaS 모델로 전환하는 것은 기본적으로 동일한 물리적 리소스에서 여러 고객을 호스팅하기 위해 다중 테넌트 모델을 사용하는 것을 의미합니다. 이는 하드웨어 투자를 최대한 활용하고 관리 비용을 줄일 수 있지만 일반적으로 데이터 일관성을 유지하면서 여러 데이터베이스에 데이터를 분산하기 위해 특별히 데이터 계층 수준에 중점을 두어 응용 프로그램을 다시 디자인해야 합니다. 사용자들은 비록 호스팅되는 환경이고 일반적으로 하드웨어 리소스를 다른 응용 프로그램과 공유하더라도 "마치 온-프레미스와 같이" 작동하는 플랫폼을 기대합니다.

이 시나리오는 일반적으로 라이선스와 유지 보수 비용을 절감하기 위해 기존의 비경제적인 일괄 처리 응용 프로그램 환경을 마이그레이션하는 것입니다. 메인프레임 응용 프로그램의 경우 코드를 다시 작성하는 데 필요한 내부 지식을 유지하기 위해 복잡성과 어려움이 따르기 때문에 기존의 많은 비즈니스 논리(일반적으로 COBOL)를 수정하지 않고 유지해야 하며, 특히 코드를 작성한 지 오래되었다면 더욱 문제가 됩니다. 이러한 환경은 신중한 평가를 거쳐 Azure에서 잘 실행할 수 있도록 하려면 얼마나 많은 튜닝과 최적화를 적용해야 하는지를 파악해야 합니다. 이 과정은 지루한 작업이 될 수 있지만 경험상 이러한 유형의 응용 프로그램은 대기 시간 요구 사항이 엄격하지 않은 대신 대규모의 일괄 작업을 필요한 기간 내에 완료할 수 있는 충분한 처리 능력을 필요로 하는 경우가 많습니다.

Windows Azure와 같은 클라우드 플랫폼은 일반적으로 사용자 지정 응용 프로그램 프로젝트에 이상적인 환경입니다. 이러한 응용 프로그램은 일반적으로 프로젝트별로 개발되며 여러 프로젝트에서 개발되어 개선되고 재사용되는 프레임워크 기반의 새로운 비즈니스 프로세스를 구축합니다. 이러한 소프트웨어 개발에 일반적으로 요구되는 것은 데이터 동기화 및 복제, 프로세스 오케스트레이션, 통합 등과 같은 하이브리드 및 연결 기능을 지원하는 클라우드 플랫폼의 능력입니다. 이러한 유형의 프로젝트에는 출시 기간 및 솔루션 개발과 배포의 속도가 중요합니다. 또한 성능 병목과 확장성 문제를 즉각 발견하고 분석하여 해결할 수 있는 방법이 필요합니다.

이 패턴은 클라우드 환경에서 직접 만든 어느 정도의 위험이 수반되는 중요한 신규 소프트웨어 개발 프로젝트를 나타냅니다. 이러한 유형의 응용 프로그램은 클라우드에서만 개발 및 테스트되어 프로덕션에 투입됩니다. 범위 내에서 응용 프로그램과 서비스를 실행하는 근본적인 복잡성 때문에 응용 프로그램의 동작과 성능 및 그러한 응용 프로그램을 호스팅하는 클라우드 플랫폼을 이해하여 비즈니스 요구 사항, 비용 모델 및 성능과 확장성 기대치에 얼마나 적합한지 최대한 파악하는 것이 매우 중요합니다. 이러한 종류의 응용 프로그램을 실행하려면 일반적으로 개발 비용과 투자 위험 간에 어느 정도의 절충이 요구됩니다.

이러한 종류의 응용 프로그램은 일반적으로 완전히 새로운 가능성이며 클라우드 플랫폼이 제공하는 탄력적이고 확장성이 큰 환경 없이는 불가능할 것입니다. 응용 프로그램 공급자는 이전에도 종종 전통적인 데이터 센터에서 전통적인 아키텍처를 사용하여 응용 프로그램을 구현하려고 시도했지만 사용자들이 생성하는 예측 불가능한 작업과 최고 부하를 감당할 수 있도록 기존의 인프라를 디자인하는 데 따르는 어려움과 비용 때문에 디자인하고 구축한 후에도 관리가 불가능했습니다. 높은 가시성과 비즈니스에 중요한 요구 사항을 위해서는 응용 프로그램 계층과 데이터 계층 모두에서 완벽하게 확장 가능하고 분할되는 솔루션을 디자인하고 구현할 수 있는 완전히 새로운 접근법이 필요한 경우가 많습니다. 복잡한 정도가 높을수록 전체 응용 프로그램 성능을 신속하게 분석하고 문제를 해결하는 능력이 구현 초기 단계부터 반드시 필요합니다.

SQL 데이터베이스를 처음 사용하는 사용자들은 일반적으로 다음과 같이 생각합니다.

SQL 데이터베이스는 서비스로 제공되는 SQL Server 버전으로, 경제적이고 탄력적이며 관리 오버헤드가 낮고 가용성이 높다.

기본적으로 SQL 데이터베이스가 SQL Server와 매우 비슷한 것은 사실입니다.

  • SQL 데이터베이스는 TDS와 T-SQL을 모두 지원하고, master 데이터베이스를 보유하며, SQL 인증을 사용하는 등 SQL Server의 많은 프로그래밍 노출 영역을 지원합니다.

  • SQL 데이터베이스는 핵심 RDBM 기능을 위해 SQL Server 엔진 버전을 재사용합니다.

  • 고객은 최소한의 학습으로 새로운 플랫폼에서 시작할 수 있을 것입니다.

하지만 프로덕션 응용 프로그램은 이 추상화 수준에서 빌드되지 않습니다. 따라서 응용 프로그램을 디자인할 때 이 수준의 이해를 넘어서 응용 프로그램이 효율적으로 문제 없이 실행될 수 있도록 SQL Server와 SQL 데이터베이스의 중요한 차이점, 이러한 차이점이 존재하는 이유, SQL 데이터베이스에서 다르게 해야 할 부분 등을 구체적으로 파악하는 것이 중요합니다.

다음은 SQL Server와 SQL 데이터베이스의 서로 다른 부분을 볼 수 있는 세 가지 중요한 예입니다. 아래의 세 영역은 현재 및 향후에도 최적의 비용으로 적절한 확장성과 성능을 갖춘 응용 프로그램을 디자인하기 위해 반드시 알아 두어야 할 중요한 부분입니다.

  • 대기 시간

  • 하드웨어

  • 리소스 용량

각 항목에 대한 자세한 내용은 아래에서 설명합니다.

 

개념 SQL Server SQL 데이터베이스

대기 시간

서버가 공동 배치되고 연결 방식을 정확하게 제어할 수 있습니다. 응용 프로그램 서버와 SQL Server 간의 일반적인 대기 시간은 1밀리초 미만입니다. 대부분의 경우 단일 작업을 완료하기 위해 많은 데이터베이스 호출을 수행하는 응용 프로그램의 성능이 괜찮습니다.

SQL 데이터베이스에서 Azure 서비스와 SQL 데이터베이스 간의 일반적인 통신은 수 밀리초 전반이며 대부분 온-프레미스보다 수치가 큽니다.

하드웨어

회사에서 엔터프라이즈급의 하드웨어를 구입하고 정확한 컴퓨터가 업무에 배치될 수 있도록 IT 전담 직원이 있습니다. 컴퓨터 오류가 거의 발생하지 않고, 응용 프로그램의 실패한 작업을 자동으로 재시도하는 등 명령이나 연결 복구를 수행하는 데 시간이 많이 걸리지 않습니다. 업그레이드 및 서비스가 가동 중지 시간이 예정된 공개 일정에 따라 수행됩니다.

Windows Azure는 클라우드의 경제성을 바탕으로 하며 전체 서비스에서 상용 하드웨어와 비슷한 컴퓨터를 사용합니다. 이러한 컴퓨터는 온-프레미스에서 일반적으로 사용되는 컴퓨터보다 성능과 내구성이 떨어지고 특정 컴퓨터에 대한 정비가 언제든지 발생할 수 있습니다. 데이터는 중복을 통해 보호되지만 연결 관련 오류가 발생할 수 있습니다.

리소스 용량

응용 프로그램 데이터베이스가 일반적으로 다른 응용 프로그램 데이터베이스와 하드웨어를 공유하지 않고 자체 컴퓨터에서 호스팅됩니다.

한 대의 컴퓨터가 최대 수백 개의 데이터베이스를 호스팅하고, 이러한 각 클라우드는 여러 응용 프로그램에서 나올 수 있으며 컴퓨터 리소스를 공유합니다. 서비스의 논리는 여러 호스팅 컴퓨터로 데이터베이스를 이동하여 서비스의 총 부하를 분산하고 리소스 공정성을 제공합니다.

Premium 데이터베이스가 도입됨에 따라 이제 Windows Azure SQL 데이터베이스에 대해 특정 수준의 용량을 예약할 수 있습니다. Premium Edition은 SQL 데이터베이스 및 보조 복제본용으로 고정된 용량을 예약함으로써 기존의 SQL Database Web 및 Business Edition에 비해 클라우드 응용 프로그램에 대해 더 예측 가능한 성능을 제공합니다.

Premium 데이터베이스는 다른 SQL Database 버전과 비교할 때 다중 테넌트 지원 문제도 크게 줄입니다.

위의 예를 고려할 때 응용 프로그램을 SQL 데이터베이스로 전환하거나 SQL 데이터베이스용으로 만드는 가장 일반적인 이유가 무엇인지 생각해 보겠습니다.

  • 탄력성. 하드웨어에 대한 투자 없이 용량을 신속하게 확보할 수 있습니다.

  • 낮은 관리 오버헤드. 대부분의 관리 기능이 SQL 데이터베이스 내장되어 있습니다. 가장 중요한 점은 CREATE DATABASE만 실행하면 여러 랙의 여러 물리적 컴퓨터에 있는 3-4개의 복제본을 통해 고가용성의 데이터베이스를 즉각 제공할 수 있다는 것입니다.

  • 가격. 서비스를 경제적으로 실행할 수 있습니다.

위의 내용은 핵심 RDBMS 기능의 기본적인 차이가 아닌 고급 개념을 다루고 있지만 이러한 개념은 기본 데이터베이스 엔진 수행 방식의 핵심이며 따라서 응용 프로그램을 디자인할 때 반드시 고려해야 합니다.

문제를 최소화하고 SQL 데이터베이스의 장점을 최대한 활용하기 위해 올바른 응용 프로그램 디자인 선택 방법을 파악하는 것이 중요합니다.

일반적인 SQL Server 응용 프로그램은 응용 프로그램과 관련된 모든 것을 하나의 데이터베이스(커질 수 있음)에 배치할 수 있지만, 일반적인 Azure SQL 데이터베이스 응용 프로그램은 응용 프로그램의 주 기능을 여러 데이터베이스로 분리할 경우 최상으로 작동합니다. 이렇게 분리된 데이터베이스는 한 대가 아닌 여러 대 컴퓨터의 리소스를 활용하며, 따라서 응용 프로그램 작업 기능이 커짐에 따라 여러 데이터베이스로 간편하게 분할할 수 있습니다.

다음으로, SQL 데이터베이스에서 상용 하드웨어와 서비스 모델을 사용한다는 것은 일부 작업이 실패하면 재시도해야 할 것을 예상하고 명령을 작성해야 함을 의미합니다. 따라서 임시 상태를 오래 유지하지 않고 작업을 쉽게 다시 시작할 수 있도록 하기 위해 작업을 작은 청크 단위로 분할하는 방법을 생각해야 합니다. 이 프로그래밍 모델에서는 프로그램 실행 중에 데이터베이스 서버가 장애 조치되더라도 응용 프로그램이 계속 작동합니다.

또한 SQL 데이터베이스 기반 응용 프로그램은 문제 해결 및 로깅에 다른 접근법을 사용합니다. 웹 서버와 마찬가지로 이러한 응용 프로그램의 문제를 해결하는 모델은 능동적인 문제 해결(예: DMV에 직접 쿼리)보다 로깅에 적합합니다. 이러한 로그 스트림은 응용 프로그램 관리에 포함되어, 응용 프로그램의 어느 부분이 정상이고 어느 부분이 주의를 필요로 하는지 보여 주는 대시보드에 정보를 제공합니다. 이 모델을 구현하면 많은 자동화를 통해 수동 작업을 최소화하는 대규모의 응용 프로그램을 만들 수 있습니다.

대기 시간은 SQL 데이터베이스 응용 프로그램을 고려할 때 중요한 또 하나의 요소입니다. 응용 프로그램의 일부는 사용자에게 로컬로 호스팅되지 않기 때문에 이에 따라 응용 프로그램의 응답 속도를 높이기 위해 비동기 실행/큐 패턴이 종종 필요한 경우가 있습니다. 또한 온-프레미스 SQL Server의 동등한 응용 프로그램과 비교할 때 작업을 완료하는 데 필요한 왕복 수에 대해 보다 신중한 선택을 하도록 응용 프로그램에서 고려해야 할 경우도 있습니다. 이러한 차이는 SQL 데이터베이스에서 효율적인 응용 프로그램을 개발할 때 달리 중점을 두어야 하는 영역과 패턴을 보여 줍니다.

마지막으로, 호스팅되는 응용 프로그램의 성능 테스트는 해당 응용 프로그램이 실행될 동일한 프로덕션 하드웨어서 최상으로 수행되지만, 공유되고 호스팅되는 하드웨어에서의 성능 테스트 세부 정보는 전용 컴퓨터에서와 다릅니다. 이에 대한 설명은 이 문서의 섹션 5에 자세히 나와 있습니다.

응용 프로그램을 디자인할 때 응용 프로그램의 SQL 부분 호스팅에 사용할 수 있는 4가지 기본 옵션이 있습니다.

  1. 가상화되지 않은 원래 환경의 SQL Server

  2. 온-프레미스/호스팅되는 VM의 SQL Server

  3. Windows Azure 가상 컴퓨터의 SQL Server

  4. Windows Azure SQL 데이터베이스

대안 1부터 4까지 순서대로 전환할 경우 운영 지출 및 매출 원가가 확실히 절감되는 방향으로 이동하지만 SQL Server 기능과 관련된 특정 성능뿐 아니라 특수 하드웨어와 같은 확장 기술을 사용하여 성능 문제를 해결하는 능력이 감소합니다. SQL 데이터베이스와 같은 가상화된 환경에서 성능 문제가 발견되면 하드웨어를 강화하는 것이 아니라 응용 프로그램을 변경하여 문제를 해결할 필요가 있습니다. 이러한 응용 프로그램 변경의 예로 특정 유형의 규모 축소 패턴을 구현하거나 응용 프로그램의 다양한 작업을 버킷으로 분류하는 것이 있습니다. 이때 일부 작업은 더 많은 리소스를 받아 동기적으로 실행되어야 하고(예: 은행 트랜잭션), 다른 작업은 그보다 적은 리소스를 사용하여 실행될 뿐 아니라 대기될 수 있습니다(예: 은행 고객에게 계정 명세서 전자 메일 전송).

지난 10년간 개체 관계형 매퍼(Entity Framework, Hibernate, NHibernate 등)와 같은 기술의 광범위한 도입과 보급을 통해 개발자가 보다 쉽게 응용 프로그램을 개발할 수 있도록 하는 노력이 집중적으로 이루어졌습니다. 이러한 기술은 솔루션 개발 속도를 높이기는 하지만, 데이터베이스를 통해 수행되는 중요 작업과 이러한 기술이 성능과 규모에 미치는 영향의 차이를 도외시합니다. 일반적인 데이터 센터 활용률이 한 자릿수 전반인 경우가 많은 클라우드 전 세대에서는 단순히 하드웨어의 과도한 프로비전으로 인해, 이러한 기술을 기반으로 하여 거의 최적화되지 않은 데이터베이스 호출을 실행하는, 사용량이 많은 응용 프로그램 조차도 그대로 유지되었을 것입니다.

지난 10년 동안 실제 응용 프로그램 실행 비용은 대부분 개발자에게 알려지지 않았으며 일반적으로 개발자들은 그러한 비용에 많은 신경을 쓸 필요가 없습니다. 대형 고객에게 응용 프로그램 실행 비용에 대한 파악을 물어보면 정형화된 답이 나옵니다. 이러한 고객들은 일반적으로 주요 ERP와 같은 대규모 시스템에 얼마나 많은 비용이 들어가는지는 알고 있지만 그들이 실행하고 유지 관리하는 수백 개의 다른 응용 프로그램은 “기타 앱” 버킷으로 분류됩니다. 이러한 응용 프로그램의 총 비용은 단순히 전체 응용 프로그램 비용에서 ERP 시스템과 같은 주요 응용 프로그램의 비용을 뺀 비용으로 산출되며 개별 응용 프로그램 비용에 대한 세부 정보는 거의 없습니다.

많은 고객들이 개별 응용 프로그램의 비용을 모르기 때문에 응용 프로그램을 유지 관리하는 개발팀에서는 이렇게 명시되지 않은 비용에 대한 인식이 거의 전무합니다. 이러한 환경에서는 응용 프로그램의 운영 비용 감소에 따라 개발자에게 주어지는 혜택이 거의 없습니다. 클라우드 공급자의 송장은 특정 응용 프로그램을 실행하는 개별 Azure VM, 웹 및 작업자 역할, SQL 데이터베이스의 비용을 세부적으로 분류할 수 있기 때문에 Windows Azure는 이러한 개념을 완전히 바꿔 놓습니다.

이 송장은 단순한 청구서가 아니며 이 정보를 바탕으로 기업은 응용 프로그램 성능 문제 해결에 관한 선택을 할 때 현명한 의사 결정을 할 수 있습니다. 예를 들어 다음과 같은 방법으로 성능 문제를 해결할 수 있습니다.

  • 데이터베이스 및 응용 프로그램의 작업자 역할에 대해 Azure에서 사용할 수 있는 리소스 확대

  • Premium Edition 옵션을 사용하여 CPU, 디스크 IO, 메모리 등의 리소스 예약

  • 청구되는 리소스 사용을 줄이도록 응용 프로그램 재설계

  • 온-프레미스로 전환 및 고객 하드웨어 구입

결론

개발자는 응용 프로그램 총 소유 비용 = 개발 비용 + 서비스 비용이라는 클라우드 개념으로 전환해야 합니다. 경우에 따라 응용 프로그램 개발에 투자하여 서비스 비용을 줄이는 것이 올바른 선택일 경우도 있고, 응용 프로그램 리소스를 늘리는 것이 올바른 선택일 경우도 있습니다. 이 선택은 조직의 기술력과 해결해야 할 문제의 규모에 따라 다릅니다. 응용 프로그램 개발 지식과 기술력을 충분히 갖추고 있으면 응용 프로그램 변경을 구현하여 응용 프로그램의 리소스 수요를 줄이는 것이 쉬울 수도 있고, 다른 상황에서는 투자를 통해 플랫폼에서 사용할 수 있는 리소스를 늘려 필요한 성능 목표를 달성하는 것이 적절할 수도 있습니다.

응용 프로그램을 효율적으로 실행하기 위해서는 원격 분석을 통해 응용 프로그램의 클라우드 서비스 사용을 파악하는 것이 중요합니다. Windows Azure에서 New Relic 또는 Opstera 등의 우수한 타사 도구를 평가하는 것이 좋지만 Windows Azure 진단을 기반으로 하는 사용자 지정 솔루션을 만들어 보는 것도 괜찮습니다. 마지막으로 위의 ORM 사례에서와 같이 너무 많은 복잡성을 도외시하면 성능이나 확장성 문제를 해결하는 데 비용과 노력이 너무 많이 들 수 있으므로 주의해야 합니다.

이 섹션에서는 데이터베이스 작업의 성능에 영향을 주는 일반적인 요소에 대해 설명합니다. 첫 번째 하위 섹션에서는 SQL Server에서 사용되는 전통적인 요소를 다룹니다. 대부분의 요소는 SQL 데이터베이스에도 계속 적용됩니다. 두 번째 섹션에서는 SQL 데이터베이스가 새로운 이유를 설명합니다. 마지막으로 Azure SQL 데이터베이스가 제공하는 문제 해결 및 진단 도구의 노출 영역을 살펴보고 실례를 통해 이러한 도구를 어떻게 사용할 수 있는지 알아보겠습니다.

SQL Server의 성능 분석은 오랜 전통을 가지고 있으며 이 섹션은 기본적인 이해를 제공하는 데 도움이 됩니다. 자세한 내용은 http://msdn.microsoft.com/ko-kr/library/dd672789(v=SQL.100).aspx를 참조하십시오.

SQL Server의 쿼리 최적화 프로그램은 시스템 성능에 영향을 주는 계획을 선택합니다. 예를 들어 최적화 프로그램은 특정 쿼리를 충족하기 위해 Index Seek 또는 Table Scan을 수행하도록 결정할 수 있습니다. 최적화 프로그램의 선택은 일반적으로 적합하지만 통계 등의 입력 데이터가 최신 데이터가 아니기 때문에 또는 쿼리가 최적화 프로그램의 모델 한계에 적중할 경우(카디널리티 추정 시 모든 사례의 조인에 존재하는 데이터 기울기에 대해 추론하지 않음) 잘못 선택할 수도 있습니다.

최적화 프로그램은 개별적으로(단일 쿼리에 영향을 줌) 또는 일련의 쿼리에 대해(일부 계획 선택으로 인해 시스템의 전반적인 성능이 최적화가 저하됨) 부적합한 선택을 할 수 있습니다. 최적화 프로그램은 기본적인 계획 간 검색 공간 추론 외에 계획 간의 최적화를 고려하지 않습니다.

최적화 프로그램에 대한 자세한 내용은 SQL Server 2008 Internals(저자: Conor Cunningham 외)에서 최적화 프로그램에 관한 장이나 곧 발행될 SQL Server 2012 Internals를 참조하십시오.

SQL Server는 사용자의 하드웨어에서 실행되므로 이러한 하드웨어를 구입하고 구성하는 것은 관리자의 책임입니다. 고객이 하드웨어를 올바르게 설정하고 구성하지 않을 경우 전반적인 시스템 성능이 저하될 수 있습니다. 일반적인 예로 다음과 같은 경우가 있습니다.

  • 다양한 구성 요소(디스크 컨트롤러 등)의 드라이버 선택/버전이 잘못 구성됨

  • BIOS 설정

  • NUMA 구성/CPU 선호도 설정

  • 메모리 설정(최대 서버 메모리 등)

  • 검사점 간격

  • MAXDOP

이 상황은 일반적으로 SQLIOSim과 같은 도구(http://www.microsoft.com/ko-kr/download/details.aspx?id=20163)를 사용하여 새 하드웨어에 대해 시스템의 모든 핵심 기능의 유효성을 검사하는 사전 승인 테스트와 자세한 설명서를 통해 해결할 수 있습니다.

잠금은 여러 사용자가 같은 데이터를 변경할 때 이를 제어하여 일관된 변경을 할 수 있도록 하는 데 사용됩니다. 잠금은 데이터베이스 시스템에 반드시 필요한 부분이며 모든 잠금이 동일하게 생성되지는 않습니다. 예를 들어 최적화 프로그램은 충돌하는 여러 쿼리에 대해 가능한 다른 계획 선택과 비교하여 전반적인 시스템 성능을 저하하는 계획을 만들 수 있습니다. 때때로 최적화 프로그램은 두 개 이상의 계획에서 여러 잠금을 반대 순서로 확보할 경우와 같이 교착 상태를 일으킬 수 있는 계획 조합을 만들 수도 있습니다. 여러 계획 선택(또는 작업 호출 패턴)은 보통 이러한 상황을 해결하는 데 필요합니다.

래치는 SQL Server내의 구조에 대한 다중 사용자 액세스를 결정한다는 점에서 잠금과 비슷합니다. 하지만 래치는 내부 물리적 페이지 구조를 다룹니다. 래치와 잠금은 어느 정도 차이가 있지만 두 문제가 모두 적용될 수 있으며 핵심 해결 방법은 대부분 비슷합니다. 핫 래치가 발생할 경우 일반적인 답은 다른 계획 셰이프를 적용하거나 호출 코드를 재배열하여 호출 패턴을 변경함으로써 전반적인 처리량에 대한 차단 래치의 영향을 줄이는 것입니다.

잠금이 데이터베이스의 다중 사용자 작업과 관련하여 문제가 되지 않더라도, 일부 작업에는 과도한 리소스가 할당되는데 일부 작업에는 충분한 리소스 할당이 거부되기 때문에 성능이나 처리량이 감소할 수 있습니다. 이 상태의 첫 징후는 작업이 평소보다 오래 걸리는 것이며 내부 원인은 하나 이상의 리소스가 압력을 받고 있기 때문입니다. 일반적인 요인에는 대기 유형 SOS_SCHEDULER_YIELD(작업이 CPU 확보를 위해 대기 중임을 나타냄), 오래 걸리는 I/O 대기(SQL Server가 I/O 요청을 처리할 수 있는 I/O 시스템보다 빠른 속도로 I/O 요청을 생성하고 있음을 나타냄, RESOURCE_SEMAPHORE(메모리) 등이 있습니다. 차단 문제를 처리하는 일반적인 방법은 더 큰 컴퓨터나 더 빠른 구성 요소를 구입하는 것입니다.

검사점은 데이터베이스 서버 작동이 중단될 경우 복구에 걸리는 시간을 최소화하기 위해 DBMS에서 메모리의 더티 페이지를 디스크로 플러시하는 프로세스입니다. 이 프로세스는 I/O 작업에서 쿼리 성능을 저하하고 대기 시간 및 처리량에 영향을 줄 수 있는 급등을 유발할 수 있습니다. 일반적으로 응용 프로그램은 검사점의 영향을 측정하여 용량 계획 및 인증에 포함할 수 있도록 검사점 간격보다 긴 시간 동안 부하를 테스트해야 할 수 있습니다.

note참고
SQL Server 2012에는 검사점의 I/O 부하를 보다 긴 시간 동안 균일하게 분산함으로써 검사점의 오버헤드를 줄일 수 있는 I/O “간접 검사점”(자세한 내용은 http://msdn.microsoft.com/ko-kr/library/ms189573.aspx#IndirectChkpt 참조)이라는 기능을 포함되어 있습니다. 이 기능은 성능 및 용량 문제 해결에 대한 I/O 영향을 줄일 수 있습니다.

다시 말하지만 Windows Azure SQL 데이터베이스는 SQL Server와 동일하지 않습니다. SQL 데이터베이스는 다른 데이터 센터에서 실행되는 호스팅 서비스로, 여러 고객이 물리적 컴퓨터를 공유하는 다중 테넌트입니다. 또한 자동 고가용성(HA) 및 자동 백업 등의 자동 기능을 포함하며, 대형 서버가 아닌 상용 하드웨어에서 실행됩니다. Azure SQL 데이터베이스는 라이선스가 아닌 서비스로 고객에게 판매됩니다. 이러한 모든 요소는 성능 및 확장성을 고려하는 방식뿐 아니라 SQL 데이터베이스에 대한 전체 비즈니스 모델에 영향을 줍니다.

또한 SQL 데이터베이스는 현재 데이터 웨어하우스 쿼리에 일반적으로 사용되는 많은 기능을 제공하지 않습니다. 즉, SQL 데이터베이스 초기 초점은 OLTP 시스템입니다. 이러한 기능에는 데이터베이스 압축, 병렬 쿼리, ColumnStore 인덱스 및 테이블 분할과 총 데이터베이스 크기 지원 및 데이터 웨어하우스 작업 중심의 I/O 하위 시스템 등이 있습니다. 현재 SQL 데이터베이스를 사용하여 데이터 웨어하우스 솔루션을 구축할 수는 없지만 이러한 SQL Server와 비교하여 기능 차이가 있다는 것을 이해해야 합니다.

이 섹션에서는 고객이 OLTP 솔루션에 중점을 둔다고 가정하여 이러한 중요한 요소가 SQL 데이터베이스의 성능 및 문제 해결에 어떤 영향을 주는지를 설명합니다.

SQL Server와 SQL 데이터베이스의 가장 큰 차이 중 하나는 SQL 데이터베이스는 다중 테넌트 서비스라는 것입니다. 각 물리적 컴퓨터에는 한 컴퓨터에 저장된 수백 개 이상의 사용자 데이터베이스가 있을 수 있습니다. 따라서 SQL 데이터베이스는 미국의 경우 한 달에 최소 몇 달러부터 시작하는 매우 경제적인 솔루션입니다. 이 차이는 매우 큰 것으로, 기본적으로 SQL 데이터베이스에는 클러스터에 있는 한 컴퓨터의 리소스를 공유하는 여러 사용자가 있으며, 공평성과 부하 균형을 최대화하기 위해 클러스터 내의 여러 컴퓨터로 고객을 이동함으로써 클러스터의 전체 부하 균형을 유지하려 합니다.

따라서 호스팅되는 데이터베이스에 대해 성능 테스트를 실행하는 고객은 다른 사용자의 여러 활성 데이터베이스를 포함하는 시스템에 대해 테스트를 실행하는 것일 수 있습니다. 보통 SQL Server와 마찬가지로 동일한 하드웨어에서 다른 고객이 실행하고 있으면 성능 테스트 결과에 영향을 줍니다. 따라서 한 번의 성능 테스트를 기반으로 SQL 데이터베이스 성능을 결정하는 것은 다른 사용자들로 인해 잘못된 결과가 나올 수 있기 때문에 주의해야 합니다.

그러나 Premium Edition 옵션을 사용하여 데이터베이스와 복제본의 리소스를 예약할 수 있습니다. 이를 통해 데이터베이스에 대해 예약된 CPU, 디스크 IO 및 메모리가 보장됩니다. 또한 Premium Edition으로 업그레이드했다가 용량이 더 이상 필요하지 않을 때 Business 또는 Web Edition으로 다운그레이드하여 필요할 때 이 옵션을 사용할 수도 있습니다.

Premium 데이터베이스를 사용하면 대기 시간에 민감한 작업 수행 시 성능 차이로 인해 작은 쿼리가 예상보다 시간이 오래 걸릴 수 있는 문제가 제거됩니다. 또한 일부 온-프레미스 응용 프로그램의 전제 조건으로 사용된 기존의 격리된 환경과도 상당히 비슷하기 때문에 이러한 응용 프로그램을 중요 변경 없이 마이그레이션할 수 있습니다. 자세한 내용은 Windows Azure SQL 데이터베이스의 Premium 데이터베이스 지침을 참조하십시오.

SQL 데이터베이스는 대규모 클러스터의 컴퓨터를 사용하여 구축됩니다. 개별적으로 이러한 컴퓨터는 크기가 많이 크지 않으며, 전체 성능 대신 가격과 성능 간의 균형을 최대화하는 표준 랙 기반 서버 컴퓨터입니다. 많은 SQL Server 응용 프로그램은 속도가 빠르거나 규모가 큰 컴퓨터, 즉 고객이 응용 프로그램을 실행할 때 리소스가 부족하면 큰 컴퓨터를 구입한다는 가정하에 만들어지므로 이 차이점은 중요합니다. SQL 데이터베이스는 호스팅 솔루션에 초고성능 하드웨어를 제공하지 않습니다. 오히려 “규모 확장” 모델이 아닌 “규모 축소” 모델입니다. 고객 응용 프로그램이 단일 컴퓨터에 대한 한도를 초과할 경우 고객이 단일 컴퓨터 대신 여러 데이터베이스를 사용하여(여러 컴퓨터에 분산) 데이터베이스를 재작성하게 됩니다.

또한 상용 하드웨어는 기존의 온-프레미스 솔루션보다 오류율이 높습니다. 이 하드웨어에는 전원 공급 장치, ECC 메모리 또는 모든 경우에 가동률을 최대화하는 기타 기능이 중복 구성되어 있지 않습니다. 또한 데이터 계층 및 응용 프로그램 계층에서 하나의 전체 솔루션을 제공하기 위해 서로 연동해야 하는 여러 컴퓨터가 클러스터를 구성하고 있습니다. 현재 SQL 데이터베이스에 대한 SLA는 데이터베이스 가용 시간의 99.9% 보장입니다. 즉, 평균 SQL 데이터베이스 인스턴스는 매달 약 42분 동안 사용하지 못할 수 있으며 그래도 목표를 달성합니다. 모든 단일 데이터베이스가 실제로 이 목표를 충족하는 것은 아니지만 대부분 데이터베이스에 대한 전체 가용성은 이 목표를 충족합니다. 이 수치는 기존의 SQL Server 시스템보다 여전히 낮기는 합니다. 많은 SQL Server 사용자가 가동 중지 시간을 공식적으로 측정하지 않지만 종종 가동 중지 이벤트가 계획되고 공지가 전송됩니다. SQL 데이터베이스 오류는 일반적으로 응용 프로그램 부분에서 비계획적이기 때문에 좀 더 무작위로 발생합니다. 따라서 적절한 경우 다른 계층에서의 캐싱을 포함하여 단일 오류 지점을 방지하는 기법(“가동” 중인 단일 데이터베이스만 항상 사용하는 중요 경로 제거에 초점을 맞춤)과, 연결 및 명령에 대해 응용 프로그램 및 서비스의 오류를 복원하는 재시도 논리를 사용함으로써 솔루션의 각 데이터베이스 가동 중지를 고려하여 디자인해야 함을 암시합니다.

대규모 서비스나 솔루션은 각 기능을 공식적으로 하나 이상의 개별 데이터베이스로 분리해야 합니다. 예를 들어 고객에게 서비스 등록을 수행하기 위해 메일을 보내는 기능이 있을 경우 단일 컴퓨터의 능력을 초과하는 많은 부하를 처리하기 위해 이 기능의 데이터를 서비스의 다른 데이터와 분리하고 나아가서는 메일 데이터를 여러 데이터베이스에 분산해야 할 수 있습니다. 여러 데이터베이스 집합을 두고 각각 서로 다른 핵심 기능을 수행하도록 하여 크기 및 가용성 측면에서 컴퓨터의 능력 한도를 관리하는 것은 가능한 일입니다.

Windows Azure에 얼마나 큰 솔루션이 구축되는지는 근본적인 차이가 있기 때문에 SQL Server 솔루션을 평가하는 데 사용되는 “성능”의 정의는 조정이 필요할 것입니다. SAN 구입 후 SQL Server 솔루션이 이전보다 성능이 좋아졌다면, SQL 데이터베이스에 SAN을 갖출 경우 I/O 대기 시간의 성능 수치가 그만큼 좋아지지 않을 수 있습니다. 대부분의 동일한 핵심 비즈니스 요구 사항을 문제 없이 충족할 수 있기는 하지만 SQL 데이터베이스가 특정 비즈니스 문제를 해결할 수 있는지 여부를 확인하는 방법으로 하드웨어의 기본적인 성능을 평가해서는 안 됩니다. 하드웨어 속도는 느리지만 대부분의 경우 여러 컴퓨터를 사용하여 원하는 성능 목표에 부합하는 동일한 비즈니스 결과를 얻을 수 있습니다.

SQL 데이터베이스는 SQL Server와 완전히 동일한 기능을 갖고 있지는 않습니다. SQL 데이터베이스 인스턴스는 프로그래밍 방식의 개념으로, 예를 들어 서버의 특정 인스턴스가 아닌 논리적 데이터베이스를 나타냅니다. 또한 SQL Server의 일부 기능은 SQL 데이터베이스의 프로그래밍 인터페이스에 포함되지 않았습니다. 예를 들어 파일 그룹이나 데이터베이스 간 분할은 표준 SQL 데이터베이스 프로그래밍 패러다임에 필요하지 않기 때문에 현재 SQL 데이터베이스에는 포함되지 않습니다. 또한 고성능 데이터 웨어하우징과 관련된 일부 용도(ColumnStore 인덱스) 역시 이번에는 포함되지 않았습니다. 이러한 차이점이 호스팅 모델에 관한 SQL 데이터베이스의 혁신을 가능하게 했으며, 대부분의 동일한 고객 혜택은 SQL 데이터베이스에서 다른 접근법을 통해 앞으로 다뤄질 것입니다.

SQL 데이터베이스는 또한 데이터베이스 실행, 관리 및 튜닝과 관련된 인건비 절감을 목표로 합니다. 이 목표의 일환으로 다음과 같이 정기적으로 수행해야 하지만 완벽하게 수행하기 어려운 몇 가지 작업을 처리하는 자동 메커니즘을 제공합니다.

  • 데이터베이스 백업(각 활성 데이터베이스에 대해 수 분 간격으로 수행)

  • 고가용성(SQL 데이터베이스는 사용자 개입 없이 각 데이터베이스에 대해 최소 세 개의 복제본을 사용하여 이를 제공함)

  • 데이터베이스 일관성 검사

  • 자동 업그레이드 - SQL 데이터베이스는 최신 버전의 서비스를 대부분 투명한 방식으로 자동 롤아웃합니다.

이러한 차이점은 각 컴퓨터의 용량 중 이러한 작업을 수행하기 위해 예약된 부분이 있음을 의미합니다. 따라서 시스템에 대한 최대 성능 한도를 결정하는 데 사용할 수 있는 방식에 영향을 주며 이에 대해서는 아래에서 자세히 설명합니다. 공식적으로 각 비즈니스 작업에 대한 성능 목표를 정의하면 성능 평가를 이러한 자동 메커니즘의 영향과 분리하는 데 도움이 됩니다.

Windows Azure는 대규모의 인터넷 서비스를 구축할 수 있게 해줍니다. 이 기능이 Windows Azure의 완전히 고유한 기능은 아니지만 SQL Server를 통해 이러한 솔루션을 구축하는 데 필요한 기술력으로는 자금이 충분하고 강한 의지가 있어야만 이러한 종류의 응용 프로그램을 만들 수 있었습니다. 또한 이와 같은 고성능 솔루션은 대부분 데이터베이스나 다른 구성 요소의 작동 중지를 방지하도록 코드를 설계함으로써 솔루션이나 서비스의 가동 중지 시간을 최소화하는 조치를 수행하곤 했습니다. 예를 들어 무중단 솔루션은 데이터베이스 미러링과 같은 일부 고성능(HA) 솔루션과 관련되거나, 하드웨어 오류를 방지하고 업그레이드 시 서버 가동 중지 시간을 최소화하기 위해 항상 실행됩니다. 이 접근법은 SQL 데이터베이스에 매우 중요합니다. 왜냐하면 응용 프로그램의 많은 부분이 서비스이고, 신규 고객들은 이 새로운 플랫폼에 완전 온라인 솔루션을 구축하려 하기 때문입니다.

SQL 데이터베이스는 이미 핵심 솔루션에 고가용성을 기본적으로 제공합니다. SQL 데이터베이스 서비스의 내부 업그레이드로 인한 가동 중지는 상용 하드웨어와 관련된 오류를 처리하는 데 사용된 것과 동일한 재시도 논리를 통해 처리됩니다. 하지만 고객 서비스에서도 응용 프로그램의 여러 계층에 걸쳐 업그레이드 프로세스를 어떻게 조정할지 고려해야 합니다. 예를 들어 서비스의 데이터 계층 위에 웹 또는 응용 프로그램 계층이 있을 경우 업그레이드에 사용되는 한 가지 공통된 기법은 다음과 같습니다.

  • 기존의 저장 프로시저(sp_createaccount_v1, sp_createaccount_v2)를 통해 단계별로 새로운 저장 프로시저를 배포합니다.

  • 응용 프로그램 계층 컴퓨터를 Windows Azure의 “VIP Swap” 기술을 사용하여 새로운 버전의 응용 프로그램으로 전환합니다. VIP swap이 관련 서비스(예: 캐시)에 영향을 줄 경우 또 다른 옵션은 내부 업그레이드입니다.

  • “v1” 버전 저장 프로시저의 기존 작업을 제거합니다.

  • 이전 작업이 모두 완료되면 저장 프로시저의 “v1” 버전을 제거하는 다른 변경을 롤아웃합니다.

응용 프로그램 업그레이드를 가동 중지 없이 수행할 수 있는 작은 단계로 분해하여 온라인으로 수행할 수 있습니다. 가동을 중지해야 하는 작업이 있을 경우 한 번에 적은 수의 내부 고객에 대해 내부 고객이 접속하지 않는 시간에 업그레이드 일정을 잡고, 사용자의 데이터를 새 데이터베이스로 복사하는 작업을 백그라운드로 수행하여 전체 서비스가 중지되는 일이 없도록 최대한 노력합니다.

이러한 세밀한 전환 작업을 위해서는 어느 정도의 엔지니어링 절차 변경도 필요하지만 분해를 통해 전체가 아닌 서비스의 일부만 가동을 중지하는 것과 같은 솔루션을 고려할 수 있습니다. 예를 들어 업그레이드 기간 동안 주요 서비스의 가동과 실행은 유지하면서 이전 달의 청구서를 고객에게 표시하는 논리를 비활성화할 수 있습니다. 또한 상용 하드웨어를 처리하는 데 필요한 분해를 통해 고객이 지각하는 가동 중지의 영향을 보다 세부적으로 재정의하고 최소화할 수 있습니다.

고객은 업그레이드 주기 동안 성능 목표를 충족할 수 있는지 여부를 확인할 필요가 있습니다. 그렇다면 서비스 자체의 배포로 인한 추가 부하나 중단을 처리하도록 서비스/응용 프로그램을 프로비전해야 합니다.

많은 고객은 SQL Server 및 솔루션의 다른 모든 구성 요소를 로컬 서브넷에 배포하며, 이는 계층 간 네트워크 대기 시간을 대부분 무시할 수 없음을 의미합니다. 솔루션을 호스팅되는 인프라로 이동할 경우 고객과 솔루션 일부 간에 대기 시간이 추가될 수 있습니다. 또한 Windows Azure의 다양한 계층은 동일한 네트워크 서브넷에 배치되지 않으므로 두 계층 간에 약간의 대기 시간 차이가 발생하게 됩니다. 작은 쿼리나 업데이트를 많이 수행하는 SQL Server 솔루션은 이러한 물리적 네트워크 차이로 인해 SQL 데이터베이스에서 실행 속도가 느릴 수 있습니다. 초기 클라이언트-서버 컴퓨팅에 익숙한 경우 동일한 솔루션이 여기에 적용됩니다. 눈에 띄는 대기 시간 차이를 처리하기 위해 솔루션의 계층 간 왕복에 대해 살펴보십시오.

이 대기 시간의 영향을 측정하는 두 가지 실용적인 방법이 있습니다.

  • Windows Azure SQL 데이터베이스 인스턴스에 대해 간단한 “SELECT 1” 쿼리를 지속적으로 실행하여 시작 및 종료 응답 시간을 추적하는 단순한 웹 또는 작업자 역할 응용 프로그램을 구현합니다.

  • 데스크톱을 이 웹/작업자 역할 인스턴스에 원격으로 두고 리소스 모니터 애플릿을 실행합니다. 네트워크 탭에 있는 “TCP 연결” 표와 “대기 시간(ms)” 열을 통해 SQL 데이터베이스에 대한 연결의 대기 시간을 확인할 수 있습니다. Windows Azure SQL 데이터베이스 가상 서버 IP 주소는 ping 또는 tracert를 정규화된 이름으로 실행하여 확인할 수 있으며, 포트 1433에 대한 연결을 검토할 수 있습니다.

대기 시간에 관한 모범 사례와 최적화에 대한 자세한 설명은 다음 Windows Azure 지침 문서를 참조하십시오. Windows Azure SQL 데이터베이스 사용 시 성능 고려 사항.

마지막으로 SQL 데이터베이스 서비스 자체에는 성능 분석 방식에 영향을 줄 수 있는 문제가 가끔 있습니다. 그중 하나는 SQL 데이터베이스가 SQL Server에 있는 모든 기능을 제공하지는 않는다는 것입니다. Windows Azure SQL 데이터베이스에 누락된 일부 기능(예: SQL 프로파일러)은 온-프레미스에서 SQL Server의 성능 문제 해결을 수행하는 데 일반적으로 사용됩니다. 이러한 기능 차이는 데이터베이스를 서버가 아닌 SQL 데이터베이스 서비스의 주요 개념으로 제공하는 프로그래밍 노출 영역상의 변경에 따른 것입니다. 대부분의 경우 SQL 팀은 이러한 제한 사항을 해결하기 위해 동일하거나 동등한 기능을 단계적으로 제공할 계획입니다.

또 다른 문제는 SQL 데이터베이스 자체에 버그가 있을 수 있다는 것입니다. SQL Server 고객은 소프트웨어를 직접 가지고 있지만 SQL 데이터베이스에서는 소프트웨어를 공유합니다. 소프트웨어에 가끔 버그가 발생하면 SQL 팀에서 문제가 해결될 때까지 문제를 완화하거나 기능을 차단해야 할 수 있습니다. SQL 팀은 서비스 이용 고객을 위해 이 프로세스를 보다 투명하고 관리하기 쉽게 수행할 수 있는 기능을 계획하고 있습니다. 서로 다른 SQL 데이터베이스 클러스터에서 특정 시점에 작업 동작의 차이가 있을 수 있기 때문에 성능 평가는 고객을 서비스의 일시적 제한으로부터 격리할 수 있도록 여러 데이터 센터에서 여러 번 실행하는 방식으로 수행하는 것이 가장 좋습니다. 이렇게 조정하면 서비스에 대한 한 번의 테스트에서 끌어낸 결과에 영향을 주는 요소가 대부분 제거됩니다.

이 섹션에서는 SQL 데이터베이스의 유효성을 검사하고 최적화하기 위한 몇 가지 권장 기법과, 피해야 하는 특정 기법에 대해 설명합니다.

플랫폼 차이를 감안할 때 SQL 데이터베이스와 하나하나 비교하는 것은 의미가 없으며 하드웨어가 다르므로 서로 다른 접근법이 필요한 플랫폼 차이가 있을 뿐입니다. 기존 솔루션을 마이그레이션할 때 고객은 핵심 작업에 필요한 성능을 결정한 다음 그러한 요구 사항에 맞게 코드를 조정하는 방법을 결정하는 기법을 사용하여 플랫폼에서 작업을 진행합니다.

다음 예를 살펴보십시오.

고객에게 로컬 SQL Server 데이터베이스를10회 호출하고 2초 동안 페이지를 렌더링하는 온-프레미스 ASP.NET 응용 프로그램이 있습니다. 이 웹 응용 프로그램을 웹 또는 응용 프로그램 계층 작업자 역할을 실행하고 데이터베이스로 SQL 데이터베이스를 사용하는 Windows Azure로 이식하려 합니다. 처음에 고객은 응용 프로그램을 단순히 이동하여 컴파일하기로 결정합니다. 시간이 지나면서 Windows Azure에 배포하기 위해 다양한 문제를 겪은 후 그들은 웹 페이지를 렌더링하는 데 이전보다 2초 느려진 4초가 걸리는 것을 알게 됩니다. 고객은 클라우드는 “느리다”는 결론을 내립니다.

이 대기 시간 차이를 설명할 수 있는 몇 가지 가능한 요인이 있습니다. 주제에 관한 구체적인 모든 사례는 이 항목 앞부분에서 설명했습니다.

  • 호출자와 웹 서버 간에 대기 시간이 있을 수 있습니다. 호출자가 데이터 센터에서 멀리 떨어져 있어서 요청이 인터넷을 통해 전송되는 데 시간이 걸립니다.

  • 두 계층이 동일한 서브넷이 있지 않아서 ASP.NET 응용 프로그램으로부터의 각 SQL 호출에 약간의 대기 시간이 추가되었을 수 있습니다.

  • 로컬에서 사용되는 컴퓨터가 SQL 데이터베이스에서 사용하는 컴퓨터와 다를 수 있습니다.

  • 쿼리 계획이 다를 수 있습니다.

즉, 가장 중요한 점은 기존 솔루션에 대해 SQL Server와 SQL 데이터베이스 성능을 비교해서는 안 된다는 것입니다. 이 둘은 다를 수밖에 없는 많은 이유가 있습니다. 이보다는 비즈니스 문제를 해결하는 데 어떤 수준의 성능이 필요한지에 중점을 두어야 합니다. 이 경우 고객의 요구 사항이 웹 페이지를 2초 동안 렌더링하는 것이라면 정확한 솔루션은 온-프레미스 SQL Server 솔루션과 Windows Azure 가상 컴퓨터(IaaS) 솔루션 및 SQL 데이터베이스 간에 다를 수 있지만 Windows Azure 플랫폼에서 이를 달성할 수 있는 여러 방법이 있습니다. 이 예에서 고려할 수 있는 가능한 변경은 2초라는 목표를 위해 성능을 향상하는 것입니다.

  • 클라이언트와 웹 서버 간의 대기 시간을 확인합니다. 테스트 웹 페이지를 작성하여 데이터베이스에 접속할 필요가 없는 웹 페이지의 응답 시간을 측정합니다. 이것이 주 요인이라면 좀 더 가까운 데이터 센터를 찾아 대기 시간을 줄이는 방법 및/또는 CDN(Content Distribution Network)을 사용하여 각 사용자에게 가까운 콘텐츠를 캐싱함으로써 고객이 지각하는 대기 시간을 줄이는 방법을 고려해 보십시오.

  • ASP.NET 응용 프로그램의 왕복을 조합하여 계층 간에 연관된 물리적 네트워크 오버헤드를 줄일 수 있는지 여부를 고려해 봅니다. 이 또한 대기 시간을 다소 개선할 수 있습니다.

  • 각 쿼리의 실행 시간을 검토하여 개선할 수 있는 느린 쿼리 계획, 제거할 수 있는 잠금 또는 개선할 수 있는 데이터베이스 리소스를 찾아봅니다. 필요할 경우 각 작업에 대한 성능 목표를 정의하여 각 부분 문제에 대해 이 방법을 반복합니다.

  • master 데이터베이스에서 새로운 sys.event_logsys.database_connection_stats DMV를 검토하여 제한에 이르기 전에 데이터베이스 스트레스를 얼마나 테스트할 수 있는지 확인함으로써 다음을 고려해야 할지를 파악합니다.

    • 응용 프로그램 수준 연결 수 줄이기

    • 달성해야 하는 처리량 수준을 얻기 위해 데이터베이스를 추가하는 데이터 모델 축소

핵심 해결 방법은 SQL 데이터베이스 성능이 SQL Server와 동일한지 확인하는 것이 아니라 비즈니스 문제에서 시작하여 목표를 정의하는 것입니다. 이 두 가지 접근법은 거의 확실히 다릅니다.

성능 문제를 찾을 때 이 문제를 다른 모든 리소스 사용 및 차이의 원인과 분리하는 것이 도움이 될 경우가 많습니다. 앞의 예를 계속 사용하여 하나의 특정 쿼리가 처리되는 시간이 총 3초 걸렸다고 가정해 보겠습니다. 이 쿼리를 가져와 데이터베이스에 대해(SQL Server Management Studio에서 호출된 저장 프로시저의 내부가 될 수 있음) 직접 실행하여 쿼리를 원래 솔루션과 분리합니다. 웹 응용 프로그램이 약간 잘못 구성되었거나 쿼리에 성능을 저하하는 계획이 있을 수 있습니다. 격리된 테스트를 실행하면 잠재적인 원인이 제거되고 처리할 주요 문제를 신속하게 구분해 내는데 도움이 됩니다. 저조하게 수행되는 작업을 가져와 쿼리 텍스트의 주요 부분을 제거하는 등 좀더 분리하면 해당 성능 문제가 발생하지 않고 다시 작업의 “속도가 빨라지는” 지점을 찾는 데 도움이 되는 경우도 있습니다. 이 기법은 SQL 제품 팀에서 고객의 문제 격리를 도울 때 자주 사용하는 방법입니다. SET STATISTICS IO ONSET STATISTICS TIME ON 설정을 연결 수준에 추가하면 실행 성능에 대한 추가 세부 정보를 제공하는 데 도움이 됩니다.

호스팅되는 인프라는 문제를 해결하기가 더 어려울 수 있습니다. 성능 카운터나 온-프레미스 배포에서 사용 가능한 기타 데이터를 직접 볼 수 없기 때문입니다. 로깅(결과에 영향을 주지 않도록 하기 위해 다른 데이터베이스에 저장될 수 있음)을 통해 반복 가능한 테스트를 만들 수 있으면 이 테스트를 여러 데이터 센터에 배포할 수 있기 때문에 많은 도움이 됩니다. 현재 각 데이터 센터에 사용되는 하드웨어는 많은 부분이 비슷하긴 하지만 어느 정도 차이가 있으며, 특정 작업에 짧은 대기 시간이 요구되는 고객 시나리오의 경우 특히 이러한 차이가 특정 테스트에서 나타날 수 있습니다. 특정 작업이 중요함을 확인했으면 응용 프로그램을 배포할 계획인 모든 데이터 센터에서 격리된 해당 예의 유효성을 검사해야 합니다. 공식 성능 테스트 도구 세트가 없는 고객도 있으므로 이 단계는 온-프레미스 유효성 검사에도 도움이 될 수 있습니다.

격리된 환경에서는 항상 여러 번의 실행을 통해 작업의 성능을 고려할 필요는 없습니다. 공유 환경에는 시스템의 다른 테넌트로 인한 영향뿐 아니라 백그라운드 시스템 작업 때문에 더 많은 차이가 있을 수 있습니다. 따라서 반복 가능한 테스트를 사용하여 여러 번 실행 하는 것이 유용합니다. 여러 번의 실행으로부터 결과를 수집하면 호출 간에 약간의 성능 차이가 보이며 특정 반복 작업에 대한 백분위수 분포 그래프를 작성하여 이에 대해 추정할 수 있습니다.

다음 예를 살펴보십시오.

고객 쿼리의 작업에 대한 목표 대기 시간 요구 사항이 20ms이고, 쿼리를 1000번 실행하여 고객은 평균 시간이 26.95ms임을 알았습니다. 모든 호출에 대한 대기 시간의 백분위수 분포를 살펴보면 데이터는 다음과 같습니다.

(x축은 동일한 쿼리를 N번 실행하는 특정 테스트의 백분위수입니다. y축은 밀리초 단위의 대기 시간입니다.)

흥미롭게도 데이터 분포는 균일하지 않으며 데이터 집합의 맨 끝에서 급등합니다. 평균만으로는 판단할 수 없습니다. 많은 대기 시간 응답 곡선은 지수 분포를 보이며, 몇 가지 핵심점과 곡선을 집어내고 주요 백분위 수의 대기 시간을 기술하여 이를 설명할 수 있습니다.

 

평균

26.95ms

50번째 백분위수

15ms

95번째 백분위수

21ms

99번째 백분위수

80ms

99.9번째 백분위수

1044ms

이 예에서 주요 문제는 데이터 분포의 꼬리에 나타납니다. 다른 데이터 요소보다 훨씬 낮은 데이터 요소가 이따금씩 있습니다. 이런 방법으로 문제를 포착하여 나타낼 수 있으면 문제가 일반적인 경우가 아님을 설명하는 데 도움이 됩니다. 이 문제는 어느 정도 중요하지만 드물게 발생하는 문제입니다.

이 특정 예는 공유 환경에서 발생할 수 있고 솔루션에 사용되는 경로를 따라 라우터 중 하나에서 삭제된 TCP 패킷과 관련된 경우가 많습니다. 패킷이 삭제될 경우 TCP는 약 1초 후 재시도합니다. 이 경우 이 문제의 가능한 원인은 네트워크 정체입니다. 이 문제가 상당히 높은 빈도로 발생하면 네트워크 하드웨어에 문제가 있거나(Microsoft 고객 지원 서비스에 문의해야 함), 어쩌면 결과에 영향을 주는 어떤 다른 문제가 있음을 암시할 수 있습니다.

데이터 로깅을 수행한 후 분포를 나타내는 통계를 사용하여 문제를 분리해 내고 이 문제가 비즈니스에 중요한지 여부를 결정합니다. 위의 예는 문제를 보여 주기 위해 만든 것이지만 이 분석 영역은 실제 문제입니다. 실제 고객에게 이와 같은 문제가 발생했을 경우 일부 고객은 이례적인 상황을 무시해 버리지만(즉, 비즈니스에 중요한 문제가 아니라서 평균 대신 50번째 백분위수 시간에 맞춰 요구 사항을 변경함) 로컬 설치에서 작업하는 다른 경우에는 문제를 격리한 후 이 기법을 사용하여 네트워크 라우터에서 발견된 실제 문제를 수정하기 위해 하드웨어를 교체했습니다.

아키텍처 및 개발 접근법을 이 클라우드 환경에 맞게 조정해야 하는 것처럼 운영 사고 방식도 변화해야 합니다. Azure SQL 데이터베이스에 효과적인 시스템을 구현하려면 클라우드 환경에 맞게 조정된 운영 프로세스가 필요합니다. 리소스 사용과 효율성에 대한 정보를 얻고 오류를 검색하는 데 사용되는 전통적인 접근법과 기술을 다중 테넌트 환경에서 효율적으로 운영 의사 결정을 할 수 있도록 하기 위해 수정하고 재평가해야 합니다.

이 섹션에서는 Windows Azure SQL 데이터베이스에서 실행 가능한 통찰력을 얻기 위해 사용할 수 있는 실용적인 기술의 예를 설명합니다. 실제 고객 문제를 조사, 격리 및 해결하는 실용적인 예를 사용하여 어떤 도구를 사용할 수 있고 어떻게 사용해야 하는지를 간략하게 설명합니다.

SQL 데이터베이스는 리소스 사용을 파악하고 오류 상태를 식별하는 데 사용할 수 있는 많은 DMV(동적 관리 뷰)를 제공합니다.

현재 권장 접근법은 다음과 같습니다.

  • 리소스 소비자 식별: 데이터베이스 수준 DMV를 사용하여 최고 리소스 소비자를 식별하고 쿼리 실행 및 효율성을 모니터링합니다(예를 들어 sys.dm_exec_query_statssys.dm_db_missing_index_details 사용).

  • 델타 접근법 사용: SQL 데이터베이스에서 실행하는 현재 요청의 스냅숏을 정기적으로 생성하여 잠금, 차단, 래치 및 기타 경합 문제를 확인합니다(예를 들어 sys.dm_exec_requests 사용).

  • 오류 모니터링: master 데이터베이스 DMV를 모니터링하여 제한과 같은 연결 관련 문제를 살펴봅니다(예를 들어 sys.event_logsys.database_connection_stats 사용).

  • 데이터베이스 리소스 사용 모니터링: sys.resource_stats DMV를 모니터링하여 일정 기간 동안의 데이터베이스 리소스 사용을 살펴봅니다.

일부 DMV는 기본 Windows Azure SQL 데이터베이스가 이동된 마지막 시점의 누적 정보를 유지하고(예: sys.dm_exec_query_stats), 다른 DMV는 특정 시점의 스냅숏을 포함합니다(sys.dm_exec_requests). 쿼리의 성능은 리소스 가용성, SQL 데이터베이스 인스턴스가 해당 시점에 상주한 기본 및 보조 노드의 동시성 및 잠금이나 차단과 같은 응용 프로그램 문제의 영향을 받습니다. 온-프레미스 환경에서 sys.dm_os_wait_stats는 일반적으로 sys.dm_exec_query_stats와 함께 사용되어 시스템 대기 정보에서 유추할 수 있는 쿼리 성능과 리소스 제약 조건을 파악합니다. sys.dm_db_wait_stats는 작업 중 실행된 스레드로 인해 발생한 모든 대기에 대한 전체적인 정보를 보여 줍니다. 예를 들어 잠금 대기는 쿼리의 데이터 경합을 나타내고 페이지 IO 래치 대기는 느린 IO 응답 시간을 나타냅니다.

다음과 같은 또 다른 기법을 사용할 수도 있습니다.

  • 모니터링 기간 시작 시 query stats의 초기 스냅숏을 생성합니다. 이를 임시 테이블이나 실제 테이블에 기록합니다.

  • 모니터링 기간 동안 exec requests의 스냅숏을 정기적으로 생성합니다.

  • query stats의 두 번째 스냅숏을 생성합니다.

  • 스냅숏 간의 델타를 비교하고 exec requests 정보를 사용하여 잠금, 차단 또는 리소스 대기가 쿼리 성능에 영향을 주었는지 파악합니다.

한 고객에게 Windows Azure SQL 데이터베이스라고 하는 웹 응용 프로그램의 사용자 요청이 느리다는 문제가 발생했습니다.

아래 스크립트는 SQL 데이터베이스 인스턴스에서 실행하는 상위 일괄 처리를 식별합니다. 이 예에서는 CPU를 많이 사용하는 쿼리를 식별하기 위해 총 CPU 시간을 순서대로 표시합니다. 동일한 쿼리 패턴으로 여러 특성에 따라 정렬하여 각 리소스(논리적 I/O, 경과 시간 등)를 많이 사용하는 다른 소비자를 확인할 수 있습니다. 많은 트랜잭션 시스템에서 상위 몇 개의 결과가 일치합니다. 예를 들어 상위 집계 CPU 소비자는 I/O 리소스도 가장 많이 사용합니다.

/* Top batches by total CPU time No execution plan
**************************************/
SELECT TOP(25)
SUBSTRING (st.text,1, 512),
SUM(qs.total_worker_time) AS total_cpu_time, 
SUM(qs.total_elapsed_time) AS total_elapsed_time,
CAST((CAST(SUM(qs.total_worker_time) AS decimal) / cast(SUM(qs.total_elapsed_time) AS decimal) * 100) AS int)  AS cpu_vs_elapsed_percentage,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS average_cpu_time,
SUM(qs.total_elapsed_time)/SUM(qs.execution_count) AS average_elapsed_time,
SUM(qs.total_logical_reads) AStotal_logical_reads,
SUM(qs.total_logical_reads)/SUM(qs.execution_count) AS average_logical_reads,
SUM(qs.total_logical_writes) AS total_logical_writes,
COUNT(*) AS number_of_statements,
qs.plan_handle,
db_name(st.dbid) AS  [database name],
st.objectid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
GROUP BY st.text, qs.plan_handle, db_name(st.dbid), st.objectid
ORDER BY SUM(qs.total_worker_time) DESC
   

이 예에서 저장 프로시저 dbo.spGetTranslationTable은 다음 일괄 처리보다 CPU를 42배 이상 많이 소비합니다.

특정 쿼리의 리소스 사용률과 효율성을 확인하는 일반적인 방법은 SSMS(SQL Server Management Studio)를 사용하여 쿼리를 격리 실행하는 것입니다. SET STATISTICS IOTIME 옵션을 사용하여 유용한 정보를 볼 수 있으며 실제 실행 계획도 확인할 수 있습니다. 아래에 예가 나와 있습니다.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
exe [dbo].[spGetTranslationTable] @lang='en'

이 예에서 쿼리는 변환 테이블의 900개의 논리적 읽기를 소비했습니다. 쿼리 계획을 살펴보면 상대적으로 작은 행 집합을 반환하는 변환 클러스터형 인덱스 검색의 결과입니다. 테이블에 새 인덱스를 만들면 논리적 I/O가 감소하고 문제가 해결됩니다.

sys.dm_exec_requests는 잠금, 차단 및 대기 유형 등과 같은 쿼리 성능에 대한 데이터를 제공합니다. 예제 쿼리의 경우 아래의 부록 섹션에서 sys.dm_exec_requestsWAIT TYPEWAIT TIME의 스냅숏을 참조하십시오. SQL 데이터베이스에는 SQL 데이터베이스 전용 기능을 나타내기 위해 몇 가지 새로운 대기 유형이 추가로 구현되었습니다. SE_REPL_SLOW_SECONDARY_THROTTLESE_REPLY_COMMIT_ACK 대기 유형은 SQL 데이터베이스가 가용성 목표를 달성하고 탄력성을 제공하기 위해 사용하는 로그 복제 메커니즘과 관련된 것입니다. DMV 결과에 이러한 대기가 있으면 대부분 복제 채널을 통한 I/O 압력을 나타냅니다. 이 문제를 완화할 수 있는 방법은 다음과 같습니다.

  • DML 작업 최소화

  • 응용 프로그램 일괄 삽입 및 트랜잭션 크기 관리

  • 응용 프로그램 삽입의 총 수 줄이기

  • 인덱스 유지 관리 등의 데이터베이스 작업 신중 관리

Tip
SE_REPL 차단 유지 기간이 응용 프로그램에 많은 영향을 줄 경우 Microsoft 고객 지원 서비스에 문의하십시오.

데이터베이스의 부하로 인해 SE_REPL 대기가 발생하거나 해당 데이터베이스를 Windows Azure에서 제한할 경우 많은 방법이 있습니다. 쓰기를 많이 사용하는 작업의 경우 데이터를 여러 데이터베이스의 데이터로 분할해 보십시오. 이 방법은 모두 시스템의 노드 부하를 줄이고 하나의 데이터베이스 컴퓨터에 제한이나 차단으로 인해 전체 응용 프로그램에 영향을 줄 수 있는 상황이 발생할 위험을 최소화합니다. 읽기를 많이 사용하는 작업의 경우 분산 캐시 기술(예: Windows Azure Caching)을 사용하여 데이터베이스의 정보를 캐싱함으로써 쿼리 빈도를 줄여 보십시오.

SE_REPL_SLOW_SECONDARY_THROTTLE을 보여 주는 스크린샷:

SE_REPL_COMMIT_ACK를 보여 주는 스크린샷:

Windows Azure SQL 데이터베이스의 노출 영역은 추가 DMV 및 기타 문제 해결 정보를 제공하기 위해 계속 확장되고 있습니다. SQL 데이터베이스에는 다음 인덱스 관련 DMV가 활성화되었습니다.

  • sys.dm_db_index_usage_stats

  • sys.dm_db_missing_index_details

  • sys.dm_db_missing_index_group_stats

  • sys.dm_db_missing_index_groups

누락된 인덱스 분석 섹션(아래 부록)에 나와 있는 쿼리는 SQL Server에서와 거의 동일한 방법으로 누락된 인덱스 권장 구성을 식별합니다. 이 정보는 계획 캐시나 SSMS를 통해 확인할 수 있는 실행 계획에서도 볼 수 있습니다. 다음 스크린샷은 이를 보여 줍니다.

각 SQL 데이터베이스는 가상 서버(즉, 논리 서버)의 멤버입니다. 이 논리 서버에는 현재 sys.event_logsys.database_connection_stats DMV를 제공하기 위해 확장된 master 데이터베이스가 있습니다. sys.database_connection_stats 뷰는 집계 5분 기간에 성공하거나 종료되거나 제한된 TDS 연결 수의 롤업을 보여 줍니다. sys.event_log 뷰는 성공한 SQL 데이터베이스 연결과 연결 실패, 교착 상태 및 제한 이벤트에 대한 자세한 정보를 보여 줍니다. 이러한 DMV를 통해 응용 프로그램 연결 문제를 신속하게 해결할 수 있습니다.

자세한 내용은 다음을 참조하십시오.

http://blogs.msdn.com/b/wayneb/archive/2012/11/02/introducing-sys-database-connection-stats-for-troubleshooting-windows-azure-sql-database.aspx

http://blogs.msdn.com/b/windowsazure/archive/2012/11/05/announcing-new-dynamic-management-views-for-windows-azure-sql-database.aspx

sys.resource_stats DMV는 일정 기간 동안의 리소스 사용을 보여 줍니다. 이 DMV를 사용하여 리소스 사용을 조사하고 응용 프로그램에 대한 용량을 계획할 수 있습니다.

지난 7일 동안의 sys.resource_stats DMV의 모든 정보를 반환하는 쿼리:

SELECT * FROM sys.resource_stats
WHERE  database_name = 'MyTestDB' AND start_time > DATEADD(day, -7, GETDATE())

반환된 결과의 스크린샷:

지난 7일 동안의 데이터베이스에 대한 평균 및 최대 리소스 사용을 반환하는 쿼리:

SELECT 
    avg(avg_cpu_cores_used) AS 'Average CPU Cores Used',
    max(avg_cpu_cores_used) AS 'Maximum CPU Cores Used',
    avg(avg_physical_read_iops + avg_physical_write_iops) AS 'Average Physical IOPS',
    max(avg_physical_read_iops + avg_physical_write_iops) AS 'Maximum Physical IOPS',
    avg(active_memory_used_kb / (1024.0 * 1024.0)) AS 'Average Memory Used in GB',
    max(active_memory_used_kb / (1024.0 * 1024.0)) AS 'Maximum Memory Used in GB',
    avg(active_session_count) AS 'Average # of Sessions',
    max(active_session_count) AS 'Maximum # of Sessions',
    avg(active_worker_count) AS 'Average # of Workers',
    max(active_worker_count) AS 'Maximum # of Workers'
FROM sys.resource_stats 
WHERE  database_name = 'MyTestDB' AND start_time > DATEADD(day, -7, GETDATE())

반환된 결과의 스크린샷:

단일 코어를 초과하는 사용을 확인하는 쿼리:

SELECT
(SELECT
    SUM(DATEDIFF(minute, start_time, end_time))
    FROM sys.resource_stats
    WHERE database_name = 'MyTestDB' AND 
          start_time > DATEADD(day, -7, GETDATE()) AND
          avg_cpu_cores_used > 1.0) * 1.0 / SUM(DATEDIFF(minute, start_time, end_time)
) AS percenage_more_than_1_core
FROM sys.resource_stats
WHERE database_name = 'MyTestDB' AND start_time > DATEADD(day, -7, GETDATE())

반환된 결과의 스크린샷:

이 섹션에서 다룬 쿼리는 단일 데이터베이스의 제한을 확인하고 문제를 해결하는 데 사용할 수 있습니다.

/* Top statements by total CPU time w/ query plan on the statement level 
note - can be expensive to run this 
**requires** statement_level_query_plan.sql
************************************************************************/
SELECT TOP(25)
SUBSTRING(qt.text,qs.statement_start_offset/2, 
(CASE WHEN qs.statement_end_offset = -1 
THEN len(convert(nvarchar(max), qt.text)) * 2 
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) 
AS statement_text,
SUBSTRING (qt.text , 1, 512) AS batch_text,
qs.total_worker_time,
qs.total_elapsed_time,
CAST((CAST(qs.total_worker_time AS decimal) / cast(qs.total_elapsed_time AS decimal) * 100) AS int)  AS cpu_vs_elapsed_percentage,
qs.total_worker_time/qs.execution_count AS average_cpu_time,
qs.total_elapsed_time/qs.execution_count AS average_elapsed_time,
qs.total_logical_reads,
qs.execution_count, 
qs.total_logical_reads/qs.execution_count AS average_logical_reads,
db_name(qt.dbid) AS [database name],
qt.objectid,
pln.statement_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY master.dbo.statement_level_query_plan(plan_handle) AS pln
WHERE statement_text LIKE
'%' + REPLACE(LEFT(SUBSTRING((select text from master.sys.dm_exec_sql_text(sql_handle)), 
statement_start_offset/2, 1 + CASE WHEN statement_end_offset = -1 
THEN LEN((SELECT text FROM master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
ELSE statement_end_offset/2 - statement_start_offset/2 
END) 
,3000), '[','[[]') + '%'
ORDER BY qs.total_worker_time DESC  

-- TOP 50 Query Plans in SQL Azure Database
SELECT TOP(50)
            CONVERT(bigint, DATEPART(yyyy,getdate())*10000000000)+(DATEPART(mm,getdate())*100000000)+(DATEPART(dd,GETDATE())*1000000)+(DATEPART(hh,GETDATE())*10000)+(DATEPART(mi,GETDATE())*100)+(DATEPART(ss,GETDATE())) AS timestampKey ,
                GETDATE() AS eventdateUTC,
            CONVERT(decimal(8,2),(CAST(qs.execution_count AS FLOAT)/(DATEDIFF(mi,qs.creation_time,qs.last_execution_time)+1))*(((CAST(qs.total_worker_time AS FLOAT) / qs.execution_count) / 100000.00)+((CAST(qs.total_elapsed_time AS float) / qs.execution_count) / 1000000.00))) AS Weighting,
            query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
                            (/ShowPlanXML//Object/@Index)[1]', 'varchar(255)') AS IndexName, 
            qp.query_plan,
                    CASE WHEN query_plan.exist('
                        declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
                        /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Hash Match"]'
                               )=1 THEN 1 ELSE 0 END AS hash_match
                    , CASE WHEN query_plan.exist('
                        declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
                        /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Table Scan"]'
                               )=1 THEN 1 ELSE 0 END AS table_scan
                    , CASE WHEN query_plan.exist('
                        declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
                        /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Clustered Index Scan"]'
                               )=1 THEN 1 ELSE 0 END AS clustered_index_scan
            ,SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
            (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
            ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) 
            AS statement_text,
            qs.total_worker_time/qs.execution_count AS average_cpu_time,
            qs.total_elapsed_time/qs.execution_count AS average_elapsed_time,
            qs.total_logical_reads/qs.execution_count AS average_logical_reads,
            qs.total_logical_writes/qs.execution_count AS average_logical_writes,
            qs.execution_count,
            qs.plan_generation_num,
            qs.total_worker_time,
            qs.total_elapsed_time,
            qs.total_logical_reads,
            qs.total_logical_writes,
            db_name() AS [db_name],
            qt.objectid,
            qs.query_hash, 
            qs.creation_time,
            qs.last_execution_time
            FROM sys.dm_exec_query_stats qs
            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
            CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
            WHERE qt.text NOT LIKE '%sys.%'
            ORDER BY qs.total_elapsed_time DESC

-- Snapshot of current sys.dm_exec_requests with WAIT TYPE and WAIT TIME
declare @i int = 0
                    DECLARE @Requests TABLE (
                    [timestampKey] [bigint] NULL,
                    [eventdateUTC] [datetime] NOT NULL,
                    [statement_text] [nvarchar](max) NULL,
                    [command] [nvarchar](32) NOT NULL,
                    [cpu_time] [int] NOT NULL,
                    [total_elapsed_time] [int] NOT NULL,
                    [wait_type] [nvarchar](60) NULL,
                    [wait_time] [int] NOT NULL,
                    [last_wait_type] [nvarchar](60) NULL,
                    [wait_resource] [nvarchar](60) NOT NULL,
                    [reads] [bigint] NOT NULL,
                    [writes] [bigint] NOT NULL,
                    [logical_reads] [bigint] NOT NULL,
                    [row_count] [bigint] NOT NULL,
                    [granted_query_memory] [int] NOT NULL,
                    [query_hash] [binary](8) NULL,
                    [query_plan] [xml] NULL,
                    [hash_match] [bit] NULL,
                    [table_scan] [bit] NULL,
                    [clustered_index_scan] [bit] NULL,
                    [session_id] [smallint] NOT NULL,
                    [blocking_session_id] [smallint] NULL,
                    [start_time] [datetime] NOT NULL,
                    [status] [nvarchar](30) NOT NULL,
                    [db_name] [nvarchar](128) NULL
                    )

                    WHILE(@i < 20)
                    BEGIN
                    INSERT INTO @Requests
                    SELECT 
                    CONVERT(bigint, datepart(yyyy,getdate())*10000000000)+(datepart(mm,getdate())*100000000)+(datepart(dd,getdate())*1000000)+(datepart(hh,getdate())*10000)+(datepart(mi,getdate())*100)+(datepart(ss,getdate())) as timestampKey ,
                    getdate() as eventdateUTC,
                    SUBSTRING(t.text,r.statement_start_offset/2+1,(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), t.text)) * 2 else r.statement_end_offset end -r.statement_start_offset)/2) as statement_text, 
                    command,
                    cpu_time,
                    total_elapsed_time,
                    wait_type,
                    wait_time,
                    last_wait_type,
                    wait_resource,
                    reads,
                    writes,
                    logical_reads,
                    row_count,
                    granted_query_memory,
                    query_hash,
                    query_plan,
                    case when query_plan.exist('
                        declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
                        /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Hash Match"]'
                               )=1 then 1 else 0 end as hash_match
                    , case when query_plan.exist('
                        declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
                        /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Table Scan"]'
                               )=1 then 1 else 0 end as table_scan
                    , case when query_plan.exist('
                        declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
                        /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Clustered Index Scan"]'
                               )=1 then 1 else 0 end as clustered_index_scan
                    ,session_id,
                    blocking_session_id,
                    start_time,
                    status,
                    db_name() as [db_name] 
                    FROM 
                    sys.dm_exec_requests r
                    cross apply sys.dm_exec_sql_text(r.sql_handle) t
                    cross apply sys.dm_exec_query_plan(r.plan_handle) p
                    WHERE @@spid<>r.session_id
                       waitfor delay '00:00:00:100'
                       set @i = @i+1
                    end
                    select * from @Requests;

-- Missing indexes stats
SELECT  CONVERT(bigint,datepart(yyyy,getdate())*10000000000)+(datepart(mm,getdate())*100000000)+(datepart(dd,getdate())*1000000)+(datepart(hh,getdate())*10000)+(datepart(mi,getdate())*100)+(datepart(ss,getdate())) [timestampKey] 
 ,getdate() [eventdateUTC], 
 db_name() as [db_name], 
 mig.index_group_handle, 
 mid.index_handle,  
 'CREATE INDEX missing_index_' + 
 CONVERT (varchar, mig.index_group_handle) + '_' + 
 CONVERT (varchar, mid.index_handle) + ' ON ' + 
 mid.statement   + ' (' + ISNULL (mid.equality_columns,'') + 
 CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL 
THEN ',' 
ELSE '' 
 END + ISNULL (mid.inequality_columns, '') + ')' + 
 ISNULL (' INCLUDE (' + mid.included_columns + ')', '') 
 AS create_index_statement,  
 migs.*, 
 mid.database_id, 
 mid.[object_id] 
FROM sys.dm_db_missing_index_groups mig 
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle 
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle 
WHERE 
 CONVERT(decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 
ORDER BY 
 migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

참고 항목

표시:
© 2014 Microsoft