Database Expert Option for ASE

1
ASE107 How to Diagnose, Optimize and Manage
Database Performance w/ Sybase Database Expert
for ASE
Tim Graham Principal Systems Consultant Sybase tim
.graham_at_sybase.com Claudia Fernandez Product
Manager Quest Software Claudia.fernandez_at_quest.com
August 16, 2004
2
The Enterprise. Unwired.
3
The Enterprise. Unwired.
Industry and Cross Platform Solutions
Unwire People
Unwire Information
Manage Information

• Adaptive Server Enterprise
• Adaptive Server Anywhere
• Sybase IQ
• Dynamic Archive
• Dynamic ODS
• Replication Server
• OpenSwitch
• Mirror Activator
• PowerDesigner
• Connectivity Options
• EAServer
• Industry Warehouse Studio

• Unwired Accelerator
• Unwired Orchestrator
• Unwired Toolkit
• Enterprise Portal
• Real Time Data Services

• SQL Anywhere Studio
• M-Business Anywhere
• Pylon Family (Mobile Email)
• Mobile Sales
• XcelleNet Frontline Solutions
• PocketBuilder
• PowerBuilder Family
• AvantGo

Sybase Workspace
4
Agenda

• The Performance Challenge
• Introducing Sybase Database Expert for ASE
• Performance Assurance Solution
• Demonstrations
• Q A

5
The Performance Challenge

• Databases are dynamic
• Optimal application performance is elusive
• Performance certainty is not guaranteed

6
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

7
Database Expert Option for ASE

• Achieve performance certainty
• Reduce TCO
• Increase employee/end user productivity
• Maximize ROI of current IT resources

8
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
• Provides a methodology to achieve performance
  gains

9
What affects performance?
Hardware
Network
Database Changes
SQL Statements
10
Domino effect of performance
Application Performance
SQL Performance
Query Plan
11
Query Plan

• 1gt set showplan on
• 2gt set noexec on
• 3gt go
• 1gt SELECT
• 2gt FROM EMPLOYEE
• 3gt WHERE EMP_ID gt 73712
• 4gt go
• QUERY PLAN FOR STATEMENT 1 (at line 1).
• STEP 1
• The type of query is SELECT.
• FROM TABLE
• EMPLOYEE
• Nested iteration.
• Using Clustered Index.

12
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
13
How does ASE generate query plans?
dbcc traceon(3604, 302,310)

• Beginning selection of qualifying indexes for
  table 'EMPLOYEE',
• varno 0, objectid 151004588.
• The table (Allpages) has 18373 rows, 1030
  pages,
• Data Page Cluster Ratio 0.999990
• Table scan cost is 18373 rows, 1030 pages,
• using no data prefetch (size 2K I/O),
• in data cache 'default data cache' (cacheid 0)
  with LRU replacement
• Selecting best index for the SEARCH CLAUSE
• EMPLOYEE.EMP_ID gt 73712.000000
• No statistics available for EMP_ID,
• using the default range selectivity to estimate
  selectivity.
• Estimated selectivity for EMP_ID,
• selectivity 0.330000.

14
How does ASE generate query plans?
dbcc traceon(3604, 302,310)

• Estimating selectivity of index 'IDX_EMP_ID_AGE',
  indid 5
• scan selectivity 0.330000, filter selectivity
  0.330000
• 6063 rows, 521 pages, index height 2,
• Data Row Cluster Ratio 0.998328,
• Index Page Cluster Ratio 0.997763,
• Data Page Cluster Ratio 0.999990
• The best qualifying index is 'EMPLOYEE_1961957491
  ' (indid 1)
• costing 342 pages,
• with an estimate of 6063 rows to be returned
  per scan of the table,
• using no index prefetch (size 2K I/O) on
  non-leaf pages,
• in index cache 'default data cache' (cacheid
  0) with LRU replacement
• using no data prefetch (size 2K I/O),
• in data cache 'default data cache' (cacheid 0)
  with LRU replacement
• Search argument selectivity is 0.330000.

15
How does ASE generate query plans?
dbcc traceon(3604, 302,310)

• TOTAL PERMUTATIONS 1
• TOTAL PLANS CONSIDERED 2
• CACHE USED BY THIS PLAN
• CacheID 0 (2K) 342 (4K) 0 (8K) 0 (16K) 0
• FINAL PLAN (total cost 6840)
• varno0 (EMPLOYEE) indexid1 (EMPLOYEE_1961957491)
  
• path0x20F8ABF4 pathtypesclause
• methodNESTED ITERATION
• scanthreads1
• outerrows1 rows6063 joinsel1.000000
  scanpgs342
• data_prefetchNO data_iosize2 data_bufreplaceLRU
  
• scanlio342 scanpio342
• corder1

To see all the query plans considered use dbcc
traceon (317)
16
Understanding query plans
2 different equivalent SQL statements, same query
plan
Elapsed Time 0.080 s
Elapsed Time 0.080 s
17
2 Different SQLs 2 Different Plans
2 different equivalent SQL statements, 2
different query plans

• Elapsed Time 0.080 s

Elapsed Time 0.110 s
18
Understanding query plans
Same SQL. Index idx_empsal Without index in the
db
Drop index idx_empsal

• Elapsed Time 0.080 s

Elapsed Time 0.110 s
19
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,
  etc.)
• Others statistics, parallel processing, etc.

20
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
  degrade?
• What SQLs query plans will experience
  performance changes?

21
Sybase Database Expert for ASE

• 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
  degrade?
• What SQLs query plans will experience
  performance changes?
• If the performance will degrade, how can it be
  optimized and maintained?

22
Sybase Database Expert for ASE

• Option for Adaptive Server Enterprise
• Supports ASE 11.0 and up (including 12.5.2)
• Windows-based product
• Non intrusive, no server-side installation
• Designed for production and quality assurance
  environments
• Quest Software (formerly LECCOTECH) provides SQL
  Expert (development environments) and Database
  Expert (QA and production environments) as OEM
  products to Sybase

23
Achieving Optimal Performance
Analyze
Performance Optimization
PerformanceDiagnosis
Performance Assurance
Predict
Preempt
Performance Management
24
Sybase Database Expert for ASE
25
Database Expert Option for ASE
SQL Inspector SQL Monitor SQL Scanner Performance
Monitor
26
Performance Diagnostics
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
• Allows to identify the top N most costly SQL
  statements

27
Performance Diagnostics
SQL Inspector

• ASE monitoring tables used
• monSysStatement
• monSysSQLText
• monSysPlanText
• SQL statistics Captured

28
Performance Diagnostics
SQL Inspector
29
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
• This is an alternative to capture SQL for ASE
  versions below 12.5.0.3

30
Performance Diagnostics
SQL Monitor
31
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

32
Performance Diagnostics
SQL Scanner
33
Performance Diagnostics
SQL Scanner
34
Performance Diagnostics
Sybase Performance Monitor

• Uses ASE 12.5.0.3 monitoring tables
• MDA tables provide statistical snapshots of the
  state of ASE
• Displays ASE performance statistics by taking
  snapshots of the MDA tables, or monitoring the
  MDA tables.
• Graphically displays the performance statistics.
• Provides detailed statistics by allowing users to
  drill-down from the charts.

35
Performance Diagnostics
Sybase Performance Monitor
36
Performance Diagnostics
Sybase Performance Monitor
37
Database Expert Option for ASE
38
Performance Optimization
SQL Optimizer

• Many ways to write a SQL statement
• Small differences in coding SQL can have great
  performance implications
• Recursive SQL transformation engine
• Applies syntactical SQL transformations
• Generates alternative equivalent SQL syntax and
  unique query plans
• Benchmarks SQL to identify the most efficient
  alternative for a db environment

39
What is Recursive SQL Transformation

• Using 2 transformation rules

SELECT FROM A WHERE A.C1 IN (SELECT B.C1 FROM
B WHERE EXISTS (SELECT x FROM C
WHERE B.C2C.C2 ))
SELECT FROM A WHERE EXISTS (SELECT x FROM B
WHERE EXISTS (SELECT x FROM C
WHERE B.C2C.C2) WHERE A.C1B.C1)
EXISTS to IN
SELECT FROM A WHERE EXISTS (SELECT x FROM B
WHERE B.C2 IN (SELECT C.C2 FROM C)
AND A.C1B.C1)
IN to EXISTS
SELECT FROM A WHERE A.C1 IN (SELECT B.C1 FROM
B WHERE B.C2 IN (SELECT C.C2 FROM C))
SELECT FROM A WHERE A.C1 IN (SELECT B.C1 FROM
B WHERE EXISTS (SELECT x FROM C
WHERE B.C2C.C2 ))
SELECT FROM A WHERE EXISTS (SELECT x FROM B
WHERE B.C2 IN (SELECT C.C2 FROM C)
AND A.C1B.C1)
EXISTS to IN
SELECT FROM A WHERE A.C1 IN (SELECT B.C1 FROM
B WHERE EXISTS (SELECT x FROM C
WHERE B.C2C.C2))
SELECT FROM A WHERE A.C1 IN (SELECT B.C1 FROM
B WHERE B.C2 IN
(SELECT C.C2 FROM C))
IN to EXISTS
SELECT FROM A WHERE EXISTS (SELECT x FROM B
WHERE EXISTS (SELECT x FROM C
WHERE B.C2C.C2) AND
A.C1B.C1)
40
(No Transcript)
41
(No Transcript)
42

• Original SQL
• Elapsed Time 0.763 s

Fastest SQL Alternative Elapsed time 0.060 s
43
Performance Optimization
SQL Optimizer

• 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

44
What is an Abstract Query Plan?

• Captures query text and save an abstract plan for
  a query in the sysqueryplans system table
• For incoming SQL queries, the text is compared to
  stored query text, and if a match is found, the
  saved abstract plan is used to execute the query

45
Abstract Plans
ASE
Based on thedatabase statistics to generate a
query plan
Execution
SQL
Optimizer
Based on the saved abstract plan to generate a
query plan
46
Using Abstract Plan

• / create abstract plan group demo1 /
• sp_add_qpgroup demo1
• Go
• / save abstract plan to group demo1 /
• set plan dump demo1 on
• Go
• / execute SQL /
• Select from A, B
• / load plan from group demo1 /
• set plan load demo1 on
• Go
• Execution Plan / next time you re-execute the
  same SQL /
• QUERY PLAN FOR STATEMENT 1 (at line 1).
• Optimized using an Abstract Plan (ID
  1989579095).

47
Saving a Plan for a SQL

• / create abstract plan under group demo1 /
• / to use index on A.key2 /
• CREATE PLAN
• SELECT FROM A WHERE A.Key1 1 AND A.Key2 2
• ( i_scan Index_key2 A )
• ( prop A
• ( parallel 1 )
• ( prefetch 2 )
• ( lru )
• ) into demo1

48
(No Transcript)
49
(No Transcript)
50
Performance Optimization
Abstract Plan Manager
51
Advantages of using Abstract Plan
The query plan will not be affected by database
upgrades that affect the ASE optimizer new ASE
features Changes in tuning options (eg) parallel
degree, table partitioning and indexing Able to
tune SQL even if you dont have the source code
52
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

53
Symptoms of Poor Indexing
Index Advisor

• Table Scans
• First Optimize SQL to see if alternative SQL
  syntax can enable index access
• Lack of indexes
• Index is not selective enough. Use (INDEX) Force
  to analyze query plan.
• Too many indexes

54
Performance Optimization
Index Advisor
55
Performance Optimization
Index Advisor
56
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?

57
Sybase Database Expert for ASE
Performance Assurance
Index Impact Analyzer Configuration
Analyzer Migration Analyzer Unused Index Analyzer
Performance Management
Performance Optimization
Performance Diagnostics
Integrated productivity tools
58
Impact Analysis SQL Repository
SQL from Database objects, files, application
code. SQL Scanner
Captured SQL SQL Inspector SQL Monitor
SQL Repository
59
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

60
Performance Management
Index Impact Analyzer
Total estimated cost from query plans
Before
After index creation
Details on SQL cost and query plan structure
changes
61
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"

62
Performance Management
Configuration Analyzer
BEFORE sp_configure "enable sort-merge join and
JTC", 0
AFTER sp_configure "enable sort-merge join and
JTC", 1
63
Performance Management
Configuration Analyzer
Displays sp_configure values. Original values and
values changed during the analysis
Total cost after sp_configure change
Total cost with original sp_configure values
64
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

65
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
66
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
67
Migration Analyzer
Performance Management
Total cost in Target Database
Total cost in Original Database
68
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

69
Unused Index Analyzer
Performance Management
Analyzes query plans of SQL statements in the
SQL Repository to identify unused indexes
70
Sybase Database Expert for ASE
Performance Assurance
Performance Management
Performance Optimization
Object Extractor SQL Worksheet Database
Explorer Code Finder SQL Formatter
Performance Diagnostics
Integrated productivity tools
71
Sybase Database Expert for ASE
72
ASE Performance Tuning Options
73
Questions?

• Thank You