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.


thank you so much!

traceable1 (thhamlin (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

Thank you!

So, is the groupid = data_space_id?

Then is the data_space_id = partition_number? I'm still having
trouble getting from allocation_units to partitions.

thanks!



On Sep 13, 5:04 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

traceable1 (thhamlin (AT) gmail (DOT) com) writes:
The relation of sys.data_spaces, sys.destination_data_spaces and
sys.filegroups is a bit complicated. A filegroup is a data space,
but the opposite does not apply.

No, the data_space_id is not the partition number. Instead you join
sys.allocation_units to sys.partitions over the container_id,
and to make it even more complicated, you join to different columns
in sys.partitions depending on type of allocation unit. In sys.partitions
you find the partition number.

I'm sorry that I don't simply give you a query, but I don't have any
multiple-filegroup database set up, so I can't test.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

Thank you so much!
i have the query:

select *
from sys.data_spaces ds,
sys.allocation_units au,
sys.partitions p,
sysindexes si
WHERE
si.name = @indname
AND si.id = p.object_id
AND si.indid = p.index_id
AND p.hobt_id = au.container_id
AND au.data_space_id = ds.data_space_id


If this comes up with nothing, it is not a partitioned index.
If it is a partitioned index, i can get the file group names from this
and only optimize the PRIMARY and 2007 partitions.

thank you!!





On Sep 14, 4:24 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

traceable1 (thhamlin (AT) gmail (DOT) com) writes:
Great to hear that you sorted it out, and thanks for posting your
query!



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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