November 30, 2015 at 4:36 am
Hi,
I have partitioned a large table in test database which was performing slow archiving process using the below query
CREATE PARTITION FUNCTION TransactionHistoryPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2015-10-05', '2015-10-31')
GO
CREATE PARTITION SCHEME TransactionHistory_PartitionScheme
AS PARTITION TransactionHistoryPartitions
TO (FG1, FG2, [PRIMARY] )
GO
CREATE NONCLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.Transaction_history (date_time_stamp)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON TransactionHistory_PartitionScheme(date_time_stamp)
GO
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%transaction_history%'
The result is as below
objectname indexname partition_id partition_number rows name
AR_TRANSACTION_HISTORY NULL 72057598879465472 1 1805135 FG1
TRANSACTION_HISTORY XIE2TRANSACTION_HISTORY 72057599821021184 1 22833928 FG1
TRANSACTION_HISTORY XIE6TRANSACTION_HISTORY 72057599821283328 1 22833928 FG1
TRANSACTION_HISTORY XIE7TRANSACTION_HISTORY 72057599821348864 1 22833928 FG1
TRANSACTION_HISTORY XIE8_RPT_TRANSACTION_HISTORY 72057599821414400 1 22833928 FG1
TRANSACTION_HISTORY XPKTRANSACTION_HISTORY 72057599821479936 1 22833928 FG1
AR_TRANSACTION_HISTORY XIE7TRANSACTION_HISTORY_AR 72057599864602624 1 1805135 FG1
TRANSACTION_HISTORY XIE1TRANSACTION_HISTORY 72057599869255680 1 22833928 FG1
TRANSACTION_HISTORY XIE3TRANSACTION_HISTORY 72057599869321216 1 22833928 FG1
TRANSACTION_HISTORY XIE4TRANSACTION_HISTORY 72057599869386752 1 22833928 FG1
TRANSACTION_HISTORY XIE5TRANSACTION_HISTORY 72057599869452288 1 22833928 FG1
TRANSACTION_HISTORY IX_TABLE1_partitioncol 72057599871680512 1 18609348 FG1
TRANSACTION_HISTORY IX_TABLE1_partitioncol 72057599871746048 2 4223912 FG2
TRANSACTION_HISTORY IX_TABLE1_partitioncol 72057599871811584 3 668 PRIMARY
In the results it is showing as partitioned into different file groups but in properties it appears as false. Kindly help
November 30, 2015 at 4:14 pm
rameelster (11/30/2015)
Hi,I have partitioned a large table in test database which was performing slow archiving process using the below query
CREATE PARTITION FUNCTION TransactionHistoryPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2015-10-05', '2015-10-31')
GO
CREATE PARTITION SCHEME TransactionHistory_PartitionScheme
AS PARTITION TransactionHistoryPartitions
TO (FG1, FG2, [PRIMARY] )
GO
CREATE NONCLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.Transaction_history (date_time_stamp)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON TransactionHistory_PartitionScheme(date_time_stamp)
GO
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%transaction_history%'
The result is as below
objectname indexname partition_id partition_number rows name
AR_TRANSACTION_HISTORY NULL 72057598879465472 1 1805135 FG1
TRANSACTION_HISTORY XIE2TRANSACTION_HISTORY 72057599821021184 1 22833928 FG1
TRANSACTION_HISTORY XIE6TRANSACTION_HISTORY 72057599821283328 1 22833928 FG1
TRANSACTION_HISTORY XIE7TRANSACTION_HISTORY 72057599821348864 1 22833928 FG1
TRANSACTION_HISTORY XIE8_RPT_TRANSACTION_HISTORY 72057599821414400 1 22833928 FG1
TRANSACTION_HISTORY XPKTRANSACTION_HISTORY 72057599821479936 1 22833928 FG1
AR_TRANSACTION_HISTORY XIE7TRANSACTION_HISTORY_AR 72057599864602624 1 1805135 FG1
TRANSACTION_HISTORY XIE1TRANSACTION_HISTORY 72057599869255680 1 22833928 FG1
TRANSACTION_HISTORY XIE3TRANSACTION_HISTORY 72057599869321216 1 22833928 FG1
TRANSACTION_HISTORY XIE4TRANSACTION_HISTORY 72057599869386752 1 22833928 FG1
TRANSACTION_HISTORY XIE5TRANSACTION_HISTORY 72057599869452288 1 22833928 FG1
TRANSACTION_HISTORY IX_TABLE1_partitioncol 72057599871680512 1 18609348 FG1
TRANSACTION_HISTORY IX_TABLE1_partitioncol 72057599871746048 2 4223912 FG2
TRANSACTION_HISTORY IX_TABLE1_partitioncol 72057599871811584 3 668 PRIMARY
In the results it is showing as partitioned into different file groups but in properties it appears as false. Kindly help
You created a partition function, a partition scheme, and an index on that scheme. No where are you applied the partitioning to the table. You either need to recreate the table on the partition scheme and transfer the data to it from the monolithic table or create the clustered index for the for the monolithic table on the partition scheme.
I'll also recommend that names like FG1 and FG2 aren't going to cut it for ease in maintenance or troubleshooting. 😉
--Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply