December 11, 2009 at 5:31 am
Interested in strategies for maintaining Terabyte tables.
Partitioning in 2005 and 2008 is severely limited by the inability to rebuild indexes on a partition basis. Partitioning can be combined with the use of views and constraints but there is a concern about optimal query plans. Any thoughts out there?
Nigel Moore
======================
December 11, 2009 at 12:47 pm
Here's an article about the very topic (table partitioning, reindexing, and VLDBs)
http://www.mssqltips.com/tip.asp?tip=1200
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 11, 2009 at 3:41 pm
Thanks for the reply but my initial statement stands. There is no benefit in partitioning SQL Server 2005 indexes when it comes to maintaining these indexes as you cannot rebuild an index for an individual partition you have to rebuild it for the whole table. You can use views over tables which can then be managed separately but there is then the concern that the SQL Server engine will not recognize this as equivalent when it comes to query plan generation.
Nigel Moore
======================
December 11, 2009 at 4:57 pm
Are you saying this based on experience?
According to Microsoft http://msdn.microsoft.com/en-us/library/ms188388.aspx
PARTITION
Specifies that only one partition of an index will be rebuilt or reorganized. PARTITION cannot be specified if index_name is not a partitioned index.
PARTITION = ALL rebuilds all partitions.
partition_number
Is the partition number of a partitioned index that is to be rebuilt or reorganized. partition_number is a constant expression that can reference variables. These include user-defined type variables or functions and user-defined functions, but cannot reference a Transact-SQL statement. partition_number must exist or the statement fails.
Since the table would be partitioned across multiple partitions, you would be able to rebuild the indexes just for one partition of that table, it is not required to rebuild the entire table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 14, 2009 at 1:46 am
As Jason rightly points out, you can (usually) rebuild an index a partition at a time, or indeed for more than one partition in 2008. So long as no LOB columns are included in the index - which means explicitly INCLUDEd for non-clustered index, but annoyingly means any column in the table for the clustered index. Bah.
Now, obviously you need Enterprise Edition (or equivalent), but the real annoyance is that a partitioned index cannot be rebuilt online. So, if you were hoping to rebuild indexes online, partition by partition, you can't - sorry about that.
This has always seemed like a glaring omission to me, since the sort of environments that need Enterprise Edition and partitioning, are very likely to also have a very minimal (or no) maintenance window. Offline operations suck in such cases.
The implication in Books Online is that you can't even rebuild the whole (partitioned) index online - something that seems wrong to me, but I am sans-SQL tonight so cannot test this. "Rebuilding a partitioned index cannot be performed online. The entire table is locked during this operation."
The ability to rebuild at the partition level while keeping the index and table available would be fantastic. Maybe in a future version...but not R2.
December 14, 2009 at 4:32 am
Yes Enterprise Editon and it has to be ONLINE, apologies for omitting that.
Nigel Moore
======================
December 14, 2009 at 10:33 am
Thanks Paul for clarifying.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 14, 2009 at 2:13 pm
Cheers Jason.
Now that we know more about the requirements, it may be that partitioning is not a good solution. The requirement to build ONLINE would certainly have been a good detail to include in post #1 🙂
Partitioned views can perform well, so long as partition elimination can be performed at compile time OR if OPTION (RECOMPILE) is used (2008 only).
December 14, 2009 at 3:46 pm
This has always seemed like a glaring omission to me, since the sort of environments that need Enterprise Edition and partitioning, are very likely to also have a very minimal (or no) maintenance window. Offline operations suck in such cases.
This is why I posted this issue. If you have an environment that is collecting data real time 24/7, 365 and users require the ability to query all that data 24/7, 365 SQL Server partitioning has a number of issues.
No ONLINE partition level rebuilding of Indexes.
A limit to the number of partitions a table can have before there are issues.
The requirement that all partitions that are to be involved in the quick switch operations reside on the same filegroup
and the difficulty of moving the partition boundaries when data resides in an existing partition.
Nigel Moore
======================
December 14, 2009 at 4:03 pm
nmoore (12/14/2009)
No ONLINE partition level rebuilding of Indexes.
True, but you can still ONLINE rebuild the whole index (I just tried this).
nmoore (12/14/2009)
A limit to the number of partitions a table can have before there are issues.
More of a problem in 2005 than 2008. Much improved in 2008 to the point where this is no longer an issue.
nmoore (12/14/2009)
The requirement that all partitions that are to be involved in the quick switch operations reside on the same file group and the difficulty of moving the partition boundaries when data resides in an existing partition.
Both these seem logical to me and no better alternative exists. It is frequently possible to design around these issues.
One restriction that does irritate me is that a database with so much as a partition scheme or function defined (not necessarily in use!) cannot be restored or attached to any edition except Enterprise (or equivalent). Something to bear in mind if you plan to make a copy of the production database available for reporting or analytics for example.
If you're stuck with 2005 for the moment, partitioned views might be for you.
December 15, 2009 at 9:49 am
Some of us on the SQL Server Microsoft MVP forum have been pushing hard for full and complete implementation of all possible actions, optimizer effectiveness, etc, etc with respect to partitioned tables. It irks me to no end some of the things they are working on while items like this that MANY can benefit from are neglected. grrrr!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 16, 2009 at 2:59 pm
Transportable filegroups would be nice.
🙂 it is almost Christmas. 🙂
Nigel Moore
======================
December 16, 2009 at 5:03 pm
nmoore (12/16/2009)
Transportable filegroups would be nice.
You'd need to downgrade to Oracle for that.
December 17, 2009 at 12:55 pm
Paul White (12/16/2009)
nmoore (12/16/2009)
Transportable filegroups would be nice.You'd need to downgrade to Oracle for that.
nice
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply