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:
CREATE
PARTITION FUNCTION AcctRangePF1 (char(10))
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.
CREATE
PARTITION SCHEME AcctRangePS1
AS
PARTITION AcctRangePF1
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.
CREATE
TABLE PS_LEDGER (
[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) ;
GO
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:
ALTER INDEX
PSCLEDGER ON dbo.PS_LEDGER
DISABLE ;
GO
To enable
an index, use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING.
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.
