SQL Server Performance

New Server Estimates/Backup Recommendations

Discussion in 'Performance Tuning for Hardware Configurations' started by ann_keast, Oct 23, 2003.

  1. ann_keast New Member

    Hello all,

    I have spent the last week calculating the expected size of the database using SQL Server BOL. I have a few questions related to my calculations and hardware specifications. the following is the hard disk space (MB) estimates for the server:

    Data Space 48112
    Index Space 176926.65
    DB Overhead Space (85%)191282.85
    Additional. Design Considerations (15%)62448.22
    Risk Factor (20%)83264.30
    DB Size Estimate562034.02


    Logs: 150
    OS, MS SQL Server, Symantec reqs.: 1852

    Total Disk Space Needed: 448 GB

    I calculated the data space correctly (according to BOL). I have some time constraints and didn't have time to complete the index space calculation so I took an average of the % (index space/data space) for each table in the test database. This is a brand new VB app to support financial contract data over the 7 years. User base is 10-50 users. We're going to use SQL Server Standard. The server specs are supposed to be good for a minimum of 3 years.

    I've been looking at a Dell PE 2650:
    2 Xeon processors at 2.4 MHz, 512MHz FSB
    2GB DDR, 2x1GB DIMMS
    Windows Server 2000
    RAID 5 (5BAY SCSI HD Backplane)
    146GBx5 10K RPM Ultra 320 SCSI HARD Drive
    Single Controller: PERC3-DI, 128MB Battery Backed Cache, 2 internal channels
    price:10K

    Questions:
    1. In estimating the size of disk space needed, I've included at 85% factor for "overhead" because the total size of the database is not dataspace + indexspace.
    When I run sp_spaceused @updateusage = 'TRUE' in Analyzer,
    DB size (MB) - 90.63
    unallocated space (MB)- 1.19
    reserved (KB)- 14848
    data (KB) - 12056
    index size (KB)- 2488
    unused (KB) - 304

    Allocation of the 90MB?
    Data Size14.516.0%
    Unallocated1.191.3%
    Overhead74.9482.7%

    So it looks like their is a significant ammount of overhead... What is this attributed to?
    What are your thoughts on this estimate?

    2. RAID 5 in 5x146GB leaves me with 584 GB (20% parity taken out) At the estimate of 551 GB needed, this doesn't leave me with much room. The machine only comes with 5 drive bays too... any recommendations on this.

    3. Finally, backup solutions. I'm considering a failover clustering. Management wants pricing on tape backup solutions. To backup that amount of data (full backup on weekends, incremental on weekdays), we need a pretty powerful tape drive. Any recommendations on backup solutions are welcome. This is mission critical data. Loss cannot happen.

    Thanks very much. I am usually dedicated to software development; this is a new world to me. Any expertise is much appreciated.

    Ann





  2. Luis Martin Moderator

    Ann,

    1) I have a question: Really Index is bigger than Data?
    2) If machine is only 5 bays, well there is no to much to do. From performance view RAID 5 is not the best, but give you max space.
    3) Are you plannig to install DLT?




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. ann_keast New Member

    Luis,

    1) I got the index size by running the following stored procedures http://corp.elegantsp.com/pipermail/remedy/2002-February/040875.html).
    Example:
    tbl_A with 49 Rows, 8KB dataspace, 16 KB index space. 16/8 = 200%
    tble a has 1 clustered indexes; INT
    3) Is DLT the same as Analysis Services? we are not going to use Analysis Services.

    Ann
  4. Luis Martin Moderator

  5. aitor_ibarra New Member

    This thread is a bit old now, so maybe you've already made your purchase decisions...

    We've got three Dell 2650's and various other Dell servers, so I know the range reasonably well.

    Assuming you really need that much space, which I doubt (the overhead % looks way too high)... 5x 147GB drives isn't going to be enough for almost half terabyte of data + expansion room. NTFS really needs 15% to 20% free disk space to work efficiently, and you loose available space due to overheads like RAID virtual disks, partitions etc.

    I'd suggest you get a PERC 4DC Raid controller and a Powervault 220S Raid array, and put your data drives in there. The 220S can take 14 drives (13 if you use it for failover clustering, which you might want to look at in future). It will cost more, but give you more flexibility, capacity, and peformance. Keep Windows etc on internal drives (RAID1 will be Ok), and look at DLT, Ultrium LTO, or S-AIT for backup, according to how much data you need to store in a full backup. Ultrium 2(aka LTO 2) can do 200GB uncompressed. S-AIT can do 400GB. DLT goes from 20GB to 100GB- I think! For backup software, I'd recommend Veritas BackupExec.
  6. gaurav_bindlish New Member

    As a rule of thumb, you should plan for having disk capacity for six times the size of the expected data in the database. This inclused only the data size and does not account for indexes. If you need I can give a detailed analysis of the same....

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  7. ann_keast New Member

    Gaurav,

    Thank you. The detailed analysis would be most appreciated!

    Ann

  8. satya Moderator

Share This Page