SQL Server Performance

16 drive RAID setup

Discussion in 'Performance Tuning for Hardware Configurations' started by Pluis, Dec 10, 2004.

  1. Pluis New Member

    Just to be sure, I wanted to know what you guys think of this...<br /><br />Version: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: ) <br /><br />Table A: 15 million records, full text index on 1 field<br />table B: 200 million records<br /><br />A : B = 1 : n<br /><br />Users can search in table A in 2 ways:<br /><br />1) Table scan. They provide additional information so only a small part of the table is scanned<br />2) Full text search. They want to search the entire table (limited to x results) so the fti is used<br /><br />users will make a selection from the query results from table A.<br />After doing this they will press a button and the corresponding records from table B will be fetched.<br />The application they use is a webapplication (ASP.NET)<br /><br />Every day around 375000 records are added to table A, and around 5 million records are added to table B.<br />Also the same amount of records are deleted from those tables.<br /><br />The most important thing is that searching in table A goes as fast as possible.<br />The new server my company has ordered has 16 SCSI harddrives and it is my job to configure them for maximum performance.<br /><br />This is what I had in mind:<br /><br />Set 1) 2 disk RAID 1: OS, Website, MySQL Database (for the forum), SQL Server Logfiles<br />Set 2) 4 disk RAID 10: Table A Datafiles<br />Set 3) 4 disk RAID 10: Table A Indexfiles + Table B Datafiles + Table C-G Data and Indexfiles (some small tables)<br />Set 4) 4 disk RAID 10: Full Text Index files<br />set 5) 2 disk RAID 0: TempDB + pagefiles.sys<br /><br />Now my questions <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />a) Is this a good setup or should I change it. Maybe even go for 1 big RAID 10 setup?<br />b) Should I still split up the datafiles of the tables (like we do now) and keep the rule of 1 datafile per physical drive? Or just make 1 big file per table?
  2. Luis Martin Moderator

    Sound good to me. How many channels do you have? (RAID)

    Luis Martin

    All postings are provided “AS IS” with no warranties for accuracy.

  3. Pluis New Member

    2 channels:

    1 internal to be used for set 1
    1 external for the scsi enclosure for sets 2 - 5
  4. satya Moderator

    Split the datafiles if the database is used frequently say for modifications and insertions, as you're performing currently.

    As a test you can run PERFMON to get few stats in this regard and join the small databases together or keep the big ones with frequent updations on a seperate drives.

    http://www.sql-server-performance.com/hardware_tuning.asp for your information.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page