Increase SQL performance with horizontal partitions

by Arthur Fuller | Jul 21, 2005 7:00:00 AM

Tags: ActiveX/COM/COM+/DCOM, Databases, SQL, Arthur Fuller, horizontal partition, database, SQL Server Newsletter

Takeaway: The primary motive behind horizontal partitioning—the process of creating at least two physical files for a database's tables—is to move seldom-used data into a second file. Here's a good way to accomplish this task.

Horizontal partitioning is the process of creating at least two physical files for a database's tables. The larger a table, the longer it takes to scan. So the general motive behind horizontal partitioning is to move seldom-used data into a second file.

A common way to do this is to assign date ranges to each partition. For example, suppose that in a given application, the data of interest is almost always from the current year. (Other data is occasionally examined, so it must be available, but it doesn't need to be in the main physical file.) You could create just two horizontal partitions—perhaps Current and Archive—or you could create a partition for each year's data. It depends on your requirements.

Books Online's information on creating a horizontal partition is quite good, so I'll just mention the steps here.

Create a publication with a Publisher (the current database) and a Subscriber (the archive database).
For each article that you want to horizontally partition, select Provide Support For Horizontal DTS Transformation Partitions.
Build the DTS package using the Transform Data Wizard. For each table to be partitioned, write an ActiveX script that defines the partition. In general, you have to determine whether any new or changed rows in the Publisher need to be moved to the Subscriber.

Books Online also offers a nice example of the ActiveX script you have to write for each article you want to partition. You can copy and paste the example and, with just a few additions, you'll be ready to go. (Search for "Defining a Horizontal Partition" in Books Online.)

How horizontal partitioning helped me

At one time, I examined one of my databases to see how I might benefit from horizontal partitioning. There were three tables of particular interest. All three had a DateEntered column, whose default was GetDate(). I made two partitions, using the last year as Current and everything before that as Archive.

Sales were down the year before, ironically resulting in even better performance—the principal interest data was about one-tenth of the total data. The performance gain wasn't quite as good but still obvious. On the other hand, multiyear queries were noticeably slower but were executed so infrequently that it didn't matter. We gained significantly using horizontal partitions. In our case, they were based on date.

Your case may differ, but I encourage you to explore it.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

4 comment(s)