Customer Coffee Corner for SAP IQ Monitoring Performance

Customer Coffee Corner for SAP IQ – Monitoring Performance (Part 1) Saroj Bagai/SAP Global Product Support February 11, 2016 nly with o e d li s e tl ti is th Use Customer an image

Agenda SAP IQ Monitoring Performance (part 1) Open Discussion about Monitoring Performance Common links for information Product components BC-SYB-IQ (IQ) BW-SYS-DB-IQ (IQNLS) XX-SER-LIKEY-SYB ( License Key) © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 2

Sub-Agenda Database Profiling IQ System Stored Procedures Event Profiling SA System Stored Procedures Key Performance Indicators (SAP IQ Cockpit) IQ Monitors © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 3

Database Profiling IQ System Stored Procedures that will return database usage statistics Name sp_iqconnection Description This system procedure provides information about connections and versions, including users are using temporary dbspace, which users are holding versions alive, what the connections are doing inside IQ, connection status, database version status and so on. sp_iqconnection [ connhandle] sp_iqconnection sp_iqcontext This system procedure tracks and displays, by connection, information about statements that executing. sp_iqcontext [ connhandle ] sp_iqcontext © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 4

Database Profiling System Stored Procedures- cont’d sp_iqcheckdb This system procedure checks validity of the current database. Optionally corrects dbspace or database allocation problems. sp_iqcheckdb ‘mode target […] resources resource-percent] sp_iqcheckdb sp_iqdbstatistics This system procedure reports results of the most recent sp_iqcheckdb sp_iqdbstatistics sp_jqdbstatistics sp_iqtablesize This system procedure provides the number of blocks used by each objects in the current database and the name of the dbspace in which the object is located. sp_iqtablesize ( table-owner. table-name ) sp_iqtablesize © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 5

Database Profiling System Stored Procedures sp_iqdbsize This system procedure provides the size of the current database sp_iqdbsize ([ main ]) sp_iqdbize sp_iqdbspaceinfo This system procedure provides the space usage by each object in the current database sp_iqdbspaceinfo [‘main | [table-name | index-name] […] ‘] sp_iqdbspaceinfo sp_iqstatus This system procedure provides miscellaneous information about the database. sp_iqstatus © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 6

Event Profiling SA System Procedures Event profiling SA System Procedures return performance statistics for events, functions, stored procedures and triggers. Name sa_server_option Description This system procedure sets database profiling options. Call sa_server_option (‘procedureprofiling’, ’on’) sa_server_option sa_procedure_profile This system procedure returns execution times for each line in database events, functions stored procedures and triggers. sa_procedure_profile [filename , save_to_file]] sa_procedure_profile_summary This system procedure summarizes execution times for all events, functions, procedures and triggers sa_procedure_profile_summary [filename [, save_to_file]] sa_procedure_profile_summary © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 7

Key Performance Indicators(SAP IQ Cockpit) • SAP IQ Servers Statistics. • Multiplex and Node Related Statistics • Logical Server Statistics © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 8

SAP IQ Servers Statistics • SAP IQ Availability Statistics • Overview Statistics • Connection Statistics • DBSpace and DBSpace File Statistics • Store input and Output Statistics • Operations And Request Statistics • Network Statistics • Transaction Statistics © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 9

Multiplex and Node-Related Statistics • Multiplex Availability • Multiplex Status • Multiplex Node Properties • Multiplex Link Availability © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 10

Logical Server Statistics • Logical Server Availability • Logical Server Engine Statistics • Logical Server Connection • Logical Server Transaction • Logical Server Cache Statistics • Logical Server Operations and Requests Statistics © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 11

SAP IQ Cockpit Demo © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 12

IQ Monitor • Gives detailed statistics of a running IQ system. - Cache Usage - Thread Management - Lock Contention - I/O Subsystem - Internal Statistics • Helps analyze system usage - Locate system bottlenecks - Tune for IQ parameters for Optimal Throughput © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 13

© 2015 SAP SE or an SAP affiliate company. All rights reserved. 14

IQ Monitor Overhead • Consumes very little system resource - Statistics are always collected - even when the monitor is not running - Can leave running in case performance issue arises (i. e. cache thrashing) to troubleshoot problem • Overhead only incurred when reporting results - Reporting interval can be increased to minimize overhead © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 15

Starting and Stopping IQ monitor • • • Monitor table must be created prior to starting the monitor Create table iqmondummy (c 1 int); Monitor is started/stopped using IQ utilities Syntax: iq utilities (main | temp) into mon_tbl start monitor [options] Options: -cache, -summary, -cache_by_type, -io, -debug Drop Monitor table after stopping the monitor Drop table iqmondummy © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 16

IQ Monitor - Example DBA)> create table iqmondummy (c 1 int) Execution time: 0. 075 seconds (DBA)> iq utilities main into iqmondummy start monitor '-debug -interval 20' Execution time: 0. 004 seconds (DBA)> iq utilities private into iqmondummy start monitor '-debug -interval 20' Execution time: 0. 004 seconds (DBA)> iq utilities main into iqmondummy stop monitor Execution time: 0. 004 seconds (DBA)> iq utilities private into iqmondummy stop monitor Execution time: 0. 003 seconds (DBA)> drop table iqmondummy © 2015 SAP SE or an SAP affiliate company. All rights reserved. Execution time: 0. 105 seconds Customer 17

IQ Monitor Output • Output printed to text file with following naming convention: <dbname>. <conn#>-[main|temp]-iqmon, Example: unidb. 31 -main-iqmon Monitor output file is in. db directory by default • Monitor_Output_Directory option can be used to control output directory • • Use UNIX “tail” command to follow monitor output: $ tail –f unidb. 31 -main-iqmon © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 18

Correlating Commands with Monitor Output • Match timestamps from ZR log with monitor output (only when –zr logging Is logged in the. srvlog) • Adjust monitor interval to isolate activity from individual statements • Start/Stop monitor before command begin/end © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 19

Filtering Monitor Output • Monitor output contains much information that is rarely needed • Monitor output can be filtered using UNIX grep command to isolate interesting fields • Examples: - grep “Prefetch. Reqs” unidb. 31 -main-iqmon - egrep “Statistic|Hit%” unidb. 31 -temp-iqmon © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 20

Monitor Instances • Separate Monitor instances required to monitor the main and temp cache • Each monitor will output to a different file: - unidb. 31 -main-iqmon - unidb. 31 -temp-iqmon • Only one main and one temp monitor may run concurrently © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 21

Monitor Subsystems • Monitor options determine subsystems to monitor -cache : Buffer cache -cache_by_type : Buffer cache by page type -summary -io : IO subsystem -threads : Thread Manager -contention: System-wide lock contention -buffalloc : Buffer Allocator © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 22

Common Links For The Information • Product documentation http: //help. sap. com/iq • Monitoring Performance • IQ Cockpit Monitor SAP IQ • Support Portal https: //support. sap. com/home. html • How to get best results from an SAP search https: //service. sap. com/sap/support/notes/2081285 © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 23

Common Links For the Information • SAP IQ communties http: //scn. sap. com/community/iq • SAP IQ Wiki http: //wiki. scn. sap. com/wiki/display/SYBIQ/IQ • IQ 16 Best Practices http: //scn. sap. com/docs/DOC-39896 • IQ 16 Hardware Sizing Guide http: //scn. sap. com/docs/DOC-41455 © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 24

Common Links For the Information • Social Media Product Support Channels https: //twitter. com/SAPSupporthelp https: //www. facebook. com/Sap. Product. Support © 2015 SAP SE or an SAP affiliate company. All rights reserved. Customer 25

Thank you © 2015 SAP SE or an SAP affiliate company. All rights reserved.