Thursday, March 8, 2007

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


  • 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!

No comments:

Post a Comment