Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1|||Hi!

> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
Partitions do not map one-to-one to filegroups. You can have multiple
partitions on a single filegroup. You can find basic info about partitions
in sys.partitions view or with $PARTITION function, like
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
To find number of rows in different filegroups, the query gets complicated:
WITH
PartitionRowCount(Partition, NumberOfRowsInPartition) AS
(
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
),
PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
DataSpaceName) AS
(
SELECT dds.partition_scheme_id,
dds.destination_id,
dds.data_space_id,
ds.name
FROM sys.destination_data_spaces dds
INNER JOIN sys.indexes i
ON dds.partition_scheme_id = i.data_space_id
INNER JOIN sys.data_spaces ds
ON dds.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('PartitionTable')
AND i.index_id < 2
)
SELECT pds.DataSpaceName,
SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
FROM PartitionRowCount prc
INNER JOIN PartitionsDataSpaces pds
ON prc.Partition = pds.Partition
GROUP BY pds.DataSpaceName;
I hope I did not miss something.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
--
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]
')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links
to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/function
s
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>|||sys.partitions - will give the row-count for the table/individual parititons
,
so that is not an issue, Im more interested in a join based query for all th
e
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated
:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>|||> this does not list the filegroup names.
It lists data space names for filegroups. Data space names include filegroup
& partition scheme names.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
-- F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual paritito
ns,
> so that is not an issue, Im more interested in a join based query for all
the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particula
r
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resid
es
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote:
>