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

 

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.