With the release last month of ArcFM 10.2.1c, some utilities are considering an upgrade — something that is a bit of an SSP specialty since we have done over 25 upgrades for 20 clients in the last 10 years. In fact, our proven approach has been so successful that we have many repeat customers.

One important part of our approach to each upgrade is the optional Database Tuning exercise, an effort led by SSP’s lead DBA, Jeff Buturff. He has provided information about this in the past (see Life in the Fast Lane from 2014), but we have been fielding a lot of questions recently about Jeff’s work and thought it might be time to provide a brief overview.

Jeff’s work usually begins after the cutover to Production is complete and the system has been stable for several weeks. At this time, Jeff will travel onsite to begin his work.

The first thing Jeff will do is review the current state of the Production Database(s), monitoring the Production database server during normal activity. During this review, he will look at many things including:

• CPU Utilization
• Disk Utilization
• Memory Utilization
• Network Utilization

Here, for example, is an analysis of the memory allocated to the SQL Server at one client:

SQL Server currently has ample memory allocated to it. There are no signs of memory pressure, and SQL Server is actually not using all the memory it’s allowed to consume.

In the following PerfMon screenshot, the TOTAL SERVER MEMORY is the amount of memory SQL Server is consuming, while the TARGET SERVER MEMORY is the amount it thinks is optimal. In this case, the total consumed is well under the target, meaning that SQL Server is using all the memory it thinks it should.

Here’s a look at Disk Utilization from a different client:

Typically, in an RDBMS, the disk subsystem is the overall performance bottleneck.  However, as we can see by the screenshot below, that is not the case for W-EGISDB04.

Currently, the disk IO subsystem on W-EGISDB04 is performing well, with relatively low disk queue lengths.  The disk IO subsystem is not a bottleneck for the system.

The great thing about the Jeff’s “Current State” analysis is that it is so easy to understand. We see a lot of clients incorporate Jeff’s data and analysis into reports used for upper management.

But the key part of Jeff’s report isn’t so much the “Current State” (i.e., how are we doing right now). It’s the “Future State” (i.e., how can we get better). Jeff’s recommendations are famous for their clarity and their ease of implementation. In fact, Jeff will often help the client DBAs implement his recommendations before the document is even final!

Here is one example from a different client:

Oracle Parameters
The following Oracle parameters are recommended for the WGISXS05 database only.

Parameter Recommended Value
MEMORY_TARGET 9G
MEMORY_MAX_TARGET 9G

The use of the above parameters represents a move to Oracle’s Automatic Memory Management (AMM) feature.

Versioning
The following Versioning statistics were collected on WGISXS05.

*                  Versioning statistics
*=======================================================
* Number of versions: 178
* Number of versions blocking DEFAULT: 176
*   Top 5 blocking versions…
*     U126420.SN_5377
*     U126389.SN_8259
*     U124831.SN_8992
*     U124831.SN_9010
*     U126910.SN_9350
* Number of states: 764
* Number of state lineages: 32924
* DEFAULT versions lineage length: 189

The number of versions blocking DEFAULT (176 out of 178 versions) is too high.  This number indicates that not enough versions are being reconciled nightly.  Also, the state_lineages (32,924) is too high when compared to the total number of states (764).

Recommendation: 

More versions need to be reconciled nightly, and any conflicts should be resolved as soon as possible.

Many of the recommendations (and Jeff usually includes several) are “low-hanging fruit” — meaning that they are quick and easy to implement.

We have found that most clients who choose this optional item are glad they did. As always, reach out to us if you want more details!