PeopleSoft Table and Index Management

For large PeopleSoft installations, the table and index size can, over time, become unmanageable and easily cross over to tens of millions of rows. To manage these large tables and indexes and to provide some performance enhancements, SQL Server provides new table and index management enhancements, as described in the section that follows.

Table and Index Partitioning
SQL Server introduces the concept of table and index partitioning. The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. You can choose an appropriate partitioning scheme to make large tables and indexes more manageable and scalable.

For PeopleSoft applications, table and index partitioning can reduce time for management and maintenance and improve scalability and performance.
Maintenance operations, such as index reorganizations and rebuilds, can be performed on a specific partition only, thus optimizing the maintenance process. With an efficient partitioning design, maintenance operations can be significantly reduced.

In this situation, the partitioning design would most likely create partitions based on the static (data that does not change or seldom changes) and dynamic (data that is affected or changed very frequently) nature of data. All static data is stored in specific partition(s) and dynamic data is stored in other partition(s). Maintenance operations are only performed against the dynamic data partition, affecting only a very small portion of the data in the table. Backups can also be performed for the dynamic partition data only.

Note. You cannot directly back up a partition; however, you can place a partition on a specific filegroup and back up that filegroup only

Performance and Scalability
Queries with an equi-join on two or more partitioned tables can show improvements in performance if their partitioning columns are the same as the columns on which the tables are joined. The SQL Server query optimizer can process the join faster, because the partitions themselves can be joined. It is important to note that the partitioning function should be the same for the tables as well.

Performance can also be improved for queries that are executed on a specific partition of the table only. The assumption is that the entire data required to process the query is contained within the same partition. Because the index size and the index tree depth are smaller as compared to a similar size non-partitioned table, the query execution will be more efficient.

What follows is an example of how to partition a table or index in PeopleSoft applications. It is important to note that the example explains the steps required to create partitions. The choice of tables and the columns to partition on will depend on your application and specific scenario.

Step 1. Create a Partition Function
A partition function specifies how the table or index is partitioned. The function maps the domain into a set of partitions.

The following example maps the rows of a table or index into partitions based on the values of a specified column:

AS RANGE LEFT FOR VALUES ( '1000', '2000', '3000', '4000');

Based on this function, the table to which this function is applied will be divided into five partitions as shown below:

Step 2. Create a Partition Scheme
A partition scheme maps the partitions produced by a partition function to a set of filegroups that you define.

The following example creates a partition scheme that specifies the filegroups to hold each one of the five partitions. This example assumes the filegroups already exist in the database.

TO (HR1fg, HR2fg, HR3fg, HR4fg, HR5fg);

Step 3. Create a Table or Index Using the Partition Scheme
The example below creates the PS_LEDGER table using the partition scheme defined in Step 2.

[BUSINESS_UNIT] [char](5) COLLATE Latin1_General_BIN NOT NULL,
[LEDGER] [char](10) COLLATE Latin1_General_BIN NOT NULL,
[ACCOUNT] [char](10) COLLATE Latin1_General_BIN NOT NULL,
[ALTACCT] [char](10) COLLATE Latin1_General_BIN NOT NULL,
ON AcctRangePS1 (ACCOUNT) ;

The PS_LEDGER table will be created on the five partitions based on the partitioning function and the scheme created in Steps 1 and 2, respectively.

The following table shows the partitions for PS_LEDGER based on the previous examples:

It is strongly recommended that based on your specific PeopleSoft application scenario and requirements, the choice to partition or not, and what tables to partition, should be evaluated. Partitioning for most scenarios is most beneficial for management and maintenance. For some specific scenarios, partitioning can yield some performance improvement as well. If the tables involved in a query are not joined on the partitioning key and are not partitioned by the same partitioning function, or for a single table query, if all the data required for the query is not colocated on the same partition, performance may be negatively impacted.

Disabling Indexes
For maintenance and performance troubleshooting, SQL Server provides the functionality to disable a non-clustered or clustered index.

Disabling an index prevents user access to the index, and for clustered indexes it prevents access to the underlying table data. The index definition remains in metadata and index statistics are kept on non-clustered indexes. 

Disabling a non-clustered index or clustered index on a view physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt.

For PeopleSoft applications, you can disable indexes for the following reasons:
• To correct I/O errors and then rebuild an index.
• To temporarily remove the index for performance troubleshooting purposes.
• To optimize space while rebuilding non-clustered indexes.

Disabling a non-clustered index physically deletes the index data. The disk space made available when data is deleted can be used for subsequent index rebuilds or other operations.

When a non-clustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index.

The following example shows how to disable an index:



Make sure to evaluate each index carefully before disabling it. Some indexes may be required for monthly, quarterly, or yearend processes. Disabling infrequently used indexes could cause performance issues for those processes.