SQL Server Performance

Requirements for a new setup

Discussion in 'Performance Tuning for Hardware Configurations' started by itbhushan, Jul 21, 2003.

  1. itbhushan New Member

    Hi,

    We would be shortly going for a complete new setup of database & the OS for a new application, so i need to plan for it. It would be very thankfull if everyone can put in there views & inputs for the same, the new operating system will be win2k & MSSQL 2000 will be the database used. The application maker has predicted that initially the size of the database will be around 150 to 200 mb & say by a year it may scale to around 2 to 5 gb depending upon the transactions.

    Based on these information i wanted to make the best possible plan which will make the application work very smoothly atleast for a period of 3 years.

    (1) How do i calculate the required number & size of CPU's or processor
    (2) Total memory size required
    (3) Total hard disk size required & how to make the best possible utilization of theses hard disks.
    (4) Should i go for MSSQL 2K standard edition or enterprise edition ?
    (5) Any other additional information required to be kept in mind during the planning.

    Thanks

    Bhushan
  2. satya Moderator

    1) Based upon the application and database usage 2 processor would be fine, more the merrier if you add more processors you will get better results. Get CPUs with larger L2 cache i.e., may use XEON calibered.

    2) 1GB is recommended, again more you add more you get better performance.

    3) Depends how long you want to keep the data in the database or to be archived, for the better performance use RAID 10 for the arrays being it support redundancy. Check this link http://www.sql-server-performance.com/sql_server_performance_audit3.asp] for checklist.

    4) Go for Enterprise edition for optimum performance.

    5) Refer thru other articles in this website to fine tune the performance.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. gaurav_bindlish New Member

    For this u'll need to do some capacity planning. U'll have to determine the CPU based on the TPCC of the database which depends on the no. of users and the operations on the system. It's a rule of thumb to go for 1 GB of RAM for 1 CPU. Similarly for disk the rule is to have 6 times the maximum size of database as disk capacity.

    The book Microsoft® SQL Server 2000™ Performance Tuning Technical Referencehttp://microsoft.com/mspress/books/4944.asp is an excellent book on capacity planning for SQL Server.

    Use enterprise edition only if the above capapcity planning shows u'll be exceeding the capacity supported by Standard Edition and also if u need the reliability features of Enterprise Manager. Else it will be difficult for u to justify the cost of the software.

    Have the optimum design for the database - The best optimization tip.

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

    i would plan on either a 2 year replacement cycle or a 2X extra capacity, which ever is longer.
    for example, if you are planning on 30% growth per year, it is less expensive to buy a system that can handle 2 years growth, and replace it two years later with a new of comparable cost to your current system, than to buy a system today that can handle 3 years growth.

    depending on your budget and expected load, i would recommend that you atleast consider a dual processor system with the Xeon 3.06GHz/ 1M cache as a baseline reference point,
    this is because more expensive systems may not necessarily perform much better.

    you should gather some information on the nature of queries that will be run against the server, the expect row count involved for each table in the queries, and the expected query rate
  5. gaurav_bindlish New Member

    Following up on this, the advantages of using Enterprise edition are

    http://www.microsoft.com/sql/techinfo/tips/administration/sql2kbenefits.asp

    Hey! I didn't know about the dynamic adjustment of the maximum number of read-ahead pages based on the amount of memory present. That's very cool...

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

    SQL can do ugly things to storage when it reads ahead 2000 pages. Be careful with that.

    You gave the server spec, but didn't talk about what type of storage you'll be using. Regardless of whether you're using locally attached SCSI or fibre attached SAN, you'll want to perform disk alignment prior to fomatting the partitions. You'll pick up a 20% performance improvement by doing this one simple step. You'll also want to format the drives with an 8K allocation unit size. That will optimize your storage for SQL. The performance improvement is smaller for locally attached SCSI, but for fibre channel it 40% improvement over the default 4K allocation unit size. NTFS is a journaling file system. Under heavy load it can be helpful to increase the journal log size. You can do this by running chkdsk /L:655356.

    For more information on Allocation unit size, disk alignment, and cache see:

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=382
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=951

    John Fullbright
    MOSMWNMTK
    fullbrij@yahoo.com



    John
    MOSMWNMTK

Share This Page