Database partition

What is best criteria to partition table?
Table size or number of records?
How many partition we can do?
If I have 20 GB size of table how many tables of how much size should i
create?
Regards
Amish ShahThe best criterion is to partition when you need to provide a solution to a
PROBLEM and the best solution is PARTITIONING.
So the question is.... do you have a PROBLEM that needs solving?
What is your PROBLEM?
Is PARTITIONING the best SOLUTION for your PROBLEM?
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"amish" <shahamishm@.gmail.com> wrote in message
news:1129544966.925091.296410@.g43g2000cwa.googlegroups.com...
> What is best criteria to partition table?
> Table size or number of records?
> How many partition we can do?
> If I have 20 GB size of table how many tables of how much size should i
> create?
>
> Regards
> Amish Shah
>|||Suppose I have table of 20GB how much partition should i create? What
should be size of each partition?
Or
If I have table of 2.5 billion rows how much partition should be
created? How much rows should be in each partition?
Regards|||Amish,
I think what Nik was pointing out is that you are asking for a solution
without describing a problem. Size alone is not enough criteria to determine
proper partitioning. How can your data be segregated? The answer to
partitioning is not <x> rows = partition. The view your build will need to
determine which table(s) from the partition to query. If you do not
partition on a meaningful field, then you will hurt your performance rather
than improve it.
Provide more specifics and I am sure someone here can help you out. Since
you mention the number of rows, I will assume you are looking for horizontal
partitioning, so in the meantime read up on horizontal partitioning in BOL.
John
"amish" wrote:
> Suppose I have table of 20GB how much partition should i create? What
> should be size of each partition?
> Or
> If I have table of 2.5 billion rows how much partition should be
> created? How much rows should be in each partition?
> Regards
>|||Hi John.
Correct. Solutions are for problems. You've not described yours at all. Will
you really have 20 billion rows? If so how big are they, 1 single ID column
and an integer would still mean you may not need partitioning as all the
data could fit onto a normal (or several) medium sized disks.
Paritioning is not the only solution for large or high use databases. You
could split the data into 2 tables each with the same key. One table has a
few columns but is very frequently accessed. In this case the packing
density of rows will be high and a lot of data accesses willl get pages from
buffers alreay in memory. The other table could contain infrequently used
data.
I've seen many people come unstuck because they've embraced something which
sounds "high-tech" or "is the current buzzword", developed their solution
and it doesn't provide what they wanted because they didn't define the
problem properly in the first place.
Please explain your current problem and what you're trying to achieve (with
and without partitioning) and we'll all be able to better help you arrive at
the best solution.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AA57FE51-EFEA-4D74-8499-4F58535D92EA@.microsoft.com...
> Amish,
> I think what Nik was pointing out is that you are asking for a solution
> without describing a problem. Size alone is not enough criteria to
> determine
> proper partitioning. How can your data be segregated? The answer to
> partitioning is not <x> rows = partition. The view your build will need
> to
> determine which table(s) from the partition to query. If you do not
> partition on a meaningful field, then you will hurt your performance
> rather
> than improve it.
> Provide more specifics and I am sure someone here can help you out. Since
> you mention the number of rows, I will assume you are looking for
> horizontal
> partitioning, so in the meantime read up on horizontal partitioning in
> BOL.
> John
> "amish" wrote:
>> Suppose I have table of 20GB how much partition should i create? What
>> should be size of each partition?
>> Or
>> If I have table of 2.5 billion rows how much partition should be
>> created? How much rows should be in each partition?
>> Regards
>>|||Hi
Thanks for the reply
Actually I have joined a new company.
Here we have some tables of 20 gb to 50 gb in size.
So I just want to know should I partition this tables?
I think if I will partition it, reindex and other management activity
will be easier for me.
I want to partition this tables so I just want to find on which
criteria and how many partition should I create for this tables.
Regards|||Amish,
Again, the answer is not based on size. When you partition you are NOT
doing it to reduce the administrative workload. in fact, you probably
increase it. You are doing it for performance reasons. And the data has to
be partitioned based on criteria.
So for example your company has 5 sales regions and they are comprised of 10
states each. you could partition the data into 5 tables, each representing
the sales data for a specific refion. This might make sense since in most
cases the queries are going to be for a specific region. You see what we
mean when we say there has to be some logical criteria for the parition.
You cannot just say. 2billion / 10 = 200 million rows per table. All you
have done with this partition is forced the creation of a view that will
ALWAYS have to join ALL the tables. No benefit there at all. And how do you
decide which table to insert into? It needs to be based on some business
logic, not just pulled out of thin air. Stop looking at the size of the
tables and start looking at the logic for partitioning.
John Scragg
"amish" wrote:
> Hi
> Thanks for the reply
> Actually I have joined a new company.
> Here we have some tables of 20 gb to 50 gb in size.
> So I just want to know should I partition this tables?
> I think if I will partition it, reindex and other management activity
> will be easier for me.
> I want to partition this tables so I just want to find on which
> criteria and how many partition should I create for this tables.
> Regards
>