1 / 26

SQL Server 2005 Performance Enhancements for Large Queries

SQL Server 2005 Performance Enhancements for Large Queries. Joe Chang jchang6@yahoo.com www.sql-server-performance.com/joe_chang.asp. When & Why Migrate to 2005. Performance gains in large queries In memory Disk performance Data Warehouse Applications Easiest to migrate

theola
Download Presentation

SQL Server 2005 Performance Enhancements for Large Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2005 Performance Enhancements for Large Queries Joe Chang jchang6@yahoo.com www.sql-server-performance.com/joe_chang.asp

  2. When & Why Migrate to 2005 • Performance gains in large queries • In memory • Disk performance • Data Warehouse Applications • Easiest to migrate • Usually internal users • Read-only queries

  3. TPC-H benchmark • Decision Support • Large volumes of data • Complex queries – 22 queries, 2 data refresh • Power & Throughput metrics • Power – run 1 queries at a time • Throughput – run multiple concurrent streams

  4. System Details • 2003 Oct: SQL Server 2000 EE build 782 • 16 Itanium 2 1.5GHz/6M, 64GB, 215 disks • 2004 Aug: Oracle 10g EE • 16 Itanium 2 1.5GHz/6M, 64GB, 166 disks • Unisys ES7000 Aries 420 • 2005 Jun: SQL 2005 HP Integrity rx8620 • 16 Itanium 2 1.6GHz/9M, 64GB, 342 disks • 2005 Jul: Oracle 10g R2 - 236 disks OS: Windows Server 2003 Datacenter, SP1 for last

  5. TPC-H 1000GB Results *1.6GHz/9M processor, others: 1.5GHz/6M

  6. TPC-H 1000GB - Power 10X Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22 SQL 2000 2530.8 60.2 873.2 807.4 1058.7 70.8 838.2 822.8 5085.8 655.4 224.5 666.0 728.7 149.4 110.9 269.1 181.3 3214.4 283.2 125.7 3489.6 156.3 Oracle 10g 1068 117.5 79.2 84 334.4 67 420.7 242.3 1268 189.6 172.8 182.8 1251 48.5 122.9 227.9 372.7 2146 460.1 156.3 1908 189.2 SQL 2005 987.3 26.2 59.2 76.4 235.7 49 236.9 163.9 1084 104.7 112.4 292 405.7 74.1 42.1 210.4 90.5 1456 193 76.4 1686 182.6

  7. TPC-H 1000GB - Power

  8. TPC-H 1000GB - Throughput Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22 SQL 2000 7111 523.7 4892 3816 5009 185.3 5545 20526 24341 2241 1464 3677 5320 1108 884.6 3057 5198 15299 7042 4029 15943 643.4 Oracle 10g 4394 1294 501 366.5 1909 252.5 2594 2496 5748 1137 866.3 1315 3857 262.3 639.5 770.6 1733 21580 1140 1236 9459 791.5 SQL 2005 5460 197.1 1486 1640 2447 161.4 1861 2311 6464 1857 1006 1555 2256 580.4 253.6 821 1630 4126 1037 2376 5320 644.1

  9. TPC-H 1000GB - Throughput

  10. TPC-H Query 1 SQL 2000 to 2005 2.56X Power 1.30X Throughput SELECT L_RETURNFLAG ,L_LINESTATUS ,SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE , SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY ,AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC,COUNT(*) AS COUNT_ORDER FROM LINEITEM WHERE L_SHIPDATE <= DATEADD(dd, -90, cast('1998/12/01' as smalldatetime)) GROUP BY L_RETURNFLAG ,L_LINESTATUS ORDER BY L_RETURNFLAG ,L_LINESTATUS

  11. TPC-H Query 3 SQL 2000 to 2005 14.7X Power 3.3X Throughput SELECT TOP 10 L_ORDERKEY, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE, O_ORDERDATE ,O_SHIPPRIORITY FROM CUSTOMER INNERJOIN ORDERS ON C_CUSTKEY = O_CUSTKEY INNERJOIN LINEITEM ON L_ORDERKEY = O_ORDERKEY WHERE C_MKTSEGMENT = 'BUILDING' AND O_ORDERDATE < '1995-03-15' AND L_SHIPDATE > '1995-03-15' GROUP BY L_ORDERKEY ,O_ORDERDATE ,O_SHIPPRIORITY ORDER BY REVENUE DESC ,O_ORDERDATE

  12. Query 8 SQL 2000 to 2005 5.0X Power 8.9X Throughput

  13. 10GB Line Item in memory test SQL Server 2000 SP4 Query time in milli-seconds versus Max Degree of Parallelism Limited parallelism gains beyond 4-8P SQL Server 2005 June CTP Continued parallelism gains beyond 4P

  14. 10GB Line Item in memory test 1 SQL Server 2000 SP4 Query time in milli-seconds versus Max Degree of Parallelism Limited parallelism gains beyond 4-8P SQL Server 2005 June CTP Continued parallelism gains beyond 4P

  15. 10GB Line Item in memory test 2 SQL Server 2000 SP4 Query time in milli-seconds versus Max Degree of Parallelism Limited parallelism gains beyond 4-8P SQL Server 2005 June CTP Continued parallelism gains beyond 4P

  16. 10GB in memory test Max Degree of Parallelism 1 30% reduction in total time at 1P Max DOP 4 Max DOP 16 50% reduction in total time at 16P

  17. In-Memory Table Scan Xeon systems have better table scan performance on SQL 2000 SQL 2000 default table scan performance depends on build

  18. Table Scan to Disk SQL 2005 Table Scan performance probably limited by disk system

  19. Loop Join – SQL 2000 & 2005 SQL 2000 No scaling from 1-2P SQL 2005 Better overall performance, scaling from 1-8P

  20. Hash Join SQL 2000 Unpredictable behavior below 3M rows Limited scaling SQL 2005 Consistent behavior < 1M rows Not as much fall off in large joins

  21. Merge Join – SQL 2000 SQL 2000 parallel merge joins has serious problems, runs slower, consumes more CPU. It should be possible to performance a parallel merge efficiently? SQL 2005 does not use parallel merge joins? Slight decrease in non-parallel performance.

  22. LiteSpeed Backup Performance HP rx8620, 16 x 1.5GHz Itanium 2 processors 2 EVA 5000 (4 HSV110 controllers, 8 enclosures x14 disks each 8 x 2Gbit/sec FC ports

  23. SQL 2005 Performance Summary • Significant improvements in large queries • Data Warehouse Applications • Improved disk performance

  24. More Information www.sql-server-performance.com/joe_chang.asp SQL Server Quantitative Performance Analysis Server System Architecture Processor Performance Direct Connect Gigabit Networking Parallel Execution Plans Large Data Operations Transferring Statistics SQL Server Backup Performance with LiteSpeed jchang6@yahoo.com

  25. System Configuration

  26. Bookmark Lookup Performance SQL 2005 has better bookmark lookup performance to Clustered Indexes SQL 2000 has better bookmark lookup performance to Heap organized tables

More Related