New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. is against this unjust law - help us

A Space for All Things .NET Related

Wellington SQL Server User Group meeting

By James Hippolite, in , posted: 10-Mar-2007 06:09

Venue: Microsoft, Level 12, Mobil on the Quay, Lampton 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 megabyes 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:

  • Dataqbase performance is limited by maximum Transactoin log throughput, only ONE possible tranacgtion 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:
      • Transaqtion 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 transacxgtion log
    • Beyond 12 to 15 spindles doesn't buy much
    • Keep on separate physical deisks for recovery
    • make RAID 10
  • Beware of write cost on RAID5:
    • In RAID 5 each write has to logicdally 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

  • Porfile 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, Dagta, 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 cdan 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 witepapers
    • 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!


Other related posts:
GeekPost Weekly Newsletter Volume 4 Issue 7
MSDN Unplugged Roadshow Wellington
Right Sizing Agile Development - MSDN Magazine Webinar

Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
New Zealand

Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".