Table and Index Partitioning

Simply stated, partitioning is the breaking up of a large object, such as a table, into smaller, manageable pieces. Partitioning has been around for a while. The feature received lots of attention once it supported the ability to use constraints with views. This provided the ability for the optimizer to eliminate partitions (or tables) joined by a union of all statements on a view. These partitions could also be distributed across servers. This technology was introduced as a distributed partitioned view (DPV) during the SQL 7.0 launch.

New to SQL Server 2005 is the capability of partitioning database tables and their indexes over filegroups. This type of partitioning has many benefits over DPV, such as being transparent to the application (meaning no application code changes are necessary). Other benefits include database recoverability, simplified maintenance, and manageability.

Although we're discussing partitioning as part of this performance-tuning chapter, partitioning is first and foremost a manageability and scalability tool. In most situations, implementing partitioning will also have performance improvements as a byproduct of scalability. These benefits are highlighted throughout this section.

Why Consider Partitioning?

For a variety of reasons, you may have very large tables. When these tables (or databases) reach a certain size, it becomes painful for activities such as database maintenance, backup, or restore operations that consume a lot of time. Environmental issues such as poor concurrency due to a large number of users on a sizable table result in lock escalations, which also translates into more pain. If archiving the data is not possible because of regulatory compliances, ISV requirements, or cultural requirements, partitioning is most likely the tool for you. If you are still unsure whether or not to implement partitioning, run your workload through the Database Tuning Advisor (DTA). DTA will make recommendations for partitioning and will also generate the code for you. DTA is covered in detail in Chapter 14. DTA can be found under the "Performance Tools" section of the Microsoft SQL Server 2005 program menu.

Note

Partitioning is covered through the book's various chapters to ensure that details are presented in their appropriate contexts.

Here is a high-level process for partitioning:

1. Create a partition function to define a data-placement strategy.

2. Create filegroups to support the partition function.

3. Create a partition scheme to define the physical data distribution strategy (map the function data to filegroups).

4. Create a table or index on the partition function.

5. Enjoy redirected queries to appropriate resource.

After implementation, partitioning will positively affect your environment and most of your processes. The following are a few processes that partitioning your environment can affect. Be careful to understand this new technology to ensure that every process benefits from it:

• Database backup and restore strategy (support for partial database availability)

• Index maintenance strategy (rebuild and defragmentation), including index views

• Data management strategy (large insert or truncates)

• End-user workload (queries)

• Concurrency

  • Enhanced lock-escalation story

  • Enhanced distribution or isolated query workloads

Implementing Partitioning

Partitioning breaks a physical object, such as a large table, into multiple manageable pieces. A row is the unit that partitioning is based on. Unlike DPVs, all partitions must reside within a single database.

Creating a partition function

Simply stated, a partition function is your primary data-partitioning strategy. The first order of business is to determine your partitioning strategy. Identifying and prioritizing challenges is the best way to decide on a partitioning strategy. Whether it's to move old data within a table to a slower disk (or SAN), enhance workload concurrency, or simplify maintenance of a very large database, identifying and prioritizing is essential. Once you've selected your strategy, you will need to create a partitioning function that matches that strategy.

Remember to evaluate a table for partitioning, as the partition function is based on the distribution of data (selectivity of a column and the range or breath of that column). The range supports the number of partitions that the table can be partitioned by. There is a product limit of a 1000 partitions per table. This range should also match up with the desired strategy. For example, spreading out (or partitioning) a huge customer table by customer last name or by geographical location for better workload management is a sound strategy. Another example of a sound strategy is to partition a table by date for the purpose of archiving data based on date range for a more efficient environment.

Note

User-defined data types, alias data type, timestamps, images, XML, varchar(max), nvarchar(max), or varbinary(max) cannot be evaluated as partitioning columns.

As an example, we will partition a trouble-ticketing system for a telephone company. When a trouble ticket is generated based on an outage, it hits the database. At this point, many activities get kicked off. Technicians get dispatched, parts are replaced or reordered, and service can be rerouted within the network. SLA's are monitored and escalations are initiated. All of these activities take place because of the trouble ticket. In this system, the activities table and ticketing table have hot spots, as shown in Figure 12-3.

Figure 12-3

In Figure 12-3 the information marked as "hot" is the new or recent data, and it is only relevant or of interest during the outage. The information marked as "Read-Only" and "Read-Mostly" is usually used for minor analysis during postmortem processes and then for application reporting. Eventually, the data becomes obsolete and should be moved to a warehouse. Unfortunately, because of internal regulatory requirements, this database has to be online for seven years. Partitioning this environment will provide sizable benefits. Under the sliding-windows scenario, every month (or quarter) a new partition would be introduced to the environment as a retainer for the current data (hot data) for the tickets and activities tables. As part of this process, a partition with data from these tables that is older than seven years would also be retired.

As we described, there is a one-to-many relationship between tickets and activities. Although there are obvious size differences between these tables, it is important to put them through identical processes. This allows you to run processes that will affect resources shared and limited to these objects. To mitigate the impact of doing daily activities such as backups and index maintenance on all this data, these tables will be partitioned based on date ranges. We will create a left partition function based on the ticketdate column, as outlined below:

 CREATE PARTITION FUNCTION PFL_Years (smallint) AS RANGE LEFT FOR VALUES ('19991231 23:59:59.997', '20011231 23:59:59.997', '20031231 23:59:59.997', '20051231 23:59:59.997', '20061231 23:59:59.997') 

Note

SQL Server rounds time to .003 seconds, meaning that a time of .997 would be rounded up to 1.0 second.

• The boundary value '19991231 23:59:59.997' is the leftmost partition and includes all values less than or equal to '19991231 23:59:59.997'.

• The boundary value '20011231 23:59:59.997' is the second partition and includes all values greater than '19991231 23:59:59.997' but less than or equal to '20011231 23:59:59.997'.

• The boundary value '20031231 23:59:59.997' is the third partition and includes all values greater than '20011231 23:59:59.997' but less than or equal to '20031231 23:59:59.997'.

• The boundary value '20051231 23:59:59.997' is the fourth partition and includes all values greater than '20031231 23:59:59.997' but less than or equal to '20051231 23:59:59.997'.

• The boundary value '20061231 23:59:59.997' is the fifth partition and includes all values greater than '20051231 23:59:59.997' but less than or equal to '20061231 23:59:59.997'.

• Finally, the sixth partition includes all values greater than '20061231 23:59:59.997'.

The range partition function specifies the boundaries of the range. The left or right keyword specifies the interval of the boundary that the value belongs to. There can be no holes in the partition domain; all values must be obtainable. In this code sample, all transactions must fall within a date specified by the sample value range.

Creating Filegroups

You should create filegroups to support the strategy set by the partition function. As a best practice, user objects should be created and mapped to a filegroup outside of the default filegroup, leaving the default filegroup for system objects. This will ensure database availability in the event of an outage that affects the availability of any filegroup outside of the default filegroup.

Creating a Partition Scheme

A partition scheme is what maps database objects such as a table to a physical entity such as a file or filegroups. There are definitely backup, restore, and data-archival considerations when making this decision. (These are discussed in Chapter 18.) The following sample code maps the partition functions or dates to individual filegroups.

 CREATE PARTITION SCHEME CYScheme AS PARTITION PFL_Years TO ([CY00], [CY02], [CY04], [CY05], [CY06], [Default]) 

This supports the placement of filegroups on individual and distinct disk subsystems. Such an option also supports the capability to move old data to an older disk subsystem and to reassign spindles to support hot data (CY06). When the old data has been moved to the old SAN, filegroups can be marked as read-only. Once this data has been backed up, it no longer needs to be part of the backup process. SQL Server will also automatically ignore these filegroups as part of index maintenance.

The other option for a partition-function scheme allows for the mapping of the partition scheme to map the partition function to individual files within a single filegroup. The following sample code maps the partition function to the default filegroup.

 CREATE PARTITION SCHEME CYScheme AS PARTITION PFL_Years TO ([Default]) 

This choice provides for the ability to introduce or delete GB of data to this 500+ GB table with a simple metadata switch (in seconds). The process utilized to accomplish this is called sliding window. Normally, this process would have taken hours because of lock escalation and index resynchronization. From a high-level, the sliding-window process consists of three types of actions. An empty partition would be created and indexed outside of the environment. It would then be switched with the partition that contains the targeted data. At the conclusion of this process, the partition with the data is now outside of the environment and can then be dropped without impact to the environment. This process would be repeated to introduce a new partition into this table structure as the repository for the "hot" data. The only impact to the environment was a brief pause while a schema lock was in place during the partition swaps, which took seconds. If you wanted to load data into the partitioned table instead of dropping it, the data would be loaded and indexed outside of the environment. To load the data, repeat the previous process. This beneficial process is only available to perform on files or partitions within a filegroup. Implementation details and best practices are covered later in this chapter and in upcoming chapters.

Create Tables and Indexes

As the following code shows, the syntax for creating a table is accomplished as it has always been. The only exception is that it is created on a partition schema instead of a specific or default filegroup. New to SQL Server 2005 is the ability to create tables, indexes, and indexed views on partition schemes. This supports the distribution of database objects across several filegroups. This is different from the existing ability to create an entire object within a filegroup (which is still available in SQL Server 2005).

 CREATE TABLE [dbo].[Tickets] (     [TiketID] [int] NOT NULL,       [CustomerID] [int] NULL,       [State] [int] NULL,        [Status] [tinyint] NOT NULL,        [TicketDate] [datetime] NOT NULL        CONSTRAINT TicketYear        CHECK ([TicketDate] >= '19991231 23:59:59.997' AND [TicketDate] <= '20061231 23:59:59.997')) ON CYScheme (TicketDate) GO CREATE TABLE [dbo].[activities] (     [TiketID] [int] NOT NULL,       [Activity detail] [varchar 255] NULL,       [ActivityDate] [datetime] NOT NULL       CONSTRAINT ActivityYear       CHECK ([Activity Date] >= '19991231 23:59:59.997' AND [ActivityDate] <= '20061231 23:59:59.997')) ON CYScheme (TicketDate) GO 

We will cover two areas of best practices here: index alignment and storage alignment.

Index Alignment

An "aligned" index uses an equivalent partition function and the same partitioning columns as its table (as shown in Figure 12-4). They actually don't have to use the identical partition function or scheme, but there has to be a one-to-one correlation of data-to-index entries within a partition.

Figure 12-4

A benefit derived from index alignment is the ability to switch partitions in or out of a table with a simple metadata operation. In the trouble-ticket example, you would be able to programmatically add a new partition for the new month (or current month) and delete the outgoing month from the table as part of the seven-year cycle.

Storage alignment

Index alignment is the first requirement of a storage-aligned solution. There are two options for storage alignment. The big differentiator is the ability to use the same partition scheme, or a different one, as long as both tables and indexes have an equal number of partitions. The first option, shown in Figure 12-5, demonstrates a storage-aligned solution with index and relevant data in distinct and isolated filegroups. If a query to compare data in CY04 and CY02 is executed on aligned index and data, the query would be localized to the aligned environment. This would benefit users working in other partitions isolated from the ongoing work within these two partitions.

Figure 12-5

Figure 12-6 shows the same query executed on a different architecture. Because of the alignment of index and data, the query will still be localized but will run in parallel on both relevant index and data partitions. This would still benefit users working in other partitions isolated from the ongoing work within these four partitions.

Figure 12-6

Additional benefits derived from storage alignment are partial database availability and piecemeal restores. For instance, in the example scenario, the CY04 partition could be offline, and the application would still be processing trouble tickets. This would occur regardless of what partition went down, as long as the default partition that contains only system-table information (as is the best practice) and the partition that contains the current month's data are online. Considerations that will affect query parallelism of this environment are discussed later in the processor section of this chapter.