Problem in 1 Billion Records Table

Question

Problem in 1 Billion Records Table

Si Thu

SSC-Addicted

Points: 435
More actions
March 24, 2013 at 10:34 pm

#275542

Hi,
I have one huge table which has 1Billion records. I tried to delete unnecessary records but it took a lot of time to delete and the delete was unsuccessful.
Now my data is 26,491,552KB and index 20,569,168KB. Now inserting and updating process become very slow. What is the best way to handle the table to make it faster read and write access.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

kk.86manu Ten Centuries Points: 1268 More actions · Answer 1

Hi,

I think you can consider the option of partitioning the table since it has more than 1 billion records.In order to make use of partitions effectively, the query’s where clause should have the column information associated to partitions.

Thanks

Si Thu SSC-Addicted Points: 435 More actions · Answer 2

Thanks for your reply.

I haven't done the table partition before can you please explain or recommend any thing that I can refer.

For my case, shall I delete the data first or make the table partition first.

Thanks.

matak SSCrazy Points: 2674 More actions · Answer 3

Just remember that partitioning the table wont make your queries faster.

For an explanation much better than i can provide myself please see this link

https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

kk.86manu Ten Centuries Points: 1268 More actions · Answer 4

Hi

Please refer the below links

http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/

http://www.sqlservercentral.com/articles/partition/64740/

Steps i would suggest

1)Create a new partitioned table with a clustered index on the partitioned column

2)Load the data from original table to the new table

3)Rename the tables original to old and new to original.Drop the old table after necessary validation is done(Row count chec etc)

4)Proceed with the update and delete operations based on the partitioned column.

Please make sure that space is available in the database for table reload.

If space is not there, you can try deleting the data in smaller chunks as a temporary solution.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Si Thu (3/24/2013)
Hi,
I have one huge table which has 1Billion records. I tried to delete unnecessary records but it took a lot of time to delete and the delete was unsuccessful.
Now my data is 26,491,552KB and index 20,569,168KB. Now inserting and updating process become very slow. What is the best way to handle the table to make it faster read and write access.
Thanks in advance.

Post the ddl for the table, including all indexes. A sample of your data would be helpful too.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Forget partitioning for now. With no experience in it you will be just as likely to mess something up as not since it is a VERY complex subsystem.

For the deleting, there are several questions:

1) how much data do you need to delete compared to total data in table?

2) does the table need to stay online during this evolution?

The best way to delete the data is with batched index seeks. With such a large amount of index space on the table hopefully you have an index you can seek to grab 50-100K rows per DELETE using an index seek. Combine this with transaction control and error handling and you can delete records efficiently without locking anything more than pages during the delete. There are other things that we will discuss depending on your answer to the 2 questions above.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 7

kk.86manu (3/25/2013)
Hi
Please refer the below links
http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/
http://www.sqlservercentral.com/articles/partition/64740/
Steps i would suggest
1)Create a new partitioned table with a clustered index on the partitioned column
2)Load the data from original table to the new table
3)Rename the tables original to old and new to original.Drop the old table after necessary validation is done(Row count chec etc)
4)Proceed with the update and delete operations based on the partitioned column.
Please make sure that space is available in the database for table reload.
If space is not there, you can try deleting the data in smaller chunks as a temporary solution.

Theres technically no need to create a new table with the partitioning, all you need to do is drop the

existing Clustered index and recreate it with the clustered Partitoning schema.

I would also suggest that you create a number of Filegroups for the partitions at the same time, this way when you move the table the data will move over to the file groups. If they're on separate disks then you might even get an uplift from that.

EDIT : But as SQL Guru states Partitioning should be the last thing on your list.

_________________________________________________________________________
SSC Guide to Posting and Best Practices