traceable1 (thhamlin@gmail.com) writes:
> I am trying to update my optimization jobs so they will not optimize
> the older partitions (for performance and snapshot space reasons).
>
> ALTER INDEX {indname} ON {tabname} REORGANIZE PARTITION = ?
>
> I can get the partition, but my question is, how do I determine if the
> index is in a partition I want to optimize?
>
> I would like to use my groupname column in sysfilegroups, because I've
> named them by year (YearFG04, YearFG05, etc). That is, I want to
> optimize all indexes which DO NOT belong to YearFG04, YearFG05 and
> YearFG06, but I DO want to optimize YearFG07 and PRIMARY.
>
> But I don't know how to tie this back to the partition_number or
> partition_id and therefore the index_id.
>
> I've been looking at this all day, and I'm sure I'm blind, but I
> cannot seem to find what i need.

There are the view sys.partition_functions, sys.partition_parameters
and sys.partition_range_values, but it does not seem exactly trivial
to unwind them.

If you want to work by filegroup name, it may be better to work from
sys.allocation_units, which has a data-space id which is a file
group id. And from sys.allocation_units you can work your way to
sys.partitions.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx