Partitioning 4: Finding partitioning in DMVs and Object Explorer

Happy Friday! Today’s post will be light on the performance side and instead, I’m going to talk about how to take a look at your server’s partitioning.

First, partitioning in SQL Server Management Studio

Object Explorer, that thing on the left side of the SSMS window, will show partition info. However, since partitioning is database-level, you’ll have to go to each database.

In your database, go to Storage, then to Partition Schemes or Partition Functions:

When I first started working on tables with partitioning, I kept expecting partitioning to be in “Programmability” with stored procedures and other programming functions. However, since partitioning is a storage feature, it makes sense to me that it’s stored here.

What about querying for all partition functions and schemes?

There’s several DMVs to find information on partitions. Since it’s Friday, I’ll just post my query and show you what the partition functions and schemes on my database look like:

USE StackOverflow2010
GO

SELECT 
functions.name AS function_name,
schemes.name AS scheme_name,
CASE WHEN boundary_value_on_right = 1
	THEN 'range right function'
	ELSE 'range left function' END
AS type_of_function,
val.boundary_id,
val.value as boundary_value
FROM sys.partition_range_values AS val
JOIN sys.partition_functions AS functions 
  ON val.function_id = functions.function_id
JOIN sys.partition_schemes AS schemes 
  ON schemes.function_id = schemes.function_id
ORDER BY functions.name, boundary_id

Alright, that’s it! Keep in mind that partition functions and schemes are database-specific, so modify the query or run it in each of the databases you want to check. Stay tuned!