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?


Sponsored



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'