Tune Workloads

Tune Workloads

Analyze and autonomously optimize workload definitions including classification rules, filters, priorities, and exception handling to ensure queries are properly managed according to business requirements.

🎯 New Autonomous Capabilities

This skill can now execute changes, not just recommend them!

With tdwm-mcp v1.5.0, this skill has evolved from advisory to autonomous:

• ✅ ADD classification criteria to existing workloads
• ✅ ADD sub-criteria for fine-grained targeting (FTSCAN, MINSTEPTIME, etc.)
• ✅ VALIDATE classification types and operators before execution
• ✅ USE TEMPLATES for common classification patterns
• ✅ VERIFY changes were applied correctly
• ✅ ACTIVATE rulesets to apply all changes

Instructions

When to Use This Skill

• Queries are being misclassified into wrong workloads
• Need to add classification rules to existing workload
• Workload SLAs are not being met due to classification issues
• After application changes or new deployments
• Regular workload management review and optimization
• Need to adjust classification criteria dynamically

Available MCP Tools

Monitoring & Analysis:

• list_active_WD - Review active workload definitions
• list_WDs - List all workloads (active and inactive)
• show_tdwm_summary - Analyze workload distribution
• show_tasm_statistics - Review TASM rule effectiveness
• show_tasm_even_history - See workload classification decisions
• show_query_band - Review query band usage
• list_rulesets - List all available rulesets

Configuration Management (NEW ✨):

• add_classification_to_rule - Add classification criteria to workload/filter/throttle
• add_subcriteria_to_target - Add sub-criteria (FTSCAN, MINSTEPTIME, etc.)
• activate_ruleset - Apply all pending changes

Available MCP Resources (NEW ✨)

Templates:

• tdwm://templates/filter - List all filter templates
• tdwm://template/filter/application-basic - Filter by application
• tdwm://template/filter/user-group - Filter by user/account
• tdwm://template/filter/query-complexity - Filter by complexity metrics

Reference Data:

• tdwm://reference/classification-types - All 31 classification types
• tdwm://reference/operators - Classification operators (I, O, IO)
• tdwm://reference/subcriteria-types - Sub-criteria types
• tdwm://reference/filter-actions - Filter actions (ACCEPT/REJECT)

Discovery:

• tdwm://rulesets - List all rulesets
• tdwm://system/active-ruleset - Get currently active ruleset
• tdwm://ruleset/{name}/filters - List filters in ruleset
• tdwm://ruleset/{name}/throttles - List throttles in ruleset
• tdwm://ruleset/{name}/pending-changes - Check pending changes

Workflows:

• tdwm://workflow/add-classification - Step-by-step classification addition guide

Step-by-Step Workflow

Phase 1: Discovery & Analysis (Read-Only)

1. Assess Current Workload Configuration

   • Get active ruleset: tdwm://system/active-ruleset
   • List all workloads: list_active_WD and list_WDs
   • Review workload distribution: show_tdwm_summary
   • Document workload hierarchy and priorities

2. Analyze Classification Accuracy

   • Use show_tdwm_summary to see query distribution
   • Check show_tasm_even_history for misclassification patterns
   • Identify queries landing in DEFAULT or wrong workloads
   • Review show_query_band to verify tagging is working

3. Review Existing Rules

   • List filters: tdwm://ruleset/{name}/filters
   • For each filter: tdwm://ruleset/{name}/filter/{filter_name}
   • List throttles: tdwm://ruleset/{name}/throttles
   • For each throttle: tdwm://ruleset/{name}/throttle/{throttle_name}
   • Check for gaps, overlaps, or conflicts in rule logic

4. Identify Classification Gaps

   • Queries not matching any classification rules
   • Missing criteria for new applications
   • Overly broad or narrow classification
   • Need for sub-criteria refinement

5. Calculate Optimal Classification

   • Based on application characteristics and query bands
   • Consider user groups and account names
   • Factor in query complexity metrics (if needed)
   • Determine priority relative to business requirements

Phase 2: Execute Changes (Autonomous - NEW!)

6. Select Approach: Template or Custom

   • Template-Based (Recommended):

     • Browse templates: tdwm://templates/filter
     • Select template matching use case
     • Read template: tdwm://template/filter/{id}
     • Customize parameters for your workload

   • Custom Classification:

     • Validate classification types: tdwm://reference/classification-types
     • Validate operators: tdwm://reference/operators
     • Design custom classification criteria

7. Add Classification to Rule

   • For existing workload/filter/throttle:

     Use: add_classification_to_rule(
       ruleset_name, rule_name, rule_type,
       classification_criteria
     )
     

   • Classification criteria structure:

     [{
       "description": "Human-readable description",
       "type": "APPL|USER|ACCT|...",  # From reference
       "value": "actual_value",
       "operator": "I|O|IO"  # Inclusion/Exclusion/Inconclusive
     }]
     

8. Add Sub-Criteria (if needed)

   • For fine-grained control, add sub-criteria:

     Use: add_subcriteria_to_target(
       ruleset_name, rule_name, target_type,
       subcriteria
     )
     

   • Common sub-criteria:

     • FTSCAN: "Y" - Full table scan queries
     • MINSTEPTIME: "300" - Queries running > 5 minutes
     • MINPARSERTIME: "10" - Queries with parsing time > 10 seconds

9. Activate Changes

   • Check pending changes: tdwm://ruleset/{name}/pending-changes
   • Activate ruleset: activate_ruleset(ruleset_name)

Phase 3: Verification (Autonomous - NEW!)

10. Verify Configuration

    • Read back filter/throttle: tdwm://ruleset/{name}/filter/{name}
    • Confirm classification was added
    • Confirm enabled state is correct

11. Monitor Impact

    • Wait 5-10 minutes for statistics to update
    • Check workload distribution: show_tdwm_summary
    • Verify queries now classified correctly
    • Monitor TASM events: show_tasm_even_history

12. Iterate if Needed

    • If still misclassifying: add more criteria
    • If too broad: add exclusion criteria (operator "O")
    • Use add_classification_to_rule for adjustments

Examples

Example 1: Fix ETL Misclassification (Autonomous)

Scenario: ETL queries ending up in DEFAULT workload instead of ETL workload

Discovery:

1. show_tdwm_summary → See 50% queries in DEFAULT (should be in ETL)
2. show_query_band → ETL sets query band 'APP=ETL_LOADER'
3. tdwm://system/active-ruleset → "Tactical"
4. tdwm://ruleset/Tactical/filter/ETL_FILTER → No APPL classification exists

Analysis:

• ETL workload exists but classification is incomplete
• ETL sets query band but filter doesn't check it
• Need to add APPL classification to ETL_FILTER

Execution (Autonomous):

1. Validate classification type:
   tdwm://reference/classification-types
   → Confirm "APPL" is valid classification type

2. Add APPL classification to ETL filter:
   add_classification_to_rule(
     ruleset_name="Tactical",
     rule_name="ETL_FILTER",
     rule_type="filter",
     classification_criteria=[{
       "description": "Match ETL application query band",
       "type": "APPL",
       "value": "ETL_LOADER",
       "operator": "I"  # Inclusion
     }]
   )

3. Check pending changes:
   tdwm://ruleset/Tactical/pending-changes
   → Confirm APPL classification queued

4. Activate:
   activate_ruleset(ruleset_name="Tactical")

5. Verify:
   tdwm://ruleset/Tactical/filter/ETL_FILTER
   → Confirm APPL classification present

6. Monitor (wait 10 minutes):
   show_tdwm_summary
   → ETL queries now in ETL workload (not DEFAULT)

Result: ETL queries properly classified, DEFAULT workload reduced by 50%

Example 2: Add User-Based Classification (Autonomous)

Scenario: Power BI users need dedicated workload but not all set query band

Discovery:

1. show_tdwm_summary → Power BI queries mixed across workloads
2. show_query_band → Only 60% of Power BI queries set query band
3. list_active_WD → POWERBI workload exists
4. tdwm://ruleset/Tactical/filter/POWERBI_FILTER → Only has APPL classification

Analysis:

• Some Power BI users don't set query band (legacy reports)
• Need to add USER classification as fallback
• Power BI users identified by username pattern "pbi_*"

Execution (Autonomous):

1. Validate classification type:
   tdwm://reference/classification-types
   → Confirm "USER" is valid

2. Add USER classification to existing filter:
   add_classification_to_rule(
     ruleset_name="Tactical",
     rule_name="POWERBI_FILTER",
     rule_type="filter",
     classification_criteria=[{
       "description": "Catch Power BI users without query band",
       "type": "USER",
       "value": "pbi_*",  # Pattern matching
       "operator": "I"
     }]
   )

3. Activate:
   activate_ruleset(ruleset_name="Tactical")

4. Verify:
   tdwm://ruleset/Tactical/filter/POWERBI_FILTER
   → Confirm both APPL and USER classifications present

5. Monitor:
   show_tdwm_summary
   → Power BI workload increased from 60% to 95% capture rate

Result: Power BI queries properly classified even without query band

Example 3: Limit Long-Running Analytics (Autonomous)

Scenario: Analytics queries running >10 minutes should be throttled

Discovery:

1. show_query_log → Analytics queries running 10-60 minutes
2. tdwm://ruleset/Tactical/throttle/ANALYTICS_LIMIT → Has APPL classification
3. show_trottle_statistics → Short analytics queries also being throttled

Analysis:

• Throttle catches all analytics queries
• Only long-running queries need throttling
• Need to add MINSTEPTIME sub-criteria (600 seconds = 10 minutes)

Execution (Autonomous):

1. Validate sub-criteria type:
   tdwm://reference/subcriteria-types
   → Confirm "MINSTEPTIME" is valid

2. Add MINSTEPTIME sub-criteria to throttle:
   add_subcriteria_to_target(
     ruleset_name="Tactical",
     throttle_name="ANALYTICS_LIMIT",
     target_type="APPL",
     subcriteria={
       "type": "MINSTEPTIME",
       "value": "600"  # 10 minutes in seconds
     }
   )

3. Activate:
   activate_ruleset(ruleset_name="Tactical")

4. Verify:
   tdwm://ruleset/Tactical/throttle/ANALYTICS_LIMIT
   → Confirm MINSTEPTIME sub-criteria present

5. Monitor:
   show_trottle_statistics
   → Only long-running analytics queries now throttled

Result: Short analytics queries run freely, only long queries throttled

Example 4: Template-Driven Application Filter (Autonomous)

Scenario: New ML application needs classification into ML workload

Discovery:

1. show_tdwm_summary → ML queries appearing in DEFAULT
2. show_query_band → ML app sets 'APP=ML_TRAINING'
3. list_active_WD → ML_WORKLOAD exists
4. tdwm://ruleset/Tactical/filters → No ML_FILTER exists yet

Analysis:

• Need to create new filter for ML workload
• Application sets query band (simple case)
• Can use template for quick creation

Execution (Template-Based):

1. Get template:
   tdwm://template/filter/application-basic
   → Shows APPL-based filter pattern

2. Create filter using create_filter_rule:
   create_filter_rule(
     ruleset_name="Tactical",
     filter_name="ML_FILTER",
     classification_criteria=[{
       "description": "ML Training Application",
       "type": "APPL",
       "value": "ML_TRAINING",
       "operator": "I"
     }],
     action_type="ACCEPT",
     workload_name="ML_WORKLOAD"
   )

3. Enable filter:
   enable_filter_rule(
     ruleset_name="Tactical",
     filter_name="ML_FILTER"
   )

4. Activate:
   activate_ruleset(ruleset_name="Tactical")

5. Verify:
   tdwm://ruleset/Tactical/filter/ML_FILTER
   → Confirm filter created with APPL classification

6. Monitor:
   show_tdwm_summary
   → ML queries now in ML_WORKLOAD

Result: ML application properly classified into dedicated workload

Example 5: Exclude Full Table Scans from Fast Track (Autonomous)

Scenario: Fast track workload getting slow queries due to full table scans

Discovery:

1. show_tdwm_summary → FAST_TRACK average response time increasing
2. show_query_log → Some FAST_TRACK queries doing full table scans
3. tdwm://ruleset/Tactical/filter/FAST_TRACK_FILTER → Has USER and APPL criteria

Analysis:

• Fast track should only handle indexed queries
• Full table scans should go to BATCH workload
• Need to add FTSCAN sub-criteria with exclusion

Execution (Autonomous):

1. Validate sub-criteria type:
   tdwm://reference/subcriteria-types
   → Confirm "FTSCAN" is valid

2. Add exclusion for full table scans:
   add_subcriteria_to_target(
     ruleset_name="Tactical",
     filter_name="FAST_TRACK_FILTER",
     target_type="USER",  # Apply to USER classification
     subcriteria={
       "type": "FTSCAN",
       "value": "N"  # Only queries NOT doing full scan
     }
   )

3. Activate:
   activate_ruleset(ruleset_name="Tactical")

4. Verify:
   tdwm://ruleset/Tactical/filter/FAST_TRACK_FILTER
   → Confirm FTSCAN sub-criteria present

5. Monitor:
   show_tdwm_summary
   → FAST_TRACK average response time improved
   → Full scan queries now in BATCH workload

Result: Fast track performance improved by excluding full scans

Best Practices

General Principles

• Start with broad classification (APPL, USER), refine with sub-criteria later
• Use query bands as primary classification method when possible
• Username/account filters are fallbacks when query bands unavailable
• Test classification changes during low-activity periods when possible
• Document business justification for each classification rule
• Monitor classification for 1-2 weeks after changes to verify effectiveness

Discovery Before Execution (NEW ✨)

• ALWAYS use tdwm://ruleset/{name}/filters to see existing filters before adding
• Check tdwm://ruleset/{name}/pending-changes before activating
• Verify with resource read after changes
• Understand current classification before adding new rules

Template Usage (NEW ✨)

• Use templates for common patterns (application-based, user-based)
• Customize template parameters for your specific needs
• Follow template best practices and examples

Execution Safety (NEW ✨)

• Validate classification types against tdwm://reference/classification-types
• Validate operators against tdwm://reference/operators
• Start with single classification, add more if needed
• Test in non-production first if possible
• Keep emergency rollback plan

Validation (NEW ✨)

• Always read back filter/throttle after adding classification
• Confirm classification appears in rule definition
• Monitor workload distribution after changes
• Check TASM events to verify classification working

Monitoring

• Query bands take precedence over username/account classification
• Operator "I" (Inclusion) matches the value
• Operator "O" (Exclusion) excludes the value
• Operator "IO" (Inconclusive) allows TASM to continue evaluation
• Sub-criteria refine the target (FTSCAN, MINSTEPTIME, etc.)
• Coordinate classification changes with application teams
• Regular classification reviews identify drift over time

Related Skills

• Use optimize-throttles skill for concurrency limit adjustments
• Use manage-workloads skill for creating new workloads from scratch
• Use emergency-response skill for crisis situations
• Use discover-configuration skill to inventory existing classifications