Yoni Okun

1
Yoni Okun
High Availability Best Practices I/O Subsystem

• Senior Consultant
• Microsoft Databases Team
• SRL Group
• yonio_at_srl.co.il

2
SQL Server 2005Always on technologies

• New sub-brand for high availability features of
  SQL Server
• Always On Technologies support mission critical
  applications that demand the highest level of
  availability
• Increased availability during planned downtime
• Application, OS, hardware upgrade
• Maintenance operations
• Protection fast recovery from unplanned
  downtime
• Human error
• System failure
• Recovery from data corruption
• Natural disasters
• Microsoft is investing heavily to expand Always
  On Technologies in future releases of SQL Server

3

• ????? SRL
• ????? SRL ????? ?-1982
• ????? ? 170 ??????
• ?????? ?????????? ????????? ????
• ??? ?? ??????? ?????? ?????
• ?????? ???? ?????? ??????? ???????? ????
• ????? ?- Professional Services
• ????? ?????? ?????? ????????
• SQL Server and BI
• Application Lifecycle Management
• ????? ?????? .NET
• Enterprise Integration Servers
• Infrastructure

4
Agenda

• SQLIO basics and IO characteristics
• General storage configuration best practices
• Monitoring and Base-lining I/O Subsystems
• QA

5
SQLIO Basic Terms and Key Concepts

• ACID Transactions
• Atomicity Either All / None Of The
  Modifications Are Performed
• Consistency Integrity Rules and Structures Must
  Be Applied On Data Modifications
• Isolation A Transaction Sees Data
  Before/After Another Transactions Modification
• Durability Modifications Are Permanently Places
  In The System
• Write Ahead Log
• All Log Records, For A Specific Page, Must Be
  Flushed To Stable Media Before The Data Page
  Itself Can Be Flushed
• Stable Media
• Refers To Storage That Can Survive System Restart
  Or Common Failure.
• Regards, Usually, to Both Physical Disk And Disk
  Cache.

6
SQLIO Basic Terms and Key Concepts (2)

• Torn IO (Torn Page)
• When Only A Portion Of The 8KB (16 Sectors) Is
  Correctly Written Or Retrieved
• Occurs When No Battery Cached Controller Exists
• Flushing Data 2 Main Mechanisms Harden Cached
  Modifications
• Lazy Writes
• Last Recently Used (LRU-K) And Memory-pressure
  Based.
• Used by Buffer Manager To Insure 5 MB Min Of Free
  List
• Checkpoint
• Recovery Interval Based
• More Suitable for Flushing Dirty Pages
• Read Ahead
• Performance Optimization That Allows Pages To Be
  Brought Into The Buffer Pool Before They're
  Actually Read

7
Core I/O Requirements

• Stable Media, Write Ordering and Torn I/O
  Prevention
• Test to Validate Requirements (Transaction/Backup
  Pull The Plug, Moving storage, etc)
• Disk Caching for Non-Storage Drives (Laptops)
  Should Be Manually Disabled
• Whitepapers SQL Server 2000 I/O Basics and
  Microsoft SQL Server I/O Basics Chapter 2
  http//www.microsoft.com/technet/prodtechnol/sql/2
  000/maintain/sqlIObasics.mspx http//www.microsoft
  .com/technet/prodtechnol/sql/2005/iobasics.mspx
• These properties also apply to any storage-level
  replication scheme (synchronous or asynchronous)
• To guarantee that what appears at secondary site
  is an image that actually appeared at some point
  in time at the primary site
• Asynchronous replication allows potential loss of
  committed transactions But with these
  requirements the data will be recoverable to a
  point that actually existed on the primary system

8
IO Characteristics Basic Terms and Key Concepts

• Oltp
• Data Manipulation Oriented
• Usually, Large Number Of User Connections, Using
  Simple Queries That Generate Random Accesses To
  The Disk.
• Olap
• Data Reading Oriented
• Usually, Fewer User Connections, Issuing Complex
  Aggregative Queries That Generate Sequential
  Accesses To The Disk
• Periodic Write Activity Must Be Also Taken Into
  Account
• Read Vs. Write
• Read - Accesses The Data From Drive And Writing
  It To The Buffer Pool.
• Write - Writing The Data From The Buffer Pool
  Onto The Hard Drive.
• Random Vs. Sequential
• Random - Accessing The Data That Is Scattered
  Across The Disk.
• Sequential - Accessing The Data By The Order It
  Is Physically Stored.
• Sector Size Vs. Stripe Size Vs. Block Size
• Sector Size - smallest physical internal storage
  unit on the disk. fixed to 512 bytes
• Allocation Unit Size NTFS Working Size Unit
• Stripe Size - unit of data written / accessed
  from a disk. set at array creation
• Block Size - is the Data Size Unit I/O Requests
  Sent By SQL Server

9
SQL Servers I/O Characteristics

• Understanding The I/O Characteristics Of Common
  SQL Server Operations/Scenarios Can Help
  Determine How To Configure Storage
• Monitor I/O To Determine Specifics Of Each
  Scenario
• Special Attention To Hybrid Systems

Note! values may change as optimizations are made
to take advantage of modern storage enhancements
10
SQL Server I/O Characteristics (2)
Note! values may change as optimizations are made
to take advantage of modern storage enhancements
11
Agenda

• SQLIO basics and IO characteristics
• General storage configuration best practices
• Monitoring and Base-lining I/O Subsystems
• QA

12
Topics Organization

• Performance
• General
• Disk Drives
• RAID Level
• Storage Design
• Number of Data Files and Filegroups
• Creating Partitions
• Configuration
• Isolation
• Managing Growth
• Failover Clustering Considerations
• Data Compression
• Remote Mirroring

13
Performance General

• Theres no one single right way to configure
  storage for optimal performance
• Storage design considerations differ for large
  vs. small or consolidated environments
• Understanding the I/O characteristics is The key
• General guidelines
• More/faster spindles is always better for
  performance (14 is the Sequntial Limit )
• Engage the engineers from all sides, early on
• Try not to over optimize, simpler designs
  generally offer good performance and more
  flexibility
• Validate configurations prior to deployment

14
Performance Disk Drives, HBAs
How Many Disks and HBAs Should I Have?

• Use Total IOPS to calculate of disks
• ROT A Single Server-class Disk Can Handle Around
  100-150 (0.8-1.2 Mb/Sec) Random IOPS and
  30-50 Mb/Sec Of Sequential I/O
• For Example , if 8 MB/Sec Of Random Disk I/O Is
  Required To Handle Company Workload ? (8000
  Kb/Sec / 8 Kb) / 150 7 Physical Disk
• Measure Disks IOPS with SQLIO.EXE (Discussed
  Later).
• Use Total throughput to calculate needed bus
• Sequential Operations will saturate HBA Early
• OLTP
• A 1 GB fiber channel can handle a maximum of 90
  MB/sec
• a single disk can maximize 1.2 MB/sec (150 IOPS
  of 8 Kb each)
• So75 disk drives will saturate the channel
• OLAP
• 3 x 40 mb/sec disk will saturate a 1 Gb channel
• A single U320 SCSI bus can feed seven to eight
  disk drives
• SAS Controller will feed 700-800 MB/Sec.

Disk Drives (Total Throughput / Transfer
Size) / IOPS
15
Performance Disk Drives (2)

• Adjust HBA Queues Configuration
• Detection Latency, Lower-then-expected
  Throughput
• Change Default Value From 8-32 To 64
• Test Implications On Other Applications
• More Disks Per Array Will Accelerate The Speed
  And The Number Of Ios Per Second.
• More Disk Controllers Will Speed Up Writing
  (Cache)
• Array / Spindle Ratio
• OLTP - Lots of spindles in array ( need IOPS
  lower latency )
• OLAP - Lots of arrays ( need high throughput )
• Don't Forget That Disk Cache Never Outperform
  Memory Cache. RAM Is Also Cheaper Than Disk Cache

16
Performance RAID Level (1)
RAID 0 - Striping
RAID 1 - Mirroring
RAID 5 Striping with Parity
RAID 10 Striping over mirror sets
17
Performance RAID Level (2)
RAID 0 - Striping
RAID 1 - Mirroring
RAID 5 Striping with Parity
RAID 10 Striping over mirror sets
18
Performance RAID Level (3)
19
Performance RAID Level (4)

• Best Practice Isolate log from data at the
  physical disk level (Expect 10 Increase In Disk
  I/O Performance) . (more on isolation later)

• Our results indicate performance gain on RAID 10
  for write intensive workloads but at a higher
  cost ()
• The performance difference between RAID 10 and
  RAID 5 can vary by vendor
• Benchmarking of the storage can give a clear
  indication of the performance diff
• Pay Attention to the Parity Rebuild Priority for
  RAID 5
• This is a HA Risk

20
Performance Storage Design
How many/what size should my LUNs be?

• LUN Size
• Vendors Recommendation Balanced With Deployment
  Needs
• Chkdsk Can Take Very Long Time To Complete For
  Large Luns
• Plan Adequately For Growth
• Increasing Lun Size / Adding More Luns
• ROT Consider The Number Of Processors On The
  Host
• Maintain Balanced Design
• HBAs Have Structures Managed On LUN Basis
• More Luns - Multiple Independent Queues, Thus
  Potential For Better Parallel I/O Operations
• Multiple Paths Will Allow More-then-a-single HBA
  Per Raid Group

21
Performance - Database Files / Filegroups
How Many Database Files Should I Have?

• More Data Files ? Better Performance
• Determined Mainly By Hardware Capacity
• Consider Disaster Recovery Requirements
• Will Target Environment For Disaster Recovery
  Restore Accommodate File Sizes?
• Number Of Data Files May Impact Scalability
• Number Of Processors On Host (Mainly Be Concerned
  For gt 4 Cpus)
• ROT Have .50 To 1 Data Files (Per Filegroup) For
  Each CPU On The Host Server
• Utilize Maximum Of Spindles
• Data Files Can Be Used To Stripe Database
  Across More Physical Spindles
• Best Practice Pre-size Data/Log Files Do No
  Rely On AUTOGROW

22
Performance - Database Files / Filegroups
How Many Filegroups Should I Have?

• Decision by Administrative and Performance-wise
  Considerations
• Tables And Indexes
• Can Specify Filegroups For Row Data And
  Large-object Data
• Partial Availability
• Database Is Available If Primary Filegroup Is
  Available Other Filegroups Can Be Offline
• A Filegroup Is Available If All Its Files Are
  Available
• Can Backup And Restore Whole Database, Filegroup,
  File
• Partitions
• Can Partition The Rows Of A Table Or Index Based
  On Ranges Of Values
• Each Partition Can Be In Its Own Filegroup
• Partitions Can Be Moved In And Out Of The Table
• Physical-Hardware- Aligning Partitions Is
  Important

23
Performance Disk Partitions

• Sector Align new partitions at creation time
• In our test lab the following has worked well
• We have used 128 sectors (64k) offsets as a rule
  of thumb
• Expect 25-35 Performance Gain
• Not Needed at Vista Longhorn 2048 sectors
  (1024k) will be the default
• More on Sector Alignment here http//support.micr
  osoft.com/default.aspx?scidkbEN-US929491
• Sector alignment cannot be controlled using
  Windows Disk Manager
• DISKPAR (note the missing T) can be used to
  create aligned partitions
• Available as part of the Windows 2000 Resource
  Kit
• New in Windows 2003 Server Service Pack 1
• DiskPart.exe contains a new /ALIGN option
  eliminating the need for Diskpar.exe
• More on Diskpart here http//technet2.microsoft.c
  om/WindowsServer/en/library/ca099518-dde5-4eac-a1f
  1-38eff6e3e5091033.mspx?mfrtrue
• NTFS Allocation Unit Size
• No impact observed on performance of SQL Server
  files
• Avoid values less than 8K (reduces risk of torn
  pages)
• Generally 64K

24
Configuration (Host / Array)

• Firmware
• Ensure both array and driver firmware is up to
  date based on vendor recommended levels
• Drivers
• Storport vs. SCSIport vs. Full Port?
• Use drivers which have been qualified with
  storage
• Storage vendor specific areas on the HBA
  manufactures website
• Driver specific settings should come from the
  storage vendor (e.g., Queue Depth, etc)
• HBA placement
• Hosts generally have multiple PCI Buses,
  overloading a single bus can result in a
  bottleneck
• Multipathing I/O software
• Recommended to use this when multiple HBAs are
  involved.
• Simplifies configuration offers advantages for
  availability
• Microsoft Multipath I/O (MPIO)
• Vendors build Device Specific Modules (DSM) on
  top of DDK provided by Microsoft

25
SQL Server and iSCSI

• The iSCSI protocol transmits SCSI packets as IP
  packets
• No issues have been encountered
• Support for SQL Server 2000 on iSCSI technology
  components
  http//support.microsoft.com/default.aspx?scidkb
  en-us833770
• Test appropriately to ensure throughput is
  sufficient
• The involvement of a network may introduce
  components not typically viewed as high speed I/O
  paths
• The iSCSI devices appear as ordinary drives
• Ensure latency involving the iSCSI traffic is
  minimized

26
Physical Isolation

• Best practice Separate log files from data files
  at the physical disk level
• However, this may not be practical or even
  possible in some deployments (i.e. consolidation)
• Dependent on specific configuration and I/O
  performance requirements of the deployment
• For consolidation consider I/O characteristics
  and group similar I/O characteristics (i.e. all
  logs )
• Combining heterogeneous workloads (workloads with
  very difference latency characteristics) can have
  negative effects on overall performance

27
Tempdb Isolation, Configuration Monitoring (1)

• TempDB Stores
• User Objects (,,_at_)
• Internal Objects (Hash Join, Sorting, Cursors,
  Lob)
• Row Versions (Triggers, On-line index ops,
  Snapshot isolation)
• Use SYS.DM_DB_FILE_SPACE_USAGE to figure out
  each Objects space
• Common Issues
• Running Out Space
• Slow Queries Due To I/O Bottleneck
• Bottleneck In The System Tables Due to Excessive
  DDL Operations
• Tempdb Placement (Dedicated Vs. Shared Physical
  Disks)
• Unless You Understand Tempdb I/O Characteristics
  Its Better to Place Tempdb on Shared Spindles
  and Utilize More Cumulative Disks
• Understand Your Own Tempdb Usage

28
Tempdb Isolation, Configuration Monitoring (2)

• Create 1 Data File Per CPU On Host Server
• Distributes Latches On The PFS And SGAM Pages
• Data Files Should Be of Equal Size
• Pre-size Data/Log Files Do No Rely On AUTOGROW
  (Unless Testing To Determine How Much Space Will
  Be Needed)
• T 1118 Disables Mixed Page Allocations
• Full Extents Are Allocated To Each Tempdb Object
  Eliminating Contention On SGAM. Causes Some Waste
  Of Disk Space In Tempdb
• Diverse Results Encountered, Depending On The
  Load Level
• Test And Refer Additional Resources On Tempdb
  Scalability
• Related DMVS
• Sys.Dm_db_session_space_usage - allocated /
  deallocated pages by session
• Sys.Dm_db_task_space_usage - page allocation /
  deallocation activity by task
• Sys.Dm_exec_requests - info about each request
  that is executing

29
Tempdb Isolation, Configuration Monitoring (3)

• Monitoring TempDB by
• Perfmon sqlservertransactions/Free space
  inTempdb (2005 new counter)
• User Objects Space With Sp_SpaceUsed
• Internal Objects Space With Sys.Dm_db_session_spac
  e_usage (Page allocation by session) ot
  Sys.Dm_db_task_space_usage (allocation by task)
• Version stores with sys.dm_tran_active_snapshot_da
  tabase_transactions
• Latching (PFS SGAM) with sys.dm_os_waiting_task
  s (on resource '2 and wait_type of
  'PAGELATCH_' )
• Make Sure To Have and Dedicate Sufficient Place

TempDB Further Reading
http//www.microsoft.com/technet/prodtechnol/sql/2
005/workingwithtempdb.mspx
30
Managing Growth

• Depends on features offered by storage array
• Many newer storage arrays offer the ability to
  dynamically grow a LUN consult with your
  storage vendor
• Two types of growth
• Capacity vs. Additional performance (more
  physical disks)
• Windows perspective
• Basic or Dynamic disks Either can be expanded
• However dynamic striped volumes cannot be
  extended
• Basic disks can be expanded using Diskpart.exe
  (see appendix for KB articles)
• Changes to underlying LUNs are automatically
  recognized by Windows

31
Clustering Considerations

• SQL Server 2005 supports mount point volumes
  (requires Windows 2003)
• A mounted volume, or mount point, allows a single
  drive letter to refer to many disks or volumes.
• For a drive letter (G) you can connect, or
  "mount," additional disks or volumes as
  directories under drive letter G without the
  additional disks or volumes requiring drive
  letters of their own.
• This Solves the 25 letters per - disk Issue
• Not supported for SQL Server 2k Failover Clusters
  (on either Windows 2000 2003)
• No support for Dynamic disks
• Refer to the Windows HCL to make sure the
  hardware has been certified as a whole
  http//www.microsoft.com/whdc/hcl/default.mspx
• Other considerations for clustering
• Microsoft Windows Clustering Storage Area
  Networks http//www.microsoft.com/windowsserver200
  3/techinfo/overview/san.mspx

32
Compression and SQL Server

• Compression Supported for Read-only Databases or
  Read-only Secondary Filegroups
• Data Performance Issues
• Read
• Less data to read from disk improves I/O
  performance But Decreases CPU performance Due to
  processing time
• Write
• Performance Primary Issue Due to Expanding IOs
  Re-Writing a Single Value May Cause Rewrite of
  Mbs or More
• Expect 2-25 Times Total Time Degradation for a
  3GB Table Scan
• Log Correctness Issues
• Do Not Compress Log Files

33
Compression and SQL Server

• Performance Test
• Win 2003 ,Quad Xeon 700mhz Processor Machine, 5
  Disks At 10,000 RPM
• Test Performed A Scan By Using A Simple SELECT
  COUNT() On A 90k Rows Table
• Compression Ratio Was 8.0 To 1.

• Usage - In Situations Where Large Portions of The
  Database Contain Historical Information That is
  Used Only for Analysis or Forecasting, and There
  Is Limited Disk Space.
• Sql Server 2008 Improves Backup Performance Via
  Compression

34
Remote Mirroring

• Use Of Third Party Remote Mirroring Technology To
  Maintain Copies Of Data
• Support Primarily Third Party
• Might Be Cache-based Or Actual Io Is Required On
  The Mirror
• Note! When Data And Log Are Placed On Different
  Mirrored Sets Extended Mirror Capabilities Are
  Needed To Maintain Write-ordering
• Third Party Solution Must Meet Microsoft SQL
  Server Storage Solution Requirements
• SQL Server Always On Partner Offering
• For More Information About The Program And
  Updated List Of Partners, See www.microsoft.com/SQ
  L/AlwaysOn

35
Agenda

• SQLIO basics and IO characteristics
• General storage configuration best practices
• Monitoring and Base-lining I/O Subsystems
• QA

36
Monitoring IO Performance (1)

• Understand potential throughput, know your
  hardware
• Each component in the path has associated
  bandwidth
• Know where the potential bottlenecks exist

PCI Bus ? HBA ? Fiber Channel Ports ? Array
Processors ? Disks
37
Monitoring IO Performance (2)

• Host Performance Monitor sys.dm_io_virtual_file
  _stats
• Establish ongoing monitoring plan and capture
  baseline data
• Identify I/O problems
• sys.dm_io_virtual_file_stats provides I/O details
  on a per file basis (Formally Fn_virtualfilestats)
  
• Historical IO Report within Sql Server
  Dashboard Reports provides General DB / Top
  Object Level IO Info
• Storage Array Vendor specific
• Cache Utilization, Hotspots, Disk Processor
  Utilization
• Useful for in depth troubleshooting trend
  analysis
• Used to determine root cause of I/O problems or
  if capacity of storage has been reached

38
Monitoring IO Performance Perfmon Counters
39
Monitoring IO Performance Perfmon Counters Con.

• Focus on
• Throughput for Sequential operation
• IOPS for Random Operations

40
Monitoring IO Performance sys.dm_io_virtual_file
_stats
41
Monitoring IO Performance Dashboard Reports
42
Validate Storage Configurations - Tools

• Sqliosim.Exe
• Correctness And Stress Tool.
• Sqliosim.Exe Simulates Various Patterns Of SQL
  Server 2005 I/O Behavior To Ensure Rudimentary
  I/O Safety
• Usage To Verify That Your I/O System Is
  Functioning Correctly Under Heavy Loads.
• Sqlio.Exe
• Sqlio.Exe Is A SQL Server 2005 I/O Utility Used
  To Establish Basic Benchmark Testing Results
• Purpose - To Test Maximum Throughput Achievable
  By The IO Subsystem For Common SQL Server IO
  Types.

43
Validate Storage Configurations - SQLIO

• SQLIO.exe Is A Free, Unsupported Tool Provided By
  Microsoft
• Test And Validate The Performance Of Each Storage
  Configuration Before Deploying SQL Server
  Application (Common Pitfall)
• Benchmark Performance And Shake Out
  Hardware/Driver Problems Early In The
  Configuration
• Test A Variety Of I/O Types And Sizes
• Make Sure Your Test Files Are Significantly
  Larger Than The Amount Of Cache On The Array
• Exception If You Are Testing Channel Throughput
  In Which Case Use Files That Will Fit In Array
  Cache
• Test Each I/O Path Individually And Then
  Combinations Of The I/O Paths
• Relatively Short Tests Are Okay, However, Longer
  Runs May Give A More Complete Understanding Of
  How The Storage Will Perform
• Allow Time In Between Tests To Allow The Hardware
  To Reset (Cache Flush)
• Keep All Of The Benchmark Data To Refer To After
  The SQL Implementation Has Taken Place

44
Validate Storage Configurations ToolsSQLIO
Sample Commands

• OLTP Type IO's
• call sqlio -kW -s60 -frandom -o32 -b8 -LS
  -F1.txt
• call sqlio -kW -s60 -frandom -o32 b64 -LS
  -F1.txt
• call sqlio -kW -s60 -fsequential -o32 b64 -LS
  -F1.txt
• call sqlio -kR -s60 -frandom -o32 -b8 -LS
  -F1.txt
• call sqlio -kR -s60 -frandom -o32 b64 -LS
  -F1.txt
• call sqlio -kR -s60 -fsequential -o32 b64 -LS
  -F1.txt
•  LOG TYPE IO's
• call sqlio -kW -s60 -fsequential -o2 -b4 -LS
  -F1.txt
• call sqlio -kW -s60 -fsequential -o2 -b8 -LS
  -F1.txt
• call sqlio -kW -s60 -fsequential -o2 -b16 -LS
  -F1.txt
• call sqlio -kW -s60 -fsequential -o2 -b32 -LS
  -F1.txt
• call sqlio -kW -s60 -fsequential -o2 -b64 -LS
  -F1.txt
•  
• RDW Type IO's(relational dataware house)
• call sqlio -kR -s60 -fsequential -o8 -b64 -LS
  -F1.txt
• call sqlio -kR -s60 -fsequential -o8 -b128 -LS
  -F1.txt

The file 1.txt should have the disks you want to
test Example file content d\sqlio_test.dat 4
0x0 100 e\sqlio_test.dat 4 0x0 100
File name Location
Threads connections
ltMask gt Set to 0x0
Test File size In MB
45
Validate Storage Configurations - SQLIO
Disk Saturation Gradually Increase Number of
Outstanding I/Os (-O) Until The Path Is
Saturated. Saturation - When Latency Increases
But Throughput Stays The Same.
HBA Saturation Typical HBAs have a bandwidth of
2 Gb/s (200 MB/s) We have observed practical
throughput in the range of 180190 MB/s.
46
Example 1 Data Warehouse (1)

• CLARiiON CX700 Array
• 135 Physical Disks (10K Rpm / Mix of 146GB and
  300GB)
• 4GB cache configured 80/20 (read/write ratio)
• 8 LUNs for Data (1 TB each)
• 4 LUNs assigned to each array service processor
• NEC S4300 Array
• 18 Physical Disks (15K Rpm / 36 GB)

• Host configuration
• HP DL740 8 CPU 3.0 Ghz Xeon w/ 16GB RAM
• 5 Emulex LP9002 HBAs (2 GB/sec)
• EMC Powerpath load balancing I/O requests to
  CX700 Array
• Tempdb / Log on separate array (exhausted
  capacity of CX700)
• Database Characteristics
• Mix of ETL, Reporting
• Two databases 3.5 TB each

47
Example 1(2) Physical Disk Layout CX700

• Eight RAID Groups (RAID 10) each containing 16
  physical disks (8 8)
• One LUN for data files from each RAID Group (RG0
  RG7) (CPU Correlated)
• Striping across data spindles achieved via SQL
  Server data files
• Vertical Raiding increases availability in case
  of an Entire Array Failure
• One RAID group for SATA drives (used for backups)

48
Example 1 Data Warehouse (3)

• First Test Done With Only 2 Hbas 400 MB/Sec
  (For Sequential Scan)
• After Adding 2 Hbas
• 750 MB/Sec Peak Scan Performance During Storage
  Validation
• 500 MB/Sec Peak Read Throughput During Real
  Workload
• 5-6ms Avg. Read Latency

49
Example 2 OLTP (1)
EMC DMX3 Symmetric SAN 10K RPM 146 GB 960
Spindles 128 GB Mirrored Cache
HP DL585 Model x64 32 GB
RAID 10 Data /Index 3 LUNs 17 GB x 3 RAID
1 Log 1 LUN 118 GB RAID 5
Backup 1 LUN 540 GB
4 Dual Ports HBA
Total 6 Servers Each with One SQL Server
Database 24 Partitions 1 Clustered Index
50
Example 2 OLTP (2)
SQLIO Test Results
SQL Server 2005 Configured with 0.5 GB memory to
force I/O 1 HBA Selects Updates
51
Agenda

• SQLIO basics and IO characteristics
• General storage configuration best practices
• Monitoring and Base-lining I/O Subsystems
• QA

52
QA
53
Key Takeaways

• Engage the storage vendor early in the design
• Get input on designing for growth and performance
• Attempt to keep storage configurations as simple
  as practical
• No single way to design for performance, this is
  dependent on the specifics of the scenario
• Understand system I/O components capacities and
  potential bottlenecks
• Benchmark and resolve any issues with I/O system
  prior to SQL Server application deployment
• Monitor your I/O performance
• Windows / SQL Tools to identify problem space and
  utilize storage vendor tools for debugging and
  resolution

54
Tools

• Diskpar.exe
• Windows 2000 Companion CD
• http//support.microsoft.com/kb/927229
• SQLIO
• Used to stress an I/O subsystem Test a
  configurations performance
• http//www.microsoft.com/downloads/details.aspx?Fa
  milyId9A8B005B-84E4-4F24-8D65-CB53442D9E19displa
  ylangen
• SQLIOSim
• Simulates SQL Server I/O Used to isolate
  hardware issues
• KB231619 How to use the SQLIOSim utility to
  simulate SQL Server activity on a disk subsystem
  http//support.microsoft.com/kb/231619
• Fiber Channel Information Tool
• Command line tool which provides configuration
  information (Host/HBA)
• http//www.microsoft.com/downloads/details.aspx?Fa
  milyID73d7b879-55b2-4629-8734-b0698096d3b1displa
  ylangen

55
Additional References

• Scalability and VLDB Resources on Microsoft.com
• http//www.microsoft.com/sql/technologies/highavai
  lability/default.mspx
• 824190 Troubleshooting Storage Area Network (SAN)
  Issues
• http//support.microsoft.com/?id824190
• Microsoft Windows Clustering Storage Area
  Networks
• http//www.microsoft.com/windowsserver2003/techinf
  o/overview/san.mspx
• 280297 How to Configure Volume Mount Points on a
  Clustered Server
• http//support.microsoft.com/?id280297
• 819546 INF SQL Server support for mounted
  volumes
• http//support.microsoft.com/?id819546
• 304736 How to Extend the Partition of a Cluster
  Shared Disk
• http//support.microsoft.com/?id304736

56
Additional References (2)

• Virtual Device Interface Specification
• http//www.microsoft.com/downloads/details.aspx?Fa
  milyID416f8a51-65a3-4e8e-a4c8-adfe15e850fcDispla
  yLangen
• SQL Server Consolidation on the 64-Bit Platform
• http//www.microsoft.com/technet/prodtechnol/sql/2
  000/deploy/64bitconsolidation.mspx
• SQL Server Consolidation on the 32-Bit Platform
  using a Clustered Environment
• http//www.microsoft.com/technet/prodtechnol/sql/2
  000/deploy/32bitconsolidation.mspx
• Windows Server System Storage Home
• http//www.microsoft.com/windowsserversystem/stora
  ge/default.mspx
• Microsoft Storage Technologies Multipath I/O
• http//www.microsoft.com/windowsserversystem/stora
  ge/technologies/mpio/default.mspx

57
Additional References (3)

• Storport in Windows Server 2003 Improving
  Manageability and Performance in Hardware RAID
  and Storage Area Networks
• http//www.microsoft.com/windowsserversystem/wss20
  03/techinfo/plandeploy/storportwp.mspx
• INF Support for Network Database Files
• http//support.microsoft.com/default.aspx?scidkb
  en-us304261
• SQL Server 2000 I/O Basics whitepaper
• http//www.microsoft.com/technet/prodtechnol/sql/2
  000/maintain/sqlIObasics.mspx
• Updated Books Online for SQL Server 2005
  http//www.microsoft.com/technet/prodtechnol/sql/2
  005/downloads/books.mspx
• Shared Scalable Database
• http//support.microsoft.com/?kbid910378

58
Resources
59
Thank you for listening yonio_at_srl.co.il