THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: My Big Fat Partitioning Query

When SQL Server first introduced table and index partitioning in SQL 2005, it was the same version that all the metadata changed. So I decided that I could learn a lot about metadata and a lot about the organization and storage of partitions if I tried to write a query, and then a view, that included almost everything anyone would ever need to know about any partitioned objects you had created.  While doing that I realized that one function that SQL Server was missing, in spite of all the incredible new metadata and built-in functions, was a function to return the name of an index, given the object_id and the index_id. Since so much of the metadata contains these ID columns, it seems like a useful thing. But I had to write one for myself:

CREATE FUNCTION dbo.index_name (@object_id int, @index_id tinyint)
RETURNS sysname
AS
BEGIN
  DECLARE @index_name sysname
  SELECT @index_name = name FROM sys.indexes
     WHERE object_id = @object_id and index_id = @index_id
  RETURN(@index_name)
END;

This function needs to be created in the database where you want to use it.

I wrote the view to return all the partitioning info to be used in my classes. It took quite a while, and was not perfect. The two pieces I kept meaning to fix were:

  • It would return no rows for a table that was not partitioned at all
     
  • It would only list one boundary point for each partition.  This meant you always had to return the partitions in order in order to see the lower and upper boundary for each. However, one of my students in a class I taught in Salt Lake City in September 2009 took on the task of 'fixing' those problems, so the version I show you here is the updated one.

I decided to make this script  public today in response to a query on the public forums. I'm not including a script that will create a partitioned table for you. If you don't have a partitioned table of your own, you probably won't even be interested in this script!

-- Create a view to return details about a partitioned table or index
--  First run the script to create the function index_name()

-- Written by Kalen Delaney, 2008
--   with a few nice enhancements by Chad Crawford, 2009

CREATE VIEW Partition_Info AS
  SELECT OBJECT_NAME(i.object_id) as Object_Name, dbo.INDEX_NAME(i.object_id,i.index_id) AS Index_Name,
    p.partition_number, fg.name AS Filegroup_Name, rows,
    au.total_pages,
    CASE boundary_value_on_right
        WHEN 1 THEN 'less than'
        ELSE 'less than or equal to'
    END as 'comparison'
    , rv.value,
    CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A'
    ELSE
      CASE
        WHEN boundary_value_on_right = 0 AND rv2.value IS NULL 
           THEN 'Greater than or equal to'
        WHEN boundary_value_on_right = 0
           THEN 'Greater than'
        ELSE 'Greater than or equal to' END + ' ' +
           ISNULL(CONVERT(varchar(15), rv2.value), 'Min Value')
                + ' ' +
                +
           CASE boundary_value_on_right
             WHEN 1 THEN 'and less than'
               ELSE 'and less than or equal to'
               END + ' ' +
                + ISNULL(CONVERT(varchar(15), rv.value),
                           'Max Value')
        END as 'TextComparison'
  FROM sys.partitions p
    JOIN sys.indexes i
      ON p.object_id = i.object_id and p.index_id = i.index_id
    LEFT JOIN sys.partition_schemes ps
      ON ps.data_space_id = i.data_space_id
    LEFT JOIN sys.partition_functions f
      ON f.function_id = ps.function_id
    LEFT JOIN sys.partition_range_values rv
      ON f.function_id = rv.function_id
          AND p.partition_number = rv.boundary_id    
    LEFT JOIN sys.partition_range_values rv2
      ON f.function_id = rv2.function_id
          AND p.partition_number - 1= rv2.boundary_id
    LEFT JOIN sys.destination_data_spaces dds
      ON dds.partition_scheme_id = ps.data_space_id
          AND dds.destination_id = p.partition_number
    LEFT JOIN sys.filegroups fg
      ON dds.data_space_id = fg.data_space_id
    JOIN sys.allocation_units au
      ON au.container_id = p.partition_id
WHERE i.index_id <2 AND au.type =1

-- Example of use:
SELECT * FROM Partition_Info
WHERE Object_Name = 'charge'
ORDER BY Object_Name, partition_number

Have fun!

~Kalen

Published Monday, July 26, 2010 9:45 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

merrillaldrich said:

Very handy! How'd you know I was going to need this today? :-)

August 1, 2010 7:54 PM
 

Paul Harris said:

This is fantastic, so helpful. I have modified the text operators to give me 4 columns, lower boundary, lower boundary comparison, upper boundary, upper boundary comparison. It makes building co-aligned OLAP partitions a breeze.

January 31, 2012 8:45 AM
 

Kalen Delaney said:

Thanks for your feedback Paul!

January 31, 2012 9:17 PM
 

Dennis Schelchkov said:

Thanks for this wonderful script Kalen, this is indeed a very helpful view, I've been using it a lot.

But just recently I have realized that total_pages column in that view only represents count of in-row pages, and it does not include LOB and row_overflow pages.

This is what the "au.type = 1" clause does.

Since I wanted to see LOB and row_overflow pages as well and also I wanted a SUM(total_pages) to match with the results of sp_spaceused for any given table I replaced the join to sys.allocation_units with a join to sys.dm_db_partition_stats. Also correct me if I'm wrong but I don't see a need for calling that Index_name function, as we alredy have a join to sys.indexes table so I'm just using i.name as Index_name. Here's the modified script I use:

CREATE VIEW Partition_Info AS

SELECT OBJECT_NAME(i.object_id) as Object_Name, i.name AS Index_Name,

   p.partition_number, fg.name AS Filegroup_Name, rows,

   dps.in_row_data_page_count + dps.lob_used_page_count + dps.row_overflow_used_page_count as total_pages,

   CASE boundary_value_on_right

       WHEN 1 THEN 'less than'

       ELSE 'less than or equal to'

   END as 'comparison'

   , rv.value,

   CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A'

   ELSE

     CASE

       WHEN boundary_value_on_right = 0 AND rv2.value IS NULL

          THEN 'Greater than or equal to'

       WHEN boundary_value_on_right = 0

          THEN 'Greater than'

       ELSE 'Greater than or equal to' END + ' ' +

          ISNULL(CONVERT(varchar(15), rv2.value), 'Min Value')

               + ' ' +

               +

          CASE boundary_value_on_right

            WHEN 1 THEN 'and less than'

              ELSE 'and less than or equal to'

              END + ' ' +

               + ISNULL(CONVERT(varchar(15), rv.value),

                          'Max Value')

       END as 'TextComparison'

 FROM sys.partitions p

   JOIN sys.indexes i

     ON p.object_id = i.object_id and p.index_id = i.index_id

   LEFT JOIN sys.partition_schemes ps

     ON ps.data_space_id = i.data_space_id

   LEFT JOIN sys.partition_functions f

     ON f.function_id = ps.function_id

   LEFT JOIN sys.partition_range_values rv

     ON f.function_id = rv.function_id

         AND p.partition_number = rv.boundary_id    

   LEFT JOIN sys.partition_range_values rv2

     ON f.function_id = rv2.function_id

         AND p.partition_number - 1= rv2.boundary_id

   LEFT JOIN sys.destination_data_spaces dds

     ON dds.partition_scheme_id = ps.data_space_id

         AND dds.destination_id = p.partition_number

   LEFT JOIN sys.filegroups fg

     ON dds.data_space_id = fg.data_space_id

   JOIN sys.dm_db_partition_stats dps

     ON dps.partition_id = p.partition_id

WHERE i.index_id <2

April 5, 2013 12:23 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement