Measuring the impact of changes on DB performance

As the old saying goes, “the one constant in life is change”, and this is especially true in database environments.  There are many reasons why database environments experience change whether it be through typical maintenance efforts such as object modifications or through an IT initiative such as a system or database upgrade.  These changes can have an impact on performance which can be positive or negative depending on the type of workload dependent on the subsystem.  The ability to accurately measure the performance impact of changes occurring in the database is vital which enables DBA's to forecast the effect of these changes as well as provide a level of validation for certain tuning approaches. 

As a best practice, change tracking should be included in every DBAs approach to database performance monitoring.  Furthermore, the tool used for change tracking should be an integrated monitoring mechanism which periodically tracks changes in the underlying database environment and provide a means to correlate between occurrences of changes and databases activity and behavior patterns. 

Foglight provides the capability for database change tracking through its cartridges for Oracle and SQL Server with the SQL Performance Investigator Extension (SQL PI).  The SQL Performance Investigator Extension (SQL PI) enables DBAs to examine top resource consumers affecting database workload through multi-dimensional drilldowns. 

Fig 1- SQL Performance Investigator Page

Once in the SQL PI page for a specific database instance, users can select the time range they are interested in investigating and then click on the Change Tracking link.  Foglight SQL PI monitors several categories of changes including the following:

  • For SQL Server:
    • Accounts
    • Database Configuration
    • Database Objects
    • Execution Plan
    • Master Configuration
    • System Configuration
    • User Defined
  • For Oracle:
    • Execution Plan
    • Oracle Configuration
    • Oracle Schema
    • System Configuration
    • User Defined

The Change Tracking view provides a summary count of changes across the above categories as well as plot their occurrences in color coded fashion on the Resource Consumption graph.  This helps users quickly determine how performance may have been affected in and around the change event.  The table below the Resource Consumption graph itemizes the changes and users can click on each record to get more details such as the details of the change or what the old and new values where in a parameter change.

Fig 2:  Change Tracking view

SQL PI also offers the ability to compare database performance across different; whether it be the same instance comparing two periods of time; or when you want to compare two different instances across the same time period.  This is especially useful when you are looking to get a before and after picture on changes introduced to the database through a maintenance effort or understanding how a newly upgraded instance performs against the same workload running on an older version.  Change Tracking details are also presented in this comparison which helps DBAs account for those changes that may have impacted performance.

Fig 3:  Compare View

DBAs have seen the value in utilizing the Change Tracking features found within Foglight’s SQL Performance Investigator Extension.  One particular example is when a customer experienced performance degradation after a database upgrade.  They were using a packaged application and part of the application upgrade required the database to be modified as well.  The customer was unhappy with the resulting performance but their support case stalled due to the vendor’s inability to reproduce the performance issue.  The customer was able to use the details found in SQL PI Change Tracking to clearly show the before and after view of performance through the Compare report as well as the resource consumption correlated to the plotted change events.  This resulted in a more productive support case and they vendor was eventually able to make the necessary improvements which pleased the customer.

For more information on Foglight and the SQL Performance Investigator Extension, please visit our documents online.  I’ve also included direct links to the Change Tracking documentation here:

Anonymous