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 35 focus 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 wont 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 dont 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 use sa_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 sqlhostvars
• SQL sa_server_option(request_level_logging,
  allsqlsqlhostvars )
• 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 ltfilenamegt
• sa_server_option( request_level_log_file,
  filename )
• ltservergt ? 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
• ltDBgt?Properties?Profiling?Start Profiling
• Execute application
• Disable profiling
• ltDBgt?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 ProceduresFunctions, 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 ltinstallation dirgt\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
Professional Services
Our experts can help further optimize the
performance of your SQL Anywhere Studio Solution
Expertise

• 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

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?