Microsoft PowerPoint - 1_3_DBA_SQL_tuning

Size: px

Start display at page:

Download "Microsoft PowerPoint - 1_3_DBA_SQL_tuning"

그린 연
5 years ago
Views:

1 아주특별한 SQL 튜닝 씨퀄로 김정선

2 아주특별한

3 Level 개념및소개수준 200 중간수준 300 고급수준 400 전문가수준 선수지식불필요 100 에더하여기술적세부사항설명 200 에더하여능숙한사용경험, 아키텍처지식필요

4 SINCE 2002

5 SQL Server Specialist Member & Leading PASS Korea Member Microsoft TechNet Global Q/A

6 Blog Twitter Jungsun_Kim

8 뉴호라이즌교육센터

9 왕눈이 BlockSniffer SQLRoadPerfmon

10 단, 현장 기술 지원 요청 시 별도 비용이 청구됩니다.

11 새로운시각 선입견탈피 Level 400

12 SELECT ContactID, FirstName, LastName, ModifiedDate FROM AdventureWorks.Person.Contact WHERE FirstName = 'Ramesh'

14 정렬 (Sort Warnings) 병렬쿼리 (Parallel Query)

16 CREATE TABLE dbo.supplier ( S_SUPPKEY int UNIQUE, S_NATIONKEY int, S_COMMNET char(2000) )

17 1 2 SELECT S_SUPPKEY, S_NATIONKEY, S_COMMENT FROM dbo.supplier WHERE S_SUPPKEY < 3057 ORDER BY S_NATIONKEY SELECT S_SUPPKEY, S_NATIONKEY, S_COMMENT FROM dbo.supplier WHERE S_SUPPKEY < 3058 ORDER BY S_NATIONKEY

18 1 2 SELECT S_SUPPKEY, S_NATIONKEY, S_COMMNET FROM dbo.supplier WHERE S_SUPPKEY < 3057 ORDER BY S_NATIONKEY 298ms SELECT S_SUPPKEY, S_NATIONKEY, S_COMMNET FROM dbo.supplier WHERE S_SUPPKEY < 3058 ORDER BY S_NATIONKEY 484ms

19 50% 예상하위트리비용 예상행수 % 예상하위트리비용 예상행수

20 1 2 SELECT S_SUPPKEY, S_NATIONKEY, S_COMMNET FROM dbo.supplier WHERE S_SUPPKEY < 3057 ORDER BY S_NATIONKEY 테이블 SUPPLIER. 검색수1, 논리적읽기수 769, SELECT S_SUPPKEY, S_NATIONKEY, S_COMMNET FROM dbo.supplier WHERE S_SUPPKEY < 3058 ORDER BY S_NATIONKEY worktable 없음 테이블 SUPPLIER. 검색수1, 논리적읽기수 769,

21 1 Duration CPU Reads Writes Duration CPU Reads Writes ,352 0

22 SELECT session_id, status, wait_type, wait_time FROM sys.dm_exec_requests WHERE session_id = 54 session_id Status wait_type wait_time 54 suspended IO_COMPLETION 23

23 SELECT granted_memory_kb, used_memory_kb, max_used_memory_kb FROM sys.dm_exec_query_memory_grants WHERE session_id = 54 granted_memory_kb used_memory_kb max_used_memory_kb

24 SELECT * FROM sys.dm_io_virtual_file_stats(db_id('tempdb'), 1) SELECT WHERE S_SUPPKEY < 3057 SELECT * FROM sys.dm_io_virtual_file_stats(db_id('tempdb'), 1) SELECT WHERE S_SUPPKEY < 3058 SELECT * FROM sys.dm_io_virtual_file_stats(db_id('tempdb'), 1) db_id num_of_reads num_of_bytes_read 1 2 num_of_writes num_of_bytes_written ,138, , ,138, , ,405, ,283,264

25 메모리에적합하지않은정렬작업을나타냅니다. SELECT 문에사용된 ORDER BY 절등쿼리내의정렬작업만포함됩니다, by Profiler

26 행수 or 열크기 예측및판단오류 = 메모리할당오차

27 1 2 SELECT TOP 100 S_SUPPKEY, S_NATIONKEY, S_COMMENT FROM dbo.supplier WHERE S_SUPPKEY < ORDER BY S_NATIONKEY 93ms SELECT TOP 101 S_SUPPKEY, S_NATIONKEY, S_COMMENT FROM dbo.supplier WHERE S_SUPPKEY < ORDER BY S_NATIONKEY 939ms

28 32 core? data file 개수 동일한파일크기 for what? = = CPU core 수 tempdb 과다동시할당병목

29 for what? = have you? =?????? tempdb 과다동시할당병목 Sort Warnings - 파일이많을수록손해? by Ramesh Meyyappan

30 해결 (solving)

31 1) 그쿼리쓰지마? 2) ORDER BY 절없애? 3) 열타입및길이변경 4) 인덱스튜닝 5) 쿼리튜닝

32 2 SELECT S_SUPPKEY, S_NATIONKEY, CONVERT( varchar(5000), S_COMMENT) FROM dbo.supplier WHERE S_SUPPKEY < 3058 ORDER BY S_NATIONKEY 정렬대상집합크기조정

33 CREATE PROC dbo.up_reuse datetime ) AS SELECT FROM Sales.SalesOrderDetail AS d INNER JOIN Sales.SalesOrderHeader AS o ON d.salesorderid = o.salesorderid WHERE d.unitprice > 0 AND o.orderdate GROUP BY o.duedate, o.shipdate ORDER BY o.shipdate DESC EXEC dbo.up_reuse EXEC dbo.up_reuse

34 Sort Warnings vs. 병렬쿼리

35 Hash Warning 해시연산중해시재귀또는해시중단 ( 해시재귀한도초과 ) 이일어난경우발생합니다, by Profiler

37 Please tell me What happened? How do I do?

38 병렬쿼리 : 5,328ms 직렬쿼리 : 1,088ms

39 A사 ) 누적대기분석 대기유형 평균대기시간 (ms) 요청횟수 CXPACKET ,501 B사 ) 상세대기분석 - 20 분간 대기유형 평균대기시간 (ms) 요청횟수 CXPACKET 3,

40 쿼리프로세서 Exchange 연산자동기화시발생. 이대기유형에대한경합이문제가되면, 병렬처리수준을낮출것을고려할수있다. by 온라인설명서?

41 No 시각 SPID 대기시간 (ms) 대기유형대기리소스 ,563 CXPACKET LATCH_EX PARALLEL ,360 LATCH_EX PARALLEL ,016 LATCH_EX PARALLEL ,032 LATCH_EX PARALLEL PAGEIOLATCH_SH 15:1: ,032 LATCH_EX PARALLEL

42 EXEC sp_configure 'max degree of parallelism', 4 RECONFIGURE SELECT * FROM Sales.SalesOrderHeader OPTION (MAXDOP 8)

43 CPU n Thread n Scheduler n

44 평균 worker수

45 Sort 실제행수 8357 스레드 0 0 스레드 스레드 스레드 스레드 Clustered Index Scan 실제행수 스레드 0 0 스레드 스레드 스레드 스레드 9 506

46 NUMA / 2 Nodes / 8 x 4 Core

49 Ordinarily a well tuned OLTP application would not parallelize unless an index is missing, there is an incomplete WHERE clause, or the query is not a true OLTP transaction. from SQL Customer Advisory Team

50 해결 (solving)

51 1) 기본적으로쿼리튜닝 불필요한병렬처리제거 효율적인병렬처리지원 2) CXPACKET 대기가문제인경우 MAXDOP이너무많다면옵션조정고려 필요시개별쿼리별 MAXDOP 힌트적용

52 50

54 총 worker Thread 수

55 기본은디폴트설정사용 필요시 MAXDOP 조정고려 운영시스템에대한충분한이해가선결 철저한검증과관리가중요

56 OLAP 성쿼리에서 Sort 연산주의 OLTP 성쿼리에서 Parallel 연산주의

57 감사합니다.

슬라이드 1

슬라이드 1 { Query Optimizing } 김정선 DB 사업부수석컨설턴트필라넷 (Feel@NET) Microsoft SQL Server MVP 김정선 (Jungsun Kim) Email: jskim@feelanet.com Blog: http://blog.naver.com/visualdb ( 현재소속 ) 필라넷, DB 사업부수석컨설턴트 SQL Server Academy/