Paul McInerney, IBM, DB2 LUW product developer Shelley Perrior, HBC, Data warehouse team lead

1. Workload management in data warehouses: Methodology and case studiespresentation toInformation Resource Management Association of CanadaJanuary 2008 Paul McInerney, IBM, DB2 LUW product developer Shelley Perrior, HBC, Data warehouse team lead Sorina Faur, BMO Financial Group, Database Development Manager

2. Agenda • Purpose • Share lessons and experiences related to workload management in data warehouses • Agenda Items • WLM methodology • HBC experience • BMO experience • Panel discussion/Q&A

3. WLM methodology Paul McInerney IBM Canada paulmci@ca.ibm.com

4. IBM Toronto Lab Home of DB2 LUW (Linux, Unix, and Windows) product development … … and much else … WLM in data warehouses | Overview and case studies

5. User-Centered Design (UCD) team Hands-on usability testing, customer visits, design protoyping, web surveys, etc. WLM in data warehouses | Overview and case studies

6. Background on presenter and presentation • Presenter experience relevant to WLM • Development of DB2 workload manager (DB2 9.5) and Query Patroller (DB2 version 8) • Work with customers to optimize their use of WLM features • Publishing of best practices • Basis of this presentation Best Practices for WLM Control Configuration (a white paper; ~ 60 pages) WLM in data warehouses | Overview and case studies

7. A definition of WLM • Workload management • WLM is mostly concerned with processing user requests to maximize business value. That is, WLM functionality considers the business value of each user request and handles it accordingly. • Examples • Protect the system from “runaway” queries • Achieve a response time objective (SLA) for a critical transaction • Allocate resources among business groups • Lots more… • Run short queries with a more consistent elapsed time • Prevent particular users from monopolizing computing resources • Prevent peak workload times from overloading computing resources • Run costly queries at a scheduled time • Hold back resources for use in a future phase of system rollout • Allow emergency rush jobs to commandeer resources WLM in data warehouses | Overview and case studies

8. Aspects of WLM WLM in data warehouses | Overview and case studies

9. Major categories of WLM goals Business value: Optimize quality of service (usually measured by response time) by considering the business value of each work request. This category addresses WLM within the confines of the operating envelope set by protection management polices. Protection: Set an operating envelope that defines what types of requests are allowed to run and how much work is allowed to run concurrently. Business value goals • Resource sharing goals • Priority goals Protection goals • Protect from individual "bad" requests • Protect from overload WLM in data warehouses | Overview and case studies

10. WLM protection goals • Protect from individual "bad" requests • Conditions addressed & actions taken • Some user requests are “bad” • Stop such requests from starting or from proceeding or starve them • Examples & analogies • Query that scans a fact table due to a typo in the query • Gardening: weeds in a garden • User ignores policy to NOT run large query during “rush hour”. • Transportation: speeding car in a school zone -> speed bumps • Protect from overload • Conditions addressed & actions taken • Too much work can be submitted simultaneously; trying to process all requests concurrently can degrade system efficiency or stability • Queue excess requests or triage requests based on size or priority • Examples & analogies • Monday morning rush hour • Gardening: Limiting the # of flowers planned based on the garden area • Seasonal/holiday retail peaks • Queues for any service WLM in data warehouses | Overview and case studies

11. WLM business value goals • Resource sharing goals • Conditions addressed & actions taken • Multiple requestor categories (business groups, applications) use the warehouse. • Allocate resources to requestor categories (min, max, target share). • Examples & analogies • The marketing dept. pays for 20% of the system but submits 80% of the workload. • Gardening; Allocate finite garden space to flowers vs. vegetables • When Joe from marketing runs his monster queries, other requests suffer. • Admin assistant: Time share requests from multiple executives • Priority goals • Conditions addressed & actions taken • Some work requests are more important and urgent than others • Provide multiple levels of service, consider priority in resource allocation or queue management • Examples & analogies • Emergency user request • Gardening: Give your favorite plants the premium location in the garden. • Customer account balance lookup vs. weekly management report of customer balances • Post office: first class vs. third class WLM in data warehouses | Overview and case studies

12. Aspects of WLM - recap WLM in data warehouses | Overview and case studies

13. Types of WLM control mechanisms • Resource allocation & priority controls • CPU allocation (min, max, share) • Priority-based engine queues (e.g. I/O prefetcher) • Etc. • Individual user request controls • Predictive vs. reactive controls • Attributes to control • General: Overall cost, elapsed time, executed time • Specific request attributes: e.g., rows read, rows returned • Concurrency controls • Limit the amount of concurrent work activities • Across user work requests or within a single request • At various levels/abstractions: connections, statements executing, etc. • Queue items when requests exceed concurrency limit WLM in data warehouses | Overview and case studies

14. WLM work categories • Work source categories • application, e.g., Sales App vs. Business Objects • business group, e.g., Marketing Dept. vs. Finance Dept. • Work type categories • size: Large query vs. short-running query. vs. regular query • other characteristics WLM in data warehouses | Overview and case studies

15. Summary WLM in data warehouses | Overview and case studies