Exploring Partitioning DMV’s on SQL 2005+

I’ve been involved with some customers where partitioning is quite complex (40+ databases with multiple partitioning schemes across multiples tables in each database).

One key issue is automation and auditing of the partitioning which if left to a human gets in a right mess. To help this I have a set of stored procedure that we can drop into the master database and return partition meta data for all databases\tables on the instance with a single stored proc call.

The stored proc is handy for getting familiar with the various meta data DMVs used by partitioning:

  • sys.partitions
  • sys.indexes
  • sys.data_spaces
  • sys.partition_schemes
  • sys.destination_data_spaces
  • sys.partition_range_values

An example is below

Your welcome to play with the stored proc code below



USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_partition_show]    Script Date: 06/12/2011 17:08:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Bob Duffy
-- Create date: 02/02/2011
-- Description:    Return Meta data on partitioning for databases and/or tables
-- =============================================
ALTER PROCEDURE [dbo].[sp_partition_show]
    @BoundaryValue sql_variant =null
    ,@TableName sysname =null
    ,@DatabaseName sysname ='ALL'    --Default to current. ALL for all databases
AS
BEGIN
    SET NOCOUNT ON;
    if @DatabaseName is null set @DatabaseName =DB_NAME()
    if @DatabaseName ='ALL' set @DatabaseName =null
    
    declare @spool table  (db_name sysname, table_name sysname, partition_number int
    , rows int, boundary_value_on_right int, range_value sql_variant, partition_function sysname)
    
   insert into @spool
   exec sp_msforeachdb  'use [?]; select db_name() as database_name
       ,object_name(p.object_id) as table_name,
       p.partition_number,
       p.rows,
       pf.boundary_value_on_right,
       prv.value as range_value,
       pf.name as partition_function 
    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
    --where i.index_id =1  
    order by object_name(p.object_id), p.partition_number'
    
    select * From @spool 
    where (db_name=@DatabaseName or @DatabaseName is null)
    and (table_name =@TableName or @TableName is null)
    and partition_function not like '%client%'
    and (range_value =CONVERT(datetime,@BoundaryValue) or @BoundaryValue is null)
END

Leave a Reply