HardwareHardware(The part you can kick)(The part you can kick)

OverviewOverview

Selection ProcessSelection Process Equipment CategoriesEquipment Categories ProcessorsProcessors MemoryMemory StorageStorage SupportSupport

Define RequirementsDefine Requirements

Service Level AgreementsService Level Agreements PeoplePeople ProcessProcess TechnologyTechnology

BudgetBudget

You may get an AttaBoy for saving a few dollars.You may get an AttaBoy for saving a few dollars. You WILL get blamed for an inadequate systemYou WILL get blamed for an inadequate system

What is the difference?What is the difference?

WorkstationWorkstation Single user (desktop/notebook)Single user (desktop/notebook)

ServerServer DepartmentDepartment EnterpriseEnterprise

ProcessorsProcessors

32 bit32 bit 64 bit64 bit AMDAMD IntelIntel Single-CoreSingle-Core Multi-CoreMulti-Core

32 Bit Processors32 Bit Processors

Proven, mature technologyProven, mature technology Low CostLow Cost CompatibleCompatible 4GB Memory Address Limit4GB Memory Address Limit

PAEPAE 3GB3GB AWEAWE

Called x86 (Intel 80386 was the first)Called x86 (Intel 80386 was the first)

64 Bit Processors64 Bit Processors

ItaniumItanium Incompatible with existing x86 processorsIncompatible with existing x86 processors 8 or more proc systems Enterprise Systems8 or more proc systems Enterprise Systems

AMD64/EMT64 (x64)AMD64/EMT64 (x64) Pioneered by AMD. Intel dragged kicking and Pioneered by AMD. Intel dragged kicking and

screaming into the market.screaming into the market. Binary compatible with x86 procsBinary compatible with x86 procs Extensions for 64 bit commandsExtensions for 64 bit commands Very price effective (10%-15% premium)Very price effective (10%-15% premium)

IntelIntel

Front Side BusFront Side Bus Uniform memory accessUniform memory access 1-4 processors is easy and fast1-4 processors is easy and fast Potential memory bottlenecksPotential memory bottlenecks Decouples memory and CPU clock ratesDecouples memory and CPU clock rates

AMDAMD

AMDAMD NUMA NonUniform Memory ArchitectureNUMA NonUniform Memory Architecture Memory directly controlled by processorMemory directly controlled by processor Matches SQLOS memory alignmentMatches SQLOS memory alignment No bus bottlenecksNo bus bottlenecks Processor action required for cross-boundary Processor action required for cross-boundary

memory accessmemory access SQL 2005 nativeSQL 2005 native

AMD vs. IntelAMD vs. Intel

Drawing © AMD Technologies

CoresCores

Single-CoreSingle-Core Cheap, provenCheap, proven

Multi-coreMulti-core NewerNewer Licensing advantage for SQL (sockets, not cores)Licensing advantage for SQL (sockets, not cores) On-dieOn-die In-PackageIn-Package

HyperThreadingHyperThreading Simulates multiple cores. 10% to 15% performance Simulates multiple cores. 10% to 15% performance

boost for most SQL applicationsboost for most SQL applications Can slow down system with many non-parallelizable Can slow down system with many non-parallelizable

queriesqueries

MemoryMemory

SpeedSpeed LatencyLatency Error CorrectionError Correction

StorageStorage

Disk characteristicsDisk characteristics Storage SubsystemsStorage Subsystems RAID ConfigurationRAID Configuration

Disk CharacteristicsDisk Characteristics

RPM – Higher is betterRPM – Higher is better On disk read cache – More is betterOn disk read cache – More is better SCSISCSI

Fast, reliable, proven, matureFast, reliable, proven, mature SATASATA

Cheaper, slower, reliability? (new)Cheaper, slower, reliability? (new) Good for second tier Storage (backup, Good for second tier Storage (backup,

historical partitions)historical partitions) Fibre ChannelFibre Channel

Storage TypesStorage Types

Locally AttachedLocally Attached SCSISCSI SATASATA

NASNAS iSCSIiSCSI SANSAN

Local StorageLocal Storage

Hot swap disksHot swap disks ControllersControllers

Battery-backed cacheBattery-backed cache

Disk arraysDisk arrays Cache on controllerCache on controller Cache in cabinet (clusterable)Cache in cabinet (clusterable)

NASNAS

Not a block-write deviceNot a block-write device SMB drive mappingSMB drive mapping Cache corruptionCache corruption

Must be on Windows CatalogMust be on Windows Catalog

CANNOT CLUSTER!!!CANNOT CLUSTER!!!

iSCSIiSCSI

SCSI via IPSCSI via IP Lower CostLower Cost Competes with NASCompetes with NAS Large variance in speed, quality, Large variance in speed, quality,

managability, reliabilitymanagability, reliability Cluster nightmare Cluster nightmare

SANSAN

Pseudo-SAN – “Smart Array”Pseudo-SAN – “Smart Array” Large write cache (GB)Large write cache (GB) Fibre Channel (SCSI++)Fibre Channel (SCSI++) OptionsOptions

Split mirror backupsSplit mirror backups Distance synchingDistance synching Virtual SnapshotVirtual Snapshot Management (Provisioning) toolsManagement (Provisioning) tools

Disk Stripe AlignmentDisk Stripe Alignment

Low-level stripes (Smart Array controller or SAN)Low-level stripes (Smart Array controller or SAN) OS-level stripesOS-level stripes MBR boot record offset (63 sectors)MBR boot record offset (63 sectors) Cache copying. IO doubling.Cache copying. IO doubling. Up to 40% slowdown. Up to 40% slowdown. Diskpart.exe (Win2003 SP1) can pad offset to Diskpart.exe (Win2003 SP1) can pad offset to

match stripe sizematch stripe size Diskpar.exe (Win 2000 Resource Kit) for older Diskpar.exe (Win 2000 Resource Kit) for older

systemssystems

RAIDRAID

Redundant Array of Inexpensive (later Redundant Array of Inexpensive (later Independent) DisksIndependent) Disks

1988 ACM SIGMOD paper “A Case for 1988 ACM SIGMOD paper “A Case for Redundant Arrays of Inexpensive Disks Redundant Arrays of Inexpensive Disks (RAID)” by David A. Patterson, Garth A. (RAID)” by David A. Patterson, Garth A. Gibson, and Randy H. Katz.Gibson, and Randy H. Katz.

Overcomes size and reliability limitations Overcomes size and reliability limitations of physical disks of physical disks

RAID LevelsRAID Levels

JBOD Just A Bunch of DisksJBOD Just A Bunch of Disks RAID 0 Striping with no redundancyRAID 0 Striping with no redundancy RAID 1 MirroringRAID 1 Mirroring RAID 2-3 not worth talking aboutRAID 2-3 not worth talking about RAID 4 Stripe with Parity DiskRAID 4 Stripe with Parity Disk RAID 5 Stripe with Parity RotationRAID 5 Stripe with Parity Rotation RAID 1+0 (10) Stripe of mirrored pairsRAID 1+0 (10) Stripe of mirrored pairs RAID 0+1 (10) Mirror of stripesRAID 0+1 (10) Mirror of stripes RAID 50 Stripe of stripes w/ parityRAID 50 Stripe of stripes w/ parity

RAID PerformanceRAID Performance

RAID 0RAID 0 ReadRead

Stripe advantage NStripe advantage N

WriteWrite 11

RAID 1RAID 1 ReadRead

Stripe Advantage 2Stripe Advantage 2

Write Write 2x2x

RAID 5RAID 5 ReadRead

Stripe Advantage N-1Stripe Advantage N-1

WriteWrite N-2 reads + 2 WritesN-2 reads + 2 Writes

RAID 1 + 0RAID 1 + 0 ReadRead

Stripe Advantage NStripe Advantage N

WriteWrite 2x2x

Baseline PerformanceBaseline Performance

SQLIO stress utilitySQLIO stress utility IOMeter stress utilityIOMeter stress utility Performance Monitor capturePerformance Monitor capture

SupportSupport

Life cycle of equipmentLife cycle of equipment Parts/service SLAParts/service SLA Management and Monitoring softwareManagement and Monitoring software Working relationshipWorking relationship

QuestionsQuestions