Home > SQL Server > Dynamic Partitioning : What objects are using a Partition Schema? (SQL Tuesday #002)

Dynamic Partitioning : What objects are using a Partition Schema? (SQL Tuesday #002)


As part of the look at dynamic partitioning one of the first problems I have come across is finding what objects are currently placing data within a partition schema, this can be both tables as well as indexes for a table or indexes for a view (which can also be partitioned).

This has tied in nicely with Adam Machanic’s SQL Tuesday in which we were to describe a confusing situation and possible solution.

Certainly it has been a bit confusing to get to the bottom of what should be a relatively trivial question – programmatically determine what objects are using my partition schema? 

If I am going to auto-balance the partitions then I have to know what objects are using the partition schema and will be affected by any balancing – we can not assume it is a single object, since we know both the table and non-clustered indexes will often be aligned on the same partition schema, as could other tables / objects.    

So the first place I chose to check was the system views for the partition objects, sys.partition_functions and sys.partition_schemes – with the partition schemes being where you would expect to start.      

SELECT * FROM sys.partition_schemes

 

name data_space_id type type_desc is_default function_id
psBalanceLeft 65601 PS PARTITION_SCHEME 0 65536

Unfortunately the results from partition schemes view is spectacularly unhelpful, aside from inheriting a number of columns from the data spaces system view, it only adds function_id – being the ID of the partition function used in the schema. It at least has the name of the partition scheme, so that definitely is going to have to be used at a later point.    

The immediately useful looking value is the function_id linking the scheme to the partition function, so I had a look inside the partition functions view remember what it has.      

SELECT * FROM sys.partition_functions

 

name function_id type type_desc fanout boundary_values_on_right
pfBalanceLeft 65536 R RANGE 10 0

The output does not particularily lead anywhere useful – the function most certainly is not going to tell me which objects are assigned to it, since the tables / indexes get directly assigned to the partition scheme, this looks a dead end. So the only other option is to go to the data spaces system view:    

SELECT ps.name, ds.*
FROM sys.partition_schemes ps
INNER JOIN sys.data_spaces ds on ps.data_space_id = ps.data_space_id

 

name name data_space_id type type_desc is_default
psBalanceLeft psBalanceLeft 65601 PS PARTITION_SCHEME 0

Not a stellar move – there are no obvious leads here.

So I can obtain the relation between the partition scheme and the storage but that it is so far. Given those two dead ends I next considered the problem from the opposite direction – sys.partitions claims to contain a row for each partitioned tables and index in the database – which should provide another starting point.    

SELECT * FROM sys.partitions

Some of the output was as follows:    

partition_id object_id index_id partition_number hobt_id rows
72057594040549376 53575229 2 1 72057594040549376 10
72057594040614912 53575229 2 2 72057594040614912 10
72057594040680448 53575229 2 3 72057594040680448 10
72057594040745984 53575229 2 4 72057594040745984 10
72057594040811520 53575229 2 5 72057594040811520 10
72057594040877056 53575229 2 6 72057594040877056 10
72057594040942592 53575229 2 7 72057594040942592 10
72057594041008128 53575229 2 8 72057594041008128 10
72057594041073664 53575229 2 9 72057594041073664 10
72057594041139200 53575229 2 10 72057594041139200 136
72057594041204736 149575571 1 1 72057594041204736 10
72057594041270272 149575571 1 2 72057594041270272 10
72057594041335808 149575571 1 3 72057594041335808 10
72057594041401344 149575571 1 4 72057594041401344 10
72057594041466880 149575571 1 5 72057594041466880 10
72057594041532416 149575571 1 6 72057594041532416 10
72057594041597952 149575571 1 7 72057594041597952 10
72057594041663488 149575571 1 8 72057594041663488 10
72057594041729024 149575571 1 9 72057594041729024 10
72057594041794560 149575571 1 10 72057594041794560 136

This definitely has my partition schema in there somewhere since I know I have 10 partitions and have set the row quantities up to be 10 rows for the first 9 partitions and 136 rows in the tenth, it is pretty visible.

I’ve also got an indexed view on the same table which explains the duplicate set of values, and a NC index on the table which explained the triplicate set of values I’ve not pasted in. This is in essence what I am after though; finding out which object_id’s reference the partition schema.

A couple of immediate dead ends have also appeared:    

  • The partition_id looks useful but is the unique id of the partition record not an ID relating to the partition schema.
  • The hobt_id is the heap or b-tree pointer for that specific partition so is not going to be able to help us since there are 10 hobt_id’s per object on the schema, all different.

It does however provide the object_id which we know we can translate into a name very easily and a partition_number column which only ever exceeds 1 on a partitioned table. So with a bit of a throw-away style query to just select those with a partition_number of 2 to make sure we only select partitioned objects gave me the following:  

SELECT o.Name, s.*
FROM sys.partitions s
INNER JOIN sys.objects o on s.object_id = o.object_id
WHERE partition_number = 2

 

Name partition_id object_id index_id partition_number
tblTestBalance 72057594039304192 53575229 1 2
foo 72057594039959552 85575343 0 2
tblTestBalance 72057594040614912 53575229 2 2
vwTestView 72057594041270272 149575571 1 2

So now I can see the partitioned objects from the other direction, but I have found no relation between the objects identified as partitioned and the partition schemas discovered earlier. 

There is also an index_id being shown and documented as ‘the index within the object to which this partition belongs’, but this is not an object_id for an index, but the index_id you would normally use within a dbcc in command, 0 for heap, 1 for clustered index etc, so there must be a relation to the sys.indexes view – which when I thought about it made complete sense – the sys.indexes table is really badly named, since it is not a row per index, but a row per heap or index. 

Not the best name in the world for it, but let’s move on – given we have both the object ID and the index_id we can join on both / filter in the where clause. 

SELECT O.Name, p.*
FROM sys.partitions p
INNER JOIN sys.objects O on p.object_id = o.object_id
INNER JOIN sys.indexes I on O.object_id = I.object_id and p.index_id = I.index_id
WHERE partition_number = 2

Same output as before, since I have selected no fields from sys.indexes yet, checking the list an immediate candidate jumped out, data_space_id – I already had an odd-looking data_space_id earlier, so can the index link to it successfully?  

SELECT O.Name, ds.name
FROM sys.partitions p
INNER JOIN sys.objects O on p.object_id = o.object_id
INNER JOIN sys.indexes I on O.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 WHERE partition_number = 2

Which gave the following results:   

name name
tblTestBalance psBalanceLeft
foo psBalanceLeft
tblTestBalance psBalanceLeft
vwTestView psBalanceLeft

I then rejoined in the partition schemes using the same join I did earlier:     

SELECT O.Name, ds.name
FROM sys.partitions p
INNER JOIN sys.objects O on p.object_id = o.object_id
INNER JOIN sys.indexes I on O.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

Running this gave 40 rows, 4 objects x 10 partitions, so I was filtered down to the partitioned objects but I returned too many rows – I could have used a group by clause but it seemed simpler to just select a single partition number, and since I know partition number 1 will always exist, that was the simplest to use.

I am now down all the way to the partition scheme, time to select some of the more interesting columns I found along the way, that are applicable to the dynamic partitioning problem I am looking at, the main one being the object name and type. 

The final solution I’ve arrived at to get to the tables / indexes and indexed views using a partition scheme is:     

SELECT O.Name as TableName, I.Name as IndexName, I.Type, I.type_desc as IndexType, ps.name as PartitionSchema
FROM sys.objects O
INNER JOIN sys.partitions p on P.object_id = O.object_id
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
WHERE p.partition_number = 1
TableName IndexName Type_ID Type_Desc PartitionSchema
tblTestBalance PK_tblTestBalance 1 CLUSTERED psBalanceLeft
foo NULL 0 HEAP psBalanceLeft
tblTestBalance testncindex 2 NONCLUSTERED psBalanceLeft
vwTestView IxViewTest 1 CLUSTERED psBalanceLeft

This can be further filtered to an individual partition scheme based on the name trivially, but the output is giving us the information I am after – a list of what objects and type of object is allocated to the partition schemas.       

There should be an easier way to get to this information than joining 5 system views, but that seems to be the only way I could manage to solve the problem.

  1. September 1, 2011 at 3:59 pm | #1

    Nicely done! This just saved me about an hour’s worth of digging, since I was trying to figure out how to drop partitioning from a table (including all of the partition scheme objects).

  2. SQLMD
    January 20, 2012 at 6:21 pm | #2

    Excellent – you saved me a bunch of time trying to determine if all our partition schemas are being used.

  3. Anandan
    July 9, 2012 at 3:36 pm | #3

    Really great stuff, I am about to remove the Table Partitioning on a table.

    Referring this blog and using your scripts, I identified the indexes that are all associated with partition schema and removed those indexes. Now, I am able drop the partition schema & partition function.

    Now, to re-create those indexes, it was advised to to rebuild all of your indexes that are using the partition schema, and specify a filegroup instead of the partition schema.
    As the table is having 12 Filegroups, Am I need to create those indexes on all the Filegroups ? Thanks in advance

  1. February 8, 2010 at 7:16 pm | #1
  2. July 26, 2012 at 11:15 am | #2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: