Index Management for Pre-packaged Products

Index Management for Pre-packaged Products
SQL Server is often used as an engine supporting third party products. Third party products may support multiple engines, and even allow users to define aspects of their products. As a result, third party products are often treated differently than in house developed products when it comes to maintenance, support and tuning.

One of our readers, in response to index management processes, provides some real world insight when it comes to managing indexes in an SQL Server database supporting PeopleSoft. Because of the flexibility of the PeopleSoft software, there is some special attention that pays off when new versions are released, and/or modifications are made to the structure of the application.

Bryan Writes:

Thought I would offer our experiences with indexing for SQL Server 2005 Professional. Our most challenging indexing responsibility is a mid-size PeopleSoft environment (350 gb). Most challenging is:

• The PeopleSoft environment is in constant use 24/7, therefore determining the level of fragmented indexes for 80,000 tables (without otherwise impacting database performance) is problematic
• For the same reason, defragmenting the indexes is challenging (hampered by a version of SQL that doesn’t allow Online reorganization or rebuild)
• As you may know, PeopleSoft indexes are defined and built from meta data created by the PeopleSoft, developers and administrators. The index definitions in the meta data must match the real SQL index definition or a PeopleSoft integrity is triggered. Moreover, a DBA cannot simply create a new index to help performance as the next PeopleSoft version upgrade would see the index as an integrity issue.
• PeopleSoft creates and destroys utility tables it uses for hundreds of thousands of inserts and deletes during normal processes. Since those tables may or may not exist at any point, it’s very difficult to address their index fragmentation. (Of course, the destruction and creation of the tables would take care of the fragmentation, but some of these tables can hang on for weeks and even spawn iterations of themselves.)

Our solution was a 3 step process:

• On a regular frequency, create a script to identify the current collection of tables with business data and to rebuild solely those table indexes. That script runs on a weekly frequency.
• For the business data table collection, record the fragmentation level on a nightly basis and store the growth rates for trending analysis on a non-production database
• Create a dynamic script that identifies and rebuilds indexes in PeopleSoft utility tables (these are not part of the business data table collection.) This captures any PeopleSoft utility table fragmentation. That dynamic script on a nightly frequency

There may be more elegant ways to manage index fragmentation, but this is the most workable within the administrative window that the 24/7 PeopleSoft up-time expectation allows us.

I’m sure many of you have experienced the need to establish similar kinds of processes for other third party software as well as other database engines. Feel free to share your experience with our readers by sending your comments to btaylor@sswug.org.

Thanks Bryan for sharing your insight.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Database Snapshots (Part 3 of 3)
Database Snapshots offer a read-only, static view of a source database at a specific point in time. Attend this session to learn how Database Snapshots can be used for reporting purposes, change management, and data recovery.

Featured White Paper(s)
Query Tuning Strategies for Microsoft SQL Server
Written by Quest Software

When you’re looking for a reliable tool to diagnose … (read more)

Featured Script
Rename Database Without Shutting Down
Use these 3 stored procedures to rename a running database through query analyzer. There must be no active users on the data… (read more)