블로그 이미지

MSSQL Tuning 목적

2013.12.08 15:33 Posted by 건방진연이

2013년 하반기에는 (주)씨퀄로 정원혁 대표님이 쓰신 <<Microsoft SQL Server 2000/2005 튜닝>>  책으로 아래와 같은 일정으로 진행을 하고 있으며 이중에 제가 진행하였던 튜닝 목적 및 튜닝을 위한 도구들에 대하여 간략하게 올려 봅니다.


[2013. 11. 09] - [윤병희] - PAL를 이용한 SQL Server 성능 분석

[2013. 11. 23] - [이승연] - 튜닝 목적

[2013. 11. 23] - [이승연] - 튜닝을 위한 도구

[2013. 12. 07] - [권순용] - 물리적 데이터베이스 아키텍처

[2013. 12. 21] - [탁세인] - 인덱스 기초

[2014. 01. 04] - [이] - 인덱스 활용

[2014. 01. 18] - [권순용] - 최적화기

[2014. 02. 08] - [윤병희] - 트랜잭션 잠금 기초/활용

[2013. 02. 22] - [김필재] - 함수

[2013. 02. 22] - [이승연] - 프로시저/커서

[2013. 03. 08] - [이승연] - 특별한 특강?

[2013. 03. 22] - [김다미] - 봄맞이 워크샵 (열심히 공부한 당신! 떠나라)

1. 왜 튜닝을 하는가?

자기 자신이 관리하는 DBMS가 정상적인 응답시간과 처리량을 가질 수 있도록 하기 위하여 우리는 튜닝을 한다.

◈ 일반적인 튜닝 방법

  • 네트워크 트래픽을 줄인다.
  • 디스크 입출력 양을 줄인다.
  • CPU 사용량을 줄인다.

◈ 튜닝의 초점
  • 응답시간(Respone Time)을 좋게 할 것인지?
  • 처리량(Throughput)을 늘릴 것 인지?

(개인적으로 응답시간을 좋게 하는것과 처리량을 늘리는 것과 아직까지 잘 모르겠다. 응답시간을 늘리려고 병렬처리를 최대로 하여 다른 쿼리들이 대기를 한다면 모를까? 일반적으로 우리가 말하는 튜닝은 처리량 즉, 데이터베이스 리소스 사용량을 최대한 줄여 쿼리 응답시간을 빠르게 한다. 다시 말하면 우리는 응답시간을 줄이기 위해서 처리량을 좋게 튜닝을 한다는 것이다.)


◈ 모니터링

튜닝을 하기 위해서는 무엇이 문제인지 파악이 되어야 한다. 항상 모니터링 솔루션을 통하여 우리 서버가 현재 어떤 상태이며 어떤 부분에 전문가의 손길을 주어야 하는지 파악하고 있어야 한다. 특별한 돈을 들이지 않고 회사에서 모니터링 하는 몇 가지 방법 소개


다음과 같은 쿼리를 프로시저로 만들고 특정 WEB Page를 만들어서 모니터링을 한다.

/********************************************************************************

*    현재 서버에서 실행되고 있는 쿼리 (sys.sysprocesses 추가)

********************************************************************************/

/*

CREATE FUNCTION dbo.AFN_THREEPARTNAME(

    @object_id INT,

    @database_id INT

)

RETURNS NVARCHAR(1000)

AS

BEGIN

    RETURN (QUOTENAME(DB_NAME(@database_id)) + '.'

          + QUOTENAME(OBJECT_SCHEMA_NAME(@object_id, @database_id))

          + '.' + QUOTENAME(OBJECT_NAME(@object_id, @database_id)));

END

*/

 

/********************************************************************************

*    현재 서버에서 실행되고 있는 쿼리 (sys.sysprocesses 추가)

********************************************************************************/

SELECT  r .session_id AS [세션ID] ,

        r.[status] AS [상태],

        r.wait_type AS [대기상태] ,

        r.scheduler_id AS [SchedulerID],

        CASE WHEN qt. objectid IS NULL THEN 'AD-HOC'

             ELSE dbo.AFN_THREEPARTNAME(qt.objectid, qt.dbid)

        END AS [SP이름] ,

        SUBSTRING(qt .[text], r. statement_start_offset / 2,

                  ( CASE WHEN r.statement_end_offset = -1

                         THEN LEN (CONVERT (NVARCHAR( MAX), qt.[text] )) * 2

                         ELSE r .statement_end_offset

                    END - r. statement_start_offset ) / 2 ) AS [statement_executing] ,

        DB_NAME(qt .[dbid]) AS [DatabaseName] ,

        OBJECT_NAME(qt .objectid) AS [ObjectName] ,

        r.cpu_time / 1000. AS [CPU시간(s)] ,

        r.logical_reads / 128. AS [논리적읽기(MB)] ,

        r.granted_query_memory / 128. AS [쿼리메모리(MB)] ,

        r.start_time ,

        r.total_elapsed_time / 1000. AS [실행되고있는시간(s)] ,

        ( r .total_elapsed_time / 1000. ) / 60 AS [실행되고있는시간(m)] ,

        r.reads ,

        r.writes ,

        r.plan_handle ,

        (SELECT CAST(qp .query_plan AS XML ) FROM sys.dm_exec_text_query_plan (r. plan_handle, r.statement_start_offset , r.statement_end_offset ) AS qp) AS [실행계획] ,

        sp.*

FROM    sys.dm_exec_requests AS r

        CROSS APPLY sys.dm_exec_sql_text(sql_handle ) AS qt

        INNER JOIN sys.sysprocesses AS sp

            ON r .session_id = sp .spid

WHERE   r .session_id > 50

ORDER BY r. total_elapsed_time DESC ,

        r.scheduler_id ,

        r.[status] ,

        r.session_id ;



프로파일러를 이용하여 특정 Duration 이상인 SP들을 모니터링 한다.


마지막으로 PerfMon을 이용하여 현재 서버 헬스 체크를 항상 한다.

Memory:Available MBytes - 사용가능한 메모리

Processor Information:% Processor Time - CPU 사용률

SQLServer:General Statistics:Processes blocked - 블럭킹 카운트

SQLServer:General Statistics:User Connections - 컨넥션 개수

SQLServer:Locks:Number of Deadlocks/sec - 교착상태 개수

SQLServer:SQL Statistics:Batch Requests/sec - 일괄처리 양


◈ 성능 베이스라인

모니터링을 하기에 앞서 우리 서버의 적정 처리량을 알아야 한다. 이를 베이스라인이라고 하며 기준을 잡아둔 베이스라인 수치보다 넘어서는지 안넘어서는지 모니터링을 해야 할 필요성이 있다. 예를들어 다음과 같은 질문에 바로 답할 수 있어야 한다.

  • 우리 회사의 평균 CPU 사용량은 ?
  • 우리 회사의 데이터베이스 서버가 가장 바쁜 시간대에는 ?
  • 가장 바쁠때 CPU, Disk, Memory 상태는 ?
  • 우리 회사의 평균 접속자는 ? 매출 ?
  • 우리 회사의 평균 Batch Request는 ? User Connection은 ?
Know-how : 어떻게 문제를 해결해야 하는지 방법을 알고 있는 것 (기술적인 지식)
Know-where : 정보가 어디에 있는지 알고 있으며, 언제든지 활용할 수 있는 것
nowhere : no-where? now-here? 우리의 고정관념

2. 성능 모니터링과 튜닝
◈ 성능을 위한 전략 수립
프로젝트 처음부터 데이터베이스 성능이 고려되는가 ?
성능이 고려되지 않은 상태에서 결과만 나오면 OK ?
결과를 만들어내기 위하여 사용된 Resource도 고민 필요

◈ 성능을 위한 전략 수립
자신만의 해결 방법을 고민해 보고 문서화를 하자. 아래 프로시저 개발 방법론 예시를 들어보겠다.
  • Naming Rule을 준수하는가
  • 주석 관리는 되고 있는가
  • 적절한 SET 옵션을 사용하고 있는가
  • SELECT 구문에 필요한 컬럼만 나열 하였는가
  • 실행계획에 SCAN 또는 HASH JOIN은 없는가
  • WHERE 조건에 명시적/암시적 형 변환은 일어나고 있지 않는가
  • 부정현 연산자를 사용하는가
  • OUTPUT 파라메터를 사용하는가
  • 커서 및 임시테이블을 사용하는가
  • 뷰를 사용하는가
  • 함수를 사용하는가
  • 등등등
◈ 병목
병목= 병(병병)의 목 = 병의 아가리 아래쪽의 잘록한 부분

◈ 튜닝의 초점


하드웨어 - Scale Up? 다른 대안은 없는가 ?

운영체제 - 서버의 용도에 맞게 분리되어 있는가 ? 파일서버, SQL 서버, IIS 서버 등등

SQL 서버 - Engine upgrade ? sp_configure ?

데이터베이스 - 디스크위치? 파일 크기 ? 테이블 디자인 ? 컬럼 디자인 ? 인덱스 디자인 ?

응용프로그램 - SARG, 트랜잭션 처리, 블럭킹 처리, 교착상태 처리


3. 튜닝 방법론

◈ 디자인

테이블 디자인, 퀴리 디자인, 많은 쿼리들중에 상위 20%의 쿼리가 리소스 사용량이 나머지 80% 쿼리의 리소스 사용량 보다 많다. (20:80 팔레트 법칙),  사용자 요구사항 파악 처리량을 늘리기 원하는지 응답속도만 빨라지길 원하는지, 그리고 데이터베이스 특성에 맞게 분리되어 있는지 OLTP성 데이터베이스인지 OLAP성 데이터베이스인지...


◈ 튜닝

TOP-DOWN 튜닝 방법 - 고객으로부터 문제의 현상을 듣고 현재 서버가 어떤 상황이라는 것을 판단하여 그 문제부터 해결하는 방식

BOTTOM-UP - Baseline을 기반으로 평소보다 문제가 있는 부분이 생기면 그 부분을 시작으로 튜닝을 하는 것


◈ 튜닝 후 문서화

튜닝 후 무조건 문서화를 하여 튜닝 하기전 과 튜닝하고 난 후의 응답속도 또는 처리량을 비교에서 문서화를 하자.