March 7, 2012 at 7:21 am
there is server that have 8 cpus and disk raid 5
if i need to design a partition table about users table that have mare than 100 million rows
1.how many paritions maybe get performance increase?
2.how many rows in each partiton ?
how to do and what matter claim attention ?
March 7, 2012 at 12:12 pm
There is no magic formula for this. You have to figure out what is the most appropriate for you situation.
Read up on it here. http://msdn.microsoft.com/en-us/library/ms190787.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2012 at 1:42 pm
1.how many paritions maybe get performance increase?
You don't partition for performance, you partition for data management and because it makes sense logically.
2.how many rows in each partiton?
How much space do you have and what is the logical column to partition on? How much data do you have and how often and much does it change?
Why are you thinking about partitioning? We partition because the backups are too large as a full backup and the reports are monthly. Also, the data does not change once written. So for us it makes sense to partition by month and make those filegroups read-only. We then back them up once individually and move them to a secure location. It is not about performance, it is about a better way to manage the administrative aspects of the data.
Jared
CE - Microsoft
March 7, 2012 at 2:48 pm
Depending on the specifics of the implementation, you may find that the easiest way to improve performance is to get off RAID5.
Partitioning would allow you to split your table across multiple filegroups, but if those filegroups are all on the same file system then I don't expect to see much improvement.
March 7, 2012 at 2:52 pm
Toby Harman (3/7/2012)
you may find that the easiest way to improve performance is to get off RAID5.
We don't even know what performance the OP is talking about. I suspect that the OP thinks that this will speed up queries without there being a problem to even begin with. A common misconception about partitioning in general.
Jared
CE - Microsoft
March 7, 2012 at 3:29 pm
I agree with Jared. Partitioning is not intended to be a solution for query performance but for data administration, like helping during archiving and purging processes.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 7, 2012 at 3:48 pm
Without wishing to start some kind of "Oh yes it is" "Oh no it isn't" discussion, partitioning can be used to improve performance on large data sets. That's the point of Hadoop!
On the other hand, we are responsing to a very vague requirement of:
nobking (3/7/2012)
how many partitions maybe get performance increase?
Partitioning allows us to spread a large table across multiple filegroups based on specific attributes. Those filegroups can be placed on different filesystems.
This allows us do a number of things, including manage backups, move less frequently accessed data to less capable storage, and even reduce our index rebuild times.
I think to take this further the OP needs to provide some more info.
March 7, 2012 at 8:12 pm
SQLKnowItAll (3/7/2012)
1.how many paritions maybe get performance increase?
You don't partition for performance, you partition for data management and because it makes sense logically.
2.how many rows in each partiton?
How much space do you have and what is the logical column to partition on? How much data do you have and how often and much does it change?
Why are you thinking about partitioning? We partition because the backups are too large as a full backup and the reports are monthly. Also, the data does not change once written. So for us it makes sense to partition by month and make those filegroups read-only. We then back them up once individually and move them to a secure location. It is not about performance, it is about a better way to manage the administrative aspects of the data.
first,thank for your suggestion
i want to partition the userinfo table because i thought that maybe improve performance of select operation
it can use 8 cpu to query each partition to get data by tablescan or indexscan or seek
March 7, 2012 at 8:42 pm
nobking (3/7/2012)
i want to partition the userinfo table because i thought that maybe improve performance of select operation
it can use 8 cpu to query each partition to get data by tablescan or indexscan or seek
Time to post the SELECT statement and a query plan I reckon.
March 8, 2012 at 8:01 am
nobking (3/7/2012)
SQLKnowItAll (3/7/2012)
1.how many paritions maybe get performance increase?
You don't partition for performance, you partition for data management and because it makes sense logically.
2.how many rows in each partiton?
How much space do you have and what is the logical column to partition on? How much data do you have and how often and much does it change?
Why are you thinking about partitioning? We partition because the backups are too large as a full backup and the reports are monthly. Also, the data does not change once written. So for us it makes sense to partition by month and make those filegroups read-only. We then back them up once individually and move them to a secure location. It is not about performance, it is about a better way to manage the administrative aspects of the data.
first,thank for your suggestion
i want to partition the userinfo table because i thought that maybe improve performance of select operation
it can use 8 cpu to query each partition to get data by tablescan or indexscan or seek
Before you even post the SELECT query... IS there a problem with performance? Partitioning is not going to force paralellism, so the number of CPUs is irrelevant (I think). In terms of getting data by "tablescan, indexscan, or seek", again, partitioning is not going to help you with this.
Let's start at IS there an issue with the query? If there is, please post the query and DDL according to the post in my signature by Jeff Moden. You don't start troubleshooting performance by partitioning.
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply