SAS910: Performance Analysis from Beginning to End

1. SAS910: Performance Analysis from Beginning to End Matthew Young-LaiASA Query ProcessingiAnywhere Solutions, Inc.August 2003

2. Goals of this presentation • To describe the performance analysis tools available in ASA • To highlight some factors that affect performance • Focus on analysis (with some tuning tips) • AM 35focus on ASA internals(with some tuning tips) • SAS 911 described the new index consultant

3. Contents • Modus operandi for this presentation • Pre-analysis checklist • Tools of the trade: • Request-level logging • Procedure execution profiler • Graphical query plan • Performance monitor • Timing Utilities

4. Modus Operandi • Emphasis on performance issues specific to 8.x, 9.x • Some relevant to older versions • New features that make analysis different in 8.x: • New join methods • Optimizing for response (first-row) • No combined indexes • Cost based optimization; new statistics • New features that make analysis different in 9.x: • Tools changes (esp. graphical plans) • Language extensions • Statistics changes • Optimizer changes • Index consultant

5. Modus Operandi • Tools demonstrated via user application (Thanks Breck Carter) • Powerbuilder application • Extracts schema information from ASA 7 DB • Stores schema in ASA 8 DB (dictionaries.rrd) • Produces HTML dictionary file for schema • Compares schemas, etc… I Show and tell!!

6. Contents • Modus operandi for this presentation • Pre-analysis checklist • Tools of the trade: • Request-level logging • Procedure execution profiler • Graphical query plan • Performance monitor • Timing Utilities

7. Pre-analysis Checklist (Database Format) • Ensure 8.x or 9.x created database, whenever possible • Done using an Unload/reload • Many improvements introduced in 8.0.0 • Clustered indexes introduced in 8.0.2 • Also has the effect of defragmenting and compacting • If unload/reload is not possible or not required, consider DBUPGRAD • Evaluate DB page size • Tradeoffs can be difficult to predict • Fanout and depth of indexes • Number of split rows • Cache usage (and waste) • The usual recommendation is 4K • Only use other page size if indicated by experimentation

8. Pre-analysis Checklist (Server Configuration) • Ensure adequate server hardware (CPU, memory, disk) • Requirements are difficult to predict • Better hardware won’t necessarily help if there are other problems • Evaluate file placement (less important with RAID) • Database file • Log file • Temp file (ASATMP environment variable) • Dbspaces

9. Pre-analysis Checklist (Internal Organization) • Check OS file fragmentation • OS tool • Server window on startup • DBFileFragments property • Check for DB fragmentation • Diagnosis • Sa_index_density() • Sa_table_fragmentation() • Correction • REORGANIZE TABLE • Unload/reload • Avoidance • PCTFREE

10. Pre-analysis Checklist (Schema) • Wide tables • Cause rows to split across multiple pages • Correction: normalization or larger page size • Wide primary keys • Make primary key and foreign key indexes larger • Make updates more expensive • Undeclared PK-FK constraints • Eliminates an index • Less space and maintenance cost • Not available for query processing • Make selectivity estimation harder

11. Pre-analysis Checklist (Schema) • Use of inefficient data types • NUMERICS and STRINGS are expensive • Column order • Columns are extracted sequentially • Extraction stops after last column needed • Place small and/or frequently accessed columns first in rows • Undeclared NOT NULL constraints • Storage inefficiency • Disallows certain optimizations • Expensive check constraints • Eg. User-defined function

12. Pre-analysis Checklist (Dynamic Actions) • Inefficient use of triggers • For maintaining update time, last user • For checking simple conditions (use check constraints instead) • Cascading referential actions • E.g., ON DELETE CASCADE • PK generation method • [Global] Auto increment preferred • Expensive user defined functions • May be executed many times during a query • Cannot be inlined and globally optimized

13. Pre-analysis checklist (Individual Queries) • All-rows (resource) vs. first-row (response) optimization • Default before 8.0.2: first-row • Default after 8.0.2: all-rows • First-row can be specified in a query using FASTFIRSTROW • Use the right cursor type • If read-only, say so • Key-set cursors more expensive • Faster optimization: less stuff to build (no articles, check constraints, etc.) • Better optimization: some rewrites skipped for updatable cursors

14. Pre-analysis checklist (Individual Queries) • Evaluate use of user-estimates • Use only when absolutely certain • Consider Create Statistics instead • see DB option user_estimates • Check existence of statistics • Small tables don’t have statistics created • DB option min_table_size_for_histogram • Create Statistics statement

15. Pre-analysis checklist (Locking Problems) • To view locks • Sa_locks() • To find the last statement for each connection • -zl option or • Sa_server_option( ‘remember_last_statement’, ‘ON’ ) • Sa_conn_activity() • To find who a connection is blocked on • Sa_conn_info() (check the “BlockedOn” column) • Sa_conn_property( ‘BlockedOn’ )

16. Contents • Modus operandi for this presentation • Pre-analysis checklist • Tools of the trade: • Request-level logging • Procedure execution profiler • Graphical query plan • Performance monitor • Timing Utilities

17. Performance analysis tools • Variety of tools available • Existing tools being improved • More powerful: new capabilities • Better integrated: everything will be brought under the Sybase Central umbrella • Feedback appreciated • When? What? How? (to use each tool) • Keep in mind the difference between hot and cold cache • For repeatable results: usesa_flush_cache()

18. Contents • Modus operandi for this presentation • Pre-analysis checklist • Tools of the trade: • Request-level logging • Procedure execution profiler • Graphical query plan • Performance monitor • Timing Utilities

19. Request-level logging • Good starting point for performance analysis of an application when all you know is that its too slow • Allows you to determine • What the server is being asked to do by the client • Whether the bottleneck is in the server or in the client • Which specific requests are taking up the time

20. Request-level logging • Server side logging of individual requests over client interface (not internal requests) • The server logs requests received and responses sent • Logged information includes: • Timestamps • Connection ids • Request type, and more

21. Request-level logging • Connect, with DBA authority, to any DB on the server • Get the server to start logging • Command line: -zr { all | sql | sql+hostvars } • SQL: sa_server_option(‘request_level_logging’, {‘all’|’sql’|’sql+hostvars’} ) • Sybase Central • Server  Properties  Options  Enable RL Logging

22. Request-level logging • Redirect request-level logging output • By default, output goes to the server window • Redirect to a file for further analysis • -zo <filename> • sa_server_option( ‘request_level_log_file’, ’filename’ ) • <server>  Properties  Options  Log file name

23. Request-level logging • Run the application • Get the server to stop logging • Shut down server • sa_server_option(‘request_level_logging’,’off’) • Sybase Central • Analyze the log • Text editor • Supplied stored procedures

24. Request-level logging • sa_get_request_times ( [ request_log_filename [, connection_id ] ] ) • Reads the request-level log • Populates table satmp_request_time with statements from log and execution times: • Time is straightforward for INSERT/UPDATE • For queries, time is from PREPARE to DROP (describe/open/fetch/close): be aware of open cursors • Analyze satmp_request_time for candidates

25. Request-level logging • Statements that are cheap but frequently executed may represent performance problems • sa_get_request_profile( [log_filename [, connection_id ] ] ) • Calls sa_get_request_times() and summarizes satmp_request_time into another global temporary table satmp_request_profile • Groups statements together and provides number of calls, execution times, etc. I Demo

26. Contents • Modus operandi for this presentation • Pre-analysis checklist • Tools of the trade: • Request-level logging • Procedure execution profiler • Graphical query plan • Performance monitor • Timing Utilities

27. Procedure profiling • Server profiling of database procedures as they execute • Measures execution time for each line of a procedure (can help pin-point problem areas) • Tracks the number of times each procedure is called • Use to analyze specific database procedures found to be expensive, e.g., via request level logging • Use to discover expensive hidden procedures, e.g., triggers, events, and nested stored procedure calls

28. Procedure profiling • Profiling can be enabled/disabled dynamically • Profiling information is transient • Profiling information is cumulative: can be reset • Resolution: 15ms in 8.0.1, 1ms in 8.0.2 on Windows • Use via Sybase Central • Need to connect, with DBA authority, to the database that houses the procedures being investigated

29. Procedure profiling • Enable profiling • <DB>PropertiesProfilingStart Profiling • Execute application • Disable profiling • <DB>PropertiesProfilingStop Profiling • Examine profiling information

30. Procedure profiling • Three ways to analyze • Entire database: all procedures • Specific type of procedures • All stored procedures and functions • All events • All triggers • Specific individual procedure • Remember procedure calls may be nested

31. Procedure profiling • Analyzing the entire database: • Select DB in left pane and Profile tab in right pane • Name • Owner • Object type • Table (for triggers) • Milliseconds • Calls

32. Procedure profiling • Analyzing specific type of procedures • Select Procedures&Functions, Events or Table/Trigger under the DB and click Profile in right pane • Name • Owner • Milliseconds • Calls

33. Procedure profiling • Analyzing specific procedure • Select procedure in left pane and click Profile in right pane • Will display body with profiling info for each line • Calls • Milliseconds • Line (number) • Source

34. Procedure profiling • Reset Profiling • Removes old information • Clear Profiling • Removes old information and disables profiling I Demo

35. Contents • Modus operandi for this presentation • Pre-analysis checklist • Tools of the trade: • Request-level logging • Procedure execution profiler • Graphical query plan • Performance monitor • Timing Utilities

36. Graphical plan • Displays, in graphical format, a detailed execution plan for a query • Use DBISQL or graphical_plan() to generate • Can be saved in XML format for later viewing • Use DBISQL to view

37. Graphical plan • Use to diagnose performance problems with specific queries • Make sure the optimization conditions are comparable • Cursor type • State of cache • Optimization goal • etc. • Can generate with or without statistics • Keep in mind when using “with statistics” • Imperfect estimates are not necessarily a problem • Timing results include overhead

38. Graphical plan I Demo • Things to look for: • Estimated vs. actual row counts • Predicate selectivities : statistics • Join selectivities : PK-FK constraints, etc. • Check optimization goal • Index scan for order-by when optimizing for response (first-row) • Best to use an index, if one exists, unless small sort • Consider adding one otherwise • Check data in cache • Sequential scans are good when cache is cold • Index scans will generally do better when data is cached • Look for expensive sub-selects

39. Contents • Modus operandi for this presentation • Pre-analysis checklist • Tools of the trade: • Request-level logging • Procedure execution profiler • Graphical query plan • Performance monitor • Timing Utilities

40. Performance Monitor • Monitor state of server in real time • A variety of counters maintained • Can use Sybase Central or NT Performance Monitor • Recommend NT perfmon • No extra server overhead: dbctrs8.dll • Uses shared memory; can monitor one engine and multiple instances of dbs/connections I Demo

41. Contents • Modus operandi for this presentation • Pre-analysis checklist • Tools of the trade: • Request-level logging • Procedure execution profiler • Graphical query plan • Performance monitor • Timing Utilities

42. Timing utilities • Some utilities available for testing performance • Available in <installation dir>\samples\asa\ • Complete documentation in Readme.txt in the same folder as the utility. • Use these tools, not graphical plan with statistics, for accurate timings • Provide an indication of high water mark given the server and db configurations

43. Timing utilities • fetchtst  • Samples\Asa\PerformanceFetch • Measures fetch rates for an arbitrary query • Put an arbitrary query in a file (test.sql, by default) • Run fetchtst – choose running parameters

44. Timing utilities • instest  • Samples\Asa\PerformanceInsert • Determines the time required for rows to be inserted into a table. • Reads query from a file • Uses PUT to insert rows

45. Timing utilities • trantest  • Samples\Asa\PerformanceTransaction • Measures the load that can be handled by a given server configuration given a database design and a set of transactions. • Simulates a number of client machines running transactions against the server • Define what transactions to execute • Can run on multiple client machines: master/slave

46. More!! • AM 35 - Adaptive Server Anywhere Internals: Performance and Tuning, Glenn Paulley

47. Years of experience with Mobile, Embedded and Workgroup solutions Proven Best Practices Rapid Implementation Methodology World-class Partners Improve the performance of your mobile/embedded/workgroup database, wired/wireless application or data movement configuration Ensure overall efficiency of your solution Identify bottlenecks, make recommendations on how to address bottlenecks and implement changes as needed Professional Services Expertise Our experts can help further optimize the performance of your SQL Anywhere Studio Solution +

48. iAnywhere Solutions at TechWave2003 Activities for iAnywhere Solutions • Ask the iAnywhere Experts on the Technology Boardwalk • Drop in during exhibit hall hours and have all your questions answered by our technical experts! • Appointments outside of exhibit hall hours are also available to speak one-on-one with our Senior Engineers. Ask questions or get your yearly technical review – ask us for details • m-Business Pavilion • Visit the m-Business Pavilion in the exhibit hall to see how companies like Intermec have built m-Business solutions using iAnywhere Solutions technology • Wi-Fi Hotspots – brought to you by Intel & iAnywhere Solutions • You can enjoy wireless internet access via a Wi-Fi hotspot provided by Intel and iAnywhere Solutions. Using either a laptop or PDA that is Wi-Fi 802.11b wirelessly-enabled, visitors can access personal email, the internet ,and "TechWave To Go", a My AvantGo channel providing up-to-date information about TechWave classes, events and more.

49. iAnywhere Solutions at TechWave2003 Activities for iAnywhere Solutions • Developer Community A one-stop source for technical information! • Access to newsgroups,new betas and code samples • Monthly technical newsletters • Technical whitepapers,tips and online product documentation • Current webcast,class,conference and seminar listings • Excellent resources for commonly asked questions • All available express bug fixes and patches • Network with thousands of industry experts http://www.ianywhere.com/developer/

50. Questions?