Sql-server – Partitioning on a single filegroup

filegroupspartitioningsql server

I have some very large tables in my database, but a substantial chunk of this data is "old".

Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other limitation is that I cannot modify the database, meaning add filegroups to it. The way things stand right now, everything resides on the PRIMARY file group.

I was thinking to partition these tables into a few partitions, such as "new", "old", "archived" and similar. I do have a "status" column I would like to use for this purpose.

Given the described scenario and limitations, I was wondering if partitioning makes any sense here. In other words, if my table is partitioned in this way, but all of the partitions sit on the same filegroup, will SQL Server be smart enough to find that special area in the underlying file where my "new" data resides and not touch the area with "old" data?

To put it differently, if, let's say, 80% of my data is "old". Does SQL Server have a mechanism to avoid accessing 100% of the underlying files and access only 20% which contains "new" data (assuming, of course, I specify my partitioning column in the WHERE clause of the queries).

I guess to answer this, one would need to understand how the partitioning is implemented internally. I appreciate any pointers.

Best Answer

There are two advantages to partitioning a table in the same filegroup:

  1. Allowing portions of a large index to be rebuilt incrementally, allowing for more efficient maintenance. Review the ALTER INDEX [foo] REBUILD PARTITION=n for more details.
  2. Leveraging partition elimination and (possibly) partition level locking to improve query maintenance. I discuss this on my blog.

There are several things to keep in mind if you're partitioning.

  • If your table has a clustered index (and it really should), your partitioning key must be part of the clustered index.
  • To avoid performance issues, you should align your partitions. This means all your indexes should include your partition key, whether as an include or as part of the index itself.
  • Index rebuilds for partitions are offline in current versions of SQL Server (2005-2012). If your partitions are to large and your rebuilding by partition, this could lead to blocking issues.

I recommend doing some thorough research on partitioning before implementing it. Kendra Little has an excellent list of resources where you can get started.

Related Question