Partition Optimization

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

traceable1 - 13 Sep 2007 19:56 GMT

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!

Reply to this Message

Erland Sommarskog - 13 Sep 2007 23:04 GMT

> I am trying to update my optimization jobs so they will not optimize
> the older partitions (for performance and snapshot space reasons).
[quoted text clipped - 14 lines]
> 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.

Signature

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

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

Reply to this Message

traceable1 - 14 Sep 2007 15:18 GMT

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!

> > I am trying to update my optimization jobs so they will not optimize
> > the older partitions (for performance and snapshot space reasons).
[quoted text clipped - 31 lines]
>
> - Show quoted text -

Reply to this Message

Erland Sommarskog - 14 Sep 2007 22:24 GMT

> 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.

Signature

Reply to this Message

traceable1 - 28 Sep 2007 16:55 GMT

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!!

> > So, is the groupid = data_space_id?
>
[quoted text clipped - 19 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Erland Sommarskog - 28 Sep 2007 22:05 GMT

> Thank you so much!
> i have the query:
[quoted text clipped - 14 lines]
> 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!

Signature

Reply to this Message