March 13, 2015 at 9:26 am
Hi All... words of wisdom on a Friday if you please .. Is it possible to show the number of rows and the range for each partition in a table ?
This shows me the range but not the row count per partition
SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv
ON sprv.function_id=spf.function_id
WHERE (spf.name=N'myDateRangePF')
ORDER BY [ID] ASC
Thanks
Simon
March 13, 2015 at 9:34 am
Here's the script I use to analyse partitions, hope it helps
SELECT
t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id,
i.data_space_id, f.function_id, f.type_desc, fg.name AS [filegroup], r.boundary_id, r.value AS BoundaryValue, p.rows,
r.*
FROM
sys.tables AS t
INNER JOIN
sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN
sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON a.container_id = p.hobt_id
INNER JOIN
sys.filegroups fg ON fg.data_space_id = a.data_space_id
INNER JOIN
sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN
sys.partition_functions AS f ON s.function_id = f.function_id
LEFT OUTER JOIN
sys.partition_range_values AS r ON f.function_id = r.function_id AND r.boundary_id = p.partition_number
WHERE
t.name = 'Table name'
AND
i.type <= 1
AND
a.type = 1 --in row data only
March 13, 2015 at 9:56 am
thats brilliant thank you Sir !
March 13, 2015 at 10:24 am
One question, why does it return NULL for the boundary_id for the final partition ? I understand why the boundary_value is NULL but the ID ..not quite sure !
March 16, 2015 at 4:07 am
Sorry for the late response, it's been St Paddy's Day weekend over here (not to mention the rugby)!
The reason that the script returns null for the boundary_id is that value comes from the sys.partition_range_values table which does not hold a record for the open ended "partition" at the end of the table (if that's the best way to describe it).
Change the LEFT OUTER JOIN to that table to an inner join and the end row will not be in the output.
I'm guessing that even though it is technically a partition it is in fact the rest of the table, it does not have a boundary value and as such has no record in the sys.partition_range_values table
Someone correct me if I'm wrong...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply