Non-partitioned table has several partition_ids?

Question

Non-partitioned table has several partition_ids?

dajonx

SSCertifiable

Points: 6950
More actions
February 18, 2015 at 8:34 am

#294784

When querying sys.partitions for a non-partitioned table, there are several partition_ids. Only one record joins with it in sys.dm_db_partition_stats. Is this an error? If so, how can I fix this?
Thank you.
My Blog!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dajonx SSCertifiable Points: 6950 More actions · Answer 1

I noticed that I was attempting to rebuild the index, but since it was being queried constantly, I had to cancel. I think that's what is causing the sys.partitions to have several orphan records. I'll have to attempt to rebuild the index offline, but I won't be able to do that until later.

Does this sound right?

My Blog!

dajonx SSCertifiable Points: 6950 More actions · Answer 2

Ok, I just rebuilt the index and I still have the extra records in sys.partitions.

Does anyone know how to clean this up?

Thank you.

My Blog!

dajonx SSCertifiable Points: 6950 More actions · Answer 3

I'm wondering since the table is constantly being queried, will the clean-up of sys.partitions not occur?

Looking for help...

My Blog!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

dajonx (2/18/2015)
When querying sys.partitions for a non-partitioned table, there are several partition_ids. Only one record joins with it in sys.dm_db_partition_stats. Is this an error? If so, how can I fix this?
Thank you.

The first step towards any solution would be for you to post the code that you used and the actual output that's in error.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

dajonx SSCertifiable Points: 6950 More actions · Answer 5

Ah, sorry. Here's the query:

select *

from sys.dm_db_partition_stats a

right join sys.partitions b

on a.partition_id = b.partition_id

WHERE b.object_id = OBJECT_ID('tableA')

The results:

partition_idobject_idindex_idpartition_numberin_row_data_page_countin_row_used_page_countin_row_reserved_page_countlob_used_page_countlob_reserved_page_countrow_overflow_used_page_countrow_overflow_reserved_page_countused_page_countreserved_page_countrow_countpartition_idobject_idindex_idpartition_numberhobt_idrowsfilestream_filegroup_iddata_compressiondata_compression_desc

7205760239081881612271514175137233750376000003750376018444957205760239081881612271514175172057602390818816184449501ROW

NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760277689139212271514171172057602776891392184247401ROW

NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760277695692812271514171172057602776956928184247401ROW

NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760277793996812271514171172057602777939968184249801ROW

NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760277807104012271514171172057602778071040184265801ROW

NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760278259302412271514176172057602782593024184294401ROW

7205760278370713612271514171137453780385000003780385018444957205760278370713612271514171172057602783707136184449501ROW

7205760278377267212271514176144854517453000004517453018444957205760278377267212271514176172057602783772672184449501ROW

Oh jeez... That's not easy to read. Is there a way to make it easier to read?

My Blog!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

dajonx (2/19/2015)
Ah, sorry. Here's the query:
select *
from sys.dm_db_partition_stats a
right join sys.partitions b
on a.partition_id = b.partition_id
WHERE b.object_id = OBJECT_ID('tableA')
The results:
partition_idobject_idindex_idpartition_numberin_row_data_page_countin_row_used_page_countin_row_reserved_page_countlob_used_page_countlob_reserved_page_countrow_overflow_used_page_countrow_overflow_reserved_page_countused_page_countreserved_page_countrow_countpartition_idobject_idindex_idpartition_numberhobt_idrowsfilestream_filegroup_iddata_compressiondata_compression_desc
7205760239081881612271514175137233750376000003750376018444957205760239081881612271514175172057602390818816184449501ROW
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760277689139212271514171172057602776891392184247401ROW
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760277695692812271514171172057602776956928184247401ROW
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760277793996812271514171172057602777939968184249801ROW
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760277807104012271514171172057602778071040184265801ROW
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL7205760278259302412271514176172057602782593024184294401ROW
7205760278370713612271514171137453780385000003780385018444957205760278370713612271514171172057602783707136184449501ROW
7205760278377267212271514176144854517453000004517453018444957205760278377267212271514176172057602783772672184449501ROW
Oh jeez... That's not easy to read. Is there a way to make it easier to read?

What your seeing is that each object has one partition even when they're not "partitioned".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

dajonx SSCertifiable Points: 6950 More actions · Answer 7

I was doing index rebuilds online (yesterday) and had to cancel them due to blocking. I think that's the reason for the extra sys.partitions records. There wasn't an associated record in sys.dm_db_partition_stats so I did a right join to show that they're orphans. I was able to do the rebuild offline, but that didn't clear it up. I was right about to do another index rebuild online, but I wanted to query the sys.partitions again and what do you know... It cleaned itself up. I didn't do anything at all.

I am really curious why it took so long to clean itself up though. I was trying to replicate the issue on a backup and I was able to get the extra sys.partition record. However, it cleared itself up in about a second. I wonder if it's because that table is constantly being queried and there just happened to be a lull so it could clean itself up properly?

My Blog!