Reconsidering the idea of monitoring pre-production database instances

It is a foregone conclusion that performance monitoring is part and parcel of managing a database instance once it is in production.  But what about pre-production?  Especially when the instance itself is reaching critical phases of testing and quality assurance prior to roll-out.  I often ask my clients how they are currently monitoring their pre-production environment and in many cases it is considered a nice-to-have instead of a necessity.  Well, in the age of continuous integration and delivery of applications (including their back end data), perhaps we need to reconsider the need for monitoring pre-production database instances.

Perhaps the following ideas and thoughts will provide food for thought.

Developers and DBAs are always keen on understanding how databases, objects, programs and data in general are being used.  This helps when future changes to the application and backend database instance are sought, helping avoid costly errors and knowing what the true effort and impact of a planned change will entail. 

Another aspect of this type of research in preproduction can help control database sprawl and provide a checkpoint as to whether certain databases need to be there in the next upgrade or migration. 

  • Will my most critical transactions be affected by the changes planned in the database instance?
  • Can certain databases be consolidated or even deleted?
  • How will system configuration changes impact the critical transaction workload?

Fig 1 - Top Programs Accessing the Database Instance

Fig 2 - Comparing Workload Statistics between Production and Pre-Production DB Instances

Fig 3 - Configuration Differences between Production and Pre-Production DB Instances

Fig 4 - Documenting impact of changes (DB/System) on Workload Performance

Monitoring database transaction workload during testing phases can help validate if certain configurations are going to stand the test of time once in production.  It will also help Developers and DBAs understand if certain tuning efforts can be instituted early on which prevents unnecessary cost and downtime associated with tuning in production. 

  • Are there certain statements that will require ongoing plan maintenance?
  • Will the database instance handle the I/O load during peak activity?
  • Are there any missing indexes worth considering?
  • Are there any reorganization candidates?

Fig 5 - Top Objects by I/O Consumption

Fig 6 - Execution Plan Comparison for a specific transaction

Fig 7 - Advisory Reporting on Workload Deviation - Missing Indexes

These are just a few considerations that should open the door so-to-speak in considering performance monitoring in the arena of pre-production monitoring of database instances.  Foglight’s Performance Investigator with its workload analytics for Oracle and SQL Server platforms provides Developers and DBAs a comprehensive solution for researching the considerations mentioned above.  Bundling Foglight with the SQL Optimizer for Oracle and SQL Server gives Developers and DBAs the ability to optimize SQL statements through automated rewrites, index recommendations and plan management.  Get more utility out of your performance monitoring practice and ensure your next database instance roll-out gets the white glove treatment it deserves!

About the Author
Amit Parikh
My career in Information Technology spans 20 years where I've held various roles both in management and as an individual contributor. I'm currently a Senior Consultant on Quest's Information...