SQL Server Performance

VERY Large SQL Database - best solution for performance SELECTs

Discussion in 'SQL Server 2005 General Developer Questions' started by mvayner, Apr 3, 2008.

  1. mvayner New Member

    Fellow DB gurus,
    I have a interesting situation. I have a very large database which I need to load. It is several txt files containing a total of 250 million records totaling approximately 300GB. Loading this in the database is not the issue. The issue is, what is the best way to design the subsystem in such a way where SELECT performance will be optimal.
    I have thought of a way to partition the data in that each TABLE will contain ~4-5 million rows (on average some more, some less).
    With a database this large, I was thinking of creating a filegroup for each table and putting the filegroup in its own drive (of course i only have 5 drives to work with).
    The question is:
    a) Is the above best solution?
    b) What is best RAID to get? I was reading that in above situation a RAID 10 is best for hardware performance. What about SAN shall I consider that?
    Any other thoughts you may have on above situation and how best to solve it?
    Thanks in advanced for advice.
  2. martins New Member

    Hi,
    The above is worth a try, and there are a couple of other options you can also try. Usually for these kinds of databases RAID 10 will perform better and DAS is also preferred to SAN.
    A denormalized approach in your tables could improve the performance of select queries quite substantially, and indexes are also crucial. Use the with (nolock) hint in all your select queries if dirty reads are not an issue.
    All the above could be viable solutions, but in the end you would have to see what fits best in your specific environment.
  3. ghemant Moderator

    True,but, I suspect If you would place your each table on separate file but the disk drives are not physically separate it wont help that much.
  4. eGainsql New Member

    In Similar kind of situation, i will reccomand this,
    1: Create a filegroup and seperate out those large tables which is most accessiable and having more no. of rows.
    2: Keep this files on different I/O controler disk.
    3: If your read operation is 75-80 % compare to write operation on those large tables then i think you can go for RAID 5 only for those filegroups. SAN will give you better performance.
    4: Try to archive the data which is in large tables.
    5: Make sure you are using correct indexes and doing timely maintanance on those tables and indexes.
    6: Finally watch the I/O resource utilization for performance check.
    Hope this will help you.

Share This Page