February 18, 2015 at 8:45 am
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?
February 18, 2015 at 3:54 pm
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
Change is inevitable... Change for the better is not.
February 19, 2015 at 6:19 am
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?
February 19, 2015 at 7:55 am
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
Change is inevitable... Change for the better is not.
February 19, 2015 at 8:04 am
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?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply