ASE 111: ASE System Management Developments

1. ASE 111: ASE System Management Developments Peter Dorfman Sr. Staff Software Engineer, Sybase, Inc. peter.dorfman@sybase.com August 15-19, 2004

2. The Enterprise. Unwired.

3. The Enterprise. Unwired. Industry and Cross Platform Solutions Manage Information Unwire Information Unwire People • 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 • Our Strategy • Enhancements and New System Management Tools • Improved Monitoring Tools • SQL Expert and Database Expert • DBXray Features and Enhancements • Your Questions

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

11. ASE 15.0: DBISQL and ASE

12. 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

13. ASE 15.0: Graphical Showplan Query Text Execution Tree Query Statistics

14. 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

15. Target Server B Target Server A ASE ASE ASE ASE ASE ASE c:\> sp_…. c:\> sp_…. Job Scheduler Architecture ASE Plugin Scheduled Jobs Folder Job Scheduler Server ASE job execution JS Agent JS Task job id scheduled job info jobs, schedules, admin data scheduled job info sybmgmtdb Job Scheduler stored procedures

16. Job Scheduler Console • Accessed in the ASE Plug-in Scheduled Jobs folder

17. 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

18. 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

19. ASE Monitoring Tables (aka MDA) • Introduced in ASE 12.5.0.3 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

20. 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

21. Monitoring Tables: Cached Objects Objective: Show object with Largest # of pages in Default data cache

22. Monitoring Tables: Data Cache Usage Objective: Show all objects in data caches, ordered by memory usage and data cache

23. 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

24. 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

25. Stored Procedure Statistics Objective:Report elapsed Time, physical or Logical IO, CPU Usage, wait time… Uses monSysStatement Table

26. 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.

27. Monitoring Tables: Index Utilization Objective:Table and index usage (counts, dates) Uses monOpenObjectActivity table

28. Monitoring Table Usage Objective:Per table inserts, deletes,updates and lock waits Uses monOpenObjectActivity table

29. 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

30. 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

31. 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 .. .. TUNING RECOMMENDATIONS --------------------------------------------------------- Consider adding a large I/O pool for 'default data cache' Statistics on object usage of data cache …and tuning recommendations

32. 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)

33. 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

34. sp_monitor: Example Objective: Display status of all processes performing queries • We have also seen • sp_monitor ‘procedure’ • sp_monitor ‘procedure’, detail

35. Migration Tool • Introduced in ASE 12.5.0.1 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 12.5.0.3 • 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

36. 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

37. 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

38. 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

39. ASE ASE Migration Process Architecture Source Server Target Server High-Speed Data Migration Table DDL Stored Procedures Triggers Logins Remote Logins Users Views Rules Remote Servers Generate Indexes Etc…. Determine connection and Database Properties Copy Database Table and Object Definitions Migration Tool Application

40. 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

41. 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

42. 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

43. Finds and Fixes Problematic SQL Scans your stored procedures or scripts Identifies problematic SQL

44. SQL Expert: Recommendations Recommends more efficient query formulations Measures actual query performance

45. SQL Expert: Performance Analysis Original SQL Elapsed Time: 0.763 sec Fastest SQL Alternative Elapsed time: 0.060 sec

46. 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

47. Database Expert: Performance Diagnostics • Utilizes 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.

48. Database Expert: Performance Monitor • CPU • Device • Data Cache • Network • Lock Usage

49. Database Expert: Performance Monitor Data and Procedure Cache Efficiency

50. Database Expert: Process Monitor • Process Activity: • Logical Reads • Physical Reads • Wait Time • ULC Flushes • Etc. Select Individual Processes Connection Details