Automatic database troubleshooting

Patent metadata

Field	Value
Publication number	US-11003641-B2
Application number	US-201715817181-A
Country	US
Kind code	B2
Filing date	Nov 18, 2017
Priority date	Sep 22, 2017
Publication date	May 11, 2021
Grant date	May 11, 2021

Abstract

Database systems are subject to automatic troubleshooting. Query execution data for individual queries executed by a database system can be collected. The query execution data can be evaluated automatically in conjunction with one or more detection metrics to determine whether or not a potential performance issue exists. After detection of a potential issue, the query execution data can be analyzed further to determine one or more causes by matching query execution data to one or more patterns that encode candidate causes. Subsequently, a root cause amongst multiple causes is determined based on causal dependency. Corrective action associated with a root cause can also be determined and optionally applied automatically.

First claim

What is claimed is:

1. A system that facilitates database troubleshooting, comprising:
a processor coupled to a memory, the processor configured to execute the following computer executable components stored in the memory:
a detection component that detects a regression of performance of a database system automatically based on comparison of query execution data for one or more queries executed by the database system to previous execution data;
a cause component that analyzes the regression of performance by automatically matching the query execution data to a pattern of a plurality of patterns that encode candidate causes to determine one or more causes of the regression of performance;
a learning component that, responsive to the cause component not finding a match to at least one of the plurality of patterns, determines existing patterns in the plurality of patterns having a similarity to the query execution data; and
a corrective action component that:
automatically applies a first corrective action corresponding to a first existing pattern of the existing patterns and determines whether the first corrective action was successful at mitigating the regression of performance; and
automatically applies a second corrective action corresponding to a second existing pattern of the existing patterns responsive to determining that the first corrective action was unsuccessful and determines whether the second corrective action was successful at mitigating the regression of performance.

2. The system of claim 1 further comprises a collection component that collects the query execution data for the one or more queries executed by the database system.

3. The system of claim 2 , the collection component saves the query execution data to a query store accessible by the detection component and cause component.

4. The system of claim 1 , the detection component further detects the regression of performance by a comparison of query execution data related to at least one of query duration, timeout requests, execution wait time, or errored requests.

5. The system of claim 1 further comprises a cause refinement component that determines dependency between a set of causes and identifies the root cause based on the dependency.

6. The system of claim 5 , the cause component and refinement component are implemented as a rule-based expert system.

7. The system of claim 5 , the corrective action component further determines at least one of the first corrective action or the second corrective action based on the root cause.

8. The system of claim 7 , the corrective action component further automatically applies at least one of the first corrective action or the second corrective action to the database system.

9. A method performed by a database troubleshooting system comprising at least one processor coupled to a memory storing machine-executable instructions, which, when executed by the processor, controls the system to perform acts, comprising:
receiving query execution data for one or more individual queries executed by a database system;
detecting a regression of performance of the database system automatically based on the query execution data and comparison of the query execution data to previous query execution data;
analyzing the regression of performance by automatically matching the query execution data to one or more patterns of a plurality of patterns that encode candidate causes to determine one or more causes of the regression of performance;
responsive to not finding a match to at least one of the plurality of patterns, determining existing patterns in the plurality of patterns having a similarity to the query execution data;
automatically applying a first corrective action corresponding to a first existing pattern of the existing patterns and determining whether the first corrective action was successful at mitigating the regression of performance; and
automatically applying a second corrective action corresponding to a second existing pattern of the existing patterns responsive to determining that the first corrective action was unsuccessful and determining whether the second corrective action was successful at mitigating the regression of performance.

10. The method of claim 9 further comprises detecting the regression of performance by comparing query execution data related to at least one of query duration, timeout requests, execution wait time, or errored requests.

11. The method of claim 9 further comprises determining the one or more causes with patterns that encode candidate causes comprising at least one of resource limits reached, workload increase, memory pressure, or locking.

12. The method of claim 9 further comprises determining a causality chain among two or more of the determined causes.

13. The method of claim 12 further comprises determining a root cause of a performance issue from the causality chain.

14. The method of claim 13 further comprises classifying a performance issue as a system issue or application issue based on the root cause.

15. The method of claim 13 further comprises identifying at least one of the first corrective action or the second corrective action based on the root cause.

16. The method of claim 15 further comprises automatically applying at least one of the first corrective action or the second corrective action to the database system.

17. A computer-readable storage medium having instructions stored thereon that enable at least one processor to perform a method upon execution of the instructions, the method comprising:
receiving query execution data for one or more individual queries executed by a database system;
detecting a regression of performance of the database system automatically based on the query execution data and comparison of the query execution data to previous query execution data; and
analyzing the regression of performance by automatically matching the query execution data to one or more patterns of a plurality of patterns that encode candidate causes to determine one or more causes of the regression of performance;
responsive to not finding a match to at least one of the plurality of patterns, determining existing patterns in the plurality of patterns having a similarity to the query execution data;
automatically applying a first corrective action corresponding to a first existing pattern of the existing patterns and determining whether the first corrective action was successful at mitigating the regression of performance; and
automatically applying a second corrective action corresponding to a second existing pattern of the existing patterns responsive to determining that the first corrective action was unsuccessful and determining whether the second corrective action was successful at mitigating the regression of performance.

18. The computer-readable storage medium of claim 17 , the method further comprises:
determining a causality chain among two or more of the one or more determined causes; and
determining a root cause based on the causality chain.

19. The computer-readable storage medium of claim 18 , the method further comprises identifying at least one of the first corrective action or the second corrective action based on the root cause.

20. The computer-readable storage medium of claim 19 , the method further comprises automatically applying at least one of the first corrective action or the second corrective action to the database system.

Assignees

• Microsoft Technology Licensing Llc

Inventors

• Dundjerski Dejan
• Pantic Mladen
• Ilic Igor
• Lotrean Petar
• Lazic Stefan
• Cunningham Conor John
• Vasic Veljko
• Cukalovic Jovan
• Jovic Vukasin
• Petrovic Djordje

Classifications

• G06F16/245 Primary

  Query processing · CPC title

• G06F11/0793

  Remedial or corrective actions (recovery from an exception in an instruction pipeline G06F9/3861; by retry G06F11/1402; for recovering from a failure of a protocol instance or entity H04L69/40) · CPC title

• G06F11/0727

  in a storage system, e.g. in a DASD or network based storage system (drivers for digital recording or reproducing units G06F3/06; circuits for error detection or correction within digital recording or reproducing units 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 title

• G06F16/217 Primary

  Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409) · CPC title

• G06F11/0757

  by exceeding a time limit, i.e. time-out, e.g. watchdogs · CPC title