Jimmy May Aspiring Geek Disk Partition Alignment for

  • Slides: 63
Download presentation
Jimmy May, Aspiring Geek Disk Partition Alignment for SQL Server® The Best Kept Secret

Jimmy May, Aspiring Geek Disk Partition Alignment for SQL Server® The Best Kept Secret in SQL Server ® I/O Performance Increase I/O Throughput By 10%, 15%, 30%, or More

Session Objectives & Takeaways • Session Objectives: • Learning how to Increase I/O Throughput

Session Objectives & Takeaways • Session Objectives: • Learning how to Increase I/O Throughput By 10%, 15%, 30%, or More The Best Kept Secret in SQL Server® I/O Performance • Demonstrating the value of performing partition alignment • Configuring partition alignment

Session Objectives & Takeaways • Partition alignment is a best practice & provides significant

Session Objectives & Takeaways • Partition alignment is a best practice & provides significant benefits • The following three must be correlated: • Partition starting offset • Stripe unit size • File allocation unit (cluster) size • Analysis of existing partitions is different for: • Windows Basic Disks • Windows Dynamic Disks • 3 rd Party Dynamic Disks • Windows Server 2008 commits alignment by default • Existing partitions must be re-built • Partition alignment will remain relevant until Windows Server 2003 is deprecated & existing partitions are re-built.

Presenter Bio Jimmy May, MCM, MCITP: DBA + DB Dev, MCDBA, MCSE Principal Performance

Presenter Bio Jimmy May, MCM, MCITP: DBA + DB Dev, MCDBA, MCSE Principal Performance Engineer: SQL Server Application Consulting & Engineering (A. C. E. ) Services Microsoft Corporation jimmymay@microsoft. com http: //blogs. msdn. com/jimmymay www. twitter. com/aspiringgeek Subscribe! Microsoft Certified Master: SQL Server 2008 Microsoft MS IT Gold Award (2008) Microsoft Oracle Center of Excellence (2007) Microsoft MVP Nominee (2006) SQL Innovator Award Recipient (2006) Contest sponsored by Microsoft & Indy. PASS Indiana Windows User Group www. iwug. net Founder & Board of Directors Indianapolis Professional Association for SQL Server www. indypass. org Founder & Member of Executive Committee SQL Server Pros www. sqlserverpros. com Founder & Visionary-in-Chief Formerly Chief Database Architect for high-throughput OLTP VLDB

Introduction • Now that SQL Server wait stats are formally documented & DMVs are

Introduction • Now that SQL Server wait stats are formally documented & DMVs are available, partition alignment may be the best kept secret related to SQL Server database performance optimization. • In combination with stripe unit size & file allocation unit size, learn how you can increase I/O throughput by 10%, 15%, 30%, & reportedly even under some common circumstances up to 50% or more.

The Joy of Public Speaking Source: Scott Adams (& Buck Woody)

The Joy of Public Speaking Source: Scott Adams (& Buck Woody)

Agenda Introduction Terms The Best Kept Secret HDD Architecture Blackbox I-III 63 Hidden Sectors

Agenda Introduction Terms The Best Kept Secret HDD Architecture Blackbox I-III 63 Hidden Sectors The Problem Default vs. Aligned Partitions Performance Impact Good News/Bad News Partition Alignment Template & Example Common Partition Offsets 4 Ways to Determine Partition Offset for Basic Disks Dynamic Disks are Different 3 Key Fundamentals File Allocation Unit Size Partition Offset & Stripe Unit Size & File Allocation Unit Size Alignment Summary Help is Here: Windows Server 2008 Vendor Considerations I & II Our Responsibilities I & II References Acknowledgements Please read the notes accompanying many of these slides

Preface: Many Factors Affect Disk I/O Perf There are myriad best practices & considerations

Preface: Many Factors Affect Disk I/O Perf There are myriad best practices & considerations for optimal disk I/O subsystem performance. Be mindful of factors such as: RAID level File allocation unit size Number, size, & speed of disks Configuration & capacity of HBAs & fabric switches Consider increasing HBA Queue Depth Network bandwidth Cache on disk, controllers, & SAN Whether disks are dedicated, shared, or virtualized Bus speed Number of paths from disk I/O subsystem to server Driver versions for all components Stripe size Stripe unit size Workload

The Best Kept Secret in SQL Server I/O • Of my 60+ external engagements

The Best Kept Secret in SQL Server I/O • Of my 60+ external engagements • Four customers have heard of & properly implemented partition alignment on SQL Server. • Steven Wort, Microsoft Software Development Engineer • “The area is bit of a black hole. . . "

Applies To: • HDD Configurations • • MBR Basic GPT Basic MBR Dynamic GPT

Applies To: • HDD Configurations • • MBR Basic GPT Basic MBR Dynamic GPT Dynamic • All Disks • Created by Windows Server 2003 & Earlier • Focus Today • MBR Basic • MBR Dynamic

Terms, Definitions, Descriptions • Hard Drive: HDD or disk, non-volatile storage device which stores

Terms, Definitions, Descriptions • Hard Drive: HDD or disk, non-volatile storage device which stores digitally encoded data on rapidly rotating platters with magnetic surfaces. • Platter: The “disks” in HDDs. Two or more per device. • Cylinder: All tracks which can be accessed by the heads while the access arms are stationary. • Track: Concentric rings on a platter. • Sector • • Wedge-shaped sections of a platter, historically 64 sectors per track. Bits which lie at the intersection of a track & a sector, historically (but not always) 512 bytes. • This is the definition we will use most. • File Allocation Unit (Cluster): Some integral number of 512 byte sectors which are treated as a unit by the OS.

Daffynitions Misunderestimating the Language What we got here is. . . a failure to

Daffynitions Misunderestimating the Language What we got here is. . . a failure to communicate. —Boss Paul, in Cool Hand Luke, 1967, starring Paul Newman • Terms which are commonly used ambiguously & interchangeably— sometimes reflecting accepted definitions—sometimes not. • • • As stated above, sectors can be the wedge-shaped areas of a platter. As stated, 512 byte entities are sometimes called sectors. Other terms include block or cluster. Block can also refer to file allocation units. File allocation units can be called clusters. Tracks can be cylinders, & vice verse • A common framework of terms is important. Communicate terms & usage up front—exactly what I'm doing here. • Today: • • • Track: Concentric rings on a platter. Sector: What is usually but not always now 512 B; Intersection of track & what used to be a wedge-shaped section of a platter. File allocation unit & cluster: Some integral number of 512 byte sectors which are treated as a unit by the OS.

Historical HDD Architecture Disks used to look something like this: Cylinder Track Sector (?

Historical HDD Architecture Disks used to look something like this: Cylinder Track Sector (? ) Sector (!) Note: In order for there to be a constant number of blocks in each track, the density of blocks in the outer tracks has to be a lot less than that of the blocks in the inner tracks, resulting in a lot of wasted space. Graphics source: Karan Mehra

“Modern” HDD Architecture: ZBR • Disks evolved. To eliminate wasted space, disk drive manufacturers

“Modern” HDD Architecture: ZBR • Disks evolved. To eliminate wasted space, disk drive manufacturers started using a technique called zoned bit recording (ZBR). • For this simplified graphic: • The inner tracks have 16 sectors. • The outer tracks have 30 sectors. • The neat & pretty pie slice-shaped sections are gone— but storage is more efficient. Graphics source: Karan Mehra

HDD Architecture: 3 -D This image is from a contemporary & otherwise excellent document,

HDD Architecture: 3 -D This image is from a contemporary & otherwise excellent document, but it represents disks as they were over two decades ago! The disk deities at Microsoft won’t allow me to perpetrate such myths. Graphics source: Veritas Storage Foundation™ 5. 0 for Windows Best Practices for Storage Management http: //eval. symantec. com/mktginfo/enterprise/white_papers/ent-whitepaper_vsfw_5. 0_best_practices_for_storage_mgmt_02 -2007. en-us. pdf

Blackbox I • We must remain mindful of Bruce Worthington's blackbox metaphor • This

Blackbox I • We must remain mindful of Bruce Worthington's blackbox metaphor • This onion has many layers • Remember The Matrix? • When Morpheus and Neo meet, Morpheus offers Neo two pills. • The red pill will answer the question, “What is the Matrix? ” • The blue pill will allow Neo to carry on as before. • As Neo reaches for the red pill Morpheus warns, “Remember, all I'm offering is the truth. Nothing more. ” • If you prefer a glimpse of the truth, see Bruce Worthington’s paper: Performance Tuning Guidelines for Windows Server 2008 http: //www. microsoft. com/whdc/system/sysperf/Perf_tun_srv. mspx Graphics Source: http: //www. arrod. co. uk/essays/matrix. php

Blackbox II • Today, disks are actually so complex as to be elude most

Blackbox II • Today, disks are actually so complex as to be elude most simple characterizations. • Bruce Worthington, Windows Principal Development Lead, makes promiscuous use of the blackbox metaphor to characterize disk internals. • We must rely on abstractions to talk about disks. • The problem is complicated because many of the tools we use are only partially reliable. • diskpar is deprecated & has typos—but it’s accurate • diskpart is used to perform alignment, but it’s NOT reliable for determining partition offset because of lack of precision (KB vs. bytes) • msinfo 32, msinfo 64, winmsd, wmic, etc. report tracks/sector = 63 • None of these report partition starting offsets for dynamic disks— no one tells you that dmdiag -v is required for that vendor-specific tools may be required

Blackbox III • As a result of these limitations and assumptions, blocks of storage

Blackbox III • As a result of these limitations and assumptions, blocks of storage (e. g. , a file system cluster) as defined by the NTFS. SYS driver can be misaligned with blocks of storage found in the hardware (e. g. , a controller cache line or the stripe unit size of a disk array) • Bruce Worthington, Windows Principal Development Lead • There is no alignment to physical sectors no matter what we believe. That went out around 1984—disks do not map sectors to physical regions in a way that we can understand. The “geometry” reported is an artifice. Cache segment alignment—on disk & on controller—is a more important consideration. • Jeff Goldner, MS Principal Group Manager for Storage

The Reality • The concepts described here apply to SQL Server data & log

The Reality • The concepts described here apply to SQL Server data & log disk I/O subsystems from which high performance is required. • This deck provides supporting: • Documentation • Quotes from people who are really, really smart • Experimental evidence • Real-life results

Preface to the Problem: -- Disks Tell Windows There are 63 Hidden Sectors --

Preface to the Problem: -- Disks Tell Windows There are 63 Hidden Sectors -- Windows Believes What It is Told Output of diskpar (Windows 2000 Resource Kit) C: >diskpar -i 0 ---- Drive 0 Geometry Infomation ---Cylinders = 12161 Tracks. Per. Cylinder = 255 Sectors. Per. Track = 63 Bytes. Per. Sector = 512 Disk. Size = 100027630080 (Bytes) = 95393 (MB) ---- Drive Partition 0 Infomation ---Statring. Offset = 32256 Partition. Length = 49319424 Hidden. Sectors = 63 Partition. Number = 1 Partition. Type = de – By default, for years Windows instantiated 63 hidden sectors in all new partitions. – These hidden sectors contain the master boot record (MBR). – Note the typos: • • “Statring. Offset” “Infomation”

And 63 Sectors < 64 Sectors • 63 simply doesn’t line up well with

And 63 Sectors < 64 Sectors • 63 simply doesn’t line up well with fundamental physical boundaries • When Windows wrote the first chunk of data to a new partition: • It wrote the first 512 B sector to the 64 th sector of the first track. • It wrote the rest to the second track. • • • This single write request required access to both tracks. To read this data required accessing both tracks. Partition alignment remedied this situation. . Fast forward to contemporary RAID arrays. . . We need to talk about stripe units, not tracks. Let's look at some pictures. . .

This Might Appear Complicated. . . Source: Scott Adams (& Buck Woody)

This Might Appear Complicated. . . Source: Scott Adams (& Buck Woody)

Partition Alignment Graphic: NTFS 4 KB Cluster: Default vs. Aligned RAID Array ***This has

Partition Alignment Graphic: NTFS 4 KB Cluster: Default vs. Aligned RAID Array ***This has CONTEMPORARY RELEVANCE*** • • • This is a very simplified graphic Contemporary relevance Corresponds to default NTFS file allocation unit of 4 KB Given common 64 KB stripe unit size See the Notes for details Graphics Source: Jimmy May

Partition Alignment Graphic: RAID Array: Default vs. Optimized for SQL Server ***This has CONTEMPORARY

Partition Alignment Graphic: RAID Array: Default vs. Optimized for SQL Server ***This has CONTEMPORARY RELEVANCE*** • This is a very simplified graphic • Mark Licata, Senior Technology Architect • The worst scenario? Random operations using 64 K IO and 64 K chunk size. One sector off and you are hitting two disks for every IO thus halving the random performance potential. • Note: On a RAID array this means accessing two different stripe units on two separate disks. Graphics Source: Jimmy May

Performance Impact: Words of Wisdom • • Kevin Cox • Member of Lubor Kollar’s

Performance Impact: Words of Wisdom • • Kevin Cox • Member of Lubor Kollar’s SQL Server Customer Advisory Team (SQL CAT); recently gained notoriety by loading a terabyte of data in less than one hour. • “All customers must do sector alignment for SQL machines. I don’t know why this doesn’t come standard by default, but it should. If they are not aligned, you will often get 2 disk IOs to get a block instead of 1. ” Joe Chang, SQL Server performance expert • • “On disk arrays, alignment is important, often 20% to 30% or more" An updated version of the Disk Partition tool for Windows Server 2003 is available • http: //support. microsoft. com/default. aspx? scid=kb; en-us; 923076&sd=rss&spid=3198 • “Disk alignment is a required optimization and must be applied by OEMs during Setup. Disk alignment provides a significant increase in system performance. Failure to perform disk alignment can decrease performance by 10 to 15 percent in RAID array systems. ” • This is the definitive KB article by Robert Smith

Performance Impact Graphic: A Picture’s Worth. . . Disk Alignment Experiment Latency & Duration

Performance Impact Graphic: A Picture’s Worth. . . Disk Alignment Experiment Latency & Duration on RAID 10, 64 KB file allocation unit 6 disks vs. 8 disks Not Aligned vs. Aligned 6 aligned disks performed as well as 8 non-aligned disks. Thus, efficiencies of ~30% were achieved.

Performance Impact (cont. ) • My experiment was simple, yet convincing. • The results

Performance Impact (cont. ) • My experiment was simple, yet convincing. • The results were consistent & significant. • Work by others have shown improvements from 10% to 40%. • Reports of 50% have been reported to me. • Jeff Goldner, Microsoft Principal Group Manager for Storage • We’ve seen up to 90% performance penalty on RAID 5 writes due to misalignment. This varies widely so it’s tough to make generalizations. • Your mileage may vary!

Performance Impact of 3 Best Practices: Disk Alignment + 64 KB FAU + RAID

Performance Impact of 3 Best Practices: Disk Alignment + 64 KB FAU + RAID 10 • At a subsequent customer site, I convinced my customer to rebuild his entire disk I/O subsystem compliant w/ 3 best practices. • Enhanced Metrics: • Serial throughput: 31% • Random Write latency: 200%-250% Random Read latency: 10% • Sequential Write IOPs by File: 120% • Random Write IOPs by File: 10 -50% • Random Write MB/s: 100% • Sequential Write MB/s: 140%

Performance Impact in the Wild • Hitachi High-End SANs • 8% – 12% •

Performance Impact in the Wild • Hitachi High-End SANs • 8% – 12% • Financial Firm • 40% faster ETL • Re-built Multi-TB Share. Point Backend • Telecom Firm • • Maintenance: 4 hours >> 90 minutes 1 M Row Insert into 140 M Row table: 6: 59 >> 0: 52 1 M Row Insert into 500 M Row table: 40% faster Re-built Dozens of TBs of Data • Wireless telecom firm • I/Os: 23. 4% • Duration: 20. 3%

Summary of Perf Improvements Scenario Performance Enhancement Workload on new production system 30% in

Summary of Perf Improvements Scenario Performance Enhancement Workload on new production system 30% in terms of latency & duration Enterprise SAN storage array 8% - 12% for average workloads SQLIO on high-end SAP system Random 8 KB writes: 18% Sequential 64 KB writes: 26% Sequential 256 KB writes: 23% SQLIO on test system 34% for random reads Major financial firm 30% - 40% as measured by several metrics Major telecom firm I/Os: 40% Maintenance tasks: 4 hours down to 90 minutes One million row inserts into a 140 million row table: From 6: 59 to 0: 52 Inserts into tables with 500 million rows: 30% Wireless telecom firm I/Os: 23. 4% Duration: 20. 3%

Good News / Bad News • Good news: Partition alignment is simple to perform.

Good News / Bad News • Good news: Partition alignment is simple to perform. • Bad news: Partition alignment must be done prior to disks being formatted. • This is great if you have a new SAN. • But it might be painful to convert large amounts of existing data on misaligned partitions.

Partition Alignment Template diskpart list disk select disk <Disk. Number> create partition primary align=<Offset_in_KB>

Partition Alignment Template diskpart list disk select disk <Disk. Number> create partition primary align=<Offset_in_KB> assign letter=<Drive. Letter> format fs=<file-system> label=<"label"> unit=<File. Allocation. Unit. Size> nowait • Diskpart /align debuted in Windows Server 2003 SP 1 • If necessary, use diskmgmt. msc to map partition numbers to drive letters. • The nowait option forces the command to return immediately while the format continues. • The format command from within diskpart (Windows Server 2008 only) & from the command line are syntactically distinct.

Partition Alignment Example & Demo C: diskpart Microsoft Disk. Part version 6. 0. 6000

Partition Alignment Example & Demo C: diskpart Microsoft Disk. Part version 6. 0. 6000 On computer: ASPIRINGGEEK DISKPART> list disk DISKPART> select disk 3 DISKPART> create partition primary align=1024 DISKPART> assign letter=E DISKPART> format fs=ntfs unit=64 K label="My. Fast. Disk" nowait

Partition Offsets • The classic, default misaligned offset • 32, 256 bytes (less than

Partition Offsets • The classic, default misaligned offset • 32, 256 bytes (less than 32 KB) • The following is a common re-defined offset—but it doesn’t always correlate well with stripe unit size • 32, 768 bytes (exactly 32 KB) • More on this later! • The following are common usually valid aligned offsets for SQL Server • 65, 536 bytes (exactly 64 KB) • 131, 072 bytes (exactly 128 KB) • 262, 144 bytes (exactly 256 KB) • 1, 048, 576 bytes (exactly 1 MB)

4 Valid Ways to Determine Partition Offset for Basic Disks—& 1 Way NOT To

4 Valid Ways to Determine Partition Offset for Basic Disks—& 1 Way NOT To • • msinfo 32, msinfo 64, winmsd WMI , WMIC diskpar (Windows 2000 Server RK) (deprecated) diskpart (Windows Server 2003) • Use diskpart to configure alignment, but NOT to report offset because it reports in KB not bytes! • For Basic Disks, WMIC is the simplest & preferred method

msinfo 32, msinfo 64, or winmsd • Execute msinfo 32 or the equivalent from

msinfo 32, msinfo 64, or winmsd • Execute msinfo 32 or the equivalent from the Run. . . Dialog • Drill down in the navigation pane • Components > Storage > Disks • Partition info is displayed in the details pane as “Partition Starting Offset” (not shown) • Note: This rich source of system information can be saved as a *. nfo file

WMIC • Execute via command line C: >wmic partition list full Thanks to Sr.

WMIC • Execute via command line C: >wmic partition list full Thanks to Sr. PFE Uttam Parui for this tip!

WMIC (cont. ) • The preferred method! • Execute via command line C: >

WMIC (cont. ) • The preferred method! • Execute via command line C: > wmic partition get Block. Size, Starting. Offset, Name, Index

diskpart list partition Usage • Note the terminal “t” in “diskpart” • Ships with

diskpart list partition Usage • Note the terminal “t” in “diskpart” • Ships with Windows Server 2003 RTM • diskpart is the tool of choice to perform alignment • Use caution in the use of diskpart to obtain alignment info • Offset info is in KB—too imprecise for our purposes • Both of the following are returned by diskpart as 32 KB • 32, 256 bytes: Default misalignment • 512 bytes less than exactly 32 KB • 32, 768 bytes: Valid alignment, exactly 32 KB • Compare output of diskpar on previous slide to output of diskpart on the next slide.

diskpart list partition Output • Repeat: Use caution when using for alignment info •

diskpart list partition Output • Repeat: Use caution when using for alignment info • Offset info is in KB—too imprecise for our purposes C: WindowsSystem 32>diskpart Microsoft Disk. Part version 6. 0. 6000 On computer: ASPIRINGGEEK DISKPART> list disk Disk ### Status ---------- Disk 0 Online Size Free ------- 93 GB 1024 KB Dyn --- Gpt --- DISKPART> select disk 0 Disk 0 is now the selected disk. DISKPART> list partition Partition ### Type Size Offset ---------------- Partition 1 OEM 47 MB 32 KB Partition 2 Primary 10 GB 48 MB Partition 3 Primary 81 GB 10 GB Partition 0 Extended 2048 MB 91 GB

Dynamic Disks Analysis • Dynamic disks “spoof” basic disk tools • Invalidates previous methods

Dynamic Disks Analysis • Dynamic disks “spoof” basic disk tools • Invalidates previous methods • None of the tools cited above produce reliable input • Previously aligned partitions appear to have invalid offset of 32, 256 bytes • Note: If disks are aligned before conversion, the alignment reportedly “sticks” • Dynamic Disks have two flavors • Windows Dynamic Disks • 3 rd-Party Dynamic Disks

Windows Dynamic Disks • dmdiag. exe -v must be used • “Verbose” switch is

Windows Dynamic Disks • dmdiag. exe -v must be used • “Verbose” switch is required! • Complex Output • Relevant Sections • --- Dynamic Disk Information -- • Rel Sec column • --- LDM Volume Information -- • Rel Sectors column

3 rd Party Dynamic Disks • 3 rd Party Disk Admin tools becoming common

3 rd Party Dynamic Disks • 3 rd Party Disk Admin tools becoming common • e. g. , Veritas Enterprise Administrator • Not only are basic tools invalid. . . • . . . dmdiag output is invalid • Out-of-scope for this presentation • You MUST Contact Your Vendor for details • Stay tuned for more. . . • http: //blogs. msdn. com/jimmymay

Getting the file allocation unit size • fsutil fsinfo ntfsinfo c: • Output for

Getting the file allocation unit size • fsutil fsinfo ntfsinfo c: • Output for default NTFS format C: >fsutil fsinfo ntfsinfo c: NTFS Volume Serial Number : 0 x 3 a 16 ff 9 d 16 ff 5879 Version : 3. 1 Number Sectors : 0 x 00000 a 2397 ff Total Clusters : 0 x 0000014472 ff Free Clusters : 0 x 0000025 b 76 a Total Reserved : 0 x 00000051 f 0 Bytes Per Sector : 512 Bytes Per Cluster : 4096 Bytes Per File. Record Segment : 1024 Clusters Per File. Record Segment : 0 Mft Valid Data Length : 0 x 000007 c 90000. . .

Getting the file allocation unit size • fsutil fsinfo ntfsinfo e: • Output for

Getting the file allocation unit size • fsutil fsinfo ntfsinfo e: • Output for 64 KB format C: >fsutil fsinfo ntfsinfo s: NTFS Volume Serial Number : 0 x 328 e 659 b 8 e 6557 fd Version : 3. 1 Number Sectors : 0 x 000012 a 187 ff Total Clusters : 0 x 0000025430 f Free Clusters : 0 x 00000253 da 8 Total Reserved : 0 x 00000000 Bytes Per Sector : 512 Bytes Per Cluster : 65536 Bytes Per File. Record Segment : 1024 Clusters Per File. Record Segment : 0 Mft Valid Data Length : 0 x 00000010000. . .

IMPORTANT: Correlation of Partition Offset & Stripe Unit Size • The following formula must

IMPORTANT: Correlation of Partition Offset & Stripe Unit Size • The following formula must result in an integer value: Partition_Offset ÷ Stripe_Unit_Size • Note: Ask your SAN Man (or Woman) for Stripe Unit Size • Example A • • Partition Offset = 32, 768 bytes (exactly 32 KB) Stripe Unit Size = 65, 536 bytes (exactly 64 KB) 32, 768 / 65, 536 = 0. 5 Even though the offset was changed from the default 31. 5 KB, the partition is still misaligned! • Example B • • Partition Offset = 65, 536 bytes (exactly 64 KB) Stripe Unit Size = 65, 536 bytes (exactly 64 KB) 65, 536 / 65, 536 = 1. 0 Good! Source: Robert Smith

Another Correlation: Stripe Unit Size & File Allocation Unit Size • The stripe unit

Another Correlation: Stripe Unit Size & File Allocation Unit Size • The stripe unit size must correlate with the file allocation unit size • Here, too, an integer value is required • Example A • • Stripe Unit Size = 65, 536 bytes (exactly 64 KB) File Allocation Unit Size = 65, 536 bytes (exactly 64 KB) 65, 536 / 65, 536 = 1. 0 Good! • Example B • • Stripe Unit Size = 262, 144 bytes (exactly 256 KB) File Allocation Unit Size = 65, 536 bytes (exactly 64 KB) 262, 144 / 65, 536 = 4. 0 Good! • This alignment is usually not a problem. • The problem is that the separate partition alignment must be done first. Source: Bruce Worthington

Summary Steps for Alignment • Be mindful of the important relationships between • Partition

Summary Steps for Alignment • Be mindful of the important relationships between • Partition Offset • Stripe Unit Size • File Allocation Unit Size • Be mindful of Basic vs. Dynamic considerations • The following is a summary of the steps required: • Partition Offset must be explicitly re-defined using diskpart from the default 31. 5 KB to an appropriate value such as 64 KB, 128 KB, or 1 MB • The result of the following must both result in integer values: Partition_Offset ÷ Stripe_Unit_Size and Stripe_Unit_Size ÷ File_Allocation_Unit_Size

Help is Here: Vista, Windows Server 2008, & Win 7 • Partition alignment done

Help is Here: Vista, Windows Server 2008, & Win 7 • Partition alignment done by default • Windows Vista • Windows Server 2008 (aka Longhorn) • Deborah Jones, Microsoft Windows Senior Software Design Engineer • The default for disks over 4 GB is 1 MB. • The setting is found here: HKLMSYSTEMCurrent. Control. SetServicesVDSAlignment • Note: Pre-existing partitions will not be aligned automatically after an upgrade to Windows Server 2008—they must be re-built—just like existing partitions today.

Vendor Considerations I • Partition Alignment is a documented Best Practice for many vendors,

Vendor Considerations I • Partition Alignment is a documented Best Practice for many vendors, e. g. , • • • EMC Hitachi DELL Veritas Xiotech • Other Vendors state it isn’t necessary, e. g. , • HP • Net. App (special case!) • Our experiments suggest that you should test & reach your own conclusions

Vendor Considerations II • Vendor consultants may not comply with their own best practices

Vendor Considerations II • Vendor consultants may not comply with their own best practices • University cited previously • Turkish utility had massive I/O problems—vendor engineer dismissed importance. . . • Financial firm had SAN setup “validated” by vendor • Insurance firm had $10 M setup & support contract with vendor • In all cases consultants “forgot” something. . . • • Collaborate with your vendors & storage team DEMAND ACCURATE, RELIABLE INFORMATION Trust, but verify Use the tools described here to validate your installations

Many Factors Affect Disk I/O Perf • Reminder: Partition Alignment is only one of

Many Factors Affect Disk I/O Perf • Reminder: Partition Alignment is only one of myriad best practices & considerations for optimal performance. Be mindful of these as well: • • RAID level File allocation unit size Number, size, & speed of disks Configuration & capacity of HBAs & fabric switches • Consider increasing HBA Queue Depth • • • Network bandwidth Cache on disk, controllers, & SAN Whether disks are dedicated, shared, or virtualized Bus speed Number of paths from disk I/O subsystem to server Driver versions for all components Stripe size Stripe unit size Workload

Many Factors Affect Disk I/O Perf • What we learned today is merely the

Many Factors Affect Disk I/O Perf • What we learned today is merely the foundation. • It is vitally important, yet there is so much more!

Our Responsibility I • How long before Windows Server 2008 is deployed at your

Our Responsibility I • How long before Windows Server 2008 is deployed at your sites on all new partitions? • 1 year? 2 years? Longer? • How long before all pre-existing partitions are rebuilt? • Until then, we’re on our own. • Until then, it is our responsibility to deliver the message to our peers, our management customers, our partners, our vendors.

Our Responsibility II • Vendors may have specific recommendations for specific products. • Alignment

Our Responsibility II • Vendors may have specific recommendations for specific products. • Alignment may be more critical for some products than others. • Some vendors don’t know about, barely know about, or don’t proselytize the importance of partition alignment. • Until they do, it is our responsibility to deliver the message.

Questions? Discussion?

Questions? Discussion?

Acknowledgements I am grateful to the following for their assistance, insight, & especially their

Acknowledgements I am grateful to the following for their assistance, insight, & especially their patience. • • • • • • Bruce Worthington, Microsoft Windows Principal Development Lead Robert Smith, Microsoft Senior Premier Field Engineer Michael Epprecht, Microsoft Senior Consultant Deborah Jones, Microsoft Windows Senior Development Lead Mark Licata, Microsoft Senior Technology Architect Frank Mc. Bath, Microsoft Technical Evangelist Steven Wort, Software Development Engineer Jeff Goldner, Microsoft Principal Group Manager for Storage Karan Mehra, Microsoft Senior Development Lead Ruud Baars, Microsoft Consultant Clement Yip, Microsoft Senior Consultant Uttam Parui, Microsoft Senior Premier Field Engineer Robert Bogue, Author, Blogger, MVP, Renaissance Man Vinay Balachandran, EMC Engineer Extraordinaire Joe Chang, SQL Server Performance Guru Joe Sack, Microsoft Senior PFE, Author SQL Server 2005 T-SQL Recipes Matt Landers, Microsoft Senior Consultant Jason Mc. Kittrick, Microsoft Senior Consultant Nico Jansen, Microsoft OE Performance Engineer Anthony Thomas, DBA John Otto, Senior Enterprise Architect, Johnson Outdoors Brent Dowling, Technology Integration Manager, State of South Dakota Larry D. Brocklin, SQL DBA, Forte Data Solutions

References • This deck is maintained at this location: Disk Partition Alignment (Sector Alignment)

References • This deck is maintained at this location: Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck http: //blogs. msdn. com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck. aspx • ¡New! Jimmy May & Denny Lee Disk Partition Alignment Best Practices for SQL Server Whitepaper SQL Server Technical Article http: //sqlcat. com/whitepapers/archive/2009/05/11/disk-partition-alignment-best-practices-for-sql-server. aspx http: //msdn. microsoft. com/en-us/library/dd 758814. aspx • Mike Ruthruff Predeployment I/O Best Practices SQL Server Best Practices Article http: //www. microsoft. com/technet/prodtechnol/sql/bestpractice/pdpliobp. mspx Published in June 2007, this excellent yet mercifully brief reference is the best I’ve seen on SQL Server I/O; consider this whitepaper mandatory reading. • Robert Smith Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000 http: //support. microsoft. com/kb/929491

References (cont. ) • Bruce Worthington ¡New! Performance Tuning Guidelines for Windows Server 2008

References (cont. ) • Bruce Worthington ¡New! Performance Tuning Guidelines for Windows Server 2008 http: //www. microsoft. com/whdc/system/sysperf/Perf_tun_srv. mspx Disk Subsystem Performance Analysis for Windows subsys_perf. doc http: //www. microsoft. com/whdc/device/storage/subsys_perf. mspx This is the definitive foundation reference on Windows I/O performance. Watch for the new! improved! update by Qi Zhang, Microsoft Software Development Engineer • Steven Wort SQLIO Automation Professional SQL Server 2005 Performance Tuning (Wrox) http: //www. amazon. com/Professional-Server-Performance-Tuning-Programmer/dp/0470176393

References (cont. ) My Blog http: //blogs. msdn. com/jimmymay My Blog DPA Posts http:

References (cont. ) My Blog http: //blogs. msdn. com/jimmymay My Blog DPA Posts http: //blogs. msdn. com/jimmymay/archive/tags/Disk+Partition+Alignment/default. aspx Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck http: //blogs. msdn. com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sqlserver-slide-deck. aspx Disk Partition Alignment (Sector Alignment) for SQL Server: Part 4: Essentials (Cheat Sheet) http: //blogs. msdn. com/jimmymay/archive/2008/12/04/disk-partition-alignment-sectoralignment-for-sql-server-part-4 -essentials-cheat-sheet. aspx Disk Partition Alignment (Sector Alignment): Make the Case: Save Hundreds of Thousands of Dollars http: //blogs. msdn. com/jimmymay/archive/2009/05/08/disk-partition-alignment-sectoralignment-make-the-case-with-this-template. aspx

Complete the Evaluation Form & Win! • You could win a Dell Mini Netbook

Complete the Evaluation Form & Win! • You could win a Dell Mini Netbook – every day – just for handing in your completed form! Each session form is another chance to win! Pick up your Evaluation Form: • Within each presentation room • At the PASS Booth near registration area Sponsored by Dell Drop off your completed Form: • Near the exit of each presentation room • At the PASS Booth near registration area

Thank you for attending this session and the 2009 PASS Summit in Seattle

Thank you for attending this session and the 2009 PASS Summit in Seattle

Visit the Microsoft Technical Learning Center Located in the Expo Hall Microsoft Ask the

Visit the Microsoft Technical Learning Center Located in the Expo Hall Microsoft Ask the Experts Lounge Microsoft Chalk Theater Presentations Microsoft Partner Village