Transcript Solution to help customers and partners accelerate their data Software: • SQL Server 2008 Enterprise • Windows Server 2008 Configuration guidelines: • Physical table.

Solution to help customers and partners accelerate their data
Software:
• SQL Server 2008 Enterprise
• Windows Server 2008
Configuration guidelines:
• Physical table structures
• Indexes
• Compression
• SQL Server settings
• Windows Server settings
• Loading
Hardware:
• Tight specifications for
servers, storage & networking
• ‘Per core’ building block
ProCurve Switch
2510G-24
J9279A
Fault
flash = 100Mbps
on = 1000Mbps
Dual-Personality Port 10/100/1000-T (T) or SFP (S)
Spd Mode
off = 10Mbps
Link
1
Mode
3
5
7
9
11
Link
13
Mode
15
17
19
21T
23T
Link
21 S
Mode
23 S
Link
2
Mode
4
6
8
10
12
Link
14
Mode
16
18
20
22T
24T
Link
22 S
Mode
24 S
10/100/1000Base-T Ports (1-24T) - Ports are Auto-MDIX
Act
Locator
Status
LED
Fan
Mode
Test
Console
FDx
Spd
Reset
Clear
Use only one (T or S) for each Port
ProCurve
Power
ProCurve Networking
HP Innovation
UID
1
2
HP
ProLiant
DL785G5
1
2
3
4
5
6
7
8
HP StorageWorks 8/24 SAN Switch
0
4
1
5
2
6
3
7
8
12
9
13
10
14
11
15
16
20
17
21
18
22
19
23
0
4
1
5
2
6
3
7
8
12
9
13
10
14
11
15
16
20
17
21
18
22
19
23
HP StorageWorks 8/24 SAN Switch
HP
StorageWorks
MSA2000
Model 2212fc
HP
StorageWorks
MSA2000
Model 2212fc
HP
StorageWorks
MSA2000
Model 2212fc
HP
StorageWorks
MSA2000
Model 2212fc
HP
StorageWorks
MSA2000
Model 2212fc
HP
StorageWorks
MSA2000
Model 2212fc
HP
StorageWorks
MSA2000
Model 2212fc
HP
StorageWorks
MSA2000
Model 2212fc
HP ProLiant DL785 G6
 (8) AMD Opteron CPUs, 6 core, 2.6 GHz
 48 total CPU cores
 24 TB optimized storage (48 TB max)
 9600 MB/s throughput
Server
HP Proliant
DL 385 G6
HP Proliant
DL 380 G6
HP Proliant
DL 585 G6
HP Proliant
DL 580 G5
HP Proliant
DL 785 G6
Dell PowerEdge R710
Dell Power Edge R900
CPU
Cores
CPU
SAN
Data Drive Count
Initial
Capacity*
Max
Capacity**
(2) AMD Opteron Istanbul
six core 2.6 GHz
(2) Intel Xeon® 5500 Series
Quad core
(4) AMD Opteron Istanbul
six core 2.6 GHz
(4) Intel Xeon® 7400 Series six core
12
(3) HP MSA2312fc
(24) 300GB 15k SAS
6TB
12TB
8
(2) HP MSA2312
(16) 300GB 15k SAS
4TB
8TB
24
(6) HP MSA2312fc
(48) 300GB 15k SAS
12TB
24TB
24
(6) HP MSA2312
(48) 300GB 15k SAS
12TB
24TB
48
(12) HP MSA2312
(96) 300GB 15k SAS
24TB
48TB
8
24
(2) EMC AX4
(6) EMC AX4
(16) 300GB 15k FC
(48) 300GB 15k FC
4TB
12TB
8TB
24TB
8
(2) IBM DS3400
(16) 200GB 15K FC
4TB
8TB
24
(6) IBM DS3400
(24) 300GB 15k FC
12TB
24TB
IBM X3950 M2
(8) AMD Opteron Istanbul
six core 2.8 GHz
(2) Intel Xeon Nehalem quad core 2.66 GHz
(4) Intel Xeon Dunnington
six core 2.67GHz
(2) Intel Xeon Nehalem quad core 2.67
GHx
(4) Intel Xeon Dunnington six core 2.67
GHz
(8) Intel Xeon Nehalem four core 2.13 GHz
32
(8) IBM DS3400
(32) 300GB 15k SAS
16TB
32TB
Bull Novascale R460 E2
(2) Intel Xeon Nehalem quad core 2.66 GHz
8
(2) EMC AX4
(16) 300GB 15k FC
4TB
8TB
Bull Novascale R480 E1
(4) Intel Xeon Dunnington
six core 2.67GHz
24
(6) EMC AX4
(48) 300GB 15k FC
12TB
24TB
IBM X3650 M2
IBM X3850 M2
SQL Server
Read Ahead Rate
A
B
HBA Port Rate
DISK
A
A
B
STORAGE
CONTROLLER
CACHE
FC
HBA
A
B
FC SWITCH
CACHE
SQL SERVER
WINDOWS
CPU CORES
SERVER
CPU Feed Rate
DISK
FC
HBA
LUN
A
B
DISK
DISK
B
LUN
Switch Port Rate
SP Port Rate
LUN Read Rate
Disk Feed Rate
Traditional SQL DW
Architecture
Shared Infrastructure
Enterprise Shared
SAN Storage
Shared Network
Bandwidth
Fast Track SQL DW Architecture
Dedicated DW Infrastructure
Architecture modeled after DW Appliances
1TB – 48TB Pre-Tested
Dedicated Network
Bandwidth
SQL 2008 Data Warehouse
4 Processor 16 Core Server
OLTP Applications
Dedicated Low Cost
SAN Arrays 1 for every
4 CPU Cores
EMC AX4 – HP MSA2312
Benefits:
Existing Appliance
Loading –
Subject Area 1
Loading –
Subject Area 2
Query times –
Subject Area 1
5:10:21 total time
SQL Server
Fast Track DW
51:31 total time
4:36:08 total time
1:50.01 total time
R 2.5x faster
3:03 avg query time
(using 9 benchmark queries)
0:15 avg query time
(using 9 benchmark queries)
R 12x faster
Query times –
Subject Area 2
56:44 avg query time
(using 4 benchmark queries)
8:09 avg query time
(using 4 benchmark queries)
R 7x faster
Price per TB (8TB) – Cal :
$22K / TB
Price per TB (16TB) – Cal:
$13K / TB
Comparison
R 6x faster
PDW with
Hub-and-spoke
Scale
Complexity
HA by default
SW-HW integration
Incremental HW Expansion,
Fast parallel loading by default,
HA by default
4
3
PDW
SQL Server 2008
with Fast Track
Reference Architecture
2
SQL Server 2008
1
Start
here
FT Storage Enclosure
Raid-1
Primary Data
ARY01D1v01
DB1-1.ndf
Disk 1 & 2
ARY01D2v02
DB1-2.ndf
ARY02D1v03
DB1-3.ndf
ARY02D2v04
DB1-4.ndf
ARY03D1v05
DB1-5.ndf
ARY03D2v06
DB1-6.ndf
Log
ARY04D1v07
DB1-7.ndf
ARY04D2v08
DB1-8.ndf
ARY05v09
DB1.ldf
Autogrow for ALL Databases should be set to 4 MB
1:31
1:32
1:36
1:33
1:32
Key Order of Index
1:34
1:37
1:35
1:33
1:38
1:34
1:39
1:35
1:40
DTEXEC (1)
Task Queue
DTEXEC (2)
Demo Results
1 Process finishes in 64 seconds
01:09.1
01:00.5
00:51.8
Elapsed Time
00:43.2
00:34.6
00:25.9
00:17.3
00:08.6
00:00.0
1
2
3
4
5
6
7
8
Duration
00:07.7
00:07.9
00:07.8
00:08.1
00:08.3
00:08.1
00:08.4
00:08.2
Start
00:00.0
00:07.7
00:15.7
00:23.5
00:31.6
00:39.9
00:48.0
00:56.4
2 Processes finish in 36 seconds
01:09.1
01:00.5
00:51.8
Elapsed Time
00:43.2
00:34.6
00:25.9
00:17.3
00:08.6
00:00.0
1
2
3
4
5
6
7
8
Duration
00:09.0
00:09.0
00:08.7
00:08.7
00:09.7
00:09.7
00:09.3
00:09.3
Start
00:00.0
00:00.0
00:09.0
00:09.0
00:17.7
00:17.7
00:27.4
00:27.4
4 Processes finish in 28 seconds
01:09.1
01:00.5
00:51.8
Elapsed Time
00:43.2
00:34.6
00:25.9
00:17.3
00:08.6
00:00.0
1
2
3
4
5
6
7
8
Duration
00:13.4
00:14.6
00:13.9
00:13.5
00:13.8
00:14.2
00:13.5
00:13.5
Start
00:00.0
00:00.0
00:00.1
00:00.1
00:13.4
00:13.6
00:14.0
00:14.6
8 Processes finish in 27 seconds
01:09.1
01:00.5
00:51.8
Elapsed Time
00:43.2
00:34.6
00:25.9
00:17.3
00:08.6
00:00.0
1
2
3
4
5
6
7
8
Duration
00:26.5
00:26.5
00:26.4
00:27.2
00:24.5
00:27.0
00:26.3
00:27.3
Start
00:00.0
00:00.0
00:00.1
00:00.1
00:00.1
00:00.1
00:00.1
00:00.1
www.microsoft.com/teched
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn
Sign up for Tech·Ed 2011 and save $500
starting June 8 – June 31st
http://northamerica.msteched.com/registration
You can also register at the
North America 2011 kiosk located at registration
Join us in Atlanta next year