August 26, 2011 at 3:16 am
I have an partitioned table, with many partitioned indexes.
To minimize the index rebuild/reorg time i only want to perform maintenance on the partitions of the index that are used the most.
I can see the table, partitions and indexes, but i cannot see usage by index partition, only by entire index. The code i am using is:
select db_name() as database_name
,object_name(p.object_id) as table_name,
i.name,
p.partition_number,
p.rows,
pf.boundary_value_on_right,
prv.value as range_value,
pf.name as partition_function,
us.user_scans,
us.user_seeks
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
left outer join
sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id
inner join
sys.dm_db_index_usage_stats us on us.index_id = i.index_id
--where i.index_id =1
order by us.user_seeks desc ,us.user_scans desc
(code taken from here and slightly modified, author has specified it can be used and modified)
I also want to see the index size broken down by partition but have no idea where to start with this one yet
Thanks all.
August 26, 2011 at 4:33 am
I have found the row count per partition. If i cross apply on the function sys.dm_db_index_physical_stats I can then pull the record_count which is the rows per partition.
I still cannot identify the index usage per partition.
August 26, 2011 at 4:45 am
Index usage is tracked on an index level, not partition level
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply