October 2, 2011 at 8:54 am
Hi,
I have a general ledger table with approx 50 million records. There is a decision made in the revision meeting to partition this table. This will be partitioned on the basis of Financial Year. What should be the strategy here ? On a high level what should be the steps involved ? Could any one help please..
October 2, 2011 at 9:56 am
What's the reason for partitioning the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2011 at 10:19 am
I am not very sure. But they said it is to improve the performance and efficient file storage management
October 2, 2011 at 11:31 am
Partitioning for performance seldom works. That's just not a good reason to partition tables in general.
The high level steps are detailed in Books Online. Create the partition scheme (and that's going to why the table is being partitioned, what filegroups the database has and several other things), create the partition function, rebuild the clustered index using the partition function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2011 at 2:58 pm
I've read that partitioning can enhance performance if it is done for the right reasons. In this case, if most of the queries are restricted to the current fiscal year then partitioning the table could help with performance.
October 2, 2011 at 5:24 pm
Lynn Pettis (10/2/2011)
I've read that partitioning can enhance performance if it is done for the right reasons. In this case, if most of the queries are restricted to the current fiscal year then partitioning the table could help with performance.
It could, but appropriately chosen indexes could do the same or better. For partitioning to really help performance, the queries would be scanning the partition, and unless they really do need the entire of that partition, that means they could probably do better with better indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2011 at 7:01 pm
Thank you all 🙂
Another question is ,
This table will be loaded with data from a feed. At the time of loading , the same table is also used by some select queries running in a reporting tool. This sometimes creates a deadlock issue. Will there be any impact on this by partitioning ?
October 2, 2011 at 11:54 pm
Maybe, but probably not. (Can't say more with such little info)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2011 at 6:34 am
Partitioning is an ADVANCED feature and you will very likely get yourself into unfortunate situations/scenarios if you ask for a few pointers on a forum, read a blog post or two and perhaps some of BOL and then slap it out there in production. Please do NOT do that. Get some professional help to most importantly help you analyze your situation to determine if it is appropriate for you at all. 50M records is chump-change in this day and age. If you cannot perform acceptably with so few rows you have really got some serious issues with code/design/indexing/HARDWARE.
For the deadlock stuff, see here for some guidance: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx. Note there are two additional parts to this blog series.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 8, 2011 at 7:16 am
TheSQLGuru (10/3/2011)
Partitioning is an ADVANCED feature and you will very likely get yourself into unfortunate situations/scenarios if you ask for a few pointers on a forum, read a blog post or two and perhaps some of BOL and then slap it out there in production. Please do NOT do that. Get some professional help to most importantly help you analyze your situation to determine if it is appropriate for you at all. 50M records is chump-change in this day and age. If you cannot perform acceptably with so few rows you have really got some serious issues with code/design/indexing/HARDWARE.
I have to agree 50M rows is not a large table - if we are talking about a DSS environment.
Also, I have to agree this particular posting appears to fall in the "wanna partition that table because partitioning a table sounds kind of cool" category.
On the other hand, I have to disagree in regards to the use of documentation and forums as a way to get initial information and feedback... do you agree with me Gail's advice is spot-on and trully professional?
_____________________________________
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.November 9, 2011 at 7:05 am
PaulB-TheOneAndOnly (11/8/2011)
TheSQLGuru (10/3/2011)
Partitioning is an ADVANCED feature and you will very likely get yourself into unfortunate situations/scenarios if you ask for a few pointers on a forum, read a blog post or two and perhaps some of BOL and then slap it out there in production. Please do NOT do that. Get some professional help to most importantly help you analyze your situation to determine if it is appropriate for you at all. 50M records is chump-change in this day and age. If you cannot perform acceptably with so few rows you have really got some serious issues with code/design/indexing/HARDWARE.I have to agree 50M rows is not a large table - if we are talking about a DSS environment.
Also, I have to agree this particular posting appears to fall in the "wanna partition that table because partitioning a table sounds kind of cool" category.
On the other hand, I have to disagree in regards to the use of documentation and forums as a way to get initial information and feedback... do you agree with me Gail's advice is spot-on and trully professional?
1) My post has nothing to say about whether or not anyone else's post is professional or accurate.
2) My post said nothing about gathering "initial information and feedback", so that part of your posting is off as well.
3) I will absolutely stick by my entire post regarding trying to implement partitioning by using some forum posts or other methods like I mentioned. And I get paid good money to clean up messes that people create when they do things like that so I really have basis for my recommendation. The OP is heading for a world of pain and I am doing what I always do here - give them the best advice and guidance I can, even if it isn't what they (or others) want to hear. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 16, 2011 at 1:56 pm
TheSQLGuru (11/9/2011)
PaulB-TheOneAndOnly (11/8/2011)
TheSQLGuru (10/3/2011)
Partitioning is an ADVANCED feature and you will very likely get yourself into unfortunate situations/scenarios if you ask for a few pointers on a forum, read a blog post or two and perhaps some of BOL and then slap it out there in production. Please do NOT do that. Get some professional help to most importantly help you analyze your situation to determine if it is appropriate for you at all. 50M records is chump-change in this day and age. If you cannot perform acceptably with so few rows you have really got some serious issues with code/design/indexing/HARDWARE.I have to agree 50M rows is not a large table - if we are talking about a DSS environment.
Also, I have to agree this particular posting appears to fall in the "wanna partition that table because partitioning a table sounds kind of cool" category.
On the other hand, I have to disagree in regards to the use of documentation and forums as a way to get initial information and feedback... do you agree with me Gail's advice is spot-on and trully professional?
1) My post has nothing to say about whether or not anyone else's post is professional or accurate.
2) My post said nothing about gathering "initial information and feedback", so that part of your posting is off as well.
In regards to #2-- I'm really sorry my translation of your, let me quote "...you will very likely get yourself into unfortunate situations/scenarios if you ask for a few pointers on a forum, read a blog post or two and perhaps some of BOL and then slap it out there in production" phrase was not good enough for you - my fault.
In regards to #1-- Can be easily derived from #2 😉
_____________________________________
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.Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply