슬라이드 1

Size: px

Start display at page:

Download "슬라이드 1"

현아 양
5 years ago
Views:

2 { Query Optimizing } 김정선 DB 사업부수석컨설턴트필라넷 (Feel@NET) Microsoft SQL Server MVP

3 김정선 (Jungsun Kim) Blog: ( 현재소속 ) 필라넷, DB 사업부수석컨설턴트 SQL Server Academy/ 트라이콤교육센터, 강사 ( 주요업무 ) SQL Server 컨설팅, 교육, 기술지원등 SQL Server 쿼리튜닝및최적화 교육과정개발및운영 ( 주요저서 ) SQL Server 2000 쿼리튜닝및최적화전문가 ( 교재 ) SQL Server 2005 성능문제해결 (MS/TechNet) Inside SQL Server 2005 Programming(MS/ 정보문화사 ) SQL Server 포켓컨설턴트 (MS/ 정보문화사 ) SQL Server New Features(MS) ( 주요활동 ) Microsoft SQL Server MVP/MCT Microsoft Technet Honor SQL Server Specialist Member

4 知彼知己百戰百勝 SQL Server 2005 향상된 Query Optimizing 능력이해 SQL Server 2008 의새로운 Optimizing 기능살펴보기 SQL Server 2005/2008 로의업그레이드이득간접체험쿼리작성및튜닝시활용

5 서론누가, 무엇을, 어떻게최적화하는가? 본론 SQL Server 2005 향상된 Query Optimizing 능력이해변화에따른부작용 SQL Server 2008, 새로운 Optimizing 기술결론知彼知己百戰百勝

6 Query Optimizer 사용자 성능향상 악성비악성 DB 튜닝 서버튜닝 쿼리튜닝 쿼리 비율 성능 비율 성능향상

7 구현 물리연산자최적화 향상된 SQL Server 2005 엔진 탐색 Cost-Based Optimizer 조인기준및순서조정 조인전 Group By 선행처리 단순화 구문단순화 Constant Folding Filter 연산자우선처리 Group By 칼럼감소

8 SELECT OrderID, OrderDate, CustomerID FROM dbo.orders WHERE OrderID <>

9 SELECT * FROM dbo.orders WHERE OrderID =

10 SELECT Pub_ID, Pub_Name FROM Pubs.dbo.Publishers WHERE Pub_ID = N CONVERT(pub_id)=

11 SELECT EmployeeID, ShipVia FROM dbo.orders WHERE EmployeeID = ShipVia AND EmployeeID =

12 SELECT * FROM dbo.orders WHERE OrderDate > DATEADD(minute, -60, GETDATE())

13 SELECT * FROM dbo.orders oh INNER JOIN dbo.[order Details] As od ON oh.orderid = od.orderid WHERE od.orderid < Orders OrderDetails

14 IF (SELECT COUNT(*) FROM dbo.orders WHERE EmployeeID = 5) > 예상행수 예상행수 : 42

15 UPDATE Product SET Name = Name 어떤파생효과가있을까? 2005 Table Update 2000 Index Update Table Update

16 SELECT CustomerID, (SELECT ContactName FROM dbo.customers AS a WHERE a.customerid = b.customerid + ) FROM dbo.orders AS b WHERE b.orderid = 예상행수 예상행수 : 29

17 SELECT Num,(SELECT Num FROM Num n3 WHERE n3.num = n1.num + 1) FROM Num n1 WHERE n1.num < 1000 AND NOT EXISTS (SELECT * FROM Num n2 WHERE n2.num = n1.num 1) 예상행수 1 예상행수 : 10,540

18 SELECT * FROM dbo.customers AS c WHERE NOT EXISTS (SELECT TOP 1 CustomerID FROM dbo.orders AS o WHERE o.customerid = c.customerid) 2005

19 SELECT * FROM dbo.orders WHERE OrderID > AND OrderID > SEEK: (OrderID > 11070) 2005 SEEK: (OrderID > 10248)

20 SELECT * FROM dbo.customers AS c WHERE NOT EXISTS (SELECT TOP 1 CustomerID FROM dbo.orders AS o WHERE o.customerid = c.customerid)

22 새로운최적화지원기술 FORCESEEK 힌트 Query 힌트에서 Table 힌트사용 Partitioning 향상 Plan Guides 향상기타향상및추가기능정리

23 SELECT * FROM dbo.orders WITH(FORCESEEK) WHERE OrderID <= ( ) 2008 SELECT * FROM dbo.orders WHERE OrderID <= ( ) 2005

24 SELECT * FROM dbo.orders WHERE OrderDate <= OPTION (TABLE HINT(dbo.Orders, INDEX(OrderDate)))

26 Plan Cache 2 쿼리실행계획을변경하고싶다면? Client (blackbox) Hash Join Nested Loop Nested Loop DB Engine 1 xml showplan hint

Partitioning 향상 Skip Scan 동작지원 Partition 단위로 2 가지병렬처리방식지원 Partition 단위의 Lock Escalation 동작지원 Indexed View 를포함한 Partition 동작지원 Data Warehouse (Start 스키마쿼리 ) Bitmap

27 Partitioning 향상 Skip Scan 동작지원 Partition 단위로 2 가지병렬처리방식지원 Partition 단위의 Lock Escalation 동작지원 Indexed View 를포함한 Partition 동작지원 Data Warehouse (Start 스키마쿼리 ) Bitmap filtering 최적화 Full-Text Search 기능향상새로운기능 Filtered Index and Statistics Merge, Row Constructor, Table Valued Parameter New Data Types, Grouping Sets, HierarchyID, 등더욱풍부해진실행계획출력

28 SQL Server 2005/2008 로업그레이드새로운 Optimizing 능력은자동으로흡수이득을최대할수있도록변화된내용을이해악성쿼리추적후튜닝부작용또한고려, 적절하게조정 좋은쿼리작성을위한기본규칙따를것 2005/2008 의새로운기능들을적절히활용

29 필라넷

2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

30 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Microsoft PowerPoint - 1_3_DBA_SQL_tuning

Microsoft PowerPoint - 1_3_DBA_SQL_tuning 아주특별한 SQL 튜닝 씨퀄로 김정선 아주특별한 Level 400 100 개념및소개수준 200 중간수준 300 고급수준 400 전문가수준 선수지식불필요 100 에더하여기술적세부사항설명 200 에더하여능숙한사용경험, 아키텍처지식필요 SINCE 2002 SQL Server Specialist Member & Leading PASS Korea Member Microsoft