In this example, one initial workload consumes the majority of the resources. A more granular breakdown of that workload is investigated. Long running outliers are noted in the analysis. The goal is to have these outliers classified into their own workload so that different workload management techniques are applied.
- Select . The Define DBQL Inputs dialog box appears.
- Complete the Define DBQL Inputs options. For more information, see Defining DBQL Inputs.
- In the Category box, select Account String, and click OK. The Unassigned requests report appears.
-
Right-click over each workload and select Auto-Generate Workloads.
The Candidate Workloads Report appears.
The CPU distribution of all workloads is 8% ADW-TACT and 92% ADW-DS. Workload WD-ADW-DS is selected for further analysis because it is consuming 92% of the total CPU.
-
Right-click WD-ADW-DS, and select Analyze Workload.
The Analyze Workload tab appears.
There are a total of five distinct query band names. The query bands can be viewed only if Queryband is selected from the Workload Correlation Parameter list. The five distinct query band names in this example are:- QueryBand (5)
- AggLevel (7)
- Function (5)
- Region (7)
- TopTierApp (3)
- Urgency (3)
There are several suitable analysis candidates available in this query band list, as denoted by the distinct value counts.
- QueryBand (5)
- For this example, select QueryBand as the correlation parameter.
-
Click
to load the query band names in the Queryband Filter drop-down list.
-
Select Function, then click Perform Analysis.
The Correlation Report and graph appear.
A total of five query band values display for the Queryband=Name function. Notice a possible distinction with Function=MIN, which included queries with greater time spent than any of the other queries.
- From the report, right-click the MIN row, and select Split to New Workload.
-
Name the new workload WD_ADW_Outliers.
This step is done to insure that unassigned clusters fall back into the original WD-ADW-DS workload classification.
The QueryBand Function=MIN is split to the new workload, ADW-Outliers. The remaining four functions are unassigned, falling back to ADW-DS if no other action is taken on them.
The Correlation/Distribution Reports and graph refresh with the remaining four unassigned query band values for the next add or split operation.
-
From the report, right-click WD_ADW_Outliers and select Analyze Workload to perform further drill-down analysis.
The Analyze Workload tab displays.
-
Select TopTierApp from the Queryband Filter list, and click Perform Analysis.
The report and graph display.
The longest running queries not only common to Function=MIN, but also QueryBand Name TopTierApp=BODSS.
-
From the report, right-click the BODSS row, and select Add ‘QueryBand’ clusters for deeper Analysis.
The BODSS value is added to the current analyzed workload, WD_ADW_Outliers.
The CPU distribution now has two unassigned TopTierApps relegated to the original WD_ADW_DS workload, rather than being part of the WD_ADW_Outliers workload.
Analyzing further on ADW_Outliers, notice that the distinct count for all the correlation parameters displays one (only one distinct value).
This means that all requests in this workload are coming from the same combination of ‘Who’ parameters (Account ADW_DS, QueryBand Name Function=MIN, and Queryband Name TopTier App=BODSS).
Only distribution parameters may be used deeper drill-down analysis. The Estimated Processing Time may be used as a distribution parameter, since the range for the current workload is wide (0.00–0158.00 seconds).
- From the Analyze Workload tab, select None from the Workload Correlation Parameter list.
- Select Estimated Processing Time from the Workload Distribution Parameter list.
-
Click Perform Analysis.
The report and graph appear.
The eight queries lie in the last bucket (bucket 10 with a range of 142.20–158.00) are long queries. However, another eight queries in the first bucket (bucket 1 with a range of 0.00–15.80) are short running queries, with a wide gap show in bucket 2 through bucket 9.
The goal from the start of this process is to isolate long running requests found within the ADW-DS workload, and apply different workload management to them. By adding the last bucket to the ADW_Outliers workload, the necessary workload definition is achieved to apply those different workload management techniques.
- Right-click Bucket 10, and select Add Estimated Processing Time clusters for deeper analysis.
-
Override the Min Estimated Processing Time to 30 seconds, and override the Max Estimated Processing Time to 999999 seconds (basically unlimited).
The very short running requests relegated back to the original WD-ADW-DS workload.
In summary, the workload classifications within this example result in WD-ADW-DS remain in its original state. To view, click Attributes under the WD-ADW-DS workload in the Candidate Workloads tree, then click on the Classification tab.
The workload WD-ADW-Outliers is created with a higher evaluation order than workload WD-ADW-DS. To view, click Attributes for WD-ADW-Outliers, then click the Classification tab.
For more information about evaluation order, see Evaluation Order of Candidate Workloads.