Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 21 '13 at 16:10

This question came from our site for professional and enthusiast programmers.

2 Answers 2

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.

share|improve this answer
2  
There is no choice about including the partitioning key in the clustered index. If the clustered index is unique, you can't do otherwise. If the clustered index is non-unique, the syntax will allow it, but SQL Server will silently add the partitioning key to the key of the clustered index. Aligning indexes may cause performance issues too. Alignment requires an explicit ON clause, this answer implies that adding the partitioning key to the index key or as an include would be enough. –  Paul White Feb 25 '13 at 3:56

The answer is "yes". It does have a mechanism on any query that filters inputs based on the logic used to define the partitions.

You have to have the appropriate filter, though, or all the partition will be scanned. This would typically involve having date filters (in your case) to choose the partition.

One way to enforce this is to have views that access only one partition, with the right logic in the view.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.