Computer systems and methods for predictive performance management of data transactions

A system for managing performance of database transactions, the system comprising:

• at least one database configured to store data;
• at least one application configured to interact with the data of the at least one database through a plurality of database transactions that comprise structured query language (SQL) statements;
• and
• a management subsystem configured to generate one or more alerts identifying degrading ones of the SQL statements, the management subsystem configured to execute on one or more computing devices and comprising,
• a monitor module comprising one or more computer hardware processors, the monitor module configured to track one or more performance measurements of the plurality of the SQL statements, the one or more performance measurements comprising at least one of (i) a total cost of execution of a particular SQL statement of the plurality of SQL statements, (ii) an average cost of all executions of the particular SQL statement, and (iii) an execution rate of the particular SQL statement,
• a modeler module comprising one or more computer hardware processors, the modeler module configured to correlate the one or more tracked performance measurements beginning at a first data point of the particular SQL statement with one or more independent variables and to assign at least a first performance model to represent the one or more tracked performance measurements to generate a first trend of and predicted performance measurements of the particular SQL statement,
• a trend change module configured to automatically detect within the one or more tracked performance measurements at least one substantial trend change in a the first trend of the one or more tracked performance measurements,
• wherein the trend change module automatically detects a trend change by iteratively selecting a second data point for the modeler module to generate at least a second performance model for a second trend of predicted performance measurements beginning at the second data point, wherein the trend change module determines a trend change at the second data point when:
• 1) at least three measured data points prior to the second data point fall outside of the second trend, and
• 2) a comparison of the first trend and the second trend of predicted performance measurements with tracked performance measurements indicates that the second trend is significantly better than the first trend at predicting the tracked performance measurements, and
• an interface module configured to trigger an alert when the second trend of the second performance model indicates that the predicted performance measurements of the particular SQL statement are degrading at or above a threshold rate to identify whether the particular SQL statement needs to be tuned;
• wherein the one or more first performance models and the second performance model are selected from a set of predetermined performance models;
• wherein the management subsystem further comprises a memory configured to store the set of predetermined performance models;
• and
• wherein the set of predetermined performance models comprises logarithmic, linear and exponential curves.

A method for managing database performance, the method comprising:

• monitoring with one or more computer hardware processors, performance values of a plurality of database transactions that comprise structured query language, (SQL) statements;
• storing the performance values in a storage device;
• correlating with one or more computer hardware processors, the performance values of at least one of the SQL statements with one or more independent variables;
• assigning at least a first performance model to represent past and predicted performance of the at least one SQL statement;
• detecting among the correlated one or more performance values a first performance value representing a change in a first trend of performance of the at least one SQL statement;
• dividing at the first performance value the correlated one or more performance values into a plurality of segments;
• generating at least a second performance model to represent at least one of the plurality of segments, the at least the second performance model indicating a trend change in the first trend that determines a second trend of predicted performance of the at least one SQL statement;
• automatically detecting the trend change by iteratively selecting a second data point wherein:
• 1) at least three data points prior to the second data point fall outside of the second trend, and
• 2) the second performance model is significantly better than the first performance module at predicting the tracked performance measurements over the second trend;
• and
• issuing an alert when the second performance model indicates that the predicted second trend of performance of the at least one SQL statement is degrading at or above a threshold rate to identify whether the particular SQL statement needs to be tuned;
• wherein the second performance model indicates whether I/O channels associated with the execution of the plurality of SQL statements will near capacity in the future.

A method for managing database performance, the method comprising:

• monitoring with one or more computer hardware processors, performance values of a plurality of database transactions that comprise structured query language (SQL) statements;
• storing the performance values in a storage device;
• correlating with one or more computer hardware processors, the performance values of at least one of the SQL statements with one or more independent variables;
• assigning at least a first performance model to represent past and predicted performance of the at least one SQL statement;
• detecting among the correlated one or more performance values a first performance value representing a change in a first trend of performance of the at least one SQL statement;
• wherein said detecting further comprises identifying a predetermined number of data values immediately preceding the first performance value that reside outside the prediction interval;
• dividing at the first performance value the correlated one or more performance values into a plurality of segments;
• generating at least a second performance model to represent at least one of the plurality of segments, the at least the second performance model indicating a trend change in the first trend that determines a second trend of predicted performance of the at least one SQL statement;
• automatically detecting the trend change by iteratively selecting a second data point wherein:
• 1) at least three data points prior to the second data point fall outside of the second trend, and
• 2) the second performance model is significantly better than the first performance module at predicting the tracked performance measurements over the second trend;
• and
• issuing an alert when the second performance model indicates that the predicted second trend of performance of the at least one SQL statement is degrading at or above a threshold rate to identify whether the particular SQL statement needs to be tuned;
• associating a prediction interval with the second performance model;
• and
• calculating the prediction interval such that a probability that a selected performance value resides outside the prediction interval is at most approximately five percent.