SQL Server 성능관리 엑셈컨설팅본부 /SQL Server 팀김범규 1. SQL Server 성능모니터링 관리자가 SQL Server 의현재상태가정상인지확인하고자한다. 어떻게 SQL Server 의상태 를확인할수있을까? 가장쉽게 DB 서버의상황을확인하는방법은작업관리자를실행시켜현재 CPU 와 Memory 사용률을확인하는것이다. 하지만 SQL Server 의 Memory 설정이자동 ( 기본값 ) 으로되어있다면 Memory 사용률을확인하는것은의미가없다. (SQL Server 는한번사용한 Memory 를여간해서는 OS 에반환하지않음으로여유메모리가부족하다는것만으로문제임을판단할수없다.) 결국작업관리자를통해얻을수있는정보는 SQL Server 의 CPU 사용률정도이고, 보다정확한정보를확인하기위해서는 Windows 에서제공하는 Perfmon (Performance Monitor) 과 SQL Server 의 DMV(Dynamic Management View) 등을이용하여야한다. 모니터링툴 Performance Monitor Windows 에서기본적으로제공되는모니터링툴로써, SQL Server 설치시에 SQL Server 관련모니터링항목들이추가된다. [ 시작 ] 에 Perfmon 을입력하여실행하고, 다음과같은화면이나타나면모니터링에필요한항목을선택한다. 예를들어현재 SQL Server 가사용하고있는메모리사용량을확인하기위해하고자한다면, [ 인스턴스명 ]:Memory Manager\Total Server Memory(KB) 을선택한다. Part 3 SQL Server 507
[ 그림 1] Perfmon 실행 [ 그림 2] 모니터링카운터추가 508 2013 기술백서 White Paper
[ 그림 3] Toal Server Memory (KB) 선택 [ 그림 4] Toal Server Memory (KB) 선택 Part 3 SQL Server 509
이와같은방법으로, 다음과같은지표들을참고하면시스템의상태를보다정확하게파악할수 있다. CPU 관련 [ 인스턴스명 ]:Processor\Processor Time [ 인스턴스명 ]:Processor\User Time Memory 관련 [ 인스턴스명 ]:Buffer Manager\Page lookups/sec [ 인스턴스명 ]:Buffer Manager\Page life expectancy Disk 관련 [ 인스턴스명 ]:Buffer Manager\Page reads/sec [ 인스턴스명 ]:Buffer Manager\Page writes/sec [ 인스턴스명 ]:Buffer Manager\Readahead pages/sec [ 인스턴스명 ]:PhysicalDisk\Avg. Disk Queue Length [ 인스턴스명 ]:PhysicalDisk\Current Disk Queue Length Transaction 관련 [ 인스턴스명 ]:Databases\Active Transactions [ 인스턴스명 ]:Databases\Transactions/sec [ 인스턴스명 ]:Databases\Active Transactions Workload 관련 [ 인스턴스명 ]:SQL Statistics\Batch Requests/sec [ 인스턴스명 ]:SQL Statistics\SQL Compilations/sec 510 2013 기술백서 White Paper
Dynamic Management View SQL Server 2005 부터 Database Engine 이직접성능관련정보를관리하고있으며, 성능관리뷰 (Dynamic Management View) 를통해관련정보를확인하는것도가능하다. 이를통해시스템 & 오브젝트정보뿐만아니라, 현재수행되고있는세션관련정보 (SQL, Plan) 와대기정보 (lock, latch, spinlock 등 ) 를실시간으로확인가능하다. 시스템 & 오브젝트관련 sys.databases sys.objects sys.objectcaches sys.indexes 세션관련 sys.sysprocesses sys.dm_exec_requests sys.dm_exec_sessions 대기관련 sys.dm_tran_locks sys.dm_waiting_tasks SQL&PROCEDURE 관련 sys.dm_exec_sql_text sys.dm_exec_query_plan sys.dm_exec_query_stats Part 3 SQL Server 511
CAWL 모니터링방법론 앞서데이터베이스의상태를파악하기위한필요한정보를확인하는방법에대해서알아보았다. 그렇다면이정보들을활용하여상태를파악하는방법에대해알아보자. 필자는기본적으로데이터베이스의상태파악을위해 CPU, Active Sessions, Wait Time, 그리 고 Logical Reads 를확인한다. 그래서이 4 가지지표의앞글자를모아 CAWL( 카울 ) 이라고부 른다. 이렇게 4 가지의기본정보만으로도시스템의이상유무를판단하는데부족함이없다. Active Sessions 은세션의상태가 running(sql 실행중 ) 이거나, suspended( 대기중 ) 인세션으 로정의하며, sys.sysprocesses 에서 spid>50 이고 status<> sleeping 조건을만족하는세션 들이다. Wait Time 은각세션들이 1 초동안대기한시간들의합으로정의한다. 하나의세션이 1 초동안대기시간할수있는최대시간은 1 초이다. 따라서만약 10 개의세션이 1 시간동안 lock 을대기하고있다면이시스템의현재대기시간은 10 초가된다. 이정보를확인하기위해서는해당정보를약간가공해서보여줄수있는스크립트혹은툴을활용하면보다효과적으로실시간정보를확인하기가용이하다. Logical Reads 는데이터검색을위해발생된 Memory IO 를의미하며, SQL Server 에서는 Page lookups/sec 지표가해당값을의미한다. SQL 이수행되기위해서는 Memory IO 가반드 시사용되므로 Logical Reads 를확인함으로써가장직관적으로 DB 의일량을확인할수있다. DBMS 의정상여부를확인하는방법에대해살펴보자. 먼저, 가장손쉽게확인가능한정보인 CPU 를확인하고, Logical Reads 과 Active Sessions 을 확인하여 DBMS 의이상유무를확인할수있다. CPU Logical Reads : DB 문제가능성少. SQL Server 이외의프로그램에의한 CPU 사용여부확인 CPU Logical Reads : DB 문제가능성多 CPU Logical Reads Active Sessions : 비상 CPU Logical Reads Active Sessions : 특정악성쿼리수행여부확인 CPU Logical Reads Active Sessions : Lock 과같은 Blocking 대기확인 Wait Time : DB 내경합으로처리시간지연발생가능 512 2013 기술백서 White Paper
Wait Time CPU : DB 가문제라기보다업무처리량이증가한상태로추정가능. DB 접속이원활 한지확인필요. 2. SQL 튜닝 SQL 튜닝대상수집 SQL 튜닝대상선정을위해일반적으로사용되는방법으로 Trace 정보를활용하는방법과 DMV(Dynamic Management View) 를활용하는방법이있다. Trace 정보를수집하여 SQL 튜닝대상을추출하는방법은전통적으로튜닝에사용되어온매우효과적인방법으로, 다양한 filter 조건을이용하여원하는정보만을수집가능하다. 예를들어, 특정사용자, DB 를사용하는 SQL 만을찾을수있으며, 1 회수행시수행시간이 3 초이상이면서 5,000 Pages 이상을 Access 하는 SQL 만을수집하는것도가능하다. 하지만조건을잘못기술할경우시스템에매우큰부하를유발시킬수있으므로주의가필요하다. 가. Profiler 를이용한 Trace 정보수집 SQL Server 는수행되는 SQL 에대한 Trace 정보를손쉽게수집할수있도록 UI 를제공한다. SSMS 의도구메뉴에서 Profiler 를실행하면다음과같은프로그램이실행된다. Part 3 SQL Server 513
[ 그림 5] Profiler 실행 [ 그림 6] Profiler 접속 514 2013 기술백서 White Paper
[ 그림 7] 이벤트선택 1 [ 그림 8] 이벤트선택 2 Part 3 SQL Server 515
[ 그림 9] Trace 시작 나. Script 를이용한 Trace 정보수집 앞에서살펴본바와같이 UI 를통해설정한 Profiler 설정을 Script 로저장도가능하다. 파일 내보내기 추적정의스크립팅 SQL Server 2005 SQL11 이렇게기존에설정한정보를 Script 저장하고, 해당 Script 를열어다음의내용을적당한값으 로수정한다. set @maxfilesize = 5 단위파일의최대사이즈 (MB), 일반적으로 100 정도로설정 exec @rc = sp_trace_create @TraceID output, 0, N'C:\trace_test', @maxfilesize, NULL 생성파일위치와이름지정 수정이완료되면해당 Script 를실행하고, 일정시간이후중지하고자할때는다음의명령을수 행한다. exec sp_trace_setstatus 2, 0 -- 지정한추적을중지합니다. exec sp_trace_setstatus 2, 2 -- 지정한추적을닫고서버에서해당정의를삭제합니다. C:\ 에생성된 trace_test.trc 파일을 profiler 를이용하여열어보면, 해당시점에수행된다양한 SQL 을확인할수있다. 이렇게 Script 를이용하여 Trace file 을수집하는방법이부하가적어, 운영담당자들은이방법을선호한다. 516 2013 기술백서 White Paper
다. PSSDIAG 를이용한긴급로그수집하기장애가발생하거나하여, 긴급히데이터를수집해야할경우가발생한다. 다음과같은방법을참고하여향후장애시점의상황을추정할수있는데이터를남겨놓을수있으며, 기술지원을요청하게될경우큰도움이될수있으므로아래의프로세스는반드시숙지하고있어야한다. 1. SQL Server 머신에공간이충분한임의의폴더를생성 ( 예를들어, D:\PSSDIAG) 하고, Microsoft 홈페이지에서최신의파일을다운받는다. 파일을실행하면파일의압축이자동으로 해제된다. 2. 명령프롬프트 (Command Prompt) 를실행하고앞에서생성한폴더로이동한다음, 압축해제후생성된 pssdiag.cmd를실행한다. pssdiag.cmd를실행하면몇가지실행관련메시지발생후다음과같은내용의메시지가출력되며이때부터필요한자료수집이시작된다. 2011/03/16 17:44:59.13 PSSDIAG Collection started. Press Ctrl+C to stop. 3. 명령프롬프트에서 Ctrl+C 를눌러자료수집이중지된다. Ctrl+C 누른후수집된자료를저장하는과정에약간의시간이소요되므로작업이완전히끝날때까지기다려야하며, 모든작업이완료되면다음과같은내용의메시지가출력된다. 2011/03/16 17:47:00.99 PSSDIAG Collection complete. Collector exiting 4. pssdiag.exe 가존재하는폴더의아래에 output 이라는폴더가생성되는데수집된자료는 모두 output 폴더에저장된다. 튜닝정보분석 지금까지시스템부하분석을위한정보를수집하는방법에대해살펴보았다. 지금부터는이정 보를통해우리가튜닝에활용할수있는정보를추출하는방법에대해서살펴보도록한다. 가. SQL Server 에 Trace file Loading 하여분석하기 Part 3 SQL Server 517
우리가지금까지수집한 Trace file 을데이터베이스넣어활용하는방법이다. 이모든데이터가 데이터베이스에넣을수만있다면굳이설명하지않더라도자연스럽게데이터를활용하고분석 할수있을것이다. 그럼 Trace file 을데이터베이스에넣는방법에대해알아보자. USE tempdb; GO SELECT * INTO temp_trc FROM fn_trace_gettable('c:\trace_test.trc', default); GO 이제는기다리기하면 Trace file 이 temp_trc 테이블에자동으로로딩된다. 참고로이것은우리가수집한 Event Class 번호이다. 이것을참고하여검색하여튜닝에활용하 면되며, 해당정보중에 reads, duration 등을내림차순으로정렬하여조회하면가장시스템에 큰부하를주는 SQL 을쉽게확인할수있다. --RPC:Completed 10 --SQL:BatchCompleted 12 --SQL:StmtCompleted 41 --SP:StmtCompleted 45 SELECT endtime, reads, duration FROM dbo.temp_trc WHERE eventclass IN (10,12) AND reads>1000 AND duration>1000 ORDER BY reads 나. RML 을이용하여 Trace file 분석하기 Microsoft 에서제공하는 RML(Replay Markup Language) utilities 을이용하면분석된정보를그래프를통해보다쉽게확인할수있다. 먼저최신의 RML utilities 를 Microsoft Download Center 에서다운받는다. (http://support.microsoft.com/kb/944837/en-us) 설치가완료되면 Command 창에서해당폴더로이동하여 ReadTrace 실행하면수집한 Trace file 을자동으로분석하여 Graphical Report 를작성하여제공해준다. ReadTrace 는해당정보를분석하기위해 518 2013 기술백서 White Paper
데이터베이스에해당 Trace file 을 load 한다. 따라서먼저사용할수있는사용할수있는데이 터베이스에대한접속정보가필요하다. cd C:\Program Files\Microsoft Corporation\RMLUtils -- DISK C: 를소문자로하면에러남 ReadTrace -I"C:\trace_test.trc" -S"KIM-BUM-KYU" -E 다. DMV 를이용하여분석하기 SQL Server 2005 이상의버전에서는 Profier 를통한별도의 Trace file 수집없이도자체적으로관리되는 DMV 를이용하면수행되었던 SQL Text 와 Procedure 에대한통계정보를확인하는것도가능하다. 경우에따라 Profiler 를이용하는방법에비해정보가조금부족할경우도있으나, 서버에부하없이원하는정보를확인할수있다는특별한장점때문에잘활용만한다면매우유용한방법이다. 다음의 DMV 를활용하면성능모니터링및관리에많은정보를얻을수있으므로참고하기바란다. sys.sysprocesses sys.dm_exec_requests sys.dm_tran_locks sys.dm waiting_tasks sys.dm_exec_query_stats sys.dm_exec_procedure_stats sys.dm_exec_query_plan Part 3 SQL Server 519