컨설팅 제안서

Transcription

1 성능모니터링 에이디컨설팅

2 목차 모니터링개요 성능모니터링개요 목표설정 성능모니터링전략 성능모니터링대상요소 성능모니터링도구 요약 참고 URL 2

3 모니터링개요 SQL Server 는동적환경에서서비스를제공하므로모니터링이매우중요 모니터링종류 - 성능모니터링 - 오류모니터링 - 리소스사용모니터링 - 프로세스모니터링 - SQL Server 내부개체모니터링 - 데이터무결성모니터링 3

4 성능모니터링개요 성능모니터링의목표 - 서버의성능평가 성능모니터링을통해가능한작업 - 성능의경향을파악 - 성능향상여부진단 - 사용자작업평가 - 성능저하발생에대한신속대응 성능모니터링요소 - Server-wide 성능모니터링 - SQL Server 개별쿼리성능모니터링 - 성능병목요소에대한모니터링 - 성능에영향을미치는리소스사용에대한모니터링 - SQL Server 내부개체에대한모니터링 4

5 성능모니터링전략 필요항목 효과적인성능모니터링을위한필요항목 - 분명한목표와기준 - 지속적인성능정보수집및분석을통한추이추적 - 효과적인모니터링단계 모니터링목표및전략수립 적절한도구선택 모니터링대상구성요소식별 모니터링대상구성요소를위한항목선택 ( 예 : 추적수집의경우수집할특정이벤트선택 ) 모니터링수행 모니터링결과데이터분석 - 데이터분석을통하여모니터링목표가이루어졌는지확인 - 목표가이루어지지않은경우, 모니터링에사용한구성요소나구성요소를위한항목을수정 5

6 성능모니터링전략 - 목표설정 성능모니터링목표의예 - 성능에대한기준선마련 - 시간에따른성능변환측정 - 특정성능문제진단 - 최적화할구성요소나프로세스식별 - 클라이언트응용프로그램의성능에대한영향도 - 유지관리일정의성능영향도분석 - 백업및복원계획의성능영향도분석 - 하드웨어구성의변경시점결정 성능모니터링시, 유의사항 - 성능정보수집자체가성능저하원인이되어서는안됨 - 목표를달성하기위한성능정보의수집이필요 - 성능에대한기준선마련은꼭필요 6

7 성능모니터링전략 모니터링의목적이무엇인가? 모니터링을위하여어떤정보를수집할것인가? 어떻게모니터링할것인가? 모니터링결과, 원하는목적을달성했는가? 효율적인모니터링전략수립 7

8 성능모니터링도구 목표에따른적절한수집도구의선택 Windows에서제공되는수집도구 - 시스템모니터 (PerfMon) - 성능로그및경고 - 작업관리자 SQL Server에서제공되는수집도구 - PSSDIAG - SQLDIAG - SQL Server Profiler - SQL Server Management Studio 작업모니터 - SQL Server Management Studio 그래픽실행계획 - 저장프로시저 - DBCC 명령 - 기본제공함수 - 추적플래그 분석도구 - ReadTrace - T-SQL 8

9 시스템모니터 용도 - 하드웨어와소프트웨어구성요소들의성능모니터링 - 리소스한계를발견하는데사용 성능로그수집시고려사항 - 문제를확인할수있도록충분한시간동안성능데이터를수집한다 - 가능하다면모든써드파티서비스와불필요한서비스들은중지한다 - 로그파일을저장하는디스크의공간을점검한다 - 로그파일의크기가지나치게커지지않도록크기를제한한다 - 원격으로모니터링하는경우에는, 시스템간의시간동기화가필요하다

10 성능로그수집 Windows 에서제공되는성능로그및경고기능을이용한수집 - Windows ) 시작 > 실행창에서 Perfmon 을입력한후 [ 확인 ] 버튼클릭 2) 왼쪽메뉴에서성능로그및경고 > 카운터로그메뉴를선택 3) 오른쪽창에서마우스오른쪽버튼을클릭하여 [ 새로그설정 ] 을선택 4) 새로그설정이름을입력한후 [ 확인 ] 버튼을클릭 5) [ 일반 ] 탭에서필요한성능카운터추가, 데이터샘플간격설정 6) [ 로그파일 ] 탭에서로그파일종류와파일이름의끝부분형식선택. 7) [ 로그파일 ] 탭의 [ 구성 ] 버튼을클릭하여로그파일이저장될위치선택 8) [ 일정 ] 탭에서수집시간설정 10

11 성능로그수집 Windows 에서제공되는성능로그및경고기능을이용한수집 - Windows ) 시작 > 실행창에서 Perfmon 을입력한후 [ 확인 ] 버튼클릭 2) 데이터수집기집합 > 사용자정의메뉴를선택하여새로만들기를설정 유의사항 1) 새데이터수집기집합이름을입력하고, 생성방법선택한후 [ 다음 ] 버튼클릭 2) 데이터로그만들기를선택하고성능카운터를선택한후 [ 다음 ] 버튼클릭 3) 수집할성능카운터추가한후샘플간격선택한후 [ 다음 ] 버튼클릭 4) 로그파일이저장될위치를선택한후 [ 다음 ] 버튼클릭 5) 실행계정선택후 [ 마침 ] 버튼클릭 - 성능로그결과파일이너무크면, 분석시어려움이있으므로적당한크기의로그파일을생성할수있도록수집기간과데이터샘플간격의조정이필요 11

12 성능로그분석 고려사항 패턴이비정상적으로높거나낮은성능카운터값을점검 어떤카운터값이비정상적이라고판단되는경우 성능모니터로그의데이터와연계하여다른데이터도점검 어떤카운터에문제가보이면, 그와관련될수있는다른카운터들도점검 - 예 : %Processor Time값이높다고해서반드시 CPU 병목은아니다 주기적으로성능로그를분석하여성능의경향을파악 기타 - SQL Statistics\Batch Requests/sec 수치와의비교 12

13 추적 성능이슈가발생하는경우에, 추적을수집하여분석함으로써어떤쿼리가문제유발원인인지확인 유의사항 잘못된추적수집방식으로인하여서비스에영향을주는경우발생 관심대상이벤트만포함 & 문제가좁혀진경우에그에따라추가이벤트포함 추적을수집하여분석함으로써다음과같은사항을확인가능 오래수행되는쿼리들 읽기를많이수행하는쿼리 리소스과다사용쿼리 커서사용 원하지않은재컴파일 (Recompile), Cache Miss 잠금, 교착상태 오류등 추적분석하여성능문제를유발하는쿼리를확인한경우에는추가분석 - 실행계획, 트랜잭션, 잠금등 13

14 추적정보수집 수집도구 - SQL Profiler: 서버에부하를줄수있으므로권장하지않음 - SERVER SIDE PSSDIAG SQLDIAG Microsoft 제품지원이사용을여러가지로그와데이터파일을수집하기위해서비스하는일반용진단의수집도구 다운로드 : 콘솔응용프로그램또는서비스로실행할수있는범용진단정보수집유틸리티 SQL Server 버전별저장위치 SQL Server 2005: ~\Microsoft SQL Server\90\Tools\binn\ SQL Server 2008: ~\Microsoft SQL Server\100\Tools\Binn\ 저장프로시저 (PSSAIG, SQLDIAG 도내부적으로는저장프로시저를사용 ) sp_trace_create sp_trace_generateevent sp_trace_setevent sp_trace_setfilter sp_trace_setstatus 14

15 추적정보수집 - PSSDIAG 종류 : 고객지원을위한 MS 내부버전, 다운로드받을수있는외부버전 대상서버버전 : SQL Server 7.0 및 SQL Server 2000 메인컴포넌트 - DiagConfig.exe: 환경설정용어플리케이션 [ 수집대상인스턴스 / 수집정보선택 ] [ 모드 / 결과저장경로 / 압축여부 / 수집시간 ] - PSSDiag.exe: 실행어플리케이션 15

16 추적정보수집 - PSSDIAG 수집가능서비스 - Windows 성능로그 - SQL Server 추적정보 - SQL Server 차단정보 - Windows 이벤트로그 - SQLDIAG 출력정보 실행권한 - sysadmin, ALTER TRACE ( /G 옵션 ["generic 모드 ] 을사용하지않는경우 ) - Windows 인증, SQL Server 인증모두사용가능 generic 모드 : SQL Server 관련정보수집을비활성화함 16

17 추적정보수집 SQLDIAG 대상서버버전 : SQL Server 2005 이상 구문 sqldiag { [/?] } { [/I configuration_file] [/O output_folder_path] [/P support_folder_path] [/N output_folder_management_option] [/M machine1 [ machine2 [/C file_compression_type] [/B [+]start_time] [/E [+]stop_time] [/A SQLdiag_application_name] [/T { tcp [,port ] np lpc via } ] [/Q] [/G] [/R] [/U] [/L] [/X] } { [START STOP STOP_ABORT] } { [START STOP STOP_ABORT] /A SQLdiag_application_name } 수집가능정보 - Windows 성능로그 - Windows 이벤트로그 - SQL Server 추적정보 - SQL Server 차단정보 - SQL Server 구성정보 17

18 추적정보수집 SQLDIAG 환경설정 : SQLDiag.Xml 환경설정파일 - SQLDiag.exe 를실행하면, 해당폴더에 3 가지의환경설정파일생성 - 환경설정파일종류 SQLDiag.XML: 기본적으로사용하는환경설정파일 SD_General.XML SD_Detailed.XML - SQLDiag.XML 은편집할수없음 - SQLDiag.XML 을편집하여사용하고자할경우에는동일폴더에 SQLDiag.XML 파일을다른이름으로복사한후편집하고, 이편집된파일을 /I 인수로사용 실행방법 - 추적수집을실행할폴더에 SQLDiag.exe 복사 - 필요하다면해당폴더에환경설정파일복사 - 시작 > 실행을선택하여 cmd 입력후, 확인버튼클릭하여프롬프트창실행 - SQLDiag.exe 가있는폴더로이동 - SQLDiag.exe 를원하는옵션으로실행 18

19 추적정보수집 저장프로시저 저장프로시저를이용한수집 - sp_trace_create(transact-sql) - sp_trace_generateevent(transact-sql) - sp_trace_setevent(transact-sql) - sp_trace_setfilter(transact-sql) - sp_trace_setstatus(transact-sql) 19

20 추적정보분석 - READTRACE ReadTrace 도구를이용한분석 1) RML (Replay Markup Language) 도구설치 ( 다운로드 : 2) 설치된폴더로이동하여 ReadTrace.exe가있는지확인 cd C:\Program Files\Microsoft Corporation\RMLUtils\ -- 기본적으로생성되는경로 3) 시작 > 실행을선택하여 cmd 를입력한다음확인버튼을클릭 4) ReadTrace 명령을이용하여분석용 DB( 디폴트 : PerfAnalysis) 를만들고, 수집된추적데이터를 I 옵션을사용하여지정 ReadTrace I C:\temp\myTraces.trc o C:\temp\result\ S.\sql2008 dperfdb1 5) 옵션도움말은 ReadTrace /? 로확인가능 6) 보고서확인 7) Performance Overview 아래의 Unique Batches 를선택하여각각의쿼리를확인 8) Top Unique Batches 탭에서각리소스별로사용량이많은쿼리들을확인하여튜닝대상선정 20

21 추적정보분석 - READTRACE READTRACE를사용하여추적을분석하기위한필요이벤트 - SQL:BatchStarting / SQL:BatchCompleted - RPC:Starting / RPC:Completed READTRACE 분석결과보고서예 :

22 추적정보분석 - READTRACE 버전별사용가능한 SQL Server 버전 ReadTrace 버전 사용가능 SQL Server 버전 SQL Server 2000 SQL Server 2005 SQL Server SQL Server 2000 SQL Server SQL Server 7.0 SQL Server

23 추적정보분석 추적결과파일을테이블에 IMPORT 하여분석 - SQL Server Management Studio 에서추적결과파일을테이블에 IMPORT SELECT * INTO <Trace_TableName> FROM fn_trace_gettable('c:\temp\mytraces.trc',default) - 적절한 T-SQL 을사용하여원하는결과를도출 SELECT * FROM <Trace_TableName> WHERE EVENTID=12 ORDER BY CPU DESC SELECT EventClass, TextData, SPID, Duration, CPU, Reads, Writes FROM <Trace_TableName> WHERE Duration > 1000 OR TextData LIKE % 문자열 % OR EventClass = SP:Recompile OR StartTime BETWEEN :45 AND :15 ORDER BY Duration DESC 23

24 동적관리개체 동적관리뷰및함수 용도 서버인스턴스상태모니터링 문제진단및성능튜닝에사용할수있는서버상태정보반환 동적관리뷰및함수유형 서버범위동적관리뷰및함수 : 서버에대한 VIEW SERVER STATE 권한필요 데이터베이스범위동적관리뷰및함수 : 데이터베이스에대한 VIEW DATABASE STATE 권한필요 24

25 동적관리뷰 평균실행시간상위 5 개쿼리에대한정보 SELECT TOP 5 d.object_id, d.database_id, s.name, s.type_desc, d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time], d.last_elapsed_time, d.execution_count FROM sys.procedures s INNER JOIN sys.dm_exec_procedure_stats d ON s.object_id = d.object_id ORDER BY [avg_elapsed_time] DESC; GO 평균 CPU 시간별상위 5 개쿼리에대한평균 CPU 시간과실행계획 SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], Plan_handle, query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY total_worker_time/execution_count DESC; GO 특정쿼리에대한실행계획조회 -- 쿼리의캐시 /plan_handle 확인 select * from sys.dm_exec_cached_plans as cp cross apply sys.dm_exec_sql_text(cp.plan_handle) as q where q.text like '% 문자열 %' -- plan_handle 을이용해실행계획확인 select * from sys.dm_exec_query_plan(0x b1f511e40e309a70c ) ) 25

26 동적관리뷰 메모리사용정보 SELECT name, SUM(single_pages_kb + multi_pages_kb) FROM sys.dm_os_memory_clerks GROUP BY name; 사례 SELECT type as [type], SUM(single_pages_kb) / 1024 as [single_pages, Mb], SUM(multi_pages_kb) / 1024 as [multi_pages, Mb], SUM(virtual_memory_reserved_kb) / 1024 as [virtual_memory_reserved, Mb], SUM(virtual_memory_committed_kb) / 1024 as [virtual_memory_committed, Mb], SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb], SUM(shared_memory_reserved_kb) / 1024 as [shared_memory_reserved, Mb], SUM(shared_memory_committed_kb) / 1024 as [shared_memory_committed, Mb], SUM(page_size_bytes) / 1024/1024 as [page_size, Mb], GETDATE() FROM sys.dm_os_memory_clerks GROUP BY type; SELECT SUM(single_pages_kb + multi_pages_kb) AS "CurrentSizeOfTokenCache(kb)", GETDATE() as nowdate FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore ; 26

27 차단정보수집 DMV 를통한블로킹발생여부확인 SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 Blocked Process Report 이벤트클래스사용 - SQL Server 프로파일러의 Blocked Process Report 이벤트클래스를사용하여지정된시간을초과하는동안블로킹된작업에대한정보를수집 EXEC sp_configure 'show advanced options', 1 ; GO RECONFIGURE ; GO EXEC sp_configure 'blocked process threshold', 20 ; GO RECONFIGURE ; GO sp_leadblocker 사용 EXEC dbo.sp_leadblocker 27

28 차단정보수집 sp_blocker_pss80.sql 사용 - SSMS 에서수집기간을설정한 sp_blocker_pss80 실행쿼리실행 int = 0 < 5 BEGIN + 1 EXEC dbo.sp_blocker_pss80 WAITFOR DELAY '00:00:05' END - 파일에결과를저장하기위해.sql 생성후, 프롬프트창에서실행 28

29 교착상태정보수집 시작옵션사용 - SQL Server 구성관리자창의 SQL Server 서비스메뉴를선택한후오른쪽창에서해당 SQL Server 인스턴스의오른쪽마우스버튼을클릭하여속성선택 - 속성창에서고급탭을선택한후, 시작매개변수에시작옵션 T1222 또는 T1204 추가 - SQL Server ErrorLog 에기록된교착상태로그확인 프로파일러사용 - Deadlock graph 이벤트클래스를추가하여추적정보수집 29

30 대기정보수집 sys.dm_os_wait_stats 동적관리뷰사용 - 실행중인스레드로인해발생한모든대기에대한정보를반환 - 조회내용 열이름데이터형식설명 wait_type nvarchar(60) 대기유형의이름 waiting_tasks_count bigint 이대기유형의대기수 wait_time_ms bigint 이대기유형의총대기시간 ( 밀리초 ). (signal_wait_time_ms 을포함 ) max_wait_time_ms bigint 이대기유형의최대대기시간 signal_wait_time_ms bigint 대기스레드가신호를받은시간과실행을시작한시간사이의차이 - 쿼리실행중에특정유형의대기시간이쿼리내의병목또는대기지점을나타낼수있으며, 마찬가지로높은대기시간이나서버전체의대기횟수가서버인스턴스내의쿼리상호작용에서병목을나타낼수있음. ( 예 : 잠금대기는쿼리의데이터경합, 페이지 IO 래치대기는느린 IO 응답시간, 페이지래치업데이트대기는잘못된파일레이아웃 ) - 통계가마지막으로다시설정된이후나서버가시작된이후로누적 30

31 대기정보수집 sys.dm_os_wait_stats 동적관리뷰사용 ( 계속 ) - 모든카운터를 0 으로다시설정하는명령어 DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); - 대기유형범위 리소스대기 : 리소스에대한액세스를요청하는경우 잠금, 래치, 네트워크및디스크 I/O 대기포함 큐대기 : 작업자가유휴상태로작업이할당될때까지대기하는경우 외부대기 : SQL Server 작업자가확장저장프로시저호출또는연결된서버쿼리같은외부이벤트가완료될때까지대기하는경우 - 주요대기유형 대기유형 CMEMTHREAD CXPACKET LCK_M_IX MSQL_DQ PAGEIOLATCH_EX SOS_SCHEDULER_YIELD WRITELOG 설명 태스크가스레드로부터안전한메모리개체를기다리는경우 쿼리프로세서교환반복기를동기화하려고하는경우 태스크가의도배타 (IX) 잠금을획득하려고대기하는경우 분산쿼리작업이완료될때까지태스크가대기하는경우 태스크가 I/O 요청에있는버퍼를래치에서기다리는경우 다른작업이실행될수있도록태스크가자발적으로스케줄러를양보하는경우 로그플러시가완료될때까지대기하는동안발생로그플러시를발생시키는일반적인작업은검사점과트랜잭션커밋 31

32 인덱스조각화정보수집 sys.dm_db_index_physical_stats 동적관리함수 - DBCC SHOWCONTIG 보다성능좋음 : DBCC SHOWCONTIG: 해당인덱스가포함된테이블에공유잠금 (S) 을설정 sys.dm_db_index_physical_stats: 의도공유잠금 (IS) 만설정 - 사용예 SELECT * FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks'),OBJECT_ID('HumanResources.Employee'),NULL /*to view all indexes otherwise, input index number */,NULL /* NULL to view all partitions of an index */,'DETAILED' /* all information */ ) - 주요반환결과 열 avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages 설명논리적조각화 ( 인덱스에서순서가잘못된페이지 ) 비율인덱스의조각 ( 물리적으로연속되는리프페이지 ) 수인덱스한조각의평균페이지수 - 조각화수준에따른수정방법결정 avg_fragmentation_in_percent 값 수정문 > 5% 및 < = 30% ALTER INDEX REORGANIZE > 30% ALTER INDEX REBUILD WITH (ONLINE = ON)* 32

33 인덱스조각화정보수집 DBCC SHOWCONTIG - AdventureWorks 데이터베이스의 Product 테이블에대해생략된결과집합반환예 USE AdventureWorks; GO DBCC SHOWCONTIG ("Production.Product", 1) WITH FAST; GO - AdventureWorks 데이터베이스의모든테이블의모든인덱스에대한전체테이블결과집합을반환예 USE AdventureWorks; GO DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES; GO - 주요반환결과 Avg. Bytes free per page: 검색된페이지에서사용가능한평균바이트수 Avg. Page density (full): 평균페이지밀도 ( 백분율 ) - 제한사항 ntext, text 및 image 데이터형식에데이터를표시하지않음 분할된테이블이나인덱스의첫번째파티션만표시 행오버플로저장소정보와 nvarchar(max), varchar(max), varbinary(max) 및 xml 과같은새로운행외부데이터형식을표시하지않음 공간인덱스지원하지않음 33

34 디스크정보 디스크구성및여유공간정보 EXEC master..xp_fixeddrives - 결과예 34

35 데이터베이스파일정보 파일구성및크기 USE databasename; GO EXEC sp_helpfile; GO - 현재데이터베이스와연결된파일의물리적이름과파일이속한파일그룹, 파일이확장할수있는최대크기, 파일의증가분, 데이터파일및로그파일여부의결과반환 데이터및로그파일에대한 I/O 통계조회 SELECT * FROM sys.dm_io_virtual_file_stats ( { database_id NULL }, { file_id NULL }); GO - Database_id 가아닌 NULL 을설정할경우, 해당인스턴스의모든데이터베이스반환 - File_id 가아닌 NULL 을설정할경우, 해당데이터베이스의모든파일반환 - 주요반환정보 열 num_of_reads 설명 파일에대해읽기가실행된횟수 num_of_bytes_read 파일에대해실행된읽기의총바이트수 num_of_writes 파일에대해쓰기가실행된횟수 num_of_bytes_written 파일에대해실행된쓰기의총바이트수 io_stall 사용자가파일에대한 I/O가완료될때까지대기한총시간 ( 밀리초 ) 35

36 SQL Server 시스템저장프로시저및함수 sp_who / sp_who2 - 현재실행중인문을포함한현재 SQL Server 사용자와프로세스에대한스냅숏정보및차단여부 sp_lock - 개체 ID, 인덱스 ID, 잠금유형, 잠금이적용되는유형이나리소스에대한스냅숏정보 sp_spaceused - 테이블이나전체데이터베이스가사용중인현재예상디스크공간의양 sp_monitor - 마지막으로실행된이후의 CPU 사용량, I/O 사용량및유휴시간양을포함하는통계를표시 시스템테이블 - sysprocesses - syslockinfo - syscacheobjects 등 36

37 SQL Server 작업모니터 SSMS 의개체탐색기에서해당하는인스턴스의오른쪽마우스버튼에서작업모니터선택 다음정보를그래픽으로표시 - SQL Server 인스턴스에서실행중인프로세스 - 차단된프로세스 - 잠금 - 사용자작업 37

38 요약 모니터링목적에적합한전략수립이필요하다 모니터링목적에부합하는성능도구의활용이필요하다 SQL Server 버전별로적절한모니터링도구와수집방식을사용하라 SQL Server 환경의변화가발생하는경우, 변화전후의모니터링이필요하다 추이모니터링이필요하다 모니터링이서비스또는서버에미칠수있는영향도를사전에충분히고려한다 시스템의성능에관한 BaseLine을수립하라 병목발생시병목관계를점검하라 모니터링결과에대한연계분석이필요하다. 성급한판단은금물!!!!! 38

39 참고 URL Tools for Performance Monitoring and Tuning: SQL Server 블로킹 : Monitoring Server Performance and Activity: Troubleshooting Performance Problems in SQL Server 성능모니터링및튜닝개요 : How to customize the quota for the TokenAndPermUserStore cache store in SQL Server 2005 Service Pack 3: sys.dm_os_wait_stats(transact-sql) 39

40 감사합니다 40