SQL Fascination

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

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

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

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:    

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

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:   

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

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.