I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have never more than 2 partitions I'm actively writing to at once. I have a FILLFACTOR set to 75 on the index since the data is coming in somewhat randomly.

The old data partitions are taking up quite a bit of space, utilizing only about 60-70% of that space. Is it possible to automatically change the FILLFACTOR on said partitions, rebuild the indexes and shrink the files? Since I'm not writing to those partitions anymore they shouldn't grow back in size.

  • Within an Alter Index statement you can specify the Partition that you want to rebuild. However, you can not set a different fill factor for a separate partition within an index that I am aware of. The Fillfactor is at the Index level, not at the Partition level in SQL Server 2012. So it seems like without changing the underlying structures you probably can not do exactly what you want. You could certainly rebuilt partitions within the index or change the fillfactor...just not change the fillfactor within a single partition on that rebuild. – mskinner Dec 19 '14 at 23:02
  • In addition to rebuilding the index on the partition with FILLFACTOR 100, consider DATA_COMPRESSION ROW or PAGE to further reduce space needs. – Dan Guzman Dec 21 '14 at 17:47
up vote 5 down vote accepted

Yes, this is possible. Here is how

  1. Create a copy of the table (you can use the partition management tool for that: https://sqlpartitionmgmt.codeplex.com/)
  2. Switch the partition you want to "compress" into the copy
  3. Rebuild the indexes on the copy with FF = 100.
  4. Switch the partition back into the original table

It should be pretty trivial to automate this with a SQL agent job.

Step 3 is best done in this order:

  • Drop all secondary indexes
  • Rebuild the cluster index with FF = 100
  • Create all the secondary indexes again with FF=100

Also, if you have SQL Server Enterprise Edition, you could compress the data with PAGE compression. This would require a different process:

  1. Create a copy of that table as above
  2. Switch the data you want to compress into the copy
  3. Compress the data by rebuilding indexes as above
  4. ALTER the original table to enable page compression on the now empty partitions (the ones you switched out)
  5. SWITCH the compressed data back into the original table.

With SQL Server 2014 Enteprise Edition, you can do the above as an online operation directly on the partition you want to compress. However, it is still a good idea to drop the secondary indexes in a copy first, because it will typically give you better data layout on the disk.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.