SQL Server Performance

SQL Server stripe and allocation size

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by Shifty1981, Jan 27, 2011.

  1. Shifty1981 New Member

    I've read many articles recommending 64K stripe size along with 64K allocation size when formatting the volume in Windows. I have a couple of questions:

    1. The document below recommends 256K stripe size in some scenarios. How do I determine if that pertains to us? Our databases are around 500GB in size and the data only stays in the DB for about 2 weeks before it is gone. We have high IO tables on their own RAID10 sets and the tempdb and transaction logs on RAID1 sets. The OS and pagefile are on a RAID1 set. These are all 6Gbps internal SCSI drives with a 512 or 1024MB cache battery backed up raid controller from DELL.

    http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/PhysDBStor.doc

    2. The 64K and 64K recommendation almost always seems to be talking about the database volume. Is the stripe and allocation size recommendation the same for:
    a. transaction log volume
    b. tempdb volume
    c. os/page file volume?

    3. our servers receive data from clients that average around 150K in size (and won't exceed 300K). Our database stores info about that data and then we store the data itself on an iSCSI SAN volume we present to the server. How should the stripe size and allocation size differ for that volume? Typically it's a RAID10 volume on 14 drives with 2 hotspares.

    Our environment is Windows Server 2008 R2 x64 Enteprise; SQL Server 2008 R2 Enterprise;

  2. satya Moderator

  3. Shifty1981 New Member

    thank. looks like that article recommends 64K generally for log drives as well.
    our other volume has files of less than 1000 bytes so I don't think changing it from 4K would be wise.
  4. satya Moderator

    There is no hard core rule that if the volume is not greater than so and so % then configure the disk in this manner, I would always recommend that for log files and database files 64K is highly recommended and also for TEMPDB file too.

Share This Page