Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem in 1 Billion Records Table Expand / Collapse
Author
Message
Posted Sunday, March 24, 2013 10:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 12:19 AM
Points: 7, Visits: 94
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 #1434746
Posted Monday, March 25, 2013 12:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 1:19 PM
Points: 48, Visits: 212
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
Post #1434756
Posted Monday, March 25, 2013 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 12:19 AM
Points: 7, Visits: 94
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.
Post #1434763
Posted Monday, March 25, 2013 1:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:23 PM
Points: 200, Visits: 1,568
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/
Post #1434766
Posted Monday, March 25, 2013 1:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 1:19 PM
Points: 48, Visits: 212
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.
Post #1434768
Posted Monday, March 25, 2013 2:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 6,777, Visits: 12,883
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1434774
Posted Tuesday, March 26, 2013 8:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 4,133, Visits: 5,851
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 at GMail
Post #1435541
Posted Tuesday, March 26, 2013 9:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:37 AM
Points: 860, Visits: 2,323
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
Post #1435573
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse