SQL%20Server%202005%20Performance%20Enhancements%20for%20Large%20Queries

1
SQL Server 2005 Performance Enhancements for
Large Queries

• Joe Chang
• jchang6_at_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
Power Throughput Composite
SQL 2000 7,330.6 3,687.4 5,199.1
Oracle 10g 13,614.2 7,131.3 9,853.3
SQL 2005 19,241.0 9,666.3 13,637.8
Oracle 10g R2 20,385.3 11,140.4 15,069.9
S2K5 SP1 23,346.3 13,458.6 17,725.9
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 Q1
5 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 1
89.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 7
6.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 Q1
5 Q16 Q17 Q18 Q19 Q20 Q21 Q22 SQL
2000 7111 523.7 4892 3816 5009 185.3 5545 20526 24
341 2241 1464 3677 5320 1108 884.6 3057 5198 15299
7042 4029 15943 643.4 Oracle 10g 4394 1294 501 3
66.5 1909 252.5 2594 2496 5748 1137 866.3 1315 385
7 262.3 639.5 770.6 1733 21580 1140 1236 9459 791.
5 SQL 2005 5460 197.1 1486 1640 2447 161.4 1861 2
311 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_D
ISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE
(1-L_DISCOUNT)(1L_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 lt 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 INNER JOIN ORDERS
ON C_CUSTKEY O_CUSTKEY INNER JOIN LINEITEM ON
L_ORDERKEY O_ORDERKEY WHERE C_MKTSEGMENT
'BUILDING' AND O_ORDERDATE lt '1995-03-15' AND
L_SHIPDATE gt '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 lt 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_at_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