ase 111 ase system management developments l.
Skip this Video
Loading SlideShow in 5 Seconds..
ASE 111: ASE System Management Developments PowerPoint Presentation
Download Presentation
ASE 111: ASE System Management Developments

Loading in 2 Seconds...

play fullscreen
1 / 59

ASE 111: ASE System Management Developments - PowerPoint PPT Presentation

  • Uploaded on

ASE 111: ASE System Management Developments. Peter Dorfman Sr. Staff Software Engineer, Sybase, Inc. August 15-19, 2004. The Enterprise. Unwired. The Enterprise. Unwired. Industry and Cross Platform Solutions. Manage Information. Unwire Information. Unwire

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'ASE 111: ASE System Management Developments' - benjamin

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
ase 111 ase system management developments

ASE 111: ASE System Management Developments

Peter Dorfman

Sr. Staff Software Engineer, Sybase, Inc.

August 15-19, 2004

the enterprise unwired3
The Enterprise. Unwired.

Industry and Cross Platform Solutions







  • 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

  • Our Strategy
  • Enhancements and New System Management Tools
  • Improved Monitoring Tools
  • SQL Expert and Database Expert
  • DBXray Features and Enhancements
  • Your Questions
our strategy
Our Strategy
  • Reduce cost of ASE ownership through system management features
  • Constantly improve ease of use
  • Build tools to support ASE product features and operational requirements
  • Support system management vendors and partners in bringing new products to market
  • Provide tools for customers who develop management tools
enhancements and new products
Enhancements and New Products
  • Sybase Central ASE Plug-in
  • Migration Tool Enhancements
  • MDA Monitoring Tables
  • sp_sysmon, sp_monitor Enhancements
  • DBXray Enhancements and Support for ASE 12.0
  • Enhanced SQL Expert
  • New Database Expert Product
ase system management tools
ASE System Management Tools
  • Sybase Central ASE Plug-in
  • Monitor Server
  • Historical Server
  • Monitor Client Library
  • DDLGen
  • DBXray for Sybase
  • Migration Tool
  • Monitoring Tables
  • Job Scheduler
  • sp_sysmon
  • sp_monitor
  • Database Expert
  • SQL Expert
  • SQL Debugger
  • PowerTransfer
sybase central ase plug in
Sybase Central ASE Plug-in
  • Some Recent Enhancements
    • SQL command history
    • Table data viewer
    • Real Time Database management
    • Web Services configuration
    • Proxy database support
    • Transportable database support
    • Job Scheduler console
    • Context-sensitive help
    • Process monitoring enhancements
    • Server shutdown
sybase central ase plug in9
Sybase Central ASE Plug-in
  • Improvements Coming in ASE 15.0
    • Enterprise View with high level status
    • DBISQL integration
    • Automated server discovery
    • Remote errorlog access
    • Remote server startup
    • Graphical showplan
    • Improved navigation through task-based folder organization
    • Command scheduling
    • SQL preview and editing
    • Update statistics wizard
ase 15 0 dbisql is our universal query tool
ASE 15.0: DBISQL is Our Universal Query Tool
  • DBISQL query tool will work with
    • ASE
    • ASA
    • ASIQ
  • ASE 15.0 will include an enhanced version of DBISQL
  • Can be accessed from ASE Plug-in or run as a standalone application
ase 15 0 graphical showplan
ASE 15.0: Graphical Showplan
  • Introducing a graphical view of query plan
  • Available within DBISQL
  • Graphical tree view of query plan
  • Runtime statistics for each query plan element
  • May add recommendations for query and database tuning
ase 15 0 graphical showplan13
ASE 15.0: Graphical Showplan

Query Text

Execution Tree

Query Statistics

job scheduler
Job Scheduler
  • Enterprise ASE scheduling system
  • Introduced in ASE 12.5.1 release
  • Maintains
    • Job definitions
    • Schedule definitions
    • Scheduled jobs
    • Job status, output and history
  • Executes any SQL command or batch
  • Runs jobs on any ASE server in your enterprise
job scheduler architecture

Target Server B

Target Server A







c:\> sp_….

c:\> sp_….

Job Scheduler Architecture

ASE Plugin

Scheduled Jobs Folder

Job Scheduler Server




JS Agent

JS Task

job id

scheduled job info



admin data

scheduled job info


Job Scheduler

stored procedures

job scheduler console
Job Scheduler Console
  • Accessed in the ASE Plug-in Scheduled Jobs folder
ddlgen utility
DDLGen Utility
  • Introduced in ASE 12.5
  • Command line utility for generating DDL for an existing database or individual objects
    • Tables, indexes, triggers, etc.
  • Available on all platforms
  • Also used by Sybase Central ASE Plugin to generate DDL for objects
  • Will continue to support new object types in future releases
  • Performance improvements in 12.5.1
monitor server and historical server
Monitor Server and Historical Server
  • Made numerous bug fixes in the past year
  • Improved stability and performance
  • Worked with a number of customers to troubleshoot, test and fix
  • Examples:
    • Get correct SP name and DB in all cases
    • Provide correct SP timing on high volume SMP systems (fixed timing “spikes”)
    • Support multiple delimiters in HS output files
    • Improved HS error handling and reporting
    • Encrypt login and password for server connections
    • HS auto-reconnect
  • Remember: Historical Server is backwards compatible
    • Use the latest HS release with your older MS and ASE
ase monitoring tables aka mda
ASE Monitoring Tables (aka MDA)
  • Introduced in ASE release
  • Provide detailed performance and status information on server resources, processes and queries
  • Full SQL language support for access to detailed monitoring data
  • Tables include data caches, locks, SQL statement statistics, SQL Text, query plan, system and process wait states, device and network IO….
  • Access performance data from remote servers via CIS
monitoring table use
Monitoring Table Use
  • Useful for:
    • Stored procedure monitoring
    • Table and index usage monitoring
    • Data cache sizing and tuning
    • SQL text capture
    • Device activity monitoring
    • Identifying wait conditions causing performance problems
    • Identifying “hog” processes, users or applications
    • Resource usage analysis and capacity planning
monitoring tables cached objects
Monitoring Tables: Cached Objects

Objective: Show object with Largest # of pages in Default data cache


Monitoring Tables: Data Cache Usage

Objective: Show all objects in data caches, ordered by memory usage and data cache

monitoring tables top query
Monitoring Tables: Top Query

Objective:Show the User, CPU Time and Query Text for the query with the greatest current amount of CPU time*

  • 1> sp_topproc
  • 2> go
  • Spid: 38, User: sa, CPU Time: 1377
  • select error, description
  • from master..sysmessages
  • where error > 150
  • and error < 25000
  • (return status = 0)

*Ask me if you would like me to email you the code

for this stored procedure

analyzing stored procedure performance
Analyzing Stored Procedure Performance
  • Historical Server provides stored procedure performance information
  • MDA tables do not provide a table with historical stored procedure statistics
  • The monSysStatement table can be used to report this information
  • w
stored procedure statistics
Stored Procedure Statistics

Objective:Report elapsed Time, physical or Logical IO, CPU Usage, wait time…

Uses monSysStatement Table

monitoring tables index utilization
Monitoring Tables: Index Utilization
  • Have you ever wanted to see
    • Which indexes are never used?
    • How frequently they are used?
    • How many inserts, deletes, updates, physical or logical I/O they incur?
  • monOpenObjectActivity table provides:
    • Table usage count
    • Index usage count
    • Last used dates
    • Physical, logical I/O
    • Row-level insert/delete/update counts
    • Lock wait counts for tables and indexes
  • NOTE: Statistics are reset when server is booted or object descriptor is reused in memory.
monitoring tables index utilization27
Monitoring Tables: Index Utilization

Objective:Table and index usage (counts, dates)

Uses monOpenObjectActivity table

monitoring table usage
Monitoring Table Usage

Objective:Per table inserts, deletes,updates and lock waits

Uses monOpenObjectActivity table

sp sysmon
  • Widely used performance monitoring report
  • Detailed analysis of:
    • Memory, engine, cache, disk, lock, procedure, recovery, etc.
  • New configuration recommendations added
  • New cache configuration “wizard” in 12.5.1
  • Option to run without clearing counters available soon!
    • Improve coexistence with Monitor Server and other applications
sp sysmon cache wizard
sp_sysmon: Cache Wizard
  • Detailed statistics on:
  • Cache performance
  • Object performance




default data cache


Run Size: 8.00 Mb Usage%: 12.00 LR/sec: 416.26 PR/sec: 0.34 Hit%: 99.92

Buffer Pool Information


IO Size Wash Run Size APF% LR/sec PR/sec Hit% APF-Eff% Usage%

------- ---------- -------- ------ -------- -------- ------ -------- ------

16 Kb 1632 Kb 8 Mb 10.00 416.26 0.34 99.92 8.72 12.30

(1 row affected)

Object Statistics


Object LR/sec PR/sec Hit%

--------------------------------------------- ------- ------- ------

master.dbo.spt_values 2.27 0.03 98.67

pubs2.dbo.salesdetail 0.58 0.01 98.04

master.dbo.spt_values.spt_valuesclust 0.70 0.00 99.46



  • Buffer pool usage
  • Object Usage of cache
sp sysmon cache wizard31
sp_sysmon: Cache Wizard



Cache Occupancy Information


Object Obj Size Size in Cache

Obj_Cached% Cache_Occp%

--------------------------------------------- ----------- -------------

----------- -----------

master.dbo.spt_values 1648 Kb 1584 Kb

96.12 19.34

pubs2.dbo.salesdetail 128 Kb 48 Kb

37.50 0.59

master.dbo.spt_values.spt_valuesclust 128 Kb 16 Kb

12.50 0.20

tempdb.dbo.tempbufpoolstats 128 Kb 32 Kb

25.00 0.39

pubs2.dbo.titleauthor 256 Kb 16 Kb

6.25 0.20





Consider adding a large I/O pool for 'default data cache'

Statistics on object usage of data cache

…and tuning recommendations

sp monitorconfig
  • Reports usage of configured server resources
    • E.g., memory, user connections, open objects, proc cache
  • Displays “high water marks”
    • Helps identify resources that are over- or under-configure
  • Example: Open Object Configuration

1> sp_monitorconfig 'number of open objects'

2> go

Usage information at date and time: Jan 24 2004 2:36PM.

Name Num_free Num_active Pct_act Max_Used Reused

------------------------- ----------- ----------- ------- ----------- ------

number of open objects 473 27 5.40 83 No

(return status = 0)

sp monitor
  • Used to report only current CPU and network utilization
  • Enhanced to provide reports on connections, wait events and stored procedures
  • Connection
    • Elapsed and CPU time, Physical IO, lock usage
  • Procedure
    • Average or detail CPU, elapsed, wait time, physical, logical, network IO
    • Filters on database and procedure name
  • Event
    • Displays wait times for wait conditions
    • E.g., waiting for disk IO or locks
  • Execute “sp_monitor help” for details

Enhanced and expanded

in ASE 12.5.2

sp monitor example
sp_monitor: Example

Objective: Display status of all processes performing queries

  • We have also seen
  • sp_monitor ‘procedure’
  • sp_monitor ‘procedure’, detail
migration tool
Migration Tool
  • Introduced in ASE release
    • ASE 12.5 introduced larger page sizes
  • Use to move existing data from an ASE using 2K page size to one using larger page size
    • Supports cross-platform move in
  • Automates schema and data movement processes
    • Generates and applies database DDL
    • Copies data to target server using CIS
  • Easier than recreating database and

using BCP

migration tool36
Migration Tool
  • Migrate data between ASE servers
  • Database schema and data migration
  • Graphical selection of source and target servers and objects and migration status
  • Used to increase page size or move databases
migration tool how it works
Migration Tool: How It Works
  • User Creates Target Database
  • Migration Tool Generates DDL for All Source Database Objects
  • Migration Automatically Identifies Dependent Objects
  • Migration Tool Creates Objects on Target Server
  • Data for Tables is Copied to Target Server Using High Speed CIS Connection
  • Indexes are Built on Target Server
  • Migration Tool Validates Object Creation on Target Server
migration tool enhancements
Migration Tool Enhancements
  • Coming in the ASE 15.0 beta and GA releases
    • Performance Significantly improved in a number of common situations
      • Index creation
      • Improved data parallelism
      • Large number of objects
    • Improved ease of use
    • Support for migration from earlier ASE 11.9.2 and later
    • Integration with Sybase Central
migration process architecture



Migration Process Architecture

Source Server

Target Server

High-Speed Data


Table DDL

Stored Procedures



Remote Logins




Remote Servers

Generate Indexes




and Database


Copy Database

Table and Object


Migration Tool


sybase partners
Sybase Partners
  • We work closely with vendors to develop and support ASE monitoring products
    • BMC
      • DBXray, Patrol, SQL Backtrack
    • Leccotech
      • SQL Expert, Database Expert
    • Quest
    • White Sands
    • HP OpenView
  • Participate in product design and provide technical advice
  • Sybase Customer Support handles DBXray and Leccotech
  • Sybase QA tests BMC DBXray and Leccotech products
query tuning sybase sql expert
Query Tuning: Sybase SQL Expert
  • Released in ASE 12.5
  • Separately licensed Windows GUI application
  • Identifies, evaluates and rewrites SQL statements according to the database structure, indexes and data distribution.
  • Uses a unique technology based on Artificial Intelligence to rewrite SQL statements.
  • Automates the SQL tuning process
  • Recommends SQL improvements
  • Eliminates the need for manual tuning
  • Solution for inexperienced and experienced SQL tuners
sql expert results
SQL Expert: Results
  • Scans existing SQL procedures
    • In database
    • From disk files
  • Monitors SQL queries in running server
  • Identifies “problematic” SQL according to customized standards
  • Determines all semantically equivalent alternative queries
  • Performs performance benchmarks of each alternative query
    • Reports actual response and execution time for each query formulation
  • Identifies most efficient query formulation
finds and fixes problematic sql
Finds and Fixes Problematic SQL

Scans your stored procedures or scripts

Identifies problematic SQL

sql expert recommendations
SQL Expert: Recommendations

Recommends more efficient query formulations

Measures actual query performance

sql expert performance analysis
SQL Expert: Performance Analysis

Original SQL

Elapsed Time: 0.763 sec

Fastest SQL Alternative

Elapsed time: 0.060 sec

sybase database expert
Sybase Database Expert

From Query Analysis to Database Performance Wizard!

  • Index Advisor
  • Performance Monitor
  • Visual SQL Inspector
  • Migration Analyzer
  • Configuration Analyzer
  • Index Impact Analyzer
  • Unused Index Analyzer
  • Object Extractor
database expert performance diagnostics
Database Expert: Performance Diagnostics
  • Utilizes ASE 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.

Database Expert: Performance Monitor

  • CPU
  • Device
  • Data Cache
  • Network
  • Lock Usage

Database Expert: Performance Monitor

Data and




database expert process monitor
Database Expert: Process Monitor
  • Process Activity:
  • Logical Reads
  • Physical Reads
  • Wait Time
  • ULC Flushes
  • Etc.






database expert sql history
Database Expert: SQL History

History of

SQL on





database expert index advisor
Database Expert: 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
database expert unused index analyzer
Database Expert: 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
performance monitoring bmc db xray
Performance Monitoring: BMC DB Xray
  • Graphical performance monitoring tool
  • Separately licensed and supported by Sybase
  • Developed in partnership by Sybase and BMC Software, Inc.
  • Provides real-time information on system resource utilization, performance and tuning
    • User details (statistics, current SQL, query plan)
    • Database space usage and object statistics
    • Resource high-water marks
    • Data and procedure caches, disk devices, network IO, etc…
  • Identifies performance problems and provides tuning recommendations
  • User-defined alerts
  • Now supports ASE 12.0 and later!
db xray main screen
DB Xray: Main Screen

Potential problem alerts

And hints

24 Hour Graph

Number of connections

And high-water mark











db xray users screen
DB Xray: Users Screen

SQL Text (and Query Plan)

User List







in summary
In Summary…
  • Sybase continues to support the monitoring tools you are using
    • Monitor Server
    • Historical Server
    • sp_sysmon, etc.
  • The ASE now provides a rich set of sources of monitoring information as well as applications for analyzing this data
    • Monitoring Tables
    • sp_monitor
  • We continue to invest in improving our products and developing new tools and applications
    • ASE Plug-in
    • Historical Server
    • Database Expert & SQL Expert
    • DBXray
  • Job Scheduler
  • Migration Tool
  • DDLGen
summary cont
Summary (Cont.)
  • Partner Products
    • Introduced Leccotech Database Expert
    • Enhanced SQL Expert
    • Added support for ASE 12.0 through in DBXray
Q & A

Questions and Comments?

Thank You!