System and method for cardinality estimation feedback loops in query processing

Abstract

Methods for cardinality estimation feedback loops in query processing are performed by systems and devices. A query host executes queries against data sources via an engine based on estimated cardinalities, and query monitors generate event signals during and at completion of execution. Event signals include indicia of actual data cardinality, runtime statistics, and query parameters in query plans, and are routed to analyzers of a feedback optimizer where event signal information is analyzed. The feedback optimizer utilizes analysis results to generate change recommendations as feedback for later executions of the queries, or similar queries, performed by a query optimizer of the query host. The query host stores change recommendations, and subsequent queries are monitored for the same or similar queries to which change recommendations are applied to query plans for execution and observance by the query monitors. Change recommendations are optionally viewed and selected via a user interface.

What is claimed is: 
     
         1 . A system comprising:
 at least one memory that stores program code; and   a processing system, comprising one or more processors, configured to receive the program code from the at least one memory and, in response to at least receiving the program code, to:   receive at least one event signal from a query host that executes a query against a database according to a query plan generated by the query host, the at least one event signal comprising one or more runtime statistics of the query;   determine via analysis of the one or more runtime statistics an actual cardinality of data queried in the database and at least one query parameter of a model for the query that is associated with an estimated cardinality utilized for the model;   generate a change recommendation for the at least one query parameter based at least on a difference between the estimated cardinality and the actual cardinality, the change recommendation being configured to alter a subsequent execution of the query and one or more similar queries; and   provide the change recommendation in a feedback signal to the query host.   
     
     
         2 . The system of  claim 1 , wherein the change recommendation is generated also based at least on a prior query parameter of a prior query executed before the query. 
     
     
         3 . The system of  claim 1 , wherein the change recommendation is generated also based at least on a correlation of queried data that includes one or more of independent correlation, partial correlation, or full correlation. 
     
     
         4 . The system of  claim 1 , wherein the one or more similar queries include at least one of:
 a same table;   a same order of two or more tables;   a same join predicate;   a same search predicate; or   one or more same outputs.   
     
     
         5 . The system of  claim 1 , wherein the at least one event signal includes a message that comprises:
 state information of a query statement before and after a change to the at least one query parameter; or   state information for temporary disabling of feedback signals due to oscillation of cardinality estimations.   
     
     
         6 . The system of  claim 1 , wherein the change recommendation for the at least one query parameter comprises a rollback to a prior model for the query or a temporary disabling of feedback signals. 
     
     
         7 . The system of  claim 1 , wherein the at least one query parameter comprises one or more of a memory grant, a join type, enablement or disablement of a join type, a forced join order, a forced cardinality estimation, a correlation type, an indexing setting, a containment type, an interleaved optimization for a table-valued function, or a deferred compilation of runtime objects such as table variables. 
     
     
         8 . A computer-implemented method, comprising:
 receiving at least one event signal from a query host that executes a query against a database according to a query plan generated by the query host, the at least one event signal comprising one or more runtime statistics of the query;   determining via analysis of the one or more runtime statistics an actual cardinality of data queried in the database and at least one query parameter of a model for the query that is associated with an estimated cardinality utilized for the model;   generating a change recommendation for the at least one query parameter based at least on a difference between the estimated cardinality and the actual cardinality, the change recommendation being configured to alter a subsequent execution of the query and one or more similar queries; and   providing the change recommendation in a feedback signal to the query host.   
     
     
         9 . The computer-implemented method of  claim 8 , wherein the change recommendation is generated also based at least on a prior query parameter of a prior query executed before the query. 
     
     
         10 . The computer-implemented method of  claim 8 , wherein the change recommendation is generated also based at least on a correlation of queried data that includes one or more of independent correlation, partial correlation, or full correlation. 
     
     
         11 . The computer-implemented method of  claim 8 , wherein the one or more similar queries include at least one of:
 a same table;   a same order of two or more tables;   a same join predicate;   a same search predicate; or   one or more same outputs.   
     
     
         12 . The computer-implemented method of  claim 8 , wherein the at least one event signal includes a message that comprises:
 state information of a query statement before and after a change to the at least one query parameter; or   state information for temporary disabling of feedback signals due to oscillation of cardinality estimations.   
     
     
         13 . The computer-implemented method of  claim 8 , wherein the change recommendation for the at least one query parameter comprises a rollback to a prior model for the query or a temporary disabling of feedback signals. 
     
     
         14 . The computer-implemented method of  claim 8 , wherein the at least one query parameter comprises one or more of a memory grant, a join type, enablement or disablement of a join type, a forced join order, a forced cardinality estimation, a correlation type, an indexing setting, a containment type, an interleaved optimization for a table-valued function, or a deferred compilation of runtime objects such as table variables. 
     
     
         15 . A computer-readable storage medium having program instructions recorded thereon that, when executed by at least one processing device, configure the at least one processing device to:
 generate at least one event signal that is provided to an optimization host for a first query executing against a database according to a first query plan and a first estimated cardinality, the at least one event signal comprising runtime statistics of the first query;   receive a feedback signal, from the optimization host, having a change recommendation for at least one query parameter of the first query;   determine a second query plan for a second query received subsequent to said receiving the feedback signal, the second query plan incorporating the change recommendation and based on a second estimated cardinality;   execute the second query according to the second query plan; and   generate at least one other event signal that comprises runtime statistics of the second query, and that is provided to the optimization host for the second query.   
     
     
         16 . The computer-readable storage medium of  claim 15 , wherein the program instructions configure the at least one processing device to:
 determine that the second query is similar to the first query; and   provide the change recommendation via a user interface as a selectable option for application to the second query.   
     
     
         17 . The computer-readable storage medium of  claim 16 , wherein the second query is similar to the first query based on one or more of:
 a same table;   a same order of two or more tables;   a same join predicate;   a same search predicate; or   one or more same outputs.   
     
     
         18 . The computer-readable storage medium of  claim 15 , wherein to generate the at least one event signal or generate the at least one other event signal, the program instructions configure the at least one processing device to:
 track state information of query statements before and after a change to query parameters; or   track state information for temporary disabling of feedback signals due to oscillation of cardinality estimations.   
     
     
         19 . The computer-readable storage medium of  claim 15 , wherein the change recommendation alters query execution based on data correlation that includes one or more of independent correlation, partial correlation, or full correlation. 
     
     
         20 . The computer-readable storage medium of  claim 15 , wherein the change recommendation alters query execution based on a rollback to a prior model for the second query or a temporary disabling of feedback signals.