ASE122: Database Expert Option for ASE

1
ASE122 Database Expert Option for ASE
Make DBAs Lives Easier Assuring Performance
with the new option for ASE
Claudia FernandezTechnical Services
Managerclaudia_at_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
What affects performance?
Hardware
Network
Database Changes
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 cost1000
Plan 2 cost3000
Plan 3 cost500
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 SQLs 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 SQLs 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
Database Expert Option for ASE
16
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 (sps,
  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
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, performa
nce 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_at_leccotech.com
• www.leccotech.com