Table Partitioning in SQL 2005

This feature of SQL Server 2005 is one of the provided Solutions for Large Tables in SQL by Microsoft. Lots of help will be available in SQL Books Online Shipped with SQL 2005 on concepts and implementation. The best part of this feature is that you can store data of a single large table in multiple data files with particular scheme so you do not loose data integrity and it’s more scalable and manageable. Even Queries fired on the table will become much faster because in partitioned table all data files or index pages will not be searched if query contains the appropriate column.

As long as indexes on the table are partition aligned, it should give greater performance. If there are history data within some partition, those filegroups can be marked readonly and placed on a separate LUN with RAID 5 as they won’t be altered and rest all readwrite partition should be on a filegroup which is built upon RAID 10 disks within SAN. With SAN, there are many other things also plays a role for better performance. In most cases SAN storage is going to be connected to the server using one or more HBAs in the server, a number of fibers to connect to the HBA to the SAN, a number of ports on the front end of the SAN, and how the disks are connected to the ports on the front of the SAN. But in general with healthy SAN setup, you will get performance benefit with physical table partitioning.

With table partitioning, you can do partition level Index REORGANIZATION with table being on-line, so you could have a nightly maintenance job to reorganize previous day’s partition which will less likely get updated in future. With this feature, you will save tone of maintenance time and data will be available all the time.

a. If you have sliding window scenario where you have to scrap off oldest partition and add a newer partition on routine basis (daily or weekly or monthly depending upon the partition definition), the ALTER table operation with SWITCH, MERGE, and SPLIT switches will be used. These operations are very quick because they are meta data change and doesn’t update the data within the table. During any of this ALTER operation it will hold schema stability lock on a table, but that would be very quick, so practically table will be fully available throughout the maintenance.

I don’t think of any downside of having partitioned table where it is necessary. One thing to note is that ONLINE INDEX REBUILD option is not available at partition level. Also, while doing online index reorganization at partition level, choose WITH SORT_IN_TEMPDB option ON, with that it won’t affect other operations on the box. Make sure you have enough space in tempdb and tempdb is designed over multiple files (guideline is to have # of files in tempdb should be equal to number of processor cores).

There are multiple whitepaper available, but the best one I would refer is following -

https://msdn2.microsoft.com/en-us/library/ms345146.aspx - partitioned tables and indexes in SQL Server 2005