Solved

SQL Server: Table partitioning with big data

Posted on 2014-02-26
7
884 Views
Last Modified: 2016-03-23
Hello,

I've a big data table which contains 4500 million records.
So, I would like to have table partitioning.

Could you please share your thoughts, what shall be the optimum number of records per partition?

Or do you have other thoughts?

Best Regards,
Mohit Pandit
0
Comment
Question by:MohitPandit
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 100 total points
ID: 39891187
0
 
LVL 4

Expert Comment

by:michaelalphi
ID: 39891188
When size of the tables become very large, partitioning of table is a significant way to improve their performance.
To accomplish this task, there are two different approaches.
Either create a brand new partitioned table and then simply copy the data from your existing table into the new table and do a table rename. Otherwise, you can partition the table in place simply by rebuilding or creating a clustered index on the table.
You can check these links to get further assistance :
http://technet.microsoft.com/en-us/library/ms188730.aspx
Partitioning large tables and indexes
How to partition an existing SQL server table
A good resource you can also explore here if you wish to go with automated solution : Table partitioning with Big data
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 39891199
Thanks for valuable feedback.

But my question was what is optimum number of records per partition.

Should I've 450 partition and each partition shall be having 10 million records/index (if create index)?

Best Regards
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 4

Assisted Solution

by:michaelalphi
michaelalphi earned 150 total points
ID: 39891246
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 39891947
It actually depends on your use. PArtitions may help in certain scenarios and may not so much in other. Above blogs by Michael definitely are a good source for this information.
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 250 total points
ID: 39893468
After you read the articles or blogs above, you can start by asking yourself questions.

How selective is the data you have in the table.  Are there millions of rows that have no real partitioning key that would group them in blocks of rows?

Do you know which column would be a good partitioning key?

Will that column that you selected be used in the queries? (if it is not then you have successfully partitioned a table, but you may not get as much benefit out of it since it cannot partition eliminate without using the key in the query)

Will data be inserted or updated in ALL partitions (based on key) or will it be a Date type key that will render all past partitions read only or at least non updated or inserted or deleted?

These should lead you to answers for your data.  Because we don't know your data, in some cases 1 Million rows would be fine per partition, but remember that if this is a growing table and you partition by date, the more recent partitions will most likely be bigger than the previous partitions because your are preferably growing the company or data not shrinking.

In some cases you could not tolerate more than 100,000 rows in the tables because of the way they are queried.

Also keep in mind that on an x64 box (which I imagine you have with that many rows) you can have 15,000 partitions max.  So if you put 4.5 Billion rows in 15,000 partitions with no room for another partition, you get the minimum rows you can have is 300,000 rows.  So that may help you determine how many you should be looking at.
0
 
LVL 5

Author Closing Comment

by:MohitPandit
ID: 39921105
thanks
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now