Hardware

SQLBits V: Highlights from Friday Afternoon

Having attended the SQL Bits conference in Newport last month I decided to collate the little tips and tricks I learned at the event, in the challenging post-lunch session on Friday Thomas Kejser gave a talk on Designing I/O Systems for SQL Server – my notes from this talk are either short, sharp facts or references to thing I plan to research further…

The most important metrics are Throughput (measured in MB/sec or IOPS) and Latency (milliseconds).

A 10K RPM disk will give 100-130 IOPS, a 15K RPM disk will give 150-180 IOPS.

Performance can be increased by short-stroking disks, if you build a single partition on a drive using only 10% of it’s capacity this partition will reside physically on the outer edge of the disk.  Doing this reduces the amount of head movement required to read the data and improves performance.

Windows Server 2008 handles disk alignment automatically but for earlier versions the  offset should be 1024kb.

For testing the effect of configurations on performance use SQLIO.EXE or IOMeter, for stability testing use SQLIOSIM.

Check out the Pre-Deployment Best Practices Whitepaper from Microsoft.

The last talk of the day was by Allan Mitchell and was initially titled Common Integration Services Problems but was changed late-on to a more introductory Solving Problems in ETL using SSIS.  The only take-away I got from the talk was to check out a tool called BIDS Helper, I have no criticism of Allan though – he’s a good speaker but in this case I already understood the bulk of the content so it was hard to stay focused.