Posts
49
Comments
50
Trackbacks
26
April 2006 Blog Posts
Partitioning: Two useful query

To obtain all information on how a table has been partitioned I usually use two views:

The first just list all the partitened objects, showing the used partitioned scheme and function:

CREATE view [sys2].[partitioned_objects]
as
select
distinct
   
p.object_id,
   
index_name = i.name,
   index_type_desc = i.type_desc,
   partition_scheme = ps.name,
   data_space_id = ps.data_space_id,
   function_name = pf.name,
   function_id = ps.function_id
from 
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id

That sys2.partitioned_object views is very useful to see how a table has been partitioned:

select * from sys2.partitioned_objects where object_id = object_id('table_name')

with the above query will list table and related indexes partition information.

Once you know what partition scheme and function the table and related indeexes uses for partitioning you may also what to see how many partition actually exists, what range values are used and so on.

This view will help you:

create view [sys2].[partitioned_objects_range_values]
as
select
 
   
p.object_id,
   p.index_id,
   p.partition_number,
   p.rows,
   index_name = i.name,
   index_type_desc = i.type_desc,
   i.data_space_id,
   pf.function_id,
   pf.type_desc,
   pf.boundary_value_on_right,
   destination_data_space_id = dds.destination_id,
   prv.parameter_id,
   prv.value
from
 
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id
inner join
   sys.destination_data_spaces dds on dds.partition_scheme_id = ds.data_space_id and p.partition_number = dds.destination_id
left outer join
   sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id

To use it, just use as the one showed before:

select * from sys2.partitioned_objects_range_values where object_id = object_id('table_name')

The query will produce a list with all partitions and all range values for the object 'table_name'

I've put my "system" views into a schema named sys2, so be sure to create it or change that create view statement to create views in your own schema.

posted @ Thursday, April 20, 2006 3:50 PM | Feedback (0)
Logging With SSIS: Beware of Parent Package Variable configuration!

In my previous post I said that "logging begins BEFORE validation and BEFORE package configuration". After talking with Mr. Kirk Haselden (who has been very kind and helped me to better understand how SSIS really works. Thanks a lot Kirk!) I found a clue and a workaround.

The problem, basically, is related to the fact that the configuration "Parent Package Variable" behaves differently from all other configurations. In fact, for the Parent Package Variable configuration, events are fired in the following way:

Logging starts
Package is validated
Parent Package Variable Configurations are loaded
Expression are applied
Package is run

which is a different from all other configuration where the configuration is the first event. So, if you need to make the logging connection manager configurable at run time you MUST avoid using Parent Package Variables and you should use the Enviroment Variables instead.

posted @ Sunday, April 02, 2006 10:50 AM | Feedback (2)
News