Partition Optimization

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!

Re: Partition Optimization

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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Partition Optimization

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 wrote:
> traceable1 (thham...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx- Hide quoted text -
>
> - Show quoted text -

Re: Partition Optimization

traceable1 (thhamlin@gmail.com) writes:
> So, is the groupid = data_space_id?

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.

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

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Partition Optimization

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 wrote:
> traceable1 (thham...@gmail.com) writes:
> > So, is the groupid = data_space_id?
>
> 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.
>
> > Then is the data_space_id = partition_number? I'm still having
> > trouble getting from allocation_units to partitions.
>
> No, the data_space_id is not thepartitionnumber. 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 thepartitionnumber.
>
> 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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Re: Partition Optimization

traceable1 (thhamlin@gmail.com) writes:
> 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.

Great to hear that you sorted it out, and thanks for posting your
query!



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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx