May 17, 2011 at 5:21 am
Our application has got few tables which has grown to a larger size. Hence one suggestion put forward is to consider table partitioning. Since this does not involve any code changes in the application, we are looking into this option.
1. Is there any disadvantage if we use table partitioning feature
2. Will table partitioning affect the current indexes that exist on the table?
3. Any suggestion on how we should try this out?
What are the effects of Partitioning over the list given below
❑ Database backup and restore strategy (support for partial database availability).
❑ Index maintenance strategy (rebuild), including index views.
❑ Data management strategy (large insert or table truncates).
❑ End-user database workload.
I want to dig out all things before go to Live environment.
Thanks in Advance
Azhar
May 17, 2011 at 8:55 am
With well over 40 views and no responses it should be clear there is no exact and certainly not a simple answer. It also sounds like you need to do a lot of research to understand table partitioning.
Here is a great place to start.
_______________________________________________________________
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/
May 17, 2011 at 9:30 am
Partitioning is helpful, but you will need to test the affect it will have on your environment. You will need to gather your queries pertaining to the table you want to partition and compare the different query statistics to determine if it will help or hurt performance. It helps manageability of the indexes since you can defragment them by partition and you can easily move a lot of data by partition switching. Partial database restores help in restoring the database in pieces faster. The best thing you can do is test it out in a test environment and read as much as you can regarding partitioning so you can learn what others have encountered.
For me personally, there's never enough information out there to fully satisfy my questions about this topic so I've tested it before settling on a resolution. Take a look at my blog for my environment's results.
May 17, 2011 at 9:39 am
azhar.iqbal499 (5/17/2011)
1. Is there any disadvantage if we use table partitioning feature
There are always pros and cons. In this case there is a management and set up overhead as you will want to add additional partitions (perhaps archive old ones) on occasion.
2. Will table partitioning affect the current indexes that exist on the table?
Yes. You will need to completely rebuild the table. Probably the best idea is to create you partitioned table, migrate the data and either rename the tables to make the switch or create a view of the same table name.
3. Any suggestion on how we should try this out?
As above. In the past, I've created a trigger on the source table and left it running for a period of time before making the switch but this may not be appropriate for you.
What are the effects of Partitioning over the list given below
? Database backup and restore strategy (support for partial database availability).
None
? Index maintenance strategy (rebuild), including index views.
? Data management strategy (large insert or table truncates).
? End-user database workload.
There should be limited impact on these. I dont currently create partitions across file groups due to our storage set up but it may be something for you to consider
May 17, 2011 at 11:09 pm
Thanks for proper Explanation.
Pl Tell me, Whether table partition improve select query performance or not?
Thanks
May 19, 2011 at 6:23 am
azhar.iqbal499 (5/17/2011)
Thanks for proper Explanation.Pl Tell me, Whether table partition improve select query performance or not?
Thanks
Partitioning is a VERY complex scenario with MANY ways you can screw it up. Without prior training and experience on it you have very little hope of doing things optimally, and can in fact do them disastrously wrong. You REALLY need to get a professional on board to help you a) understand your needs and b) help you accomplish them (partitioning or not).
To directly answer your question: table partitioning 'may' improve SELECT performance, but quite a few things have to be in place for it to do so. Partitioning was NOT developed primarily to improve performance - it was primarily for MAINTENANCE.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply