SQL Server Performance

Hardware Planning for DWH

Discussion in 'SQL Server 2005 General DBA Questions' started by sonnysingh, Aug 1, 2010.

  1. sonnysingh Member

    I need to do sizing the DWH DB server. The current scenarios are...
    1. Serversthat will last min 5 years (or want to retain 5 years data)
    2. Averagetotal of transactions: 5,00,363 transactions per day
    3. Numberof transactions in peak hours: 20,848 (approx.)transactions per hour
    4. Numberof transactions in peak minutes: 347 (approx.) transactions per minute
    5. Yearlygrowth: 20% per year
    6. Archival of data periodically

    How should I measure up the best possible hardware for DWH in terms of memory, I/O bandwidth, CPU and RAID etc... I really need guidelines with understandability on what kind of server should be best for DWH DB. I have been through some following articles but I think that I am confuse (might not much experience this side). So, I need your help...
    • http://sqlblog.com/blogs/joe_chang/archive/2008/03/04/storage-performance-for-sql-server.aspx
    • http://sqlblog.com/blogs/joe_chang/archive/2007/12/20/server-sizing-for-sql-server-2000-and-2005.aspx
    • http://sqlblog.com/blogs/joe_chang/archive/2010/04/07/intel-xeon-5600-westmere-ep-and-7500-nehalem-ex.aspx
    • http://www.sqlservercentral.com/Forums/Topic794148-146-1.aspx#bm795529
    • http://www.intel.com/pressroom/kits/quickreffam.htm#Xeon
    • http://sql-server-performance.com/Community/forums/t/15483.aspx
    • http://sql-server-performance.com/Community/forums/t/15483.aspx?PageIndex=3
    Thanks in Advance
  2. satya Moderator

    What to do with those links you have posted here?
    Anyway what is the budget that is allocated for this new hardware?
    As you got the preliminary specification of how the DB & BI usage looks like, the disks are more improtant to choose for quicker performance/The disk types that you use in the system can affect reliability and performance. All else being equal, larger drives increase mean seek time. SharePoint Server 2010 supports the following types of drives: Fibre Channel (FC) or Solid State Drive (SSD) or Flash Disk. See this http://go.microsoft.com/fwlink/?LinkID=105583 which isa pre-deployment IO guidance.
    There is much more to add here, but having the replies for above question should get us go.
  3. sonnysingh Member

    With those links I have tried to understand what would be the best possible hardware for DWH. I came up with following specs but not sure I am on the right direction as logically I do not understand the some of things here. Approx Budget will be $10,000.00.
    1. What would be DB files layout for RAID 10 as i think this will be suitable. (need to advise on this part)
    2. Max 2 processors with 6 or more cores
    3. 2 HBAs (single port fibre channel)
    4. 64 ECC GB FB DIMMs
    5. 16M or more L2/L3 Cache
    6. 4-8 PCI-E slot SAS controller (need to advise on this part)
    7. Storage (raid) Controllers speed GB/sec preferable (need to advise on this part)
    8. Archived data storage on RAID
    I have tried to understand some of hardware glossary from net but as you it is not that simple either. Hence please help me....
    Thanks in Advance...
  4. satya Moderator

    Not an answer, but do you have the vendor contact. if so what was their opinion or views on the above information?
  5. sonnysingh Member

    No I haven't contact any vendor yet as I want to understand some basic knowledge on the area. And that's why I have asked here in forum for help.
    I hope that someone can guide us here....
  6. rohit2900 Member

    You haven't mentioned intital size of the DB.... And how much we can expect it to grow over a period of 5 years
  7. sonnysingh Member

    Initial size will 15 GB and approx.. grow 30% every year...
  8. satya Moderator

    Good it is something you wanted to learn and hanlde the things, see my response below for your Qs:
    1. What would be DB files layout for RAID 10 as i think this will be suitable. (need to advise on this part)
    In general I see that Operating system paging file and LDFs to be put on RAID1 having the TEMPDB on its own physical array and not sharing any of logical partitions for other parts of DB.
    All the MDFS on RAID 10 to ensure faster performance, partition for page file (dependant on much memory you have but about 10-20GB 2) partition for transaction log files 100GB 3) partition for Datafiles 100GB.

    1. Max 2 processors with 6 or more cores -
    make sure the logical processors & DUAL core capacity, I believe if the environment is 64 bit having a 32 CPU is best to handle the load.
    1. 2 HBAs (single port fibre channel)
    Better to have 4 HBAs in order to eliminate the general server traffic and have an exclusive for DB related disks.
    1. 4-8 PCI-E slot SAS controller (need to advise on this part)
    See Joe Chang's site http://www.qdpma.com/ServerSizing.html
    1. Storage (raid) Controllers speed GB/sec preferable (need to advise on this part)
    THere is no clear indication of values that disks with higher rotational speeds and lower seek times are typically better performers. However, to find the optimal performance for a particular company’s business, it is a good idea to look at that servers’ type of workload. For servers that frequently perform sequential workloads, having a large disk cache is more important.Although disk performance is commonly attributed to the disk seek time and rotational speed, the amount of cache also plays a role. However, the performance attributed to cache size is only valid as long as the cache is not full. If the cache is full, there is a significant drop in performance. Ideally, the cache size is large enough so that it never fills for the particular I/O workload.
    Disk controller caches on servers ranges in size up to hundreds of MBs. The disk controller on a SAN disk system can have up to tens of GBs.
  9. sonnysingh Member

    I do not know that you might or might not have idea how much this kind response can help people here.... Now I can read more literature on hardware with keep your response in mind and try to understand and make sense out of this for me that enhance my knowledge...
    Once again thanks Satya
  10. satya Moderator

    Not taking the comments on downside, I must say if the question is open-ended the response will be the same. The forum is best medium to lead to the information which is a 20% effort (me thinks) and remaining 80% you should put on to understand the material and apply them in practice.
    Hope this helps.
  11. rohit2900 Member

    I was just think abt the server configuration and db size assuming 40% growth every year.
    End of 1st Year: 21GB
    End of 2nd Year: 30GB
    End of 3rd Year: 42GB
    End of 4th Year: 58GB
    End of 5th Year: 72GB
    Now I know the best answer will be get the best possible hardware fitting into your cost, but was just wondering do we really need such a heavy configuration to handle a db of just 72GB or lets say 100 GB.
    Just a thought.... :)
    Lets see if anyone else wanna add anything to this.
  12. rohit2900 Member

    Actually we've a server with almost 2-3 db's 250GB+, 3 db's 150-200GB and close to 15 db's between 50-120GB
    and server configuration is below
    HPDL 380 with 2 Xeon processors (eight cores) 2.93 GHZ
    16GB of RAM, 3 Data Drives RAID5 (On SAN), 2 Log Drives RAID1 (On SAN), I don't have much info as how Raid array has been configured like number of spindles and their speed.
    4 1GBps nic cards two active.
    And still the performance is quite okey (Not at all bad) as sometimes we might need to sit with developers to tune up their queires.
    Thats y I was wondering do we really need such a hardware.
    Satya garu any thoughts on this???
  13. satya Moderator

    Your points opens a new dimension of availability, scalability & performance on the basis of growth of data in next 5 years. Here we need to attend each of the aspects seperately, do not mixup or think that scalability means performances - which is an opposite way of keeping the database platform tasks alive.
    As far as tuning the queries are concerned it is a constant fight to go about and educate developers on using best coding practices temp table vs temp variable and so on. Not to fight with them stating your code is crap, rather enlighten them showing how best they can help the SQL optimizer by using index hints for frequently running queries.
  14. mishrsk New Member

    Hi Sonny,
    This is a very open ended question. You can google "SQL Server Sizing Tools" and get the recommendation from different vendors. It will give you a fair idea on what throughput you are looking at. Now it's your choice which hardware you are going to choose amongst the ones recommended or can look for similar throughput HW from a different vendor.

Share This Page