AUTOMATED VALIDATION OF DATABASE INDEX CREATION

• US 20160378634A1
• Filed: 06/26/2015
• Published: 12/29/2016
• Est. Priority Date: 06/26/2015
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Inventors: Jovanovic, Andrija, Jovandic, Isidora, Krakovic, Dejan, Acimovic, Milos, Nikolicic, Zeljko, Lotrean, Petar, Grbic, Miroslav, Maksimovic, Dorde, Stanojevic, Svetlana
• Granted Patent: US 10,061,678 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 11/3034 where the computing system component is a storage system, e.g. DASD based or network based digital input from or digital output to record carriers G06F3/06; digital recording or reproducing G11B20/18; for distributed storage of data in networks, e.g. transport arrangements for network file system [NFS], storage area networks [SAN] or network attached storage [NAS], H04L67/1097
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2272 Management thereof

Abstract

Automated validation of the creation of indices in an environment that include multiple and potential many databases, such as perhaps a cloud computing environment. A validation module validates index impact of a created index by using a validation data store that contains validation data originating from a database collection. Index impact may be estimated by evaluating validation data generated prior to and after the creation of the index to thereby determine whether the created index results in overall improved query performance on the database collection for those queries that target the newly indexed database entity. Such validation data need not even contain private data that was contained within the query itself, and might be, for instance, query performance data, or execution plans associated with the query, with private data redacted.

Claims

1. A computing system comprising:

a validation module that operates to automatically validate index creation on a database entity in an environment that includes multiple databases,

the validation module configured to automatically validate index impact of the created index by using a validation data store that contains validation data originating from a plurality of databases.

2. The system in accordance with claim 1, the validation data store comprising a database that contains aggregated validation data from the plurality of databases.

3. The system in accordance with claim 1, further comprising:

the validation data store.

4. The system in accordance with claim 1, further comprising:

at least one of the plurality of databases.

5. The system in accordance with claim 1, the plurality of databases at least partially contained within a cloud computing environment.

6. The system in accordance with claim 1, the validation module configured to automatically validate performance improvement by evaluating validation data generated prior to creation of the index, evaluating validation data generated after the creation of the index, and comparing the evaluations.

7. The system in accordance with claim 6, the validation module configured to recommend or decide to revert or keep the created index based on the comparison.

8. The system in accordance with claim 1, the validation data comprising query performance data.

9. The system in accordance with claim 1, the validation data comprising live workload data.

10. The system in accordance with claim 1, the validation data comprising metadata from queries issued against the plurality of databases.

11. The system in accordance with claim 1, the metadata comprising at least a portion of execution plans for performing queries against the database entity.

12. The system in accordance with claim 1, the validation data excluding private data from the queries issued against the plurality of databases.

13. The system in accordance with claim 1, the validation module configured to perform validation for indexes created on any of the plurality of databases.

14. The system in accordance with claim 1, the plurality of databases comprising at least a million databases.

15. A computer-implemented method for a validation module to automatically validating index creation on a database entity in an environment that includes multiple databases, the method comprising:

an act of the validation module determining that creation of an index is to be validated;

an act of the validation module communicating with a validation data store that contains validation data originating from a plurality of databases; and

an act of evaluating validation data from the validation data store that was generated before and after the creation of the index to estimate index impact; and

an act of recommending or performing reversion or keeping of the created index based on the estimated index impact.

16. The computer-implemented method in accordance with claim 15, the evaluated validation data comprising metadata of the queries made to the plurality of databases.

17. The method in accordance with claim 15, the method being performed for a plurality of index creations for indexes corresponding to a plurality of database entities of the plurality of databases.

18. A computing system comprising:

a validation module that operates to automatically validate index creation of an index of a database entity of a database using live workload data of the database entity, the live workload data including validation data generated prior to creation of the index and validation data generated after the creation of the index, the validation module configured to evaluate the validation data generated prior to and after creation of the index to thereby generated an index impact estimate.

19. The system in accordance with claim 18,

the validation module further configured to recommend a keep or revert decision with respect to the created index based on the index impact estimate.

20. The system in accordance with claim 18,

the validation module further configured to trigger a keep or revert decision with respect to the created index based on the index impact estimate.