SQL Server Performance

maximum recommended rows per table

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by mvayner, Apr 6, 2008.

  1. mvayner New Member

    Hello,
    I have about 250 million records which i need to insert into db. Obviously putting all the records into one table is not very efficient for performance.
    Therefore, I will be partitioning the data by state. What is the maximum rows recommended to have in a table? Would 3 million rows be ok for performance (SELECTs only - after initial load).
    Thanks.
  2. Luis Martin Moderator

    There is no rule for that.
    Try to normalize you table.
  3. mvayner New Member

    The problem is that the table is already normalized.
    The table structure is:
    ID
    FirstName
    LastName
    StateID
    I was thinking of instead of putting ALL the 250 million records into one Person table, I would partition it to person's by state... and based on the data i have the largest state would contain 15million records.
    My problem is with performance. Yes, I have ordered a serious box.. 16GB ram, RAID 10, quad-core, etc... but trying to figure out if the table needs further partitioning... i was thinking also maybe partition it by zip code, since I have that data too.
    Any thoughts?
  4. preethi Member

    I am afraid that you are thinking that the partition should be uniform based on the rows. You need to partition based on load and usage.
    What data is used frequently? Do you use joins with other data?
    Try to make the highly used data spread across disks. It will improve the performance. you can have multiple small chucks in one partition. I prefer to partition over numeric data.

Share This Page