1 / 44

ASE122: Database Expert Option for ASE

ASE122: Database Expert Option for ASE. Make DBAs’ Lives Easier — Assuring Performance with the new option for ASE. Claudia Fernandez Technical Services Manager claudia@leccotech.com Tel: 415-901-7880 August 5, 2003. Agenda. The Performance Challenge

winter-decker
Download Presentation

ASE122: Database Expert Option for ASE

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ASE122: Database Expert Option for ASE Make DBAs’ Lives Easier — Assuring Performance with the new option for ASE Claudia FernandezTechnical Services Managerclaudia@leccotech.com Tel: 415-901-7880August 5, 2003

  2. Agenda • The Performance Challenge • Introducing The New Database Expert Option to ASE • Performance Assurance Solution • Q & A

  3. The Performance Challenge • Databases are dynamic • Optimal application performance is elusive • Performance certainty is not guaranteed

  4. Effects of Performance Issues • Low Return on Investment • Hardware • Software • High TCO (Total Cost of Ownership) • Reduced Productivity • Internal Employees • IT Staff • Low End User Satisfaction

  5. Introducing the New Option for ASE Database Expert Option for Adaptive Server Enterprise • Achieve performance certainty • Reduce TCO • Increase employee/end user productivity • Maximize ROI of current IT resources

  6. Database Expert: Performance Certainty Solution • Assures reliable database performance • Maximizes performance through SQL optimization, Abstract Plans and Indexing Strategy • Ensures successful ASE version upgrade • Stabilizes ASE and application performance • Predicts where performance problems will occur before deploying applications in production • Improves group productivity • Provides a methodology to achieve performance gains

  7. Indexes 60% 90% What affects performance? Hardware Network DatabaseChanges SQL Statements

  8. Domino effect of performance Application Performance SQL Performance Query Plan

  9. How does ASE generate query plans? Plan 1 Internally Rewrites & Generates Multiple Query plans Plan 2 SQL Cost Estimation Plan 3 Plan 1 cost=1000 Plan 2 cost=3000 Plan 3 cost=500

  10. Understanding query plans Set showplan on Elapsed Time: 0.080 s Elapsed Time: 0.110 s

  11. How to influence query plans? • SQL Optimization • Complex nature of SQL • SQL transformations • Use forces • Abstract Plans • Save and reuse query plans • Force query plan generation • Indexes • Provide more options to the ASE optimizer • Sp_configure changes (enable sort-merge and JTC) • Others: statistics, parallel processing, etc.

  12. Will the query plans change? When... • Migrating to a new ASE version • ASE 12.5.0.3 to ASE 12.5.1 • ASE 12.0 to ASE 12.5 • ASE 11.9.2 to ASE 12.0, etc • Deploying applications from development to production • Changing sp_configure parameters • Adding indexes If a query plan changes, then the performance may change... Will the performance be improved? Will it be degraded? What SQL’s query plans will experience performance changes?

  13. The Database Expert Option for ASE Total Performance Management Solution Provides an answer to all these questions: • What is the performance impact of database environment changes? • Will the query plans change? • Will the performance be improved? Will it be degraded? • What SQL’s query plans will experience performance changes? • If the performance will degrade, how can it be optimized and maintained?

  14. Database Expert Option for ASE • New product option for Adaptive Server Enterprise • Scheduled release date Q3 Y2003 • Supports ASE 11.9.2 and up (including 12.5.1) • Windows based product • Non intrusive, no server-side installation • Designed for production and quality assurance environments • LECCOTECH provides SQL Expert (development environments) and Database Expert (QA and production environments) as OEM products to Sybase

  15. Performance Assurance Performance Management Performance Optimization Performance Diagnostics Integrated productivity tools Database Expert Option for ASE

  16. Performance Assurance Performance Management Performance Optimization Performance Diagnostics Performance Diagnostics Integrated productivity tools Database Expert Option to ASE Visual SQL Inspector SQL Monitor SQL Scanner

  17. Performance Diagnostics Visual SQL Inspector • Gathers SQL performance statistics from ASE (12.5.0.3 & up) monitoring tables • Allows users to schedule monitoring tasks to capture performance statistics • Consolidates captured statistics in different data views • Displays charts to visualize overall resource consumption of a database over a period of time • Advanced filtering controls speed up the process of locating problematic SQL

  18. Performance Diagnostics Visual SQL Inspector • ASE monitoring tables used: • monSysStatement • monSysSQLText • monSysPlanText • SQL statistics Captured:

  19. Performance Diagnostics Visual SQL Inspector

  20. Performance Diagnostics SQL Monitor • Provides an alternative approach to capture running SQL statements • Supports ASE from 11.9.2 • Captures SQL statements through the Sybase Monitor Server • Requires the Sybase Monitor Server to be properly configured and running

  21. Performance Diagnostics SQL Monitor

  22. Performance Diagnostics SQL Scanner • Proactively identifies problematic SQL without running applications • Extracts SQL from ASE database objects (sp’s, views, etc), files, source code (PowerBuilder, etc) • Analyzes query plans for multiple SQL statements and categorizes them according to suspected levels of performance problems

  23. Performance Diagnostics SQL Scanner

  24. Performance Assurance Performance Management SQL Optimizer Abstract Plan Manager Index Advisor Performance Optimization Performance Diagnostics Integrated productivity tools Database Expert Option to ASE

  25. Performance Optimization SQL Optimizer • Many ways to write a SQL statement • Small differences in coding SQL can have great performance implications • AI-based SQL transformation generates every possible alternative and unique query plan • Benchmarks SQL to identify the most efficient alternative for a db environment

  26. Performance Optimization SQL Optimizer

  27. Performance Optimization Abstract Plan Manager • Abstract Plans: • Available in ASE version 12.0 and up • Query plans can be saved and edited as Abstract Plans • Force ASE to generate a query plan based on the saved Abstract Plan • Allows tuning SQL without source code changes • Solution for tuning in SQL in third party applications such as PeopleSoft

  28. Performance Optimization Abstract Plan Manager

  29. Performance Optimization Index Advisor • Proposes new index scenarios to improve the performance of a given SQL statement • Provides performance estimations for every index scenario to assist the user in selecting which index alternative to test, evaluate or implement • Benchmarks index alternatives to identify which alternative will yield the greatest performance gain for the SQL statement • Allows users to evaluate their own user-defined index scenarios

  30. Performance Optimization Index Advisor

  31. Performance Optimization Index Advisor

  32. Performance Optimization Index Advisor • What is the performance impact on other SQL statements if the recommended indexes are created? • Will the recommended indexes improve or degraded the overall performance?

  33. Database Expert Option to ASE Performance Assurance Index Impact Analyzer Configuration Analyzer Migration Analyzer Unused Index Analyzer Performance Management Performance Optimization Performance Diagnostics Integrated productivity tools

  34. Performance Management Index Impact Analyzer • Evaluates the effect of the creation of the indexes in the database system • Shows which SQL statements are impacted by the new indexes • Identifies the index alternative that yields the highest performance gain with the least impact on the database system

  35. Performance Management Index Impact Analyzer BEFORE AFTER

  36. Performance Management Configuration Analyzer • Analyzes the effect on SQL performance when changing ASE configuration parameters • Provides a GUI for the user to evaluate different sp_configure parameter values • SQL related configuration parameters • "cis cursor rows" • "enable sort-merge joins and JTC" • "global async prefetch limit" • "max async i/os per engine" • "max async i/os per server" • "max parallel degree" • "max scan parallel degree" • "memory per worker process" • "number of large i/o buffers" • "number of sort buffers" • "number of worker processes"

  37. Performance Management Configuration Analyzer BEFORE sp_configure "enable sort-merge join and JTC", 0 AFTER sp_configure "enable sort-merge join and JTC", 1

  38. Performance Management Migration Analyzer • Compares SQL performance changes between different database environment • Allows users to preempt performance degradation when performing database migrations, database upgrades and application rollouts • Integrates Abstract Plan Management to stabilize SQL performance

  39. Performance Management Migration Analyzer Development Database Production Database SQL Repository Master Plan Snapshot Scenario Diagnostics Predicts performance change before applications are migrated to the destination database (e.g. new ASE version, production, etc) Plan cost analysis Query plan changes Identifies SQL with plan changes

  40. Performance Management Migration Analyzer Abstract Plans ASE 12.0 ASE 12.5 SQL Repository Master Plan Snapshot Scenario Diagnostics If performance degradation is identified, performance can be managed through Abstract Plans

  41. Performance Management Unused Index Analyzer • Identifies unused indexes by analyzing query plans from SQL statements in applications • Reports unused indexes that can be deleted to free up space, improve speed of DML statements and decrease maintenance • Reports: • Tables that are referenced in the SQL statements • Indexes in each table that are used in the query plans, and the number of referenced SQL for each index • Indexes in each table that are not used in the query plans

  42. Database Expert Option to ASE Performance Assurance Performance Management Performance Optimization Object Extractor SQL Worksheet Database Explorer Code Finder SQL Formatter Performance Diagnostics Integrated productivity tools

  43. Database Expert Option to ASE

  44. Questions? Thanks. Claudia Fernandez claudia@leccotech.com www.leccotech.com

More Related