Question
Marc Jellinek on Wed, 19 Apr 2017 13:34:16
I inherited an instance of Azure SQL Data Warehouse. Within SQLDW, there is a table which is partitioned on a date column.
I'm looking to archive off 2016 data by switching partitions to another table. But I can't seem to determine which partitions hold 2016 data.
I've run the following query, which tells me how many partitions are associated with the table, but I can't figure out which partitions hold 2016 data.
SELECT sch.name,
tbl.name,
idx.name,
part.*
FROM sys.schemas sch
INNER JOIN sys.tables tbl ON sch.schema_id = tbl.schema_id
INNER JOIN sys.partitions part ON tbl.object_id = part.object_id
INNER JOIN sys.indexes idx ON part.object_id = idx.object_id AND part.index_id = part.index_id
WHERE sch.name = 'myschema' AND
tbl.name = 'mytable'
How can I determine which partitions hold 2016 data?
Replies
Markus Bohse on Wed, 19 Apr 2017 14:31:07
Just answered the same question on Stackoverflow, but here it is for others to enjoy as well.
Since this is Azure SQL Data warehouse I assume your table has a clustered colmnstore index. In that case you can use pdw_nodes_column_store_segments to find the partition. Try something like this:
DECLARE @object int
SET @object = OBJECT_ID('YourTable')
SELECT Object_name(p.object_id) as TableName, p.partition_number,
c.name,
min_data_id, max_data_id, segment_id, s.row_count
FROM [sys].[pdw_nodes_column_store_segments] s
INNER JOIN sys.partitions AS p
ON s.hobt_id = p.hobt_id
JOIN sys.columns c
ON c.object_id =p.object_id
AND c.column_id = s.column_id
WHERE p.object_id = @object ORDER BY p.partition_number, s.column_id, segment_id;
GO
Marc Jellinek on Wed, 19 Apr 2017 15:19:02
I went a different route. Rather that using pdw_nodes_column_store_segments, I used sys.data_spaces, sys.partition_schemes, sys.partition_functions and sys.partition_range_values. Is there a benefit to using pdw_nodes_column_store_segments?
SELECT sch.name AS [schema_name],tbl.[name] AS [table_name],
ds.type_desc,
prt.[partition_number],
rng.[value] AS [current_partition_range_boundary_value],
prt.[rows] AS [partition_rows]
FROM sys.schemas sch
INNER JOIN sys.tables tbl ON sch.schema_id = tbl.schema_id
INNER JOIN sys.partitions prt ON prt.[object_id] = tbl.[object_id]
INNER JOIN sys.indexes idx ON prt.[object_id] = idx.[object_id] AND prt.[index_id] = idx.[index_id]
INNER JOIN sys.data_spaces ds ON idx.[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]
LEFT JOIN sys.partition_range_values rng ON pf.[function_id] = rng.[function_id] AND rng.[boundary_id] = prt.[partition_number]
WHERE sch.name = 'dbo' AND
tbl.name = 'AppTrax'