Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

closed as too broad by Mikael Eriksson, Paul White, Jon Seigel, ypercube, Mark Storey-Smith Feb 24 at 1:02

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

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 Feb 23 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 Feb 24 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 Feb 25 at 4:38

1 Answer 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

share|improve this answer
    
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 Feb 25 at 4:36

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