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!
