July 17, 2010 at 4:15 pm
I want to optimize the performance of a large table by partitioning it and storing each partition in a separate filegroup. The database and the table have been in use for the last 12 months and are currently stored within a single filegroup.
To do this I will create the partition function and scheme.
Must I also drop and recreate any indexes that are on this table?
Will I have to do this with the database off-line?
Thanks
July 17, 2010 at 8:24 pm
hxkresl (7/17/2010)
I want to optimize the performance of a large table by partitioning it and storing each partition in a separate filegroup. The database and the table have been in use for the last 12 months and are currently stored within a single filegroup.To do this I will create the partition function and scheme.
Must I also drop and recreate any indexes that are on this table?
Will I have to do this with the database off-line?
It depends. If the existing table has a clustered index (rather than a clustered primary key), you can achieve online repartitioning using the CREATE CLUSTERED INDEX x ON table (columns) WITH (ONLINE = ON, DROP_EXISTING = ON) ON partition_scheme (partitioning column) syntax. This assumes that the table meets all the other conditions for online index operations (no LOB columns, for example). See Books Online for more details on that.
If the clustered index is UNIQUE, it must also include the partitioning column in its key.
Other non-clustered indexes will not automatically be partitioned by this operation. If you decide to align the indexes (partition them using the same scheme as for the clustered index), you can use the same syntax as above, with the same considerations.
You might find it worthwhile switching to the BULK_LOGGED recovery model for the index operations. Be sure to back the log up just before switching to BULK_LOGGED and just after switching back to FULL.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 18, 2010 at 9:27 am
I am working my way through this answer. Thanks for all the help, Paul NZ!
July 18, 2010 at 11:04 am
Paul already provided the answer, but partitioning need good preparation, to get the best results in the long term !
- Did you read through the BOL article on partitioning ?
- "Database Partitioning Myths and Truths" http://www.mssqltips.com/tip.asp?tip=1914
and off course
the great white papers:
- "Partitioned Tables and Indexes in SQL Server 2005" http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx
- "Partitioned Table and Index Strategies Using SQL Server 2008" http://msdn.microsoft.com/en-us/library/dd578580%28SQL.100%29.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 18, 2010 at 11:21 am
I have googled and read and reviewed the training kit but there were many pieces and finally I wanted an answer framed to my specific question.
ALZDBA, the recommended links are very appreciated. I truly love this forum and all your help.
July 19, 2010 at 12:20 am
hxkresl (7/18/2010)
I am working my way through this answer. Thanks for all the help, Paul NZ!
Cool - let us know if anything still puzzles you.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply