Customer Success Story
Smile Saver, Inc.

Click here to see the purchase justification document written by Smile Saver's DBA.

ProActive DBA Increases Performance by 150% for Sybase Database at Smile Saver, Inc.
Smile Saver, Inc., a Signature Subsidiary, is a leading provider of low cost dental insurance in the Southern California area. Mark Moyer, Smile Saver’s DBA, decided to evaluate ProActive DBA to see if it could help them locate performance bottlenecks in their database and take over mundane maintenance chores. During the evaluation period, Mark was able to increase database performance by 150% with minimal effort.

"ProActive DBA made performance improvements simple. Right out of the box, it justified its cost because we got a 150% performance increase," said Mark Moyer.

A proactive database administrator (hence, the product name ProActive DBA) is one that performs maintenance and tuning on a regular basis to prevent emergencies from occurring that usually result in reactive solutions. ProActive DBA makes its users more proactive because it is a complete database structural analysis and maintenance solution. Using a simple paradigm of "Analyze, Automate, Accelerate", the product provides analysis features for every aspect of Sybase and Microsoft SQL Server’s structure and is capable of automatically fixing performance problems it finds.

Smile Saver is running Sybase Adaptive Server Enterprise (ASE) 11.5 on a Compaq Proliant 6000 with four CPUs, 512 MB of RAM, and Windows NT Server 4.0. The database’s devices exist on a hardware RAID 5 configuration consisting of 12 drives. Smile Saver has written and deployed a Power Builder application which is used by their staff as a front-end to the information housed in their database. A typical batch process which they ran daily took 50 minutes to complete. After using ProActive DBA to analyze and defragment their database, the batch process completed in just 20 minutes!

Increase Database Performance Immediately
Here is the process Mark used to increase his database’s performance:

• Analyze. The first step is to let ProActive DBA analyze the database. ProActive DBA lets you do this through its task scheduler so it can occur at off-peak hours. During this process, ProActive DBA analyzes all database tables and indexes to determine their level of fragmentation, the amount of space reserved/used/wasted, and the accuracy of indexes distribution statistics. Mark used ProActive DBA’s Object Information report to determine what was causing his database’s most critical tables to bottleneck. This summary report is broken down by the following sections: space allocation analysis, fragmentation analysis, cache effectiveness analysis, page usage information, and index distribution analysis.
• Automate. Mark used the automated Database Maintenance Facility to defragment tables whose fragmentation levels exceeded acceptable thresholds, update index statistics on indexes, and to perform database verification. Using the ProActive DBA Task Scheduler, he scheduled this to occur at off-peak hours.
• Accelerate. Once the automated maintenance had completed, Mark generated historical trending reports by table and index through ProActive DBA to show the reduction in fragmentation, wasted space, and index distribution inaccuracies.

Automated Database Maintenance
ProActive DBA has a comprehensive task scheduler that can be used to schedule database analysis, database maintenance, and execution of any Transact SQL script or Win32 program. The task scheduler is also capable of sending notifications via e-mail.

Before using ProActive DBA, Smile Saver had been using a different scheduling product which was much more difficult to use and didn’t provide as many features. Additionally, in Enterprises where more DBAs have to manage more than one database server, ProActive DBA can be used to manage and schedule tasks for all database servers from just one workstation. Because it is based on a true client/server architecture, proactive management occurs through the ProActive DBA Console, and analysis, maintenance, and tuning occur through the ProActive Agents which can run on the same machine as the database server.

True I/O Load Balancing Analysis and Cache Effectiveness Analysis
SQL Server Segments are one of the least used performance/tuning technologies today. They provide a means to split database tables and their indexes onto different disk devices. This can provide enormous performance gains. ProActive DBA provides a graphical facility where you can determine exactly which device and the location on those devices that tables and indexes reside.

The use of data caches is another area that is overlooked and often misunderstood. Microsoft SQL Server provides a self-tuning cache which can enjoy serious performance gains by routine defragmentation. Sybase provides a lot of knobs and controls to allow customization of their cache. This is quite powerful and offers the maximum gain in performance if configured properly. If misused, it can cause performance degradation. ProActive DBA provides reports that indicate the effectiveness of caches for both environments.

The performance increase gained by using ProActive DBA has made Mark proactive. He now has the time to investigate I/O load balancing and configuring caches to further increase performance.

When asked to describe what ProActive DBA does, Mark said, "ProActive DBA helped reduce the cholesterol level of our database."

Kevin Coe, MIS Director at Smile Saver, had this to say about ProActive DBA, "it’s detailed analysis reports are very comprehensive."

True Database Structure Analysis
While performing database verification, Mark sometimes ran across errors in the output indicating problems with specific database pages. Using ProActive DBA, he was able to easily open the page graphically to investigate problems. ProActive DBA automatically translates the page’s header and rows to a readable format to make it easy for you to discover problems. You can additionally generate a Transact SQL script with INSERT statements for every row on the page.

Mark Moyer - Smile Saver's DBA
Purchase Justification

Goal
Improve the performance of the Sybase SQL Adaptive Server in a test environment, using production data.

Benchmarks
First we ran the ‘Mid-month Roster Extract’ process and another SQL Select statement in order to determine the control benchmark. The Mid-month extract ran for 59 minutes and the SQL Select ran for 20 minutes.

Analyze
The ProActive DBA software analyzed our critical database tables. That helped us to identify the current level of data page fragmentation. The analysis also revealed the effectiveness of our data caches and a wealth of other vital statistics.

Automate
ProActive DBA comes equipped with a task scheduler that will allow you to run SQL scripts, CMD command files and PDBA analysis tasks. We had the software build us an analysis script which looked at the current fragmentation level of DMS tables ‘RELATOR’ and ‘RELATOR_FLAG’. Then we scripted the defrag process and scheduled the tasks to run over the weekend. After the defrag was run, we again had the software run another analysis script so we could determine the results of the defrag operation.

Accelerate
The results from the second analysis were impressive. (see attached documentation). Basically, total fragmentation levels from both tables went from about 80% down to 20%. Table scan times went from about 1 hour 45 minutes to about 20 minutes and disk space utilization increased as well. For example: the RELATOR table used to take up 1.6 GB of disk space. Now it occupies 1.3 GB.

The Bottom Line:
As you can see from the table below, Smile Saver realized substantial performance gains from ProActive DBA:

Task	Before	After	Improvement
Mid-Month Extract	59 min.	21 min.	180%
SQL Select	20 min.	5 min.	300%

Users are much happier, and productivity has increased as a result.

Additional Benefits
ProActive DBA has a Task Scheduler where you can schedule ISQL scripts and Windows NT Command files (.CMD). The tasks can be scheduled for a specific time as well as sequential execution of multiple tasks.

ProActive DBA also gives you a graphical representation of all the devices owned by each database. You can determine the exact location of tables and indexes on each device. This helps verify which segments and devices hold the data making I/O load balancing easier.

This ability to "look under the hood" of the database has educational benefits as well.