PeopleSoft Database Maintenance Part1

Managing Indexes
SQL Server maintains indexes automatically. However, indexes can become fragmented over time. The fragmentation can be of two types: internal and external.

Internal fragmentation occurs when large amounts of data are deleted and pages are less full. A certain amount of free space on index pages is beneficial as it allows room for future inserts into these pages without having to split a page.

When the next logical page of an index is not the next physical page, it is called external fragmentation. This may impact performance when SQL Server is doing an ordered scan of all or part of a table, or an index. The access by a range of values is no longer sequential, limiting the ability of the storage engine to issue large I/O requests.

Parallel Index Operations
For PeopleSoft applications, creating or maintaining indexes on large tables can benefit tremendously when you execute the index creation or maintenance operation on parallel CPUs.

SQL Server 2005 provides the ability to use the MAXDOP query hint to manually configure the number of processors that are used to run the index statement. In prior versions of SQL Server, the server setting of MAXDOP (and the current workload) determined the number of processors to be used by the index operation.

The MAXDOP index option overrides the max degree of parallelism configuration option for the query specifying this option.
Parallel index execution and the MAXDOP index option apply to the following Transact-SQL statements:

• DROP INDEX (for clustered indexes only)
• ALTER TABLE DROP (clustered index) CONSTRAINT19

Note. Parallel index operations are available only in SQL Server 2005 Enterprise Edition.

Following is an example of using the MAXDOP index option with an ALTER INDEX statement:


For PeopleSoft applications, it is recommended that you set the MAXDOP server setting to 1. However, during index operations, this setting could be overridden by using the MAXDOP query hint as shown above, for better performance and CPU resource utilization.

Index-Related Dynamic Management Views
SQL Server 2005 provides the ability to query and return server and database state information to monitor the health of a server instance, diagnose problems, and tune performance through dynamic management views. The following sections describe some index-specific dynamic management views that can be used for monitoring and tuning indexes.

Identify Frequently Used Indexes
The sys.dm_db_index_usage_stats dynamic management view returns information on the usage frequency of an index. The following example query can be used to return information on the frequency of seeks, scans, and lookups by a user query on all indexes for all user tables in a specific database:

select db_name(database_id)as 'DB Name', object_name(isu.object_id) as 'Table
, as 'Index Name', user_seeks as 'Seeks', user_scans as 'Scans' , user_
lookups as 'Lookups'
from sys.dm_db_index_usage_stats isu
inner join sys.indexes si
on si.index_id = isu.index_id
and si.object_id = isu.object_id
inner join sys.objects so
on so.object_id = si.object_id
and so.type = 'U'

The sys.dm_db_index_usage_stats dynamic management view or the query using it can be used in PeopleSoft applications to retrieve information on the index usage statistics of the database. This information can help to evaluate index usage and plan for index maintenance operations as well. It is important to note that the information in this dynamic management view is reset or deleted when the SQL Server service is started.

Identify Missing Indexes
The SQL Server 2005 query optimizer has the ability to identify missing indexes for queries. When the query optimizer generates a query plan, it analyzes the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a query plan based on available indexes, but stores information about the desired missing indexes.

This information can be retrieved from the dynamic management views and analyzed to improve the indexing and query performance.
The dynamic management views that identify and store the missing index information consist of the following:

sys.dm_db_missing_index_group_stats: Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
sys.dm_db_missing_index_groups: Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.
sys.dm_db_missing_index_details: Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
sys.dm_db_missing_index_columns: Returns information about the database table columns that are missing an index. The following example query can be used to identify missing index information for PeopleSoft applications.

select d.*
, s.avg_total_user_cost
, s.avg_user_impact
, s.last_user_seek
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
order by s.avg_user_impact desc
--- suggested index columns & usage
declare @handle int
select @handle = d.index_handle
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
select *
from sys.dm_db_missing_index_columns(@handle)
order by column_id

It is highly recommended for PeopleSoft applications that you do a thorough analysis of the missing index data before adding any indexes. Adding indexes may improve query performance; however, adding indexes may add overhead for update and insert operations.

Note. Use only PeopleTools to add indexes.

Note. The information in this dynamic management view is reset or deleted when SQL Server service is started.

Identify Indexes Not Used to a Point in Time
Indexes that are created in the database, but have not been used by any query until a point in time can be identified with the sys.dm_db_index_usage_stats dynamic management view.

The following example query identifies unused indexes:

select object_name(i.object_id),
from sys.indexes i, sys.objects o
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = db_id('PSFTDB') )
and o.type = 'U'
and o.object_id = i.object_id
order by object_name(i.object_id) asc

For PeopleSoft applications, it is important to understand that some indexes could be used quite infrequently; however, they still could be quite critical for performance of some specific functionality. For example, a batch process could run monthly, quarterly, or even annually. The index identified by the previous example query may never be used, but a batch process that is scheduled to be run could be using it. Deleting such an index could have adverse effects on performance for scheduled (but unrun) batch processes. Though it may lower overhead to delete indexes that are never used, thorough analysis should be made before deleting any index.

Note. The dynamic management view used to identify the unused index information gets reset or information in it is deleted when SQL Server is restarted. So at best, the information revealed by the previous query is from the last known SQL Server restart to the point in time the dynamic management view query was executed.

Detecting Fragmentation
In SQL Server 2005, fragmentation can be detected by using the index-related dynamic management view sys.dm_db_index_ physical_stats. The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement in SQL Server 2000.

The algorithm for calculating fragmentation is more precise in SQL Server 2005 than in SQL Server 2000. As a result, the fragmentation values will appear higher.

The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. For heaps, the value represents the extent fragmentation of the heap. For indexes, the value represents the logical fragmentation of the index. Unlike DBCC SHOWCONTIG, the fragmentation calculation algorithms in both cases consider storage that spans multiple files and, therefore, are more accurate.

The following example query demonstrates how the dynamic management view returns fragmentation information:

SELECT database_id,object_id, index_id, index_type_desc, index_depth, index_
level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats

For PeopleSoft applications, the value for avg_fragmentation_in_percent should ideally be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent may be acceptable.

Reducing Fragmentation
In SQL Server 2005, there are three ways to reduce fragmentation:
• Drop and re-create the index
• Reorganize the index
• Rebuild the index

The CREATE INDEX .. DROP EXISTING = ON statement can be used to drop and re-create the index.

The following example demonstrates using the CREATE INDEX statement to drop and re-create an index:


Use the DROP_EXISTING option to change the characteristics of an index or to rebuild indexes without having to drop the index and re-create it. The benefit of using the DROP_EXISTING option is that you can modify indexes created with PRIMARY KEY or UNIQUE constraints. This option performs the following:
• Removes all fragmentation.
• Recalculates index statistics.

The second way to reduce fragmentation is to reorganize the index. To reorganize the index, use the ALTER INDEX REORGANIZE statement. It is the replacement for DBCC INDEXDEFRAG, and it will reorder the leaf-level pages of the index in a logical order. Use this option to perform online logical index defragmentation.

It is possible that an interruption may happen to the defragmentation process. The operation can be interrupted without losing work already completed.
The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation and it does not update statistics.

The following example demonstrates the ALTER INDEX REORGANIZE statement:


The third way to reduce fragmentation is to rebuild the index. To do so, use the ALTER INDEX REBUILD statement. It is the replacement for DBCC DBREINDEX and it will rebuild the index online or offline. Use this option to:

• Remove heavy defragmentation.
• Rebuild the physical index online or offline.

The ALTER INDEX REBUILD statement requires a statistics update.

The following example demonstrates the ALTER INDEX REBUILD statement:


In general, when the avg_fragmentation_in_percent value is between 5 and 30 percent, the ALTER INDEX REORGANIZE statement can be used to remove fragmentation. For heavy fragmentation (more than 30 percent) the ALTER INDEX REBUILD or CREATE INDEX DROP EXISTING statements can be used.

Use the following guidelines to decide between the two options.

* A nonclustered index can be converted to a clustered index type by specifying CLUSTERED in the index definition. This operation must be performed with the ONLINE option set to OFF. Conversion from clustered to nonclustered is not supported regardless of the ONLINE setting.

** If the index is re-created by using the same name, columns and sort order, the sort operation may be omitted. The rebuild operation checks that the rows are sorted while building the index.

Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. This causes slower response times. If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect on performance. It is also not recommended to remove fragmentation for fragmentation of 5 percent or less. Depending on the index size, the cost may outweigh the benefit.

Online Index Reorganization
For most PeopleSoft applications, high application uptime and availability are expected. Database maintenance operations can sometimes affect the uptime requirement, because the database may have to be taken offline for database maintenance.

SQL Server 2005 provides capabilities for database maintenance, specifically index reorganizations to be done online without affecting application uptime and availability.

Online Operations
In SQL Server 2005 you can create, rebuild, or drop indexes online. The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated non-clustered indexes during these index operations. When the indexes are being built with the ONLINE option, concurrent user access to query and modify the underlying table data is permitted.

The ONLINE option is available in the following Transact-SQL statements.
• ALTER TABLE (To add or drop UNIQUE or PRIMARY KEY constraints with CLUSTERED index option)

The following example demonstrates an online index rebuild operation:

In the following example, all indexes on the PS_LEDGER table are rebuilt online.


When you perform online index operations, the following guidelines apply:
• Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) datatypes: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
• Non-unique non-clustered indexes can be created online when the table contains LOB data types, but none of these columns are used in the index definition as either key or non-key (included) columns. Non-clustered indexes defined with LOB data type columns must be created or rebuilt offline.
• Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.
• The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.

You can perform concurrent online index operations on the same table only when doing the following:

• Creating multiple non-clustered indexes.
• Reorganizing different indexes on the same table.
• Reorganizing different indexes while rebuilding non-overlapping indexes on the same table.

All other online index operations performed at the same time fail. For example, you cannot rebuild two or more indexes on the same table concurrently, or create a new index while rebuilding an existing index on the same table.