Partitioned table showing false in table properties

• rameelster

  Right there with Babe

  Points: 779

  More actions

  November 30, 2015 at 4:36 am

  #306676

  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

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  November 30, 2015 at 4:14 pm

  #1843054

  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

  ---

  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