Systems and methods for cardinality estimation feedback loops in query processing

Abstract

A method for a cardinality estimation feedback loop in query processing is performed by a system and a device. A query host executes a query against a data source based on an estimated cardinality via an engine, and a query monitor generates event signals during and upon completion of the execution. The event signals include: an actual data cardinality, runtime statistics, and a marker of query parameters in a query plan, and are routed to an analyzer of a feedback optimizer, where the event signal information is analyzed. The feedback optimizer utilizes the analysis results to generate change recommendations as feedback for a subsequent execution of the query or a similar query executed by a query optimizer of the query host. The query host stores the change recommendations and monitors subsequent queries for the same or a similar query, and the change recommendations for the query are applied to the query plan for execution and observation by the query monitor. The change recommendations are optionally viewable and selectable via a user interface.

Description

Background Art

[0001] Many modern relational database engines rely on cost - based query optimization, where the efficiency of the selected query plan depends on the accuracy of cardinality estimation. Cardinality estimation can be based on statistics related to data distribution and different models related to query shape. There are currently models for estimating the cardinality for specific types of query operators, and depending on factors such as data correlation or included assumptions, these models can produce significantly different results. Additionally, application workloads can be vulnerable to changes in the internal query - processing cardinality estimation models, which can lead to a sudden performance degradation because the execution plan used is different from a previously known good execution plan. When these sudden performance issues occur, workload degradation can affect the number of queries that can be executed against the database due to factors such as memory / processor shortage or improper allocation and a significant increase in runtime.

[0002] Internal query - processing model changes are code enhancements and optimizations that, due to the complexity of the query optimizer and the infinite different types of workload profiles running on a relational database, can produce results that degrade execution performance compared to a previously known good execution plan. Summary of the Invention

[0003] This "Summary of the Invention" is provided to introduce a selection of concepts in a simplified form that are further described below in the "Detailed Description". This "Summary of the Invention" is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.

[0004] A method for a cardinality - estimation feedback loop in query processing for a database such as a relational database is executed by a system and a device. A query host executes a query against a data source via an engine based on an estimated cardinality, and a query monitor is used to generate event signals during execution. The event signals include: the actual data cardinality, runtime statistics, and markers of query parameters in the query plan, and are routed to an analyzer of a feedback optimizer, where the information in the event signals from the monitor is analyzed. Then the feedback optimizer uses the information from the analysis to generate feedback recommendations for optimizing the execution of the query or subsequent executions of similar queries performed by the query optimizer of the query host. After being received by the query host, the feedback recommendations are stored and monitored for subsequent queries for the same or similar queries, and the feedback recommendations for such queries are applied to the query plan for the query monitor to execute and comply with. The feedback recommendations can be selectively viewed and selected via a user interface.

[0005] Other features and advantages, as well as the structure and operation of various examples, will be described in detail below with reference to the accompanying drawings. Note that the ideas and techniques are not limited to the specific examples described herein. Such examples are for illustrative purposes only. Based on the teachings contained herein, other examples will be apparent to those skilled in the relevant art(s). BRIEF DESCRIPTION OF THE DRAWINGS

[0006] The accompanying drawings, which are incorporated herein and form part of the specification, illustrate embodiments of the present application and, together with the specification, further serve to explain the principles of the embodiments and enable one skilled in the relevant art(s) to make and use the embodiments.

[0007] Figure 1 FIG. shows a block diagram of a networking system for cardinality estimation feedback loop in query processing according to an example embodiment.

[0008] Figure 2 FIG. shows a block diagram of a computing system configured for cardinality estimation feedback loop in query processing according to an example embodiment.

[0009] Figure 3 FIG. shows a flowchart of a cardinality estimation feedback loop in query processing according to an example embodiment.

[0010] Figure 4 FIG. shows a flowchart of a cardinality estimation feedback loop in query processing according to an example embodiment.

[0011] Figure 5 FIG. shows a block diagram of a system for cardinality estimation feedback loop in query processing according to an example embodiment.

[0012] Figure 6 FIG. shows a flowchart of a cardinality estimation feedback loop in query processing according to an example embodiment.

[0013] Figure 7 FIG. shows a flowchart of a cardinality estimation feedback loop in query processing according to an example embodiment.

[0014] Figure 8 FIG. shows a block diagram of a system having a user interface for leveraging a cardinality estimation feedback loop in query processing according to an example embodiment.

[0015] Figure 9 FIG. shows a flowchart of a cardinality estimation feedback loop in query processing according to an example embodiment.

[0016] Figure 10 FIG. shows a block diagram of an example computing device that may be used to implement embodiments.

[0017] The features and advantages of the embodiments will become more apparent from the following detailed description taken in conjunction with the accompanying drawings, in which like reference numerals identify corresponding elements throughout. In the drawings, like reference numerals generally denote identical, functionally similar, and / or structurally similar elements. The figure in which an element first appears is indicated by the leftmost digit(s) in the corresponding reference numeral. Detailed Description

[0018] I. Introduction

[0019] The following detailed description discloses multiple embodiments. The scope of this patent application is not limited to the disclosed embodiments, but also includes combinations of the disclosed embodiments and modifications to the disclosed embodiments.

[0020] References in the specification to "one embodiment", "an embodiment", "example embodiment", etc., indicate that the described embodiment may include a particular feature, structure, or characteristic, but each embodiment may not necessarily include the particular feature, structure, or characteristic. Moreover, these phrases do not necessarily refer to the same embodiment. Further, when a particular feature, structure, or characteristic is described in connection with an embodiment, it is considered within the knowledge of those skilled in the art to implement such feature, structure, or characteristic in combination with other embodiments (whether or not explicitly described).

[0021] In the discussion, unless otherwise specified, adjectives that modify one or more features of the embodiments of the present disclosure, such as "substantially", "about", and "approximately", should be understood to mean that the condition or characteristic is defined within an acceptable tolerance for the operation of the embodiments for their intended application.

[0022] Furthermore, it should be understood that the spatial descriptions used herein (e.g., "above", "below", "upper", "left", "right", "lower", "top", "bottom", "vertical", "horizontal", etc.) are for illustrative purposes only, and the actual implementation of the structures and drawings described herein may be spatially arranged in any orientation or manner. Additionally, the drawings may not be provided to scale, and the orientation or organization of the elements of the drawings may be different in the embodiments.

[0023] Numerous exemplary embodiments are described below. Note that any section / subsection headings provided herein are not intended to be limiting. Embodiments are described throughout this document, and any type of embodiment may be included under any section / subsection. Additionally, the embodiments disclosed in any section / subsection may be combined with any other embodiments described in the same section / subsection and / or different section / subsections in any way.

[0024] Section II below describes an example embodiment of a cardinality estimation feedback loop in query processing. Section III below describes an example computing device embodiment that can be used to implement the features of the embodiments described herein. Section IV describes other examples and advantages, and Section V provides some concluding remarks.

[0025] II. Example Embodiment of a Cardinality Estimation Feedback Loop in Query Processing

[0026] In accordance with embodiments herein, a method for a cardinality estimation feedback loop in query processing for a database (e.g., a relational database) is performed by systems and devices. A query can be executed using or based on an estimated cardinality and a corresponding query plan, and the query is monitored by a query host that executes the query. A query monitor is used to generate event signals during and upon completion of query execution to capture and / or generate an event signal that includes a token of the actual data cardinality of the data being queried, runtime statistics of the query and / or the query host, and query parameters in the query plan being used. The event signal is transmitted to a feedback optimizer, which can be hosted separately from the query host, where a signal router routes the event signal to an appropriate signal analyzer. Information in the event signal from the monitor is then analyzed, and the analysis result is passed to a feedback manager of the feedback optimizer to generate a change recommendation feedback, which is used as guidance or preemption guidance for the optimization of subsequent executions of the query or similar queries executed by a query optimizer of the query host. The feedback optimizer can provide a change recommendation to the query host, and the change recommendation can be stored at the query host.

[0027] Subsequent queries received by the query host can be monitored to identify, via a user interface (UI), the same or similar queries to which the change recommendation can be applied, either automatically or as an optional option. That is, the change recommendation can be applied to subsequent query plans so that an efficient execution considering the actual cardinality can be performed. Then, during and upon completion of execution, the query monitor observes these subsequent queries, and the feedback loop can be repeated to further refine the query plan. Queries can be semantically similar to another query, in whole or in part, based on semantic equivalence, etc., as described in additional detail herein.

[0028] In the embodiments herein, cardinality estimation (CE) feedback improves cardinality estimation itself, e.g., but not limited to, by analyzing profile data of past query executions and heuristically finding corrections that allow the cardinality estimator to work more optimally for a given workload. This can take the form of additional query hints, creation of additional statistics, and / or other changes determined or deemed necessary. The profile data for a query can include estimated and actual cardinality values for each node in the query plan. Selecting a query plan to handle the characteristics of a relational database (e.g., data correlation, memory authorization, join type, index, inclusion type, interleaved optimization for table-valued functions (TVFs), and / or deferred compilation of runtime objects such as table variables) affects the efficiency of query execution, which can be directly affected by CE. By enabling lightweight profiling in the query host (e.g., by way of example and not limitation, SQL ), profile data can be collected for each query with minimal overhead and impact to query execution for use in analysis in the optimization service.

[0029] If executed within the query engine, the task of analyzing profile data for CE feedback may, by its nature, consume processor and memory resources, thus competing with the query execution workload and potentially increasing the operating cost of similar workloads. To avoid such stress, the CE feedback analysis task can be performed by the optimization service as a separately hosted service. Thus, the embodiments described herein provide an infrastructure that unifies such feedback analysis for query and non-query feedback analysis through the same or equivalent external optimization service. However, the embodiments herein are not limited to a permanently hosted optimization service, and by extension of the described embodiments, a feedback loop that generates and processes signals entirely within the query engine is also envisioned herein.

[0030] When a query is initially submitted for execution against a database, it first goes through a normal query execution cycle. According to an embodiment, query profile data is collected along with additional standard profile data. At the end of query execution, the query profile data and / or the standard profile data is submitted to the optimization service for analysis and potentially for feedback. When feedback is determined, the optimization service can provide such feedback in the form of query plan / model hints, etc., to take CE estimation into account. Alternative feedback mechanisms may include but are not limited to creating new filter statistics or join hints, updating existing statistics to reflect new data distributions, etc.

[0031] Regarding the data stream, through the embodiments in this document, the query engine / host may be largely unaware of the existence of the optimization service. To this end, the optimization service is configured to analyze signals from the query optimizer of the query server host or event signals (e.g., XEvents messages), and apply feedback in the form of hints for query plans, models, parameters, etc., database settings, new statistics, other database / server artifacts, etc., as described herein.

[0032] The communication connection between the optimization service and the query server host can be initiated by the optimization service (based on the above principles) and continue as long as both the optimization service and the query service host are available for communication. According to an embodiment, for the communication between the optimization service and the query service host, there may be two persistent connections - an event stream and a connection for recommendations, feedback, hints, etc. For example, this connection can be the standard Tabular Data Stream (TDS) application layer protocol.

[0033] Generally, for the data persistence of feedback, recommendations, hints, etc., the query server host can store query hints (including query parameter, plan, and / or model changes) locally or remotely via configuration changes for the query engine. Such "change recommendations" can also be recorded in some form when they arrive from the optimization service so that they can be monitored and rolled back via the query server host as needed. These change recommendations and associated logging can also persist in such a way that events such as query server restarts, backup / restore cycles, etc. continue to provide the same recommendation behavior, regardless of whether the optimization service itself is connected.

[0034] The optimization service can also include an option to store historical information in a local or remote data store (e.g., intermediate storage) for later analysis. The data that can be stored here can be limited to metadata - that is, embodiments can stipulate that user data, whether in its original form, query plan, statistics, etc., may not be stored to ensure the integrity of user data and user privacy. Embodiments also take into account that this intermediate storage can be unaffected by reset events, etc., unless, for example, the associated database is migrated to another address.

[0035] The optimization service can also be configured to provide means for migrating intermediate data from one service database to another. For example, the monitored database data can be stored in a separate associated database, and the associated database can be backed up / restored. Embodiments also provide the ability to notify the optimization service that the monitored database has been migrated to a new location, thereby remapping the intermediate data to the new location without a backup / restore cycle.

[0036] The embodiments for optimizing services in this document are applicable to any type of query host / engine and can be implemented for server-based and / or cloud-based query engine instances, and can include the implementation of (multiple) optimization services across local and / or cloud settings for multiple query host / engine instances, where the instances belong to different types of query hosts / engines, so as to be able to learn from a wider workload and apply feedback to a wider workload. The embodiments also include the ability to utilize other cloud-based services, such as machine learning (ML), etc.

[0037] Accordingly, the cardinality estimation feedback loop in query processing provides refinement of query execution while minimizing the overhead during query execution. The described embodiments provide a system configured to collect, store, analyze, react to, and recommend model changes that occur during the compilation and execution of a query, so that the system can react to and adapt to specific compilation and runtime statistics to improve the current or subsequent execution of the same or similar queries against a database (e.g., a relational database).

[0038] These and other embodiments will be described in further detail below and in subsequent chapters and subsections.

[0039] Systems, devices, and apparatuses can be configured in various ways to perform their functions for the cardinality estimation feedback loop in query processing for a database such as a relational database. For example, Figure 1 is a block diagram of a networked system 100 according to an embodiment. According to an embodiment, the system 100 is configured to enable a cardinality estimation feedback loop in query processing. As Figure 1 shown, the system 100 includes an optimization service host 102, (multiple) client devices 114, and a query host 104. In an embodiment, the optimization service host 102, the query host 104, and (multiple) client devices 114 can communicate with each other via a network 112. It should be noted that in various embodiments, there can be various numbers of host devices, client devices, and / or ML hosts. Additionally, according to an embodiment, Figure 1 any combination of the components shown can exist in the system 100.

[0040] As described above, the optimization service host 102, (multiple) client devices 114, and the query host 104 are communicatively coupled via the network 112. The network 112 can include any type of communication link connecting computing devices and servers, such as but not limited to the Internet, wired or wireless networks and their parts, point-to-point connections, local area networks, enterprise networks, etc. In some embodiments, for example, for traditional records, in addition to or instead of using a network, data is transferred between (multiple) client devices 114, the query host 104, and / or the optimization service host 102 on a physical storage medium.

[0041] The query host 104 may include one or more server computers or computing devices, which may include one or more distributed or "cloud-based" servers. In an embodiment, the query host 104 may be associated with, or be part of, a cloud-based service platform, such as that from Microsoft Corporation of Redmond, Washington In some embodiments, the query host 104 may include local servers. Various systems / devices such as the optimization service host 102 and / or client devices such as client devices 114 may be configured to provide data and information related to CE estimation and query execution / processing, including queries and CE feedback, to the query host 104 via the network 112. The query host 104 may be configured to execute queries provided from client devices 114 via the network 112 to monitor runtime statistics, determine the cardinality of query data, monitor query parameters, etc. during the execution of the queries, and provide such information to the optimization service host 102. As shown, the query host 104 includes event signal generators 110, which may be configured to generate information and / or event signals provided to the optimization service host 102 to perform the feedback operations described herein. More details regarding event signal generation and query execution monitoring are provided below.

[0042] It should be noted that, as described herein, embodiments of the query host 104 are applicable to any type of system where, for example, queries are received via a network for execution against databases (including data sets). One example mentioned above is that the query host 104 is a "cloud" implementation, application, or service in a network architecture / platform. A cloud platform may include a set of networked computing resources, including servers, routers, etc., which are configurable, shareable, provide data security, and are accessible via a network such as the Internet. For entities accessing applications / services via the network, cloud applications / services such as those for machine learning may run on these computing resources, typically on top of an operating system running on the resources. The cloud platform may support multi-tenancy, where software based on the cloud platform serves multiple tenants, each tenant including one or more users sharing common access rights to the software services of the cloud platform. Additionally, the cloud platform may support a hypervisor implemented as hardware, software, and / or firmware that runs virtual machines (emulated computer systems, including operating systems) for the tenants. The hypervisor provides a virtual operating platform for the tenants.

[0043] System 100 also includes a database (DB) storage device 118 that stores one or more databases or data sets against which query host 104 executes queries. In various embodiments, DB storage device 118 may be communicatively coupled to query host 104 via network 112, may be part of query host 104 as shown, may be an external storage system of query host 104, or may be a cloud storage system.

[0044] (Multiple) client devices 114 may be any type or combination of computing devices or systems, including terminals, personal computers, laptop computers, tablet devices, smart phones, personal digital assistants, telephones, etc., including internal / external storage devices that may be used to generate and / or provide queries for execution by query host 104. In an embodiment, (multiple) client devices 114 may be used by various types of users, such as administrators, support staff agents, customers, clients, etc., to run queries against databases. (Multiple) client devices 114 may include one or more UIs that may be stored and executed by them or may be provided from query host 104. Such UIs are described in more detail herein.

[0045] Optimization service host 102 may include one or more server computers or computing devices, which may include one or more distributed or “cloud-based” servers as described above. Optimization service host 102 may include a feedback optimizer 108 that is configured to route event signals to one or more analyzers for feedback determination (e.g., generation and provision of changed recommendations), as described in further detail herein. In an embodiment, optimization service host 102 may be remote from query host 104 or may be part of query host 104. Optimization service host 102 may also be configured to communicate with query host 104 via other connections as an alternative or supplement to network 112.

[0046] System 100 may include a storage device shown as data store 106, which may be a stand-alone storage system, and / or may be associated with the optimization service host 102 either internally or externally. In an embodiment, data store 106 may be communicatively coupled to other systems and / or devices via network 112. That is, data store 106 may be any type of storage device or array of devices, and although shown communicatively coupled to the optimization service host 102, may be a network storage device accessible via network 112. In addition to or in place of the illustrated embodiment, additional instances of data store 106 may be included. Data store 106 may be an intermediate feedback storage device and may be configured to store different types of data / information, such as query information 116, including but not limited to metadata related to queries, query processing / execution data, query plan analysis, etc., as described herein.

[0047] As described herein, cardinality estimation (CE) is a phase in query optimization and compilation that involves predicting how many rows of data a query operator tree may process. CE is used by a query optimizer associated with a query processor / engine to generate an optimal or optimized query execution plan, and when the cardinality estimate is accurate, the query optimizer produces an appropriate plan. However, when the row estimate deviates significantly from the actual number of rows, this can lead to query performance issues.

[0048] The CE feedback embodiments herein automatically learn and apply optimal CE assumptions to both repeatable queries and singleton queries. The query processor / engine is able to select an optimized tuning combination for a query plan based on query runtime history. Given that a very small percentage of compiled queries with incorrect cardinality estimates and thus misselected associated query parameters can result in a disproportionately large percentage of processor and system resource usage, the embodiments herein provide increased system efficiency as well as appropriate resource use and allocation.

[0049] Host devices such as optimization service host 102 and / or query host 104 may be configured in various ways for a cardinality estimation feedback loop in query processing. For example, now referring to Figure 2 , according to an example embodiment, a block diagram of system 200 is shown for a cardinality estimation feedback loop in query processing of a database (e.g., a relational database). System 200 may be an Figure 1 embodiment of system 100. System 200 is described below.

[0050] System 200 includes a computing device 202, which may be an Figure 1 embodiment of optimization service host 102, and computing device 218 may be an Figure 1Example of query host 104, each computing device can be any type of server or computing device, including "cloud" implementations, as mentioned elsewhere herein, or otherwise known. As Figure 2 shown, computing device 202 and computing device 218 can each respectively include one or more of (a plurality of) processors ("processors") 204 and one or more of (a plurality of) processors ("processors") 220, one or more memories in memory and / or other physical storage devices ("memory") 206, and one or more memories in memory and / or other physical storage devices ("memory") 222, and one or more network interfaces ("network interfaces") 207 and one or more network interfaces ("network interfaces") 224. Computing device 202 can include a feedback optimizer 208, which can be configured to analyze query information and provide change recommendations via feedback, and computing device 218 can include a query manager 228, which can be configured to implement and / or provide change recommendations for query execution, execute queries, and monitor / generate query statistics and information for use by feedback optimizer 208.

[0051] System 200 can also include additional components (not shown for simplicity and illustrative clarity), including but not limited to components and sub-components of other devices and / or systems herein, and those described below with respect to Figure 10 such as operating systems and the like.

[0052] Processor 204 / processor 220 and memory 206 / memory 222 can be any type of (a plurality of) processor circuits and memories described herein and / or understood by those skilled in the relevant art(s) who would benefit from this disclosure. Processor 204 / processor 220 and memory 206 / memory 222 can each respectively include one or more processors or memories, different types of processors or memories (e.g., caches for query processing), remote processors or memories, and / or distributed processors or memories. Processor 204 / processor 220 can be a multi-core processor configured to execute more than one processing thread simultaneously. Processor 204 / processor 220 can include circuitry configured to execute computer program instructions, such as but not limited to embodiments of feedback optimizer 208 and / or query manager 218, which can be implemented as computer program instructions for a cardinality estimation feedback loop in query processing for a database, as described herein.

[0053] In an embodiment, memory 206 / memory 222 can include Figure 1data storage 106 and can be configured to store such computer program instructions / code, as well as store other information and data described in the present disclosure, including but not limited to query information 216 (which can be an Figure 1 embodiment of query information 116 of Figure 1 ), such as queries, query statistics, information about query processing / execution, query plan analysis, metadata, etc. In an embodiment, the memory 222 may include Figure 1 the DB storage device 118 of Figure 1 , or the computing device 202 may otherwise (internally or externally) utilize the DB storage device 118.

[0054] The network interface 207 / network interface 224 can be any type or number of wired and / or wireless network adapters, modems, etc., which are configured to enable the system 200 (including the computing device 202 and the computing device 218) to communicate with other devices and / or systems via a network (shown as connection 238), such as communication between the computing device 202 and the computing device 218, and communication between the system and the computing device and other systems / devices used in the network described herein via a network such as the network 112 described above with respect to Figure 1 description (e.g., (multiple) client devices 114 and / or data storage 106).

[0055] The computing device 218 of the system 200 may also include one or more UIs (UIs) 226 and a query store 236. In an embodiment, the query store 236 may be part of the memory 222 and is configured to store currently executed queries and previously executed queries, as well as query plans for executing such queries. In an embodiment, the query store 236 may store one or more CE models used by the query processor 230 to estimate the cardinality for execution according to the query plan. The UI 226 is configured to display change recommendations to the user. For example, the change recommendations may be selectable options to enable selectable options for rolling back the implemented change recommendations, and to enable or disable the execution of feedback.

[0056] The feedback optimizer 208 of the computing device 202 includes multiple components for performing the functions and operations described herein for the cardinality estimation feedback loop in query processing. For example, the feedback optimizer 208 may be configured to analyze query information and provide change recommendations to the query manager 228 via feedback. As shown, the feedback optimizer 208 includes a signal router 210, a query plan signal analyzer 212, and a feedback manager 214.

[0057] The signal router 210 is configured to route signals, such as event signals received from the query manager 228, to the appropriate analyzers of the query plan signal analyzer 212. The query plan signal analyzer 212 is configured to analyze the runtime statistics and other query information of the event signals and provide analysis results associated with the query data cardinality to the feedback manager 214, and then the feedback manager 214 is configured to determine change recommendations for the query parameters based on the cardinality of the data and the performance of the query execution. In an embodiment, the change recommendations can be applied to the same query or similar queries for their subsequent execution. Additionally, the change recommendation options determined by the feedback manager 214 can be selected to be provided via a feedback signal based on a probability analysis.

[0058] The query manager 228 of the computing device 218 includes multiple components for performing the functions and operations described herein for the cardinality estimation feedback loop in query processing. For example, the query manager 228 can be configured to implement and / or make available change recommendations for query execution, execute queries, and monitor / generate query statistics and information for use by the feedback optimizer 208. The query manager 228 includes a query processor 230, a query signal generator 232, and one or more engines / query monitors (monitors) 234. In some implementations, the monitor 234 can include a portion of the query signal generator 232, and vice versa.

[0059] In an embodiment, portions of the query manager 228 can be executed at or communicate with the client device(s) 114 such that the entries of the query can be monitored by the monitor 234 and change recommendations can be provided to the user via the UI 226 before query execution initialization.

[0060] The query processor 230 is configured to execute a query against the database according to the query plan and the estimated data cardinality, and can be software and / or hardware used in conjunction with the processor 220. The query signal generator 232 is configured to generate event signals with runtime statistics for query execution. As described above, the event signals are provided to the optimization host, such as the computing device 202 including the feedback optimizer 208.

[0061] Monitor 234 may include one or more monitors for databases, query engines, query execution, and / or received change recommendations. When a query is executed, the monitors in Monitor 234 for databases, query engines, and query execution may monitor runtime performance and operations to provide information to query signal generator 232. Monitor 234 may also include monitors to observe queries input to computing device 218 and query manager 228 to determine whether a previously executed query for which change recommendations were generated or other queries similar to the previously executed query are received. In such a case, the same change recommendations may be applied to execute and / or display the same change recommendations to the user. The query store 236 described above may also be configured to store received change recommendations.

[0062] Although shown separately for clarity, in embodiments, one or more components of feedback optimizer 208 and / or query manager 228 may be combined together and / or as part of other components of system 200. In some embodiments, fewer than Figure 2 all of the components of feedback optimizer 208 and / or query manager 228 shown may be included. In a software implementation, one or more components of feedback optimizer 208 and / or query manager 228 may be stored separately in memory 206 and / or memory 222, and may be executed separately by processor 204 and / or 220.

[0063] As noted above for Figure 1 and Figure 2 the embodiments herein provide a cardinality estimation feedback loop in query processing. Figure 1 System 100 of Figure 2 and Figure 3 and 4 System 200 of Figure 3 Each may be configured to perform such functions and operations. For example, Figure 4 and Figure 2 will now be described. Figure 1 Flowchart 300 according to an example embodiment is shown in Figure 2 and flowchart 400 according to an example embodiment is shown in

[0064] Flowchart 300 begins at step 302. In step 302, an event signal is received from a query host that executes a query against a database according to a query plan generated by the query host, and the event signal includes runtime statistics of the query. For example, signal router 210 of feedback optimizer 208 may be configured to receive an event signal from query signal generator 232 of query manager 228 in the query host (e.g., computing device 218). The event signal may be generated based on the query plan and the estimated cardinality of the data being queried thereby, and based on the query executed against the database of DB storage device 118 by query manager 228. The event signal may include runtime statistics of the query being executed at the query host and may be generated / provided as an XEvent signal / message.

[0065] In step 304, selected event signals from the event signal are provided to a query plan signal analyzer. For example, signal router 210 may be configured to provide the received event signal to an appropriate analyzer of feedback optimizer 208, such as query plan signal analyzer 212, to analyze the information in the event signal. Signal router 210 may be configured to determine a suitable analyzer for event signal routing based on the information included in the event signal, including but not limited to identifiers of analyzers, monitors, and / or signal generators, etc. In an embodiment, in addition to runtime statistics, the event signal may also include the query, query parameters, the actual cardinality of the query data, the estimated cardinality used by the query plan, etc. or their markers.

[0066] In step 306, the actual cardinality of the data queried in the database and at least one query parameter of the model for the query are determined via analysis of the runtime statistics, and the at least one query parameter is associated with the estimated cardinality for the model. For example, query plan signal analyzer 212 may be configured to determine the actual cardinality of the data queried and the query parameters of the query plan or model. That is, query plan signal analyzer 212 may analyze the runtime statistics provided in the (multiple) event signals described in steps 302 and 304. According to an embodiment, the query parameters may be based on the query plan / model and may include but not limited to data correlation, memory authorization, connection type, index, inclusion type, interleaved optimization for table-valued functions, delayed compilation of runtime objects such as table variables, etc., and may be determined based on information related to the runtime statistics. In addition to the other information described in step 304, the actual cardinality of the data may be provided in the event signal or may be determined based on runtime statistics including markers such as unique data access.

[0067] In step 308, a change recommendation for at least one query parameter is determined based at least on a difference between an estimated cardinality and an actual cardinality. For example, the feedback manager 214 may be configured to generate a change recommendation for a query parameter. In an embodiment, the difference between the estimated cardinality for a query plan and the actual cardinality of the data being queried determines what changes to the query parameter should be recommended and the extent of such changes to optimize the query processor 230 (i.e., the query engine). As an example scenario, when the estimated cardinality is low and partially relevant is assumed, making an independent relevance determination on a data column in a query database table with a relatively high cardinality may cause the feedback manager to recommend changing the query predicate used in the query plan. As described above, the feedback manager 214 may also be configured to generate a change recommendation based at least on other information provided in the event signal.

[0068] In step 310, a marker of the change recommendation is provided to the query host in a feedback signal. For example, the feedback manager 214 may be configured to provide the change recommendation from step 308 to the query manager 228 of the computing device 218 (as the query host) via the network interface 207, e.g., via TDS signaling. Consider that in an embodiment, the feedback may include zero or more change recommendations for a given query analysis and optimization determination.

[0069] Embodiments herein also provide for the maintenance and / or processing of ML (machine learning) models and model training data that may be used to perform the techniques described herein.

[0070] Now also referring to Figure 4 , flowchart 400 begins at step 402.

[0071] In step 402, information is stored in a data storage system, the information including one or more of the following: a query, at least one query parameter, an actual cardinality, an estimated cardinality, runtime statistics, an event signal, or a change recommendation. For example, as described above, the feedback manager 214 may be configured to receive information in the event signal and store such data as query information 116 in an intermediate storage device (e.g., data store 106) for later use in determining a change recommendation. Similarly, in addition to the analysis results, the query plan signal analyzer 212 may be configured to store any type of information received from the event signal as query information 116 in the intermediate storage device. In some embodiments, the stored data may be limited to metadata (e.g., table, column, and statistic names, but not including user data, query plans, or actual statistics in their original form). Step 402 may be performed simultaneously, partially simultaneously, or after any of steps 306, 308, and / or 310 of flowchart 300 described above.

[0072] In step 404, the information is retrieved to determine subsequent change recommendations. For example, the information stored in step 402 can be retrieved later by the feedback manager 214 to make a determination for change recommendations (e.g., in a subsequent execution of step 308) or for alternative analysis for query processing.

[0073] Now referring Figure 5 , according to an example embodiment, a block diagram of a system 500 for a cardinality estimation feedback loop in query processing is shown. System 500 is described in accordance with Figure 1 system 100, Figure 2 system 200, flowchart 300, and flowchart 400. System 500 is illustrated with respect to the query plan signal analyzer 212 and the feedback manager 214, and can be an embodiment of system 200.

[0074] Similar to that described above in flowchart 300, the query plan signal analyzer 212 receives an event signal 502 from the query manager 228 and / or the query signal generator 232. According to an embodiment, the query plan signal analyzer 212 analyzes runtime statistics and other information therein from the event signal 502 to determine analysis result information. The analysis results can include, but are not limited to, cardinality information 504, correlation information 506, and / or status information 508.

[0075] The cardinality information 504 can include the actual cardinality, the estimated cardinality for the query plan, the difference between the estimated cardinality and the actual cardinality, etc. The correlation information 506 can include an indication of the correlation of the data columns for the query in the database, including but not limited to independent (i.e., no or little) correlation, partial correlation, or full correlation. The status information 508 can include the status information of the query statement before and after a change to the query parameters based on a change recommendation, the status information of a temporary disabling of the feedback signal due to oscillation of the cardinality estimation, etc.

[0076] Although not shown for the sake of brevity and illustrative clarity, additional information provided with or determined from the event signal 502 can include: the query, the query plan, memory authorizations, connection types, index settings, enabling or disabling of connection types, forced join order, forced cardinality estimation, correlation types, inclusion types, interleaved optimization for table-valued functions, deferred compilation of runtime objects such as table variables, etc. In an embodiment, the query plan signal analyzer 212 can store some or all of the above data and information, including the analysis results, in the data memory 106.

[0077] Analysis results such as cardinality information 504, correlation information 506, status information 508, etc. can be provided by the query plan signal analyzer 212 to the feedback manager 214 via signal 512. Additionally, in an embodiment, the feedback manager 214 can receive previous query information 510 from the data store 106. The feedback manager 214 is configured to generate one or more change recommendations, such as change recommendation 512, based on the received analysis results and / or previous query information 510. The change recommendation 512 is then provided to the query host, such as the computing device 218 and the query manager 228, via the feedback signal 516.

[0078] Turning now to Figure 6 , according to an example embodiment, a flowchart 600 for a cardinality estimation feedback loop in query processing is shown. Figure 1 The system 100 and Figure 2 The system 200 of Figure 2 Each can be configured to perform the functions and operations according to flowchart 600. In an embodiment, Figure 3 The query manager 228 of the computing device 218 (query host) in Figure 1 The system 100 and Figure 2 The system 200 of

[0079] In step 602, at least one event signal is generated, which is provided to the optimization host for performing a first query against the database according to a first query plan and a first estimated cardinality. The at least one event signal includes runtime statistics of the first query. For example, the query signal generator 232 of the system 200 can be configured to generate event signals as described herein. The event signal can be generated based on a query executed by the query processor 230 against a database such as the DB storage device 118 of the system 100. The query is executed according to the query plan determined by the query processor 230 and the estimation of the data cardinality. As described herein, the monitor 234 is configured to monitor aspects of query execution, and the query signal generator 232 can generate event signals from these aspects, which can be provided to the optimization host (e.g., the computing device 202) and the query feedback optimizer (e.g., the feedback optimizer 208).

[0080] In an embodiment, aspects of query execution may include runtime statistics that may be affected by or related to cardinality estimation, such as but not limited to actual processor usage and estimated processor usage, actual memory usage and estimated memory usage, actual data cardinality and estimated data cardinality, data correlation, status information, and the like. Runtime statistics may also include information from query execution related to query parameters of a query plan or model, e.g., memory grants, join types, index settings, inclusion types, interleaved optimization of table-valued functions, deferred compilation of runtime objects such as table variables, and the like.

[0081] In step 604, a feedback signal is received from an optimization host, the feedback signal having a change recommendation for at least one query parameter of a first query. For example, a feedback optimizer 208 of the optimization host (e.g., computing device 202) may provide a feedback signal having the (multiple) change recommendations as described above to a query manager 228 of the query host (e.g., computing device 218). The received change recommendation may indicate that there is no feedback generated / provided for the execution of the query of step 602, or may indicate that one or more hints or change recommendations for the execution of the query are available for consideration and / or implementation. The change recommendation may be associated with one or more query parameters used in the execution of the query.

[0082] In step 606, a second query plan for a second query is determined, the second query plan being received after the receipt of the feedback signal, the second query plan incorporating the change recommendation and being based on a second estimated cardinality. For example, a second query plan different from the first query plan of step 602 may be determined by a query processor 230. The second query plan includes changes or alterations with respect to the first query plan, i.e., based on the change recommendation and the second estimated cardinality. In an embodiment, the change recommendation is associated with the difference between the estimated cardinality and the actual cardinality of the first query executed in step 602, and thus the second estimated cardinality may be determined taking into account the actual cardinality.

[0083] The change recommendation may change query execution via the second query plan (e.g., query parameters for executing the second query) such that a previously used CE model is updated or changed. The change recommendation may change query execution via the second query plan (e.g., query parameters for executing the second query) based on data correlation such as independent correlation, partial correlation, or full correlation.

[0084] In step 608, the second query is executed according to the second query plan. For example, the second query may be executed by a query processor 230 using the second query plan from step 606. A monitor 234 is configured to monitor the execution of the second query similarly to that described above for step 602 and elsewhere herein.

[0085] In step 610, at least one other event signal is generated, the at least one other event signal including: runtime statistics of the second query and being provided to an optimization host for the second query. For example, the query signal generator 232 is configured to generate (a)n event signal based on system and execution monitoring performed by the monitor 234, the event signal representing runtime statistics for the execution of the second query. As in step 602, the generated event signal is provided to the optimization host (e.g., the computing device 202) and the query feedback optimizer (e.g., the feedback optimizer 208).

[0086] Thus, through the cardinality estimation feedback loop in query processing, optimization for query execution is achieved, for example, through change recommendations based on the impact of cardinality estimation, and the feedback loop can iterate and further optimize the execution of the same query and similar queries.

[0087] Figure 7 A flowchart 700 for a cardinality estimation feedback loop in query processing according to an example embodiment is shown. The flowchart 700 can be Figure 6 an embodiment of the flowchart 600 of. Based on the following description, other structural and operational examples will be apparent to those skilled in the relevant art. The flowchart 700 is described with respect to Figure 1 the system 100 and Figure 2 the system 200 as follows. The flowchart 700 begins at step 702.

[0088] In step 702, after the execution of the first query and / or the receipt of a change recommendation for the feedback signal, a second query is received. For example, as similarly described in step 606 of the flowchart 600 above, after the execution of the first query and / or the receipt of a change recommendation for the feedback signal in step 602 of the flowchart 600, a second query can be received for execution by a query host (e.g., the computing device 218) via the query manager 228 of the system 200, for example. The query can be received via the network interface 224 from a UI (e.g., the UI 226) through the network 112, the UI being provided to (a) client device(s) 114 through the network 112, or operated locally at the query host.

[0089] As described herein, change recommendations for query parameters for a given query can be stored in the query store 236 and later applied as well to optimize the execution of the same query and similar queries. That is, the optimization and improvement of the system efficiency of a query for executing a single query can be used for multiple other similar but not identical queries, thereby further increasing the optimization and improvement of the system efficiency with minimal additional overhead. For example, an accurate cardinality estimate associated with query execution allows for proper allocation of system memory and processing resources, and this can prevent under-allocation of resources (where query execution takes longer to run) as well as over-allocation of resources (where fewer queries can be executed at once). Additionally, an accurate cardinality estimate associated with query execution allows for more accurate and efficient modeling, thereby reducing the amount of processing and memory resources required to execute a given query. For example, when determining an accurate cardinality estimate, different join types, indexes, and / or inclusion types can be selected, which in turn reduces processing and memory resource usage and also results in proper resource allocation.

[0090] As an example and as noted herein, such minimal additional overhead can be the monitoring of the monitor 234 of the query manager 228, which is configured to observe incoming queries to the computing device 218 and the query manager 228 to determine whether a previously executed query for which it generated a change recommendation or other queries similar to the previously executed query are received to re-apply the change recommendation.

[0091] In step 704, it is determined that the second query is similar to the first query. For example, the monitor of the monitor 234 can perform step 704. If the queries match, then a later query can be determined to be the same as the previous query, which can be determined by the monitor 234. Similarly, the monitor 234 is configured to determine similar queries based on one or more of the same data tables for the queries, the same order of two or more tables, common or identical join predicates, common or identical search predicates, identical outputs in the same output, etc. Query entries can be monitored by the monitor 234 when the query is entered and, in some embodiments, can be received by the query manager 228 before determining that the received query is the same as or similar to a previous query. In the latter case, the similarity determination can be made before initializing the execution of the query in order to implement or provide one or more appropriate change recommendations to the user or selection.

[0092] In step 706, the change recommendation is provided via the user interface as an optional option for applying to the second query. For example, the change recommendation can be provided via the UI 226 as an optional option for executing the second query. Further details regarding Figure 8 providing query hints with respect to change recommendations and with respect to the UI are provided below.

[0093] In step 708, change recommendations are used to alter query execution based on data correlations including one or more of independent correlation, partial correlation, or full correlation. For example, as described herein, change recommendations provided in a feedback signal can be based on the cardinality of query data from a previous query that is the same or similar to a subsequent query received for execution. In an embodiment, such change recommendations provide a query execution plan change that accounts for data correlation assumptions related to the estimated cardinality for a particular type of query parameter or operator. When the estimated cardinality is incorrect, change recommendations related to the data correlation can be provided.

[0094] Now also referring Figure 8 , a block diagram of a system 800 having a user interface (UI) for leveraging a cardinality estimation feedback loop in query processing is shown according to an example embodiment Figure 8 of the system 800. Figure 8 may be Figure 2 an embodiment of the system 200 in Figure 7 , and shows the UI 226 of the system 200, as well as the query processor 230, the monitor 234, and the query store 236. The system 800 is described with respect to

[0095] the flowchart 700 of

[0096] It should be noted that a representation of the UI 226 can be provided to a client device, such as the client device(s) 114, for display to a user, as described herein, where data and selections made by the user via the UI 226 are communicated to the query manager 228 of the system 200.

[0097] It should also be noted that the fields shown for the UI 226 in the system 800 are exemplary and non-limiting in nature and are for illustrative purposes. Fewer or more fields are contemplated herein according to embodiments, and the illustrated fields can be combined, implemented, and / or arranged in any manner for the UI 226, as will be understood by those of skill in the relevant art(s) benefiting from this disclosure.

[0098] As described above, change recommendations can be provided via a feedback signal (e.g., signal 812) from the feedback manager 214 of the system 200. The received change recommendations can be stored by the query host (e.g., computing device 218) in the query store 236 as the (multiple) feedback / change recommendations 814. The change recommendations can be associated and / or indexed based on the queries associated with them, which can be tracked by the monitor 234 and / or the query store 236 (etc.). In an embodiment, a query identifier (ID) can be persisted along with different aspects of query execution, query signal generation, CE feedback processing, information persistence, etc.

[0099] The monitor 234 can include a feedback / change recommendation monitor (change monitor) 816, which is configured to monitor the query store 236 for receiving new change recommendations stored as the (multiple) feedback / change recommendations 814. The monitor 234 can also include a query input monitor 818, which is configured to monitor the query input of the field 802 and / or monitor the received queries for determining the receipt of the same or similar queries as the previously executed queries.

[0100] Regarding the UI 226, the user can input a query input via the field 802 and set specific query parameters via the field 804. When the query input monitor 818 determines that an incoming query is received, the incoming query can be referenced by the change monitor 816 or the input monitor 818 against the indexed queries in which the feedback / change recommendations 814 are stored, where the indexed queries are the same or similar to the previous queries for which the (multiple) feedback / change recommendations 814 were provided previously. The identification of the same or similar queries can thus cause the query store 236 to provide an appropriate feedback / change recommendation from the (multiple) feedback / change recommendations 814 as a query hint for display in the field 810 of the UI 226. The user can then select one or more of the displayed hints / change recommendations to be implemented by the query processor 230 when executing the incoming query. Thus, the query processor 230 can change the query plan for the incoming query based on the received change recommendations to account for the cardinality of the query data, as disclosed herein.

[0101] In some scenarios, such as, but not limited to, oscillations or significant variability in cardinality estimation, the user may select field 806 to roll back previously integrated change recommendations for a query, which may be represented as "implemented" etc. in field 810. That is, the query store 236 (and / or another component such as monitor 234) may track and / or store change recommendations implemented for a query such that if the changes made to the query do not improve query processing performance, the query execution can be reverted to a known query plan / model. In some embodiments, the rollback may be automatically performed by the query processor 230 based on the received change recommendations. It is expected that in some cases, user-authorized changes may not be automatically rolled back by the system and instead require user intervention. It is also expected that recently implemented change recommendations may be marked as "temporary" changes that can be used for rollback until these change recommendations can be marked as "stable".

[0102] Similarly, field 808 provides the user with the option to disable or enable feedback processing. In an embodiment, disabling or enabling feedback may be temporary, e.g., for performing a single query, or may remain in effect until changed by the user.

[0103] Figure 9 A flowchart 900 for a cardinality estimation feedback loop in query processing according to an example embodiment is shown. In an embodiment, the scrubbing manager 216 may operate according to flowchart 900. Figure 1 of system 100 and Figure 2 of system 200 may operate according to flowchart 900, which may provide additional details and embodiments of the above flowcharts and flowcharts. Based on the following description, other structural and operational examples will be apparent to those skilled in the (multiple) relevant arts. Flowchart 900 is described below and begins at step 902.

[0104] In step 902, the received query is initiated for execution. In an embodiment, the query may be received via the UI 226 and query execution is initiated to begin processing by the query processor 230. At step 904, the query processor 230 may be configured to determine whether an existing query plan for the received query is stored for reuse. If not, then at step 906, the query processor 230 compiles and / or stores a new query plan with an estimated cardinality based on the CE model.

[0105] If an existing query plan is stored at step 904, then in step 908, it is determined whether a CE model recommendation is stored for determining the cardinality estimation. For example, a feedback signal with change recommendations may be received and the change recommendations may be stored as (a) feedback / change recommendation(s) 814 in the query store 236, as Figure 8as shown and as described herein. If the CE model change is not recommended and / or not available, the existing query plan determined in step 904 can be used for query execution. If the CE model change is recommended and / or available, at step 912, the query processor 230 compiles and / or stores a new query plan with estimated cardinalities based on the changed CE model.

[0106] From any of steps 906, step 910, or step 912, flowchart 900 can proceed to step 914, where after the above initialization, the query is executed by the query processor 914. During the query execution at step 914, for generating runtime statistics by the query signal generator 232, the query execution can be monitored by one or more monitors 234 in the monitor 234 at step 916. An event signal with runtime statistics can be sent to the feedback optimizer 208, where at step 918, the feedback optimizer 208 heuristically determines whether feedback should be provided in the form of a change recommendation, as described herein. If no or no feedback is determined to be needed, flowchart 900 can proceed to step 920, where an indication of no change recommendation is provided to the query host, or alternatively, no action is taken (and flowchart 900 can return to step 902).

[0107] If the heuristics and analysis of the query plan signal analyzer 212 and / or the feedback manager 214 justify feedback generation, at step 922, the query plan signal analyzer 212 and / or the feedback manager 214 can store the CE model used and the statistics for the query in an intermediate storage device, e.g., as query information 116 in the data store 106, or as query information 216. From step 922, the feedback manager 214 can determine whether a change recommendation stored for feedback exists in the query information 116 in the data store 106 or in the query information 216. If not, flowchart 900 continues to step 926, where the feedback manager 214 determines whether a change recommendation will or can be generated. If not, the process proceeds to the above step 920, but if a change recommendation will be generated at step 926, the feedback manager 214 performs the generation and, at step 928, stores the feedback / change recommendation(s) in the intermediate storage device and / or provides it / them to the query host for storage in the query store 236.

[0108] From any of step 920 or step 928, the flowchart can continue back to step 902 to further iterate the cardinality estimation feedback loop to optimize query processing, as described herein. As described herein, flowchart 900 can also monitor received queries that are the same as or similar to a previous query at step 930.

[0109] III. Example Computing Device Embodiments

[0110] The embodiments described herein may be implemented in hardware, or in a combination of hardware with software and / or firmware. For example, the embodiments described herein may be implemented as computer program code / instructions configured to execute in one or more processors and stored in a computer-readable storage medium. Alternatively, the embodiments described herein may be implemented as hardware logic / circuitry.

[0111] As described herein, the described embodiments (including but not limited to Figure 1 system 100, Figure 2 system 200, Figure 5 system 500, and Figure 8 system 800, along with any of its components and / or its sub-components, and the flowcharts / flow diagrams (including portions thereof) described herein, and / or additional examples described herein) may be implemented in hardware or in hardware with any combination of software and / or firmware, including being implemented as computer program code configured to execute in one or more processors and stored in a computer-readable storage medium, or being implemented as hardware logic / circuitry, such as implemented together in a system-on-chip (SoC), a field-programmable gate array (FPGA), or an application-specific integrated circuit (ASIC). The SoC may include an integrated circuit chip that includes one or more of the following: a processor (e.g., a microcontroller, a microprocessor, a digital signal processor (DSP), etc.), a memory, one or more communication interfaces, and / or additional circuitry and / or embedded firmware to perform its functions.

[0112] The embodiments described herein may be implemented in one or more computing devices similar to mobile systems and / or computing devices in fixed or mobile computer embodiments, including one or more features of the mobile systems and / or computing devices described herein, as well as alternative features. The descriptions of the mobile systems and computing devices provided herein are for illustrative purposes and are not intended to be limiting. The embodiments may be implemented in other types of computer systems, as known to those of ordinary skill in the relevant art(s).

[0113] Figure 10Depicts an exemplary implementation of a computing device 1000 in which embodiments may be implemented. For example, the embodiments described herein may be implemented in one or more computing devices similar to the computing device 1000 in a fixed or mobile computer embodiment, including one or more features and / or alternative features of the computing device 1000. The description of the computing device 1000 provided herein is for illustrative purposes and is not intended to be limiting. As is known to those of ordinary skill in the relevant art(s), the embodiments may be implemented in other types of computer systems and / or game consoles, etc.

[0114] As Figure 10 shown, the computing device 1000 includes one or more processors (referred to as processor circuitry 1002), a system memory 1004, and a bus 1006 that couples various system components, including the system memory 1004, to the processor circuitry 1002. The processor circuitry 1002 is an electrical and / or optical circuit implemented as a central processing unit (CPU), microcontroller, microprocessor, and / or other physical hardware processor circuitry in one or more physical hardware circuit device elements and / or integrated circuit devices (semiconductor material chips or dies). The processor circuitry 1002 may execute program code stored in a computer-readable medium, such as program code of an operating system 1030, an application 1032, other programs 1034, etc. The bus 1006 represents one or more buses of multiple types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, and a processor or local bus using any of various bus architectures. The system memory 1004 includes a read-only memory (ROM) 1008 and a random access memory (RAM) 1010. A basic input / output system 1012 (BIOS) is stored in the ROM 1008.

[0115] The computing device 1000 also has one or more of the following drives: a hard disk drive 1014 for reading from and writing to a hard disk, a disk drive 1016 for reading from or writing to a removable disk 1018, and an optical disk drive 1020 for reading from or writing to a removable optical disk 1022 such as a CD ROM, a DVD ROM, or other optical medium. The hard disk drive 1014, the disk drive 1016, and the optical disk drive 1020 are connected to the bus 1006 through a hard disk drive interface 1024, a disk drive interface 1026, and an optical drive interface 1028, respectively. The drives and their associated computer-readable media provide non-volatile storage of computer-readable instructions, data structures, program modules, and other data for the computer. Although hard disks, removable disks, and removable optical disks are described, other types of hardware-based computer-readable storage media can also be used to store data, such as flash memory cards, digital video disks, RAM, ROM, and other hardware storage media.

[0116] Multiple program modules can be stored on the hard disk, disk, optical disk, ROM, or RAM. These programs include an operating system 1030, one or more application programs 1032, other programs 1034, and program data 1036. The application programs 1032 or other programs 1034 can include, for example, systems for implementing the embodiments described herein, such as but not limited to Figure 1 system 100, Figure 2 system 200, Figure 5 system 500, and Figure 8 system 800, along with any of its components and / or its sub-components, and the flowcharts / flow diagrams described herein (including parts thereof), and / or the additional examples described herein.

[0117] A user can input commands and information into the computing device 1000 through input devices such as a keyboard 1038 and a pointing device 1040. Other input devices (not shown) can include a microphone, a joystick, a gamepad, a satellite antenna, a scanner, a touch screen and / or a touchpad, a voice recognition system for receiving voice input, a gesture recognition system for receiving gesture input, etc. These and other input devices are typically connected to the processor circuit 1002 through a serial port interface 1042 coupled to the bus 1006, but can also be connected through other interfaces, such as a parallel port, a game port, or a Universal Serial Bus (USB).

[0118] The display screen 1044 is also connected to the bus 1006 via an interface (e.g., the video adapter 1046). The display screen 1044 can be external to the computing device 1000 or incorporated into the computing device 1000. The display screen 1044 can display information and can be a user interface for receiving user commands and / or other information (e.g., via touch, finger gestures, virtual keyboard, etc.). In addition to the display screen 1044, the computing device 1000 can include other peripheral output devices (not shown), such as speakers and printers.

[0119] The computing device 1000 is connected to a network 1048 (e.g., the Internet) via an adapter or network interface 1050, a modem 1052, or other means for establishing communication over a network. The modem 1052 (which can be internal or external) can be connected to the bus 1006 via the serial port interface 1042, as Figure 10 shown, or can be connected to the bus 1006 using another interface type (including a parallel interface).

[0120] As used herein, terms such as "computer program medium", "computer-readable medium", "computer-readable storage medium", and "computer-readable storage device" are used to refer to physical hardware media. Examples of such physical hardware media include hard disks associated with the hard disk drive 1014, removable disks 1018, removable optical disks 1022, other physical hardware media such as RAM, ROM, flash memory cards, digital video disks, zip disks, MEM, nanotechnology-based memory devices, and other types of physical / tangible hardware storage media (including Figure 10 the memory 1020). Such computer-readable media and / or storage media are different from (excluding) and do not overlap with communication media and propagated signals. Communication media embody computer-readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave. The term "modulated data signal" refers to a signal in which one or more of the characteristics of the signal are set or changed in a manner that encodes information in the signal. By way of example and not limitation, communication media include wireless media (such as acoustic, RF, infrared, and other wireless media) and wired media. Embodiments also relate to such communication media that are separate from and do not overlap with embodiments involving computer-readable storage media.

[0121] As described above, computer programs and modules (including application program 1032 and other programs 1034) may be stored on a hard disk, magnetic disk, optical disk, ROM, RAM, or other hardware storage medium. Such computer programs may also be received via network interface 1050, serial port interface 1042, or any other interface type. Such computer programs, when executed or loaded by an application, enable computing device 1000 to implement the features of the embodiments disclosed herein. Thus, such computer programs represent the controller of computing device 1000.

[0122] The embodiments also relate to a computer program product including computer code or instructions stored on any computer-readable medium or computer-readable storage medium. Such computer program products include hard disk drives, optical disk drives, memory device packages, portable storage sticks, memory cards, and other types of physical storage hardware.

[0123] IV. Other Examples and Advantages

[0124] As described, systems and devices embodying the techniques herein may be configured and enabled in various ways to perform their respective functions. In an embodiment, one or more steps or operations of any of the flowcharts and / or flow diagrams described herein may not be performed. Additionally, steps or operations may be performed that are additional to or in place of steps or operations in any of the flowcharts and / or flow diagrams described herein. Further, in an example, one or more operations of any of the flowcharts and / or flow diagrams described herein may be performed out of order, in an alternative order, or partially (or fully) concurrently with each other or with other operations.

[0125] The embodiments described herein provide increased memory and processor usage efficiency by optimizing query plans and cardinality estimation feedback for the CE model. The UI is also improved by allowing change recommendations to be presented and selected based on the cardinality of the CE model used to change query plans and / or query parameters, a feature that was not previously available for query execution.

[0126] Furthermore, the described embodiments do not exist in a software implementation of a cardinality estimation feedback loop for query processing. Traditional solutions only perform cardinality estimation for a particular query based on data distribution and query shape, lacking the ability to implement runtime statistics analysis and correlation feedback to optimize query plans based on the cardinality of query data, which is a major cost factor in relational databases for query processing.

[0127] It is also contemplated herein that CE feedback may be aggregated over a complete workload including more than one individual query.

[0128] Additionally, the embodiments herein do not significantly increase the system load and / or overhead of query execution relative to the query host. Thus, poorly planned and / or modeled queries with slow or long runtimes are not further degraded in performance by monitoring and signal generation (which allows for rapid release of utilized system resources), but are optimized in subsequent executions.

[0129] Accordingly, reactive use of cardinality estimation feedback from the current execution of a query is enabled to determine an appropriate model selection for the current query to be applied in subsequent executions of that (or a similar) query—and proactive use of the cardinality estimation model analysis is also provided to drive query processing decisions for semantically similar future queries.

[0130] The additional examples and embodiments described in this section may be applicable to the examples disclosed in any other section or subsection of this disclosure.

[0131] The embodiments in this specification provide systems, devices, and methods for a cardinality estimation feedback loop in query processing. For example, a system is described herein. The system may be configured and enabled in various ways for such a cardinality estimation feedback loop as described herein. The system includes a processing system that includes one or more processors and a memory configured to store program code to be executed by the processing system. The program code includes a signal router, a query plan signal analyzer, and a feedback manager. The signal router is configured to: receive an event signal from a query host that executes a query against a database according to a query plan generated by the query host, the event signal including runtime statistics of the query, and provide a selected one of the event signals in the event signal to the query plan signal analyzer. The query plan signal analyzer is configured to: determine an actual cardinality of data queried in the database and at least one query parameter of a model for the query via analysis of the runtime statistics, the at least one query parameter being associated with an estimated cardinality for the model. The feedback manager is configured to: determine a change recommendation for the at least one query parameter based at least on a difference between the estimated cardinality and the actual cardinality, and provide a flag of the change recommendation to the query host in a feedback signal.

[0132] In an embodiment of the system, the feedback manager is configured to store information in a data storage system, the information including one or more of the following: a query, at least one query parameter, an actual cardinality, an estimated cardinality, runtime statistics, an event signal, or a change recommendation; and retrieve the information to determine subsequent change recommendations.

[0133] In an embodiment of the system, the feedback manager is configured to: also determine a change recommendation based at least on previous query parameters of a previous query executed prior to the query.

[0134] In an embodiment of the system, the feedback manager is configured to: determine a change recommendation based at least on the relevance of the data being queried, the relevance of the data being queried including one or more of independent relevance, partial relevance, or full relevance.

[0135] In an embodiment of the system, the change recommendation includes information that changes the subsequent execution of the query and one or more similar queries, and relative to the query, one or more similar queries include at least one of the following: the same table, the same order of two or more tables, the same join predicate, the same search predicate, or one or more identical outputs.

[0136] In an embodiment of the system, the change recommendation for at least one query parameter includes: a rollback to a previous model used for the query, or a temporary disabling of the feedback signal.

[0137] In an embodiment of the system, at least one query parameter includes one or more of the following: memory authorization, connection type, enabling or disabling of the connection type, forced join order, forced cardinality estimation, relevance type, inclusion type, interleaved optimization for table-valued functions, or deferred compilation of runtime objects such as table variables.

[0138] In an embodiment of the system, the query plan signal analyzer is configured to determine status information, the status information including: the status information of the query statement before and after a change to at least one query parameter; or the status information of a temporary disabling of the feedback signal due to oscillation of the cardinality estimation. In this embodiment, the feedback manager is configured to determine a change recommendation based at least on the determined status information.

[0139] Also described herein is a computer-implemented method. The computer-implemented method can be used in a cardinality estimation feedback loop in query processing as described herein. The computer-implemented method includes receiving at least one event signal from a query host, the query host executing a query against a database according to a query plan generated by the query host, the at least one event signal including one or more runtime statistics of the query; and determining an actual cardinality of the data being queried in the database and at least one query parameter of a model used for the query via an analysis of the one or more runtime statistics, the at least one query parameter being associated with an estimated cardinality of the model. The computer-implemented method further includes generating a change recommendation for 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 change the subsequent execution of the query and one or more similar queries; and providing the change recommendation to the query host in a feedback signal.

[0140] In an embodiment of the computer-implemented method, the change recommendation is further generated based at least on previous query parameters of a previous query executed before the query.

[0141] In an embodiment of the computer-implemented method, a change recommendation is also generated based at least on the relevance of the data being queried, the relevance of the data being queried including one or more of independent relevance, partial relevance, or full relevance.

[0142] In an embodiment of the computer-implemented method, one or more similar queries include at least one of the following: the same table, the same order of two or more tables, the same join predicate, the same search predicate, or one or more identical outputs.

[0143] In an embodiment of the computer-implemented method, at least one event signal includes a message that includes: status information of a query statement before and after a change to at least one query parameter; or status information of a temporary disablement of a feedback signal due to oscillation of a cardinality estimate.

[0144] In an embodiment of the computer-implemented method, a change recommendation for at least one query parameter includes: a rollback to a previous model used for the query, or a temporary disablement of a feedback signal.

[0145] In an embodiment of the computer-implemented method, at least one query parameter includes one or more of the following: memory authorization, connection type, enabling or disabling of a connection type, forced join order, forced cardinality estimate, relevance type, index setting, inclusion type, interleaved optimization for a table-valued function, or deferred compilation of a runtime object such as a table variable.

[0146] A computer-readable storage medium having program instructions recorded thereon is also described, the program instructions configuring at least one processing device to perform a cardinality estimation feedback loop in query processing when executed by the at least one processing device. The at least one processing device is configured to generate at least one event signal, the at least one event signal being provided to an optimization host for performing a first query against a database according to a first query plan and a first estimated cardinality, the at least one event signal including runtime statistics of the first query; and receive a feedback signal from the optimization host, the feedback signal having a change recommendation for at least one query parameter of the first query. The at least one processing device is further configured to determine a second query plan, the second query plan being received after the receipt of the feedback signal, the second query plan incorporating the change recommendation and being based on a second estimated cardinality; execute a second query according to the second query plan; and generate at least one other event signal, the at least one other event signal including runtime statistics of the second query and being provided to the optimization host for the second query.

[0147] In an embodiment of the computer-readable storage medium, the program instructions configure at least one processing device to determine that a second query is similar to a first query and provide a change recommendation as an alternative option for application to the second query via a user interface.

[0148] In an embodiment of the computer-readable storage medium, based on one or more of the following, a second query is similar to a first query: the same table, the same order of two or more tables, the same join predicate, the same search predicate, or one or more identical outputs.

[0149] In an embodiment of the computer-readable storage medium, to generate at least one event signal or generate at least one other event signal, the program instructions configure at least one processing device to track the status information of a query statement before and after a change to query parameters, or track the status information of a temporary disablement of a feedback signal due to oscillation of cardinality estimation.

[0150] In an embodiment of the computer-readable storage medium, the change recommendation changes query execution based on data correlation, where the data correlation includes one or more of independent correlation, partial correlation, or complete correlation.

[0151] V. Conclusion

[0152] Although various embodiments of the disclosed subject matter have been described above, it should be understood that they are presented by way of example and not limitation. Those skilled in the relevant art(s) will understand that various changes may be made in form and detail without departing from the spirit and scope of the embodiments as defined in the appended claims. Accordingly, the breadth and scope of the disclosed subject matter should not be limited by any of the above-described exemplary embodiments, but should be defined only in accordance with the appended claims and their equivalents.

Claims

1. A system, comprising: a processing system including one or more processors; and a memory configured to store program code to be executed by the processing system, the program code including: a signal router, a query plan analyzer, and a feedback manager; the signal router being configured to: receive an event signal from a query host that executes a query against a database according to a query plan generated by the query host, the event signal including runtime statistics of the query; and provide a selected event signal from the event signals to the query plan signal analyzer; the query plan signal analyzer being configured to: determine, via analysis of the runtime statistics, an actual cardinality of data queried in the database and at least one query parameter of a model for the query, the at least one query parameter being associated with an estimated cardinality for the model; and determine status information for temporarily disabling a feedback signal, wherein the temporary disabling of the feedback signal is due to detected oscillation of cardinality estimation; and the feedback manager being configured to: determine a change recommendation for the at least one query parameter based at least on a difference between the estimated cardinality and the actual cardinality and at least on the status information; and provide a marker of the change recommendation to the query host in a feedback signal.

2. The system according to claim 1, wherein the feedback manager is configured to: store information in a data storage system, the information including one or more of: the query, the at least one query parameter, the actual cardinality, the estimated cardinality, the runtime statistics, the event signal, or the change recommendation; and retrieve the information to determine subsequent change recommendations.

3. The system according to claim 2, wherein the feedback manager is configured to: further determine the change recommendation based at least on previous query parameters of a previous query executed before the query.

4. The system according to claim 1, wherein the feedback manager is configured to: further determine the change recommendation based at least on a relevance of the data queried, the relevance of the data queried including one or more of independent relevance, partial relevance, or full relevance.

5. The system according to claim 1, wherein the change recommendation includes information for changing subsequent executions of the query and one or more similar queries; and wherein, relative to the query, the one or more similar queries include at least one of: the same table; the same order of two or more tables; the same join predicate; the same search predicate; or one or more same outputs.

6. The system according to claim 1, wherein the change recommendation for the at least one query parameter includes: a rollback to a previous model for the query, or a temporary disabling of the feedback signal.

7. The system according to claim 1, wherein the at least one query parameter includes one or more of the following: memory authorization, connection type, enabling or disabling of the connection type, forced connection order, forced cardinality estimation, correlation type, index setting, inclusion type, interleaved optimization for table-valued functions, or deferred compilation of runtime objects.

8. The system according to claim 1, wherein the query plan signal analyzer is further configured to determine the status information, the status information including: status information of the query statement before a change to the at least one query parameter, and status information of the query statement after the change to the at least one query parameter.

9. A computer-implemented method, including: receiving, by a signal router, an event signal from a query host that executes a query against a database according to a query plan generated by the query host, the event signal including runtime statistics of the query; and providing, by the signal router, a selected event signal from the event signals to a query plan signal analyzer; determining, by the query plan signal analyzer, an actual cardinality of data queried in the database and at least one query parameter of a model for the query via analysis of the runtime statistics, the at least one query parameter being associated with an estimated cardinality for the model; determining, by the query plan signal analyzer, status information for a temporary disabling of a feedback signal, wherein the temporary disabling of the feedback signal is due to detected oscillations in cardinality estimation; determining, by a feedback manager, a change recommendation for the at least one query parameter based at least on a difference between the estimated cardinality and the actual cardinality and at least on the status information; and providing, by the feedback manager, a flag of the change recommendation to the query host in a feedback signal.

10. The computer-implemented method according to claim 9, further including: storing, by the feedback manager, information in a data storage system, the information including one or more of the following: the query, the at least one query parameter, the actual cardinality, the estimated cardinality, the runtime statistics, the event signal, or the change recommendation; and retrieving, by the feedback manager, the information to determine subsequent change recommendations.

11. The computer-implemented method according to claim 9, further including: determining, by the feedback manager, that the change recommendation is further based at least on previous query parameters of a previous query executed before the query.

12. The computer-implemented method according to claim 9, further including: determining, by the feedback manager, that the change recommendation is further based at least on a correlation of the data queried, the correlation of the data queried including one or more of independent correlation, partial correlation, or complete correlation.

13. The computer-implemented method according to claim 9, wherein the change recommendation includes information for changing subsequent executions of the query and one or more similar queries; and wherein, relative to the query, the one or more similar queries include at least one of the following: Same table; Same order of two or more tables; Same join predicate; Same search predicate; or One or more identical outputs.

14. The computer-implemented method according to claim 9 wherein the at least one query parameter includes one or more of the following: memory authorization, connection type, enabling or disabling of the connection type, forced connection order, forced cardinality estimation, correlation type, index setting, inclusion type, interleaved optimization for table-valued functions, or deferred compilation of runtime objects.

15. The computer-implemented method according to claim 9, wherein the determination of the status information by the query plan signal analyzer further includes: determining the status information of the query statement before the change to the at least one query parameter and the status information of the query statement after the change to the at least one query parameter.

16. A computer-readable storage medium having program instructions recorded thereon, the program instructions when executed by at least one processing device configure the at least one processing device to perform a method, the method including: receiving, by a signal router, an event signal from a query host that executes a query against a database according to a query plan generated by the query host, the event signal including runtime statistics of the query; and providing, by the signal router, a selected event signal from the event signals to a query plan signal analyzer; determining, by the query plan signal analyzer via analysis of the runtime statistics, an actual cardinality of data queried in the database and at least one query parameter of a model for the query, the at least one query parameter being associated with an estimated cardinality of the model; determining, by the query plan signal analyzer, status information for a temporary disablement of a feedback signal, wherein the temporary disablement of the feedback signal is due to a detected oscillation in cardinality estimation; determining, by a feedback manager, a change recommendation for the at least one query parameter based at least on a difference between the estimated cardinality and the actual cardinality and at least on the status information; and providing, by the feedback manager, a marker of the change recommendation to the query host in a feedback signal.

17. The computer-readable storage medium according to claim 16, wherein the method further includes: storing, by the feedback manager, information in a data storage system, the information including one or more of the following: the query, the at least one query parameter, the actual cardinality, the estimated cardinality, the runtime statistics, the event signal, or the change recommendation; and retrieving, by the feedback manager, the information to determine subsequent change recommendations.

18. The computer-readable storage medium according to claim 17, wherein the method further includes: determining, by the feedback manager, that the change recommendation is further based at least on previous query parameters of a previous query executed before the query; or The change recommendation is determined by the feedback manager to be at least based on the relevance of the queried data, and the relevance of the queried data includes one or more of independent relevance, partial relevance, or complete relevance.

19. The computer-readable storage medium according to claim 16, wherein the change recommendation includes information for changing subsequent executions of the query and one or more similar queries; and wherein the one or more similar queries include at least one of the following with respect to the query: Same table; Same order of two or more tables; Same join predicate; Same search predicate; or One or more same outputs.

20. The computer-readable storage medium according to claim 16, wherein the at least one query parameter includes one or more of the following: memory authorization, connection type, enabling or disabling of the connection type, forced connection order, forced cardinality estimation, relevance type, index setting, inclusion type, interleaved optimization for table-valued functions, or deferred compilation of runtime objects.

Images