{ 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/ 트라이콤교육센터, 강사 ( 주요업무 ) SQL Server 컨설팅, 교육, 기술지원등 SQL Server 쿼리튜닝및최적화 교육과정개발및운영 ( 주요저서 ) SQL Server 2000 쿼리튜닝및최적화전문가 ( 교재 ) SQL Server 2005 성능문제해결 (MS/TechNet) Inside SQL Server 2005 Programming(MS/ 정보문화사 ) SQL Server 20005 포켓컨설턴트 (MS/ 정보문화사 ) SQL Server 20005 New Features(MS) ( 주요활동 ) Microsoft SQL Server MVP/MCT Microsoft Technet Honor SQL Server Specialist Member
知彼知己百戰百勝 SQL Server 2005 향상된 Query Optimizing 능력이해 SQL Server 2008 의새로운 Optimizing 기능살펴보기 SQL Server 2005/2008 로의업그레이드이득간접체험쿼리작성및튜닝시활용
서론누가, 무엇을, 어떻게최적화하는가? 본론 SQL Server 2005 향상된 Query Optimizing 능력이해변화에따른부작용 SQL Server 2008, 새로운 Optimizing 기술결론知彼知己百戰百勝
Query Optimizer 사용자 성능향상 100 50 0 악성비악성 DB 튜닝 서버튜닝 쿼리튜닝 쿼리 비율 성능 비율 성능향상
구현 물리연산자최적화 향상된 SQL Server 2005 엔진 탐색 Cost-Based Optimizer 조인기준및순서조정 조인전 Group By 선행처리 단순화 구문단순화 Constant Folding Filter 연산자우선처리 Group By 칼럼감소
SELECT OrderID, OrderDate, CustomerID FROM dbo.orders WHERE OrderID <> 10250 2005 2000
SELECT * FROM dbo.orders WHERE OrderID = 10248.0 2005 2000
SELECT Pub_ID, Pub_Name FROM Pubs.dbo.Publishers WHERE Pub_ID = N 0736 -- CONVERT(pub_id)= 0736 2005 2000
SELECT EmployeeID, ShipVia FROM dbo.orders WHERE EmployeeID = ShipVia AND EmployeeID = 2 2005 2000
SELECT * FROM dbo.orders WHERE OrderDate > DATEADD(minute, -60, GETDATE()) 2005 2000
SELECT * FROM dbo.orders oh INNER JOIN dbo.[order Details] As od ON oh.orderid = od.orderid WHERE od.orderid < 10250 Orders 2005 2000 OrderDetails
IF (SELECT COUNT(*) FROM dbo.orders WHERE EmployeeID = 5) > 0... 2005 예상행수 1 2000 예상행수 : 42
UPDATE Product SET Name = Name 어떤파생효과가있을까? 2005 Table Update 2000 Index Update Table Update
SELECT CustomerID, (SELECT ContactName FROM dbo.customers AS a WHERE a.customerid = b.customerid + ) FROM dbo.orders AS b WHERE b.orderid = 10248 2005 예상행수 1 2000 예상행수 : 29
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) 2005 2000 예상행수 1 예상행수 : 10,540
SELECT * FROM dbo.customers AS c WHERE NOT EXISTS (SELECT TOP 1 CustomerID FROM dbo.orders AS o WHERE o.customerid = c.customerid) 2005
SELECT * FROM dbo.orders WHERE OrderID > 10248 AND OrderID > 11070 2000 SEEK: (OrderID > 11070) 2005 SEEK: (OrderID > 10248)
SELECT * FROM dbo.customers AS c WHERE NOT EXISTS (SELECT TOP 1 CustomerID FROM dbo.orders AS o WHERE o.customerid = c.customerid) 2000 2005
새로운최적화지원기술 FORCESEEK 힌트 Query 힌트에서 Table 힌트사용 Partitioning 향상 Plan Guides 향상기타향상및추가기능정리
SELECT * FROM dbo.orders WITH(FORCESEEK) WHERE OrderID <= (10247 + 10) 2008 SELECT * FROM dbo.orders WHERE OrderID <= (10247 + 10) 2005
SELECT * FROM dbo.orders WHERE OrderDate <= 19970101 OPTION (TABLE HINT(dbo.Orders, INDEX(OrderDate)))
2008 2005
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 filtering 최적화 Full-Text Search 기능향상새로운기능 Filtered Index and Statistics Merge, Row Constructor, Table Valued Parameter New Data Types, Grouping Sets, HierarchyID, 등더욱풍부해진실행계획출력
SQL Server 2005/2008 로업그레이드새로운 Optimizing 능력은자동으로흡수이득을최대할수있도록변화된내용을이해악성쿼리추적후튜닝부작용또한고려, 적절하게조정 좋은쿼리작성을위한기본규칙따를것 2005/2008 의새로운기능들을적절히활용
필라넷
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.