Developing a SQL Server 2008 Fast Track Data Warehouse - MSDN

wastecypriotInternet και Εφαρμογές Web

10 Νοε 2013 (πριν από 3 χρόνια και 11 μήνες)

97 εμφανίσεις

BIE07
-
INT

SQL Fast Track DW Overview


Fast Track DW Implementation Key Principles (Server)


Fast Track DW Implementation Key Principles (Storage Layer)


Fast Track DW Implementation Key Principles (Data
Loading)


Q&A

Fast Track DW
Overview





SQL 2008 Data Warehouse

4 Processor 16 Core Server

Shared Network


Bandwidth

Enterprise Shared SAN
Storage

Dedicated Network
Bandwidth

Traditional SQL DW

Architecture

Shared Infrastructure

Fast Track SQL DW Architecture

Dedicated DW Infrastructure

Architecture modeled after DW Appliances


1TB


48TB Pre
-
Tested

Dedicated Low Cost

SAN Arrays 1 for every


4 CPU Cores

EMC AX4


HP MSA2312


OLTP Applications

Benefits:

-
More System Predictability Thus User Experience

-
Pretested Configurations Lowers TCO

-
Balanced CPU to I/O Channel Optimized for DW

-
Modular Building Block Approach

-
Scale Out or Up within limits of Server and SAN

method

configurations

Best
practices



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 and
networking


‘Per core’ building block


All databases contain both scans and seeks among with other
types of reads and writes,
DW workload
indicate that the vast
majority of reads are sequential


not all

Fast Track DW Implementation Key
Principles


Server Layer
FC

HBA

A

B

A

B

FC

HBA

A

B

A

B

FC SWITCH

STORAGE

CONTROLLER

A

B

A

B

CACHE

SERVER

CACHE

SQL SERVER

WINDOWS

CPU CORES

CPU Feed Rate

HBA Port
Rate

Switch Port Rate

SP Port Rate

A

B

DISK

DISK

LUN

DISK

DISK

LUN

SQL Server

Read Ahead Rate

LUN Read Rate

Disk Feed Rate

SQL Server 2008 Potential Performance Bottlenecks

Current Fast Track Architectures are rated at 200
MB/s per CPU core



Server






Windows Server OS

MCR 1.6 GB/s

Storage Enclosure

Storage Enclosure


Fiber Switch

500 MB/s

500 MB/s

500 MB/s

500 MB/s

300 MB/s

300 MB/s

300 MB/s

300 MB/s

300 MB/s

300 MB/s

300 MB/s

300 MB/s


HBA


HBA

Min

2

GB/s

Min

2 GB/s

Server






SQL Server OS

BCR 1.2 GB/s


HBA


HBA

Storage Enclosure

Storage Enclosure

Fiber Switch

1.2 GB/s

1.2

GB/s

300 MB/s

300 MB/s

300 MB/s

300 MB/s

150 MB/s

150 MB/s

150 MB/s

150 MB/s

150 MB/s

150 MB/s

150 MB/s

150 MB/s



Fast Track DW Implementation Key
Principles



SWITCH

S
P


A

S
P
B

SQL Server 2008 Minimum Server Configuration

SMP Core
-
Balanced Architecture using Dual Read on HP MSA 2312

Per MSA2312 Drive Details



Each MSA can hold 12 drives, this configuration requires 11



MSA is 2U in total (capacitor eliminates need for battery)



Each MSA SP port controls 4 LUNs, SP
-
A also controls LOG LUN



Each pair of LUNs consists of (2) 300GB 15k FC drives RAID1

Each SP rated at 500MB/s or 1000MB/s

for both SP’s

Using 300GB 15k FC drives

each LUN rated at 125MB/s

each SP controls 4 LUN’s at 500MB/s or 1000MB/s per MSA
DAE

Each SP port rated at 4Gb/s

or 400MB/s and 1600MB/s for all 4 SP ports.

Each HBA port rated at 4Gb/s

or 400MB/s and 1600MB/s for all 4 HBA ports.

03

04

RAID GP02

LUN3

LUN4

01

02

RAID GP01

LUN1

LUN2

05

06

RAID GP03

LUN5

LUN6

07

08

RAID GP04

LUN7

LUN8

09

10

RAID GP05

LUN0

(Logs)

HS

Quad Core CPU

* Compressed Data

200MB/s per
Core*

200MB/s per
Core*

HBA FC 1

4Gb/s or 400MB/s x 2

200MB/s per
Core*

200MB/s per
Core*

HBA FC 2

4Gb/s or 400MB/s x 2

DAE = Disk Array Enclosure

HBA = Host Bus Adapter

SP = Storage Processor

FC =
Fibre

Channel

Ports = 4Gbs FC


LUN16

LUN 2

LUN 3

Local Drive 1

Log LUN 1

Permanent DB Log

LUN 1

TempDB

TempDB.mdf (25GB)

TempDB_02.ndf (25GB)

TempDB_03ndf (25GB)

TempDB_16.ndf (25GB)

Permanent FG

Permanent_1.ndf

Permanant_DB

Stage
Database

Stage FG

Stage_1.ndf

Stage_2.ndf

Stage_3.ndf

Stage_16.ndf

Stage DB Log

Permanent_2.ndf

Permanent_3.ndf

Permanent_16.ndf

Log LUN 2

Permanent DB Log

Stage DB Log

Fast Track DW Implementation Key
Principles




1:32

1:31

1:35

1:34

1:33

1:36

1:38

1:37

1:40

1:39

1:32

1:31

1:35

1:34

1:33

Key Order of Index









Target Database

Step 1

“Base Load”

Step 2

“Stage Insert”

Step 3

“Transform”

Step 4

“Final Append”

Destination Partitioned CI Table

Target Database

Step 1

“Base Load”

Destination Partitioned CI
Table

http://
www.microsoft.com/sqlserver/2008/en/us/fasttrack.aspx
http://
msdn.microsoft.com/en
-
us/library/dd459178.aspx
www.microsoft.com/teched

www.microsoft.com/learning


http://microsoft.com/technet


http://microsoft.com/msdn


It’s a free download!

Go to
www.microsoft.com/ipd

At the end of the day, IT operations is really
about running your business as efficiently as you
can so you have more dollars left for innovation.
IPD guides help us achieve this.”

Peter
Zerger
,
Consulting Practice Lead for
Management
Solutions, AKOS Technology Services


Fast Track DW Implementation Key
Principles



Fans

6 hot plug redundant fans, 3 shown

Core I/O
-

2 USB, 1 serial, 1 video port,

3 RJ
-
45 PS2 keyboard/mouse support

I/O slots

11 PCIe slots std.,

Option to upgrade to 2 HTx and 7
PCIe

Power Supplies
-

3+3 redundant power
supplies

No single points of failure

in Failover Clustering!

Fast Track DW

Case Study



Current Environment

Proposed Microsoft Platform

Teradata

SQL Server

Fast
Track DW

Comparison

Loading


Subject
Area 1

5:10:21 total time

0:51:31
total time

R

6x faster

Loading

Subject
Area 2

4:36:08 total time

1:50.01 total time

R

2.5x faster

Query
times

Subject
Area 1

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

Large Retailer with limited capabilities because of their legacy based business
intelligence solution. The solution has capacity for 212 users at the cost of ~1 million in
annual maintenance
. Competition


Netezza & Oracle

1)
Lower their maintenance cost

2)
They wanted to address the business needs (POS data, etc)

3)
They also wanted to proliferate the advantages of Business Intelligence across their
enterprise.



Business

Needs

Solution

Situation


Full MS BI stack Fast Track , SSRS, Excel Services , PPS & Office 2007


Our solution will replace and extend the existing DB2 AS400 system


SSIS will replace existing COBOL ETL (including ODI)



Benefit

1)
2)




ARY01D1v01

ARY01D2v02

ARY02D1v03

ARY02D2v04

ARY03D1v05

ARY03D2v06

ARY04D1v07

ARY04D2v08

DB1
-
1.ndf

DB1
-
7.ndf

DB1
-
5.ndf

DB1
-
3.ndf

DB1
-
2.ndf

DB1
-
4.ndf

DB1
-
6.ndf

DB1
-
8.ndf

4MB

4MB

4MB

4MB

4MB

4MB

4MB

4MB

57

©2009 Microsoft Corporation

Fast Track SMP RA for SQL Server 2008 CPU Core Calculator v2.4
Updated 10/09/2009 - uw
This spreadsheet can be used to estimate the number of cores required to support a user workload and workload mix.
Enter your factors into the green fields and the results will be calculated in the pink cells.
The spreadsheet uses a weighted average to determine the number of cores required based on your inputs.
User Variable Input
Anticipated total number of users expected on the
system
3,000
users
Adjust for
workload mix
Estimated % of
workload
Estimated %
data found in
SQL Server
cache
Estimated Query
Data
Scan Volume MB
(Uncompressed)
Desired Query
Response Time
(seconds)
(under load)
Estimated Disk
Scan volume MB
(Uncompressed)
Estimated percent of actual query concurrency
1%
concurrency
Simple
70%
10%
8,000
25
7,200
Fast Track DW CPU max core consumption rate
(MCR) in MB/s of page compressed data per core
200
MB/s
Average
20%
0%
75,000
180
75,000
Estimated compression ratio (default = 2.5:1)
2.5
:1
Complex
10%
0%
450,000
1,200
450,000
Estimated drive serial throughput speed in
compressed MB/s
100
MB/s
100%
Number of data drives in single storage array
8
drives
Usable capacity per drive
272
GB
Space Reserved for TempDB
26%
Calculations and Results
% of core
consumption
rate achieved
Expected per
CPU core
consumption
rate (MB/s)
Calculated Single
Query Scan
Volume in MB
(compressed)
Calculated
Target
Concurrent
Queries
Estimated
Target Queries
per Hour
Required IO
Throughput in
MB/s
Estimated
Number of Cores
Required
Estimated Single
Query Run Time
(seconds)
Simple
100%
200
2,880
21
3,024
2,419
12.10
0.5
Average
50%
100
30,000
6
120
1,000
10.00
9.4
Complex
25%
50
180,000
3
9
450
9.00
112.5
30
3,153
3,869
32.00
Arrays
Required based
on throughput
Single Array
Throughput in
MB/s
Throughput in
MB/s for All
Required Arrays
5
800
4,000
Suggested Fast Track RA Server
Requirements
No of CPU
cores
Number of
arrays
Total Compressed
Data Capacity
(TB)
Max achievable
IO Throughput
in MB/s
Max
achievable CPU
consumption in
MB/s
Required IO
Throughput in
MB/s
32
8
16
6,400
6,400
3,869