Wellington SQL Server User Group meeting

Venue: Microsoft, Level 12, Mobil on the Quay, Lambton Quay, Wellington.

Time: Thursday 8 March 2007 @ 6pm

Who: Rob Hawthorne

What: Disk Management

Disk Types

• IDE
• SATA
• SCSI

Disk Management

• SAN - Best for Enterprise Class environments
• NAS - Best for Medium Class environments and can include:
  • snapshot
  • File & Print management functions
  • Medium - high performance
  • Good levels of redundancy
  • Reduced scalability
• iSCSI
  • a new technology
  • SCSI over IP networks
  • Some hardware devices, but mostly software based
  • Doesn't care about the disks
  • Cheap remote mirroring solutin
  • Slow! Need a fast network (1GB min)

Performance Issues

There are 3 major I/O performance factors:

• I/O bandwidth: The aggregate bandwidth, typically measured in megabytes per second that can be sustained to a database device.
• I/O latency: The latency is...
• I/O something else: I missed before he took away the slide.

Scalability limits:

• Database performance is limited by maximum Transaction log throughput, only ONE possible transaction log per database!
  • disk I/O
• can be resolved by:
  • Multiple spindles

Disk I/O:

• Determine i/o pattern
  • Writes
    • Transaction Log (~100% sequential)
    • Lazy writer (random)
  • Read
    • Random vs. Sequential
• Establish disk I/O baseline or SLA outside SQL Server, using:
  • SQLIOSIM or IOMeter (Interl, public domain)
  • Special cases:
    • Transaction log
    • 1 tempdb file for each cpu
    • Max Paalle lBCP load = 1 BCP / CPU
      • Into SQL Server 005 partioned tables

I/O Bottlenecks

• Are typically easy to find
• Be very careful with the transaction log
  • Beyond 12 to 15 spindles doesn't buy much
  • Keep on separate physical disks for recovery
  • make RAID 10
• Beware of write cost on RAID5:
  • In RAID 5 each write has to logically read old data + old parity (to compute parity) and write new data and new parity
  • Each RAID 5 write = 2 READS + 2 WRITES!
    • However: disk guys work real hard to optimize this
  • Recent bulk load tests showed >50% degradation comparing RAID 0+1 vs. RAID 5

Optimizing for the Log

• Profile the log disk
  • How many writes / second can your disk sustain?
• Keep the log disk purely for the log
  • Keeps the disk heads writing sequentially minimizing seeks
• Beware of unprotected write back

Base practice for SQL Server and Disks

• Separate logs, Data, TempDB, Indexes and System DBs, Pagefile an OS
  • Nothing new here - Make better use of fileGroups
  • Watch for TempDB usage in 2005
  • Table partitioning can change the game!
  • Make sure Model D B has settings to persist
• Auto-grow can be very harmful to system performance
  • Understand the app!
  • Watch for growing logs and data files in DMV (Dynamic management views)
• Formatting
  • 64KB as a general recommendation
  • SQL uses 8KB pages
  • Read and write 8 x 8KB pages
  • Use DISKPAR.exe (no spelling mistake, diskpar is name) to align disk sectors
• Battery-backup controllers are generally best
• Speed of disk drives i.e. 20 or 15K RPM)
  • Faster = Better for performance
• Disk controllers need to be configured to align with application profile (understand the app!)
  • Read vs. Write
• Sector sizes
• Paging will cause performance issues
• Row-Overflow will cause PageSplits
  • Understand the app!
  • Row-Overflow > 8,060 bytes per row (8KB)
  • varchar, nvarchar, varbinary, sql_variant

Summary

• Understand the app!
• Apply common-sense
• Buy the best you can afford
• Read the whitepapers
  • SQL Server 2000 I/O Basics
  • SQL Server 2005 I/O Basics Chapter 2
• If suffering performance issues, identify if disk is cause (i.e. high CPU could be disk related)
• If need help, ask!