Transcript Performance Enhancements Large Queries

SQL Server 2005 Performance
Enhancements for Large Queries
Joe Chang
[email protected]
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
Usually internal users
Read-only queries
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
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
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
13,458.6
17,725.9
S2K5 SP1*
23,346.3
*1.6GHz/9M processor, others: 1.5GHz/6M
TPC-H 1000GB - Power
10,000
seconds
SQL 2000
SQL 2005
Oracle 10g
Oracle 10g R2
1,000
10X
100
10
Q1
Q2
Q3
Q4
Q5
Q6
Q7
Q8
Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Q1
Q10
Q19
Q2
Q11
Q20
Q3
Q12
Q21
Q4
Q13
Q22
Q5
Q14
Q6
Q15
Q7
Q16
Q8
Q17
Q9
Q18
SQL 2000
2530.8
655.4
283.2
60.2
224.5
125.7
873.2
666.0
3489.6
807.4
728.7
156.3
1058.7
149.4
70.8
110.9
838.2
269.1
822.8
181.3
5085.8
3214.4
Oracle 10g
1068
189.6
460.1
117.5
172.8
156.3
79.2
182.8
1908
84
1251
189.2
334.4
48.5
67
122.9
420.7
227.9
242.3
372.7
1268
2146
SQL 2005
987.3
104.7
193
26.2
112.4
76.4
59.2
292
1686
76.4
405.7
182.6
235.7
74.1
49
42.1
236.9
210.4
163.9
90.5
1084
1456
TPC-H 1000GB - Power
seconds
10,000
SQL 2000
Oracle 10g
SQL 2005
Oracle 10g R2
1,000
100
10
Q1
Q2
Q3
Q4
Q5
Q6
Q7
Q8
Q9
Q10
Q11
Q17
Q18
Q19
Q20
Q21
Q22
seconds
10,000
SQL 2000
Oracle 10g
SQL 2005
Oracle 10g R2
1,000
100
10
Q12
Q13
Q14
Q15
Q16
TPC-H 1000GB - Throughput
Average seconds
100,000
SQL 2000
Oracle 10g
SQL 2005
Oracle 10g R2
10,000
1,000
100
Q1
Q2
Q3
Q4
Q5
Q6
Q7
Q8
Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Q1
Q10
Q19
Q2
Q11
Q20
Q3
Q12
Q21
Q4
Q13
Q22
Q5
Q14
Q6
Q15
Q7
Q16
Q8
Q17
Q9
Q18
SQL 2000
7111
2241
7042
523.7
1464
4029
4892
3677
15943
3816
5320
643.4
5009
1108
185.3
884.6
5545
3057
20526
5198
24341
15299
Oracle 10g
4394
1137
1140
1294
866.3
1236
501
1315
9459
366.5
3857
791.5
1909
262.3
252.5
639.5
2594
770.6
2496
1733
5748
21580
SQL 2005
5460
1857
1037
197.1
1006
2376
1486
1555
5320
1640
2256
644.1
2447
580.4
161.4
253.6
1861
821
2311
1630
6464
4126
TPC-H 1000GB - Throughput
Average seconds
100,000
SQL 2000
Oracle 10g
SQL 2005
Oracle 10g R2
10,000
1,000
100
Q1
Q2
Q3
Q4
Q5
Q6
Q7
Q8
Q9
Q10
Q11
Q17
Q18
Q19
Q20
Q21
Q22
Average seconds
100,000
SQL 2000
Oracle 10g
SQL 2005
Oracle 10g R2
10,000
1,000
100
Q12
Q13
Q14
Q15
Q16
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
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 < '1995-03-15' AND L_SHIPDATE > '1995-03-15'
GROUP BY L_ORDERKEY ,O_ORDERDATE ,O_SHIPPRIORITY
ORDER BY REVENUE DESC ,O_ORDERDATE
Query 8
SQL 2000 to 2005
5.0X Power
8.9X Throughput
10GB Line Item in memory test
SQL Server 2000
SP4
Query time in milliseconds versus
Max Degree of
Parallelism
Limited parallelism
gains beyond 4-8P
1,000,000
DOP 1
DOP 2
DOP 4
DOP 8
DOP 16
100,000
10,000
1,000
100
1
2
3
4
5
6
7
8
9
10 11 12 13
14 15 16
17 18 19
20 21 22
SQL Server 2005
June CTP
Continued
parallelism gains
beyond 4P
1,000,000
DOP 1
DOP 2
DOP 4
DOP 8
DOP 16
100,000
10,000
1,000
100
1
2
3
4
5
6
7
8
9
10 11
12 13 14
15 16
17 18 19
20 21 22
10GB Line Item in memory test 1
SQL Server 2000
SP4
Query time in milliseconds versus
Max Degree of
Parallelism
Limited parallelism
gains beyond 4-8P
1,000,000
DOP 1
DOP 2
DOP 4
DOP 8
DOP 16
100,000
10,000
1,000
100
1
2
3
4
5
6
7
8
9
10
11
SQL Server 2005
June CTP
Continued
parallelism gains
beyond 4P
1,000,000
DOP 1
DOP 2
DOP 4
DOP 8
DOP 16
100,000
10,000
1,000
100
1
2
3
4
5
6
7
8
9
10
11
10GB Line Item in memory test 2
SQL Server 2000
SP4
Query time in milliseconds versus
Max Degree of
Parallelism
Limited parallelism
gains beyond 4-8P
1,000,000
DOP 1
DOP 2
DOP 4
DOP 8
DOP 16
100,000
10,000
1,000
100
12
13
14
15
16
17
18
19
20
21
22
SQL Server 2005
June CTP
Continued
parallelism gains
beyond 4P
1,000,000
DOP 1
DOP 2
DOP 4
DOP 8
DOP 16
100,000
10,000
1,000
100
12
13
14
15
16
17
18
19
20
21
22
10GB in memory test
Max Degree of
Parallelism 1
1,000,000
SQL 2000
SQL 2005
100,000
10,000
30% reduction in
total time at 1P
1,000
100
1
2
3
4
5
6
7
8
9
10 11
12 13 14
15 16
17 18 19
20 21 22
1,000,000
SQL 2000
Max DOP 4
SQL 2005
100,000
10,000
1,000
100
1
2
3
4
5
6
7
8
9
10 11
12 13 14 15
16 17 18
19 20 21 22
Max DOP 16
100,000
SQL 2000
SQL 2005
10,000
50% reduction in
total time at 16P
1,000
100
1
2
3
4
5
6
7
8
9
10
11 12
13
14
15 16
17
18
19
20 21
22
In-Memory Table Scan
5,000
4,500
SQL 2000 Clust. Index Scan
SQL 2000 Heap Table Scan
SQL 2005 Clust. Index Scan
SQL 2005 Heap Table
4,000
MB/sec
3,500
3,000
2,500
2,000
1,500
1,000
500
0
Default
RowLock
PagLock
TabLock
Xeon systems have better table scan performance on SQL 2000
SQL 2000 default table scan performance depends on build
NoLock
Table Scan to Disk
1,600
1,400
SQL 2000 Clust. Index Scan
SQL 2005 Clust. Index Scan
SQL 2000 Heap Table Scan
SQL 2005 Heap Table
1,200
MB/sec
1,000
800
600
400
200
0
Default
RowLock
PagLock
TabLock
SQL 2005 Table Scan performance probably limited by disk system
NoLock
Loop Join – SQL 2000 & 2005
400
SQL 2000
000's rows/sec
350
No scaling
from 1-2P
300
250
200
150
100
50
DOP 1
DOP 2
DOP 4
DOP 8
DOP 16
0
100
1,000
000's rows per query
10,000
100,000
SQL 2005
600
000's row/sec
500
Better overall
performance,
scaling from
1-8P
400
300
200
100
DOP 1
DOP 2
DOP 4
DOP 8
DOP 16
0
100
1,000
000's rows per query
10,000
100,000
Hash Join
3,000
2,500
000's rows/sec
SQL 2000
DOP 1
DOP 2
2,000
Unpredictable
behavior below
3M rows
Limited scaling
DOP 4
DOP 8
1,500
DOP 16
1,000
500
0
100
1,000
000's rows per query
10,000
100,000
SQL 2005
3,000
Consistent
behavior <
1M rows
Not as much
fall off in
large joins
000's rows/sec
2,500
DOP 1
2,000
DOP 2
DOP 4
DOP 8
DOP 16
1,500
1,000
500
0
100
1,000
10,000
000's rows per query
100,000
Merge Join – SQL 2000
2,500
DOP 1
DOP 2
DOP 4
DOP 8
DOP 16
000's rows/sec
2,000
1,500
1,000
500
0
100
10,000
1,000
000's rows per query
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.
100,000
LiteSpeed Backup Performance
1200
MB/sec
1000
Threads 6
8
10
12
800
600
400
200
0
2.6:1
5:1
Compression Ratio
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
8:1
SQL 2005 Performance Summary
Significant improvements in large queries
Data Warehouse Applications
Improved disk performance
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
[email protected]
System Configuration
rx8620
16 Itanium 2
8 2Gb/s
1.5GHz
FC ports
HSV110
HSV110
HSV110
6 SCSI Disks
HSV110
Bookmark Lookup Performance
2,000,000
1,800,000
1,600,000
Rows/sec
1,400,000
2000 Clustered Index
2005 Clustered Index
2000 Heap
2005 Heap
1,200,000
1,000,000
800,000
600,000
400,000
200,000
0
1
2
4
8
Degree of Parallelism
16
SQL 2005 has better bookmark lookup performance to Clustered Indexes
SQL 2000 has better bookmark lookup performance to Heap organized tables