1

We are having table that contains 6,616,711 (6.6 million) records in it. I want to know, How to manage this table? If there is SO question similar to this please give me link. Having following concern, too.

  • How to manage large database?
  • What is best system/hardware configuration for this type of scenario?
  • Partitioning this table will help us?
Improve this question
3
  • 1
    6.6 million records isn't large... Your system/hardware configuration needs to be dictated by how many (concurrent) users need to access it. Partitioning can help for administrative purposes or if the table mutates a lot.
    – Colin 't Hart
    Commented Feb 23, 2014 at 14:21
  • 1
    How many GB is the table using for data and (nonclustered) index data? To manage it, start by having a good clustered index, then treat it like every other table. If it helps, I would consider a 70 million row table to be "medium" size, and a table with 100GB of data perhaps "medium" as well for many SQL Server installations. Many are much smaller; some are much, much larger - some have a TB of RAM (not disk space, but RAM - i.e. buffer space). Defrag your indexes when they're too fragmented, watch backup, restore, and CHECKDB times.
    – Anti-weakpasswords
    Commented Feb 24, 2014 at 0:47
  • I forgot to ask one thing that. While we are adding new column to this table with default value 0 its taking to much time. Will it take that much time? or we've better option?
    – Vicky Thakor
    Commented Feb 25, 2014 at 4:38

1 Answer 1

Reset to default
1

Partitioning a table will help you in maintaining.

Here are some useful articles on maintaining a large table:

Handling Large SQL Server Tables with Data Partitioning

The only downside is that it only exists in the Enterprise and Developer editions.

Simplify Database Maintenance with Table Partitions

And more info on hardware, horizontal, and vertical partitioning:

Partitioning

Partitioning a database improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan. Maintenance tasks, such as rebuilding indexes or backing up a table, can run more quickly.

Partitioning can be achieved without splitting tables by physically putting tables on individual disk drives. Putting a table on one physical drive and related tables on a separate drive can improve query performance because, when queries that involve joins between the tables are run, multiple disk heads read data at the same time. SQL Server filegroups can be used to specify on which disks to put the tables.

Also, there are a lot of useful answers in this thread

Hope this helps

Improve this answer
1
  • I forgot to ask one thing that. While we are adding new column to this table with default value 0 its taking to much time. Will it take that much time? or we've better option?
    – Vicky Thakor
    Commented Feb 25, 2014 at 4:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.