# US 2024/0004855 A1 · Framework for workload prediction and physical database design

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2024/0004855 A1
- Document ID: US20240004855A1
- Status: Published application
- Kind: A1
- Application number: 18/462,513
- Filed: 2023-09-07
- Published: 2024-01-04
- Earliest claimed: 2022-03-28
- Assignees: Sap SE
- Applicants: Sap SE
- Inventors: Michael BRENDLE; Norman MAY; Robert SCHULZE; Alexander BOEHM; Michael GROSSNIKLAUS; Guido MOERKOTTE
- Web page: https://patentgrep.com/patent/US20240004855A1

## CPC

- G06F 16/2282: {Tablespace storage structures; Management thereof}
- G06F 9/5083: {Techniques for rebalancing the load in a distributed system}
- G06F 11/3414: {Workload generation, e.g. scripts, playback}
- G06F 16/213: {with details for schema evolution support}
- G06F 16/256: {in federated or virtual databases}
- G06F 2209/5019: Workload prediction
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

According to some embodiments, methods and systems may be associated with a cloud computing environment. A workload prediction framework may receive observed workload information associated with a database in the cloud computing environment (e.g., a DataBase as a Service (“DBaaS”)). Based on the observed workload information, a Statement Arrival Rate (“SAR”) prediction may be generated. In addition, a host variable assignment prediction may be generated based on the observed workload information. The workload prediction framework may then use the SAR prediction and the host variable assignment prediction to automatically create a workload prediction for the database. A physical database design advisor (e.g., a table partitioning advisor) may receive the workload prediction and, responsive to the workload prediction, automatically generate a recommended physical layout for the database (e.g., using a cost model, the current physical layout, and an objective function).

## Claims

### Claim 1

A system associated with a cloud computing environment, comprising:
a workload prediction framework, including:
a computer processor, and
a computer memory storing instructions that, when executed by the computer processor, cause the workload prediction framework to:
receive observed workload information associated with a database in the cloud computing environment,
based on the observed workload information, generate a Statement Arrival Rate (“SAR”) classification via a condition-based SAR classifier,
generate a SAR prediction in accordance with the SAR classification,
based on the observed workload information, generate a host variable assignment prediction, and
use the SAR prediction and the host variable assignment prediction to automatically create a workload prediction for the database; and
a physical database design advisor, coupled to the workload prediction framework, to receive the workload prediction and, responsive to the workload prediction, automatically generate a recommended physical layout for the database.

### Claim 2 (depends on 1)

The system of claim 1, wherein the workload prediction uses a Pearson correlation between a series of discrete timestamps and a series of statement arrival frequencies, abbreviated as SAR(,qi).

### Claim 3 (depends on 2)

The system of claim 2, wherein the workload prediction is linear if the Pearson correlation coefficient ρ is greater or equal than a threshold φlin∈[0, 1]:
SAR(,qi) is linear⇔|ρ(SAR(,qi))|≥φlin.

### Claim 4 (depends on 2)

The system of claim 2, wherein the workload prediction is exponential if the Pearson correlation coefficient ρ between the series of discrete timestamps and the series of the logarithm of each statement arrival frequency is greater or equal than a threshold φexp∈[0, 1]:
SAR(,qi) is exponential⇔|ρ(log(SAR(,qi)))|φexp.

### Claim 5 (depends on 1)

The system of claim 1, wherein the workload prediction is reoccurring based on a computed Discrete Fourier Transform (“DFT”).

### Claim 6 (depends on 5)

The system of claim 5, wherein workload prediction is based on whether a sinusoid with an amplitude greater or equal than a threshold θ(, qi) is present in the DFT.

### Claim 7 (depends on 1)

The system of claim 1, wherein the workload prediction is static when observed SAR frequencies fluctuate only within limits around a mean statement arrival frequency μi.

### Claim 8 (depends on 7)

The system of claim 7, wherein the workload prediction is based on a Root Mean Square Error (“RMSE”) between the observed SAR frequencies and ui is less than or equal to a threshold φstatic multiplied by μi.

### Claim 9 (depends on 1)

The system of claim 1, wherein the workload prediction is based on a combination of multiple workload drift types.

### Claim 10 (depends on 1)

The system of claim 1, wherein the SAR prediction is generated by the following algorithm:
discretizing the observed workload,
detecting at least one workload drift type,
classifying multiple workload drift types, and
predicting a future SAR using an observed SAR and the classified workload drift.

### Claim 11 (depends on 1)

The system of claim 1, wherein the physical database design advisor is associated with at least one of: (i) a table partitioning advisor, (ii) an index advisor, (iii) a data compression advisor, (iv) a buffer pool size advisor, and (v) any other advisor that is based on access counters of a workload.

### Claim 12 (depends on 1)

The system of claim 1, wherein the physical database design advisor is associated with a table partitioning advisor that utilizes a cost model, a current physical layout, and an objective function.

### Claim 13 (depends on 12)

The system of claim 12, wherein the table partitioning advisor computes repartitioning costs on a per-column partition basis.

### Claim 14 (depends on 13)

The system of claim 13, wherein the table partitioning advisor proposes a future layout that minimizes a combined workload and repartitioning cost of the workload prediction.

### Claim 15

A computer-implemented method associated with a cloud computing environment, comprising:
receiving, by a computer processor of a workload prediction framework, observed workload information associated with a database in the cloud computing environment;
based on the observed workload information, generating a Statement Arrival Rate (“SAR”) classification via a condition-based SAR classifier;
generating a SAR prediction in accordance with the SAR classification;
based on the observed workload information, generating a host variable assignment prediction;
automatically creating a workload prediction for the database using the SAR prediction and the host variable assignment prediction; and
responsive to the workload prediction, automatically generating a recommended physical layout for the database by a physical database design advisor.

### Claim 16 (depends on 15)

The method of claim 15, wherein the workload prediction uses a Pearson correlation between a series of discrete timestamps and a series of statement arrival frequencies, abbreviated as SAR(, qi).

### Claim 17 (depends on 16)

The method of claim 16, wherein the workload prediction is linear if the Pearson correlation coefficient ρ is greater or equal than a threshold φlin∈[0, 1]:
SAR(,qi) is linear⇔|ρ(SAR(,qi))|≥φlin.

### Claim 18 (depends on 16)

The method of claim 16, wherein the workload prediction is exponential if the Pearson correlation coefficient ρ between the series of discrete timestamps and the series of the logarithm of each statement arrival frequency is greater or equal than a threshold φexp∈[0, 1]:
SAR(,qi) is exponential⇔|ρ(log(SAR(,qi)))|φexp.

### Claim 19

A non-transitory, computer-readable medium storing instructions, that, when executed by a processor, cause the processor to perform a method associated with a cloud computing environment, the method comprising:
receiving, by a computer processor of a workload prediction framework, observed workload information associated with a database in the cloud computing environment;
based on the observed workload information, generating a Statement Arrival Rate (“SAR”) classification via a condition-based SAR classifier;
generating a SAR prediction in accordance with the SAR classification;
based on the observed workload information, generating a host variable assignment prediction;
automatically creating a workload prediction for the database using the SAR prediction and the host variable assignment prediction; and
responsive to the workload prediction, automatically generating a recommended physical layout for the database by a physical database design advisor.

### Claim 20 (depends on 19)

The medium of claim 19, wherein the workload prediction is reoccurring based on a computed Discrete Fourier Transform (“DFT”).

### Claim 21 (depends on 20)

The medium of claim 20, wherein workload prediction is based on whether a sinusoid with an amplitude greater or equal than a threshold θ(, qi) is present in the DFT.

### Claim 22 (depends on 19)

The medium of claim 19, wherein the workload prediction is static when observed SAR frequencies fluctuate only within limits around a mean statement arrival frequency μi.

### Claim 23 (depends on 22)

The medium of claim 22, wherein the workload prediction is based on a Root Mean Square Error (“RMSE”) between the observed SAR frequencies and ui is less than or equal to a threshold φstatic multiplied by μi.
