Where to see how a Partition schema and function is defined
-
Please see the below. The dependencies of a table(the table depends on) shows Partition function and partition scheme. However I cant seem to find how and where this function is defined? I can see only user defined functions? Please guide on this
-
within SSMS
Database ->Storage->Partition Functions
Database ->Storage->Partition Schemes
-
Here's a nice overview:
with ctePartRangesFULL
as (
select distinct
p.[object_id]
, OBJECT_SCHEMA_NAME(p.[object_id]) as TbSchema
, OBJECT_NAME(p.[object_id]) as TbName
, p.index_id
, i.name as IxName
, p.partition_number
, p.rows
, p.data_compression_desc
, au.total_pages
, prv.value as Boundary_Value
, ds1.NAME AS [FILEGROUP_NAME]
, pf.boundary_value_on_right
, au.type
, au.type_desc
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
INNER JOIN sys.data_spaces ds1
on ds1.data_space_id = dds.data_space_id
INNER JOIN sys.allocation_units au
ON au.container_id = p.partition_id
left outer JOIN sys.partition_range_values prv
on prv.function_id = ps.function_id
and p.partition_number = prv.boundary_id
WHERE p.index_id = 1
)
, ctePartRangesTot as (
Select [object_id]
, index_id
, partition_number
, sum( total_pages ) as TotalPages
from ctePartRangesFULL
group by [object_id]
, index_id
, partition_number
)
Select R1.[object_id]
, R1.TbSchema
, R1.TbName
, R1.index_id
, R1.IxName
, R1.partition_number
, R1.data_compression_desc
, R1.rows
, T.TotalPages
, T.TotalPages * 8 / 1024 total_MB
, R2.Boundary_Value as LEFT_Boundary_Value
, R1.Boundary_Value
, R1.[FILEGROUP_NAME]
, R1.boundary_value_on_right
, CASE R1.boundary_value_on_right
WHEN 1 THEN '<'
ELSE '<='
END as 'Boundary_Comparison'
, CASE WHEN ISNULL(R1.Boundary_Value, R2.Boundary_Value) IS NULL THEN 'N/A'
ELSE CASE WHEN R1.boundary_value_on_right = 0
AND R2.Boundary_Value IS NULL THEN '>='
WHEN R1.boundary_value_on_right = 0 THEN '>'
ELSE '>='
END + ' [' + ISNULL(CONVERT(varchar(128), R2.Boundary_Value), 'Min. Value') + '] ' + CASE R1.boundary_value_on_right
WHEN 1 THEN 'and <'
ELSE 'and <='
END + ' [' + ISNULL(CONVERT(varchar(128), R1.Boundary_Value), 'Max. Value') + ']'
END as 'Boundary_Expression'
from ctePartRangesFULL R1
left join ctePartRangesFULL R2
on R2.object_id = R1.object_id
and R2.index_id = R1.index_id
and R2.type = R1.type
and R2.partition_number = R1.partition_number - 1
inner join ctePartRangesTot T
on T.object_id = R1.object_id
and T.index_id = R1.index_id
and T.partition_number = R1.partition_number
where R1.type = 1
--and R1.[object_id] = object_id ( 'Logs_Payload' )
order by TbSchema
, TbName
, index_id
, partition_number ;Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply