아주특별한 SQL 튜닝 씨퀄로 김정선
아주특별한
Level 400 100 개념및소개수준 200 중간수준 300 고급수준 400 전문가수준 선수지식불필요 100 에더하여기술적세부사항설명 200 에더하여능숙한사용경험, 아키텍처지식필요
SINCE 2002
SQL Server Specialist Member & Leading PASS Korea Member Microsoft TechNet Global Q/A
Blog http://blog.naver.com/visualdb Twitter Jungsun_Kim
뉴호라이즌교육센터
왕눈이 BlockSniffer SQLRoadPerfmon
단, 현장 기술 지원 요청 시 별도 비용이 청구됩니다.
새로운시각 선입견탈피 Level 400
SELECT ContactID, FirstName, LastName, ModifiedDate FROM AdventureWorks.Person.Contact WHERE FirstName = 'Ramesh'
정렬 (Sort Warnings) 병렬쿼리 (Parallel Query)
CREATE TABLE dbo.supplier ( S_SUPPKEY int UNIQUE, S_NATIONKEY int, S_COMMNET char(2000) )
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
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
50% 예상하위트리비용 0.639147 예상행수 3056.97 50% 예상하위트리비용 0.639169 예상행수 3057.97
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,
1 Duration CPU Reads Writes 298 21 769 0 2 Duration CPU Reads Writes 484 31 1,352 0
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
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 8192 8192 8192
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 2 20 1,138,688 2 16,384 1 2 20 1,138,688 2 16,384 2 2 117 7,405,568 98 6,283,264
메모리에적합하지않은정렬작업을나타냅니다. SELECT 문에사용된 ORDER BY 절등쿼리내의정렬작업만포함됩니다, by Profiler
행수 or 열크기 예측및판단오류 = 메모리할당오차
1 2 SELECT TOP 100 S_SUPPKEY, S_NATIONKEY, S_COMMENT FROM dbo.supplier WHERE S_SUPPKEY < 20000 ORDER BY S_NATIONKEY 93ms SELECT TOP 101 S_SUPPKEY, S_NATIONKEY, S_COMMENT FROM dbo.supplier WHERE S_SUPPKEY < 20000 ORDER BY S_NATIONKEY 939ms
32 core? data file 개수 동일한파일크기 for what? = = CPU core 수 tempdb 과다동시할당병목
for what? = have you? =?????? tempdb 과다동시할당병목 Sort Warnings - 파일이많을수록손해? by Ramesh Meyyappan
해결 (solving)
1) 그쿼리쓰지마? 2) ORDER BY 절없애? 3) 열타입및길이변경 4) 인덱스튜닝 5) 쿼리튜닝
2 SELECT S_SUPPKEY, S_NATIONKEY, CONVERT( varchar(5000), S_COMMENT) FROM dbo.supplier WHERE S_SUPPKEY < 3058 ORDER BY S_NATIONKEY 정렬대상집합크기조정
CREATE PROC dbo.up_reuse ( @date 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 >= @date GROUP BY o.duedate, o.shipdate ORDER BY o.shipdate DESC EXEC dbo.up_reuse 2004-01 01-01 01 EXEC dbo.up_reuse 2001-01-01
Sort Warnings vs. 병렬쿼리
Hash Warning 해시연산중해시재귀또는해시중단 ( 해시재귀한도초과 ) 이일어난경우발생합니다, by Profiler
Please tell me What happened? How do I do?
병렬쿼리 : 5,328ms 직렬쿼리 : 1,088ms
A사 ) 누적대기분석 대기유형 평균대기시간 (ms) 요청횟수 CXPACKET 421 234,501 B사 ) 상세대기분석 - 20 분간 대기유형 평균대기시간 (ms) 요청횟수 CXPACKET 3,154 702
쿼리프로세서 Exchange 연산자동기화시발생. 이대기유형에대한경합이문제가되면, 병렬처리수준을낮출것을고려할수있다. by 온라인설명서?
No 시각 SPID 대기시간 (ms) 대기유형대기리소스 1 2010-04-05 309 5,563 CXPACKET 2 2010-04-05 309 875 LATCH_EX PARALLEL 3 2010-04-05 309 1,360 LATCH_EX PARALLEL 4 2010-04-05 309 5,016 LATCH_EX PARALLEL 5 2010-04-05 309 2,032 LATCH_EX PARALLEL 6 2010-04-05 309 109 PAGEIOLATCH_SH 15:1:..... 16 2010-04-05 309 2,032 LATCH_EX PARALLEL
EXEC sp_configure 'max degree of parallelism', 4 RECONFIGURE SELECT * FROM Sales.SalesOrderHeader OPTION (MAXDOP 8)
CPU n Thread n Scheduler n
평균 worker수 33 65 33 33 97 33 33 65
Sort 실제행수 8357 스레드 0 0 스레드 1 270 스레드 10 270 스레드 32 270 스레드 9 270 Clustered Index Scan 실제행수 19048 스레드 0 0 스레드 1 1038 스레드 10 1144 스레드 32 378 스레드 9 506
NUMA / 2 Nodes / 8 x 4 Core
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
해결 (solving)
1) 기본적으로쿼리튜닝 불필요한병렬처리제거 효율적인병렬처리지원 2) CXPACKET 대기가문제인경우 MAXDOP이너무많다면옵션조정고려 필요시개별쿼리별 MAXDOP 힌트적용
50
총 worker Thread 수
기본은디폴트설정사용 필요시 MAXDOP 조정고려 운영시스템에대한충분한이해가선결 철저한검증과관리가중요
OLAP 성쿼리에서 Sort 연산주의 OLTP 성쿼리에서 Parallel 연산주의
감사합니다.