Sybase 12.5.1, User Manual

Page 1: ...User s Guide Historical Server 12 5 1...

Page 2: ...nywhere Information Everywhere InformationConnect InternetBuilder iScript Jaguar CTS jConnect for JDBC Mail Anywhere Studio MainframeConnect Maintenance Express Manage Anywhere Studio M Business Chann...

Page 3: ...r connections 11 Initial configuration on UNIX platforms 12 Assumptions on UNIX platforms 12 Configuration procedures on UNIX platforms 12 Initial configuration on Windows NT 15 Assumptions on Windows...

Page 4: ...Server 46 Assumptions before connection 46 How to connect 47 Required permissions for Historical Server activities 47 Mutually exclusive sessions 49 Historical Server commands 50 hs_create_alarm 51 h...

Page 5: ...sing the Process ID 122 Using the application name 123 Empty rows versus no rows in views 123 Table of valid key and result data item combinations 124 Examples of valid combinations 146 Examples of in...

Page 6: ...O performance summary 179 Engine activity 179 Lock performance summary 179 Network activity for recording session 180 Network activity for sample interval 180 Network performance summary 181 Page I O...

Page 7: ...esents some basic Historical Server concepts Chapter 2 Configuring Historical Server describes how to complete an initial Historical Server configuration or change a configuration for both UNIX and Wi...

Page 8: ...cribes the new features in Adaptive Server version 12 5 1 the system changes added to support those features and the changes that may affect your existing applications ASE Replicator User s Guide desc...

Page 9: ...plications that access Adaptive Server performance data Monitor Server User s Guide describes how to use Monitor Server to obtain performance statistics from SQL Server and Adaptive Server Performance...

Page 10: ...r beginning users of the database management system This manual also contains descriptions of the pubs2 and pubs3 sample databases Using Adaptive Server Distributed Transaction Management Features exp...

Page 11: ...d starting the Technical Library The Technical Library Product Manuals Web site is an HTML version of the Technical Library CD that you can access using a standard Web browser In addition to product m...

Page 12: ...in this manual The following table describes the style conventions used in this manual Description Examples Command names command parameters and other keywords appear in the text in Helvetica typefac...

Page 13: ...urchased a support contract has one or more designated people who are authorized to contact Sybase Technical Support If you cannot resolve a problem using the manuals or online help please have the de...

Page 14: ...xiv Historical Server...

Page 15: ...ed procedures Adaptive Server Monitor components Adaptive Server Monitor consists of four components that gather or display Adaptive Server performance data Adaptive Server Enterprise Monitor Server M...

Page 16: ...nd the monitors in the Adaptive Server Enterprise plug in for Sybase Central are Monitor Client Library applications Adaptive Server Monitor architecture Figure 1 1 shows the relationships between Ada...

Page 17: ...formation The Adaptive Server Enterprise plug in for Sybase Central includes a set of monitors showing different aspects of Adaptive Server resource usage at various levels of detail Each open monitor...

Page 18: ...hs_create_alarm optional hs_initiate_recording When you create a recording session Historical Server assigns it a session ID You can list the session IDs of defined recording sessions using the hs_lis...

Page 19: ...recording sessions Views start time and end time these attributes define the data from the input recording sessions that you want to include in the playback session Summarization level you can specif...

Page 20: ...a item includes embedded spaces you must surround the name with quotation marks when you use it Some sample data items are Page I O Login Name and CPU Time Table A 1 on page 103 lists all available da...

Page 21: ...ple interval For example Rate for Sample for Page I O is the average number of page I Os that occurred each second during the most recent sample interval The calculation is Rate for Session this stati...

Page 22: ...ty over the session Only a few data items can use this statistic type The meaning of the returned value depends on the data item For example Average for Session for Procedure Elapsed Time is the avera...

Page 23: ...ps so users can access recording sessions that they created during previous executions of Historical Server The control file restricts user access to private recording session files Recording session...

Page 24: ...e directories For more information about the files created by Historical Server see Chapter 5 Data Files Accessing control file information Use the Historical Server hs_list command to gain access to...

Page 25: ...rver home directory specified in the start up command The account must have search execute and write access to the locations of recording session data files as specified by users who create recording...

Page 26: ...tructions in this section are based on the following assumptions Historical Server software was unloaded from the delivery media using the instructions provided with the delivery media An Adaptive Ser...

Page 27: ...must appear in the script file The ones with default values may be omitted if the default values are acceptable A sample script file for starting Historical Server follows histserver Dserver1HistDir S...

Page 28: ...storical Server was installed points to the appropriate directory service Use an editor to check and update libtcl cfg files Add Historical Server to the directory service using dsedit_dce if your sys...

Page 29: ...file If you are relying on a directory service for making client server connections make sure that the libtcl cfg file on all Historical Server client machines points to the appropriate directory ser...

Page 30: ...o the sql ini file in the Sybase installation directory on the Historical Server machine Configuration procedures on Windows NT To complete Historical Server configuration 1 Add connectivity informati...

Page 31: ...or Server knows it by For example use the value you used in the S parameter in the Monitor Server start up command If you edit a sql ini file using a text editor instead of dsedit make sure a carriage...

Page 32: ...Use ocscfg to check and update libtcl cfg files 3 Review start up parameters in the NT Registry This task ensures that the default start up parameter values that the installation process inserted into...

Page 33: ...is an integer in sequential order dataType defines the type of data in the parameter value parameter is a start up option preceded by a dash and followed by the parameter value An example containing t...

Page 34: ...and parameters The section applies to Historical Server running on both UNIX and NT platforms Function Starts Historical Server Syntax histserver histsrvr DhomeDir d delim IinterfacesFile llogFile Uu...

Page 35: ...cal Server can share the same home directory See Configuring multiple instances of Historical Server on page 23 for more information Note If any UNIX shell specific characters such as the C shell s ti...

Page 36: ...user has unrestricted access to the Historical Server data files Ppassword Password of user specified with the U parameter A user who logs in to Historical Server must supply this username and passwo...

Page 37: ...fere with the timely acquisition of new samples If the timestamps in the recorded data files indicate that samples are not being collected at a reasonable approximation of the requested sample interva...

Page 38: ...ver using the same home directory If this distinction is important to Historical Server administration use different home directories Contention on the control file if a large control file receives he...

Page 39: ...pdating the NT Registry services list 3 Adding connectivity information for Historical Server 4 Configuring Historical Server client machines 5 Creating a bat file optional Adding start up parameters...

Page 40: ...er 10 Make sure the entry for the new Historical Server name remains highlighted 11 Select Edit Add Key 12 In the Key Name box enter Parameters Leave the Class box blank 13 In the tree view highlight...

Page 41: ...ve Subtree As command or the Print command Value name Datatype String Arg0 REG_SZ ShsName where hsName is the Historical Server name you used in step 5 Example SHS_SERVER1 Arg1 REG_SZ DdataDirectoryNa...

Page 42: ...the SYBHIS_hsName key entry remains highlighted 10 Select Edit Add Key 11 In the Key Name box enter Security Leave the Class box blank 12 In the tree view highlight the following Security key entry S...

Page 43: ...tor Monitor Server paired with each Adaptive Server Historical Server Use dsedit to add entries to a sql ini file To add these entries you must know the following information Monitor Server and Adapti...

Page 44: ...ou are using sql ini files for making client server connections then update all of the sql ini files used by Historical Server clients The client sql ini files must contain entries for Historical Serv...

Page 45: ...exe See Inferring start up parameters from the NT Registry on page 37 for precedence rules used by Historical Server to obtain start up parameters See Setting Historical Server start up parameters on...

Page 46: ...Configuring multiple instances of Historical Server 32 Historical Server...

Page 47: ...ime Execute a script file that contains the histserver command and all appropriate parameters If you followed the configuration instructions in Chapter 2 Configuring Historical Server you would start...

Page 48: ...Server displays the following message to indicate that start up was successful Initialization is over Ready to accept connections Historical Server writes messages to its log file during start up You...

Page 49: ...s To determine current activity in Historical Server connect to Historical Server with the isql utility and issue the hs_status activity command Deferred versus immediate shutdown To close Historical...

Page 50: ...is the password that was used with the P parameter to the Historical Server start up command If U was not used in the start up command any user can stop Historical Server and this parameter is option...

Page 51: ...File Manager The command line shell captures start up error messages if any occur whereas the File Manager does not The start up command typed directly from a command line shell The name of the Histor...

Page 52: ...T Registry entries Verifying that Historical Server is running On Windows NT check the status of Historical Server in the Windows NT Control Panel Services window Stopping Historical Server on Windows...

Page 53: ...s activity command Deferred versus immediate shutdown on Windows NT To close Historical Server connect to it using the isql utility and issue one of the following commands hs_shutdown defers shutdown...

Page 54: ...box is presented Historical Server shuts down in an uncontrolled way risking data loss and file corruption To stop Historical Server using isql 1 Connect to Historical Server using isql isql Uhs_supe...

Page 55: ...y its controlled shutdown should take less than 20 seconds If 20 seconds or more are used for the controlled shutdown however the system may intervene and terminate Historical Server in an uncontrolle...

Page 56: ...Starting and stopping Historical Server on Windows NT 42 Historical Server...

Page 57: ...sessions Use these commands to create a recording session hs_create_recording_session defines the characteristics of a recording session hs_create_view defines a view which is a collection of data to...

Page 58: ...atistic types are all case insensitive File names view names and other user supplied names are case sensitive Creating a playback session Use these commands to create a playback session hs_create_play...

Page 59: ...successful execution One indicates an error condition If a status of one 1 is returned an error message and its appropriate error code severity level and state are returned to the client Historical S...

Page 60: ...endix D Examples of Recording Session Views provides examples of views that you might use as a start for your input files These views also appear in the views file that was installed in the sample his...

Page 61: ...es for the utility For example in SQL Advantage choose the Connect menu item In isql start isql using the Historical Server name as the value of the S parameter Required permissions for Historical Ser...

Page 62: ...he Monitor Server you want to connect to which indirectly implies the Adaptive Server you want performance information about Create recording sessions Valid login and password in the Adaptive Server b...

Page 63: ...recording For example a user might connect to Historical Server at 3 p m define a recording session that starts at 10 p m and then disconnect from Historical Server At 10 p m it is irrelevant whether...

Page 64: ...ommand you must successfully execute a hs_terminate_playback command to end the playback session This command cancels the definition and ends playback Historical Server commands The following pages de...

Page 65: ...ew definition Surround the statistic type with quotation marks alarm_action action to take when an alarm condition occurs Values are log logs messages when the alarm condition occurs You specify the l...

Page 66: ...xecutes in the background The commands within the script or program are executed in the foreground and displayed if applicable For example if you want the clock to display in an alarm condition you sh...

Page 67: ...a items in the view for the row of sample data that triggered the alarm When an alarm condition occurs and alarm_action is execute Historical Server runs the script or program specified in the alarm_a...

Page 68: ...A filter limits the scope of collected data for a single data item in a view Filters are optional Syntax hs_create_filter view_name data_item_name data_item_stat filter_type value_spec Parameters vie...

Page 69: ...lter_type is value_spec syntax is eq or neq value1 value2 See Using wildcards on page 56 for information about using a wildcard character when valuen is a character string See Specifying filters on ob...

Page 70: ...other filter The result is that less that specified top n values are returned Using wildcards The wildcard character is the percent sign When filter_type is eq or neq you can use the wildcard in value...

Page 71: ...posite filter value for an Object Name data item a Database Name data item also must be present in the view If owner_name is a subcomponent of any composite filter value for an Object Name data item a...

Page 72: ...tart_time specifies the date and time of the beginning of the recorded data to be played back The default is to start playback from the beginning of the first session specified by session_id Use the v...

Page 73: ...ight saving time MST U S mountain time zone standard time MDT U S mountain time zone daylight saving time PST U S Pacific time zone standard time PDT U S Pacific time zone daylight saving time MET Mid...

Page 74: ...e recorded data to be played back The default is to end playback at the end of the last session specified Use the value NULL to accept the default The format of this parameter is the same as the start...

Page 75: ...first and last samples when the recording session times do not align with the requested playback session times Choose this option to add or change certain data items when summarization is not required...

Page 76: ...Choose this option to summarize data into any desired granularity This type of summary can mediate deviations in activity and is useful for observing trends over time The actual entire and user defin...

Page 77: ...back to estimate values if necessary for certain data items Some data items cannot be included in a playback view unless you allow estimation in the playback session This parameter is ignored if summa...

Page 78: ...Server home directory The operating system account that started Historical Server must have execute search and write permission on the specified directory protection_level when target is file this par...

Page 79: ...sSessionId where s is a constant SessionId is assigned by Historical Server null implements the default value no_script When target is client this parameter is ignored delete_option when target is fil...

Page 80: ...Examples 1 This example creates a playback session based on a single input session session 7 The session is played back in its entirety with no summarization or normalization and without creating a ne...

Page 81: ...data from 9 a m to 5 p m every day from Monday through Friday you could not play back those five recording sessions to create a new summarized weekly session However if you eliminate the time gaps by...

Page 82: ...ta items then the view is defined using all of the data items from the corresponding view in the input sessions However if a data item from that corresponding view is not valid in the playback view an...

Page 83: ...ited Parameters monServerName name of the Monitor Server used to collect data from the Adaptive Server for which you want to collect historical data The user name and password you used to connect to H...

Page 84: ...tain time zone standard time MDT U S mountain time zone daylight saving time PST U S Pacific time zone standard time PDT U S Pacific time zone daylight saving time MET Middle European time zone standa...

Page 85: ...es that recording is to continue when non fatal errors are detected For example when the Monitor Server fills all of its configured buffers while summarizing monitoring information one or more non fat...

Page 86: ...s of the value of error_option You must issue this command before you define any views alarms or filters for the recording session After you have issued this command and have defined at least one view...

Page 87: ...the view You must define at least one view for each recording session If you intend to use the Historical Server playback features to examine data in a recording session you might want to think about...

Page 88: ...ve recording session files are deleted Files associated with active recording sessions are not deleted even if their session IDs fall within the range specified in the hs_delete command An inactive re...

Page 89: ...ding Usage The hs_create_recording_session command and one or more hs_create_view commands must precede the hs_initiate_recording command One or more hs_create_alarm and hs_create_filter commands may...

Page 90: ...ssions latest selects only the recording session if any most recently initiated on the current client connection If restriction is omitted all active and inactive recording sessions that you have perm...

Page 91: ...he session active inactive active remotely or inactive status of the session cannot be determined unambiguously Sessions that are currently active in another instance of Historical Server that is usin...

Page 92: ...D View name Data item Data item statistic type Number of alarms defined for the data item Number of filters defined for the data item One of the following keywords recorded indicates that the data is...

Page 93: ...e following columns Session ID View name Data item Statistic type One of the following keywords recorded indicates that the data for this data item if played back would be the data from the original r...

Page 94: ...al entire or a user specified interval and if any view in the playback session contains a data item with a statistic type of Value for Session or Rate for Session then the only permissible value for s...

Page 95: ...See Views with Process ID on page 81 for more explanation about when a Process ID is returned Views that contain keys other than those listed in the previous item return rows even when there is no ac...

Page 96: ...gaps are allowed in the specified input sessions so there will not be gaps in the playback session Gaps in available data might occur when The input sessions specified for the playback session are no...

Page 97: ...be represented by 32 empty samples if summarization_level is entire gaps are ignored hs_shutdown Description Shuts down Historical Server Syntax hs_shutdown wait_option Parameters wait_option specifi...

Page 98: ...ignals at any time including while waiting for the hs_shutdown command to complete is equivalent to issuing the hs_shutdown no_wait command Warning Sybase strongly recommends that you do not use the k...

Page 99: ...ivity displays the following information about the current level of activity on Historical Server The activity information is relevant when stopping Historical Server because Historical Server does no...

Page 100: ...st issue hs_terminate_playback before you can start defining any additional playback sessions or recording sessions Unlike a recording session definition playback session definitions are not stored in...

Page 101: ...terminate_recording 5 delete Usage If you initiate a recording you are the session owner To terminate a recording session you must be the session owner or the Historical Server superuser The hs_termin...

Page 102: ...hs_terminate_recording 88 Historical Server...

Page 103: ...s for the same Historical Server use the same control file For information about multiple Historical Server instances and control files see Configuring multiple instances of Historical Server on page...

Page 104: ...ed columns from the file Permissions on files All of the historical monitoring data files for a given recording session are stored in the same directory This directory is by default the directory spec...

Page 105: ...the other files created by Historical Server the control file is not a standard format Windows NT text file Lines of text in the control file are terminated only with new line characters rather than t...

Page 106: ...rarchical sequential order in the control file where the hierarchy of record types in top down order is Session control View control Data item control Alarm control and filter control For example a se...

Page 107: ...es might differ slightly from the start_time and end_time parameter values specified in the command For example start_time might be scheduled as 1 p m but because of a busy system the session might ac...

Page 108: ...een the data item control records as appropriate Data item control record The data item control record contains Record identifier the word dataitem Data item name Statistic type In sessions created fr...

Page 109: ...by the hs_create_filter command One filter control record exists for each filter that was applied to the current data item Data file A data file contains the data for a single view recorded during a...

Page 110: ...s This format is acceptable to the Bulk Copy utility provided that the columns into which the data items are being copied will accept nulls Error message file Any error messages that are received whil...

Page 111: ...viewname where s is a constant SessionId is the unique identifier for the session viewnumber is a number greater than or equal to one that reflects the order in which views were defined for the sessio...

Page 112: ...y example see Bulk copy example on page 99 Script use example Use the following isql command to connect to the Adaptive Server in which you want to store the recorded monitoring data isql Uusername Pp...

Page 113: ...ables by having the Historical Server create a script file for you Use the script_type parameter of the hs_create_recording_session command After you create the tables on Adaptive Server you can use t...

Page 114: ...Server You can use the following steps to record the data create a table to accept the data and then use the bcp utility to import the data into the table In this example the view data file that is c...

Page 115: ...flist d view_data_file output_file_name where list is a comma separated list of the field numbers that you want to cut and save from the original view data file The first field has field number 1 view...

Page 116: ...d cut f1 2 5 d d1_1_device_io d1_1_device io new where field 1 is the date and time of the sample 2 is the second field which is the device name and 5 is the fifth field which is the device I O value...

Page 117: ...ithout consideration for specific logins That is an execution class is configured for the application with null scope Execution class name followed by asterisk in addition to the execution class confi...

Page 118: ...11 0 and later The ID of a data cache in Adaptive Server version 11 0 or later Particular database tables and indexes may be bound to a specific data cache or all objects in a database may be bound to...

Page 119: ...ve otherwise prefetching is not providing much benefit This may suggest that a buffer pool should be eliminated or it may imply that a clustered index on some table is fragmented and that the index sh...

Page 120: ...nning a given application were in the Running state over the time all processes were in the Running state Type Result CPU Time Version 11 0 and later If used in a view with no keys this data item is t...

Page 121: ...her Adaptive Server version 11 5 and later is saving the SQL text of the currently executing query batches and if so how much Value of 0 saving SQL text disabled Value of 1 or more maximum number of b...

Page 122: ...SQL statement is in the query batch if both Current Stmt Procedure Database ID is equal to 0 and Current Stmt Procedure ID is equal 0 Otherwise the currently executing SQL statement is in the stored...

Page 123: ...ting SQL statement for a particular process If the currently executing SQL statement is not contained in a stored procedure this name is NoOwner Type Result Current Stmt Procedure Text Version 11 5 an...

Page 124: ...Number of deadlocks Type Result Demand Lock Version 11 0 and later A character string Y or N that indicates whether or not a lock that has been upgraded to demand lock status Type Result Device Hit P...

Page 125: ...rnel Structures Memory Size Version 11 0 and later Amount of memory in bytes allocated to the kernel structures Type Result Large I O Denied Version 11 0 and later The number of times the buffer manag...

Page 126: ...condition Type Key Lock Results Summarized Version 11 0 and later Lock results summarized at a granted or not granted level 1 the lock result summary granted is composed of the lock results granted no...

Page 127: ...tely without having to wait for another lock to be released Type Result Locks Granted After Wait Version 11 5 and later Number of locks that were granted after waiting for another lock to be released...

Page 128: ...er Total number of network bytes sent and received Type Result Net Max Packet Size Version 11 0 and later Configured maximum size for a network packet Type Result Net Packet Size Received Version 11 0...

Page 129: ...in bytes allocated for the page cache Type Result Page Hit Percent Version 11 0 and later Percentage of times that a data page read could be satisfied from cache without requiring a physical page rea...

Page 130: ...es a stored procedure or a line in a stored procedure was executed Type Result Procedure Header Size Version 11 0 and later Amount of memory in bytes allocated for the procedure header Type Result Pro...

Page 131: ...ess state 0 None 1 alarm sleep Waiting on an alarm 2 background Adaptive Server process executing 3 bad status Undetermined error condition 4 infected Tagged by Adaptive Server as unprocessable 5 lock...

Page 132: ...later Updates that require two steps to complete First records for deleting the existing entry and inserting the new entry are written to the log but only the deletes are actually performed on the dat...

Page 133: ...de worker thread pool in Adaptive Server version 11 5 and later Type Result Thread Max Used Version 11 5 and later The maximum number of threads from the server wide worker thread pool that were concu...

Page 134: ...efinitions 120 Historical Server Transactions Version 11 0 and later Total number of committed Transact SQL statement blocks delimited by a begin transaction and commit transaction statement Type Resu...

Page 135: ...ical Server returns You can combine key data items within a view to narrow the scope of the returned data With the inclusion of each successive key envision adding the word per to a view definition Fo...

Page 136: ...uaranteed to uniquely identify a process The Kernel Process ID data item however uniquely identifies a process View definition Results Page I O Since there are no keys in the view the result is page I...

Page 137: ...o be included to ensure uniqueness of the key Otherwise the view could erroneously summarize two different processes Using the application name Historical Server accumulates performance data per appli...

Page 138: ...equired keys listed for the result data item The view can include the optional keys listed for the result data item However not all of the optional keys listed for a data item are guaranteed to work t...

Page 139: ...Efficiency Valid at server level No Required keys Cache ID Cache Hit Pct Valid at server level No Required keys Cache ID Cache Hits Valid at server level No Required keys Cache ID Cache ID KEY 4 Cache...

Page 140: ...ys Cache ID Cache Reuse Dirty Valid at server level Yes Required keys None Optional keys Cache ID Cache Size Valid at server level No Required keys Cache ID Cache Spinlock Contention Valid at server l...

Page 141: ...s ID or Application Name Optional keys Engine Number CPU Time3 Valid at server level Yes Required keys None Optional keys Process ID or Application Name Engine Number CPU Yields Valid at server level...

Page 142: ...rent Stmt Batch Text2 Valid at server level No Required keys Process ID Current Stmt Batch Text Byte Offset2 Valid at server level No Required keys Process ID Current Stmt Batch Text Enabled2 Valid at...

Page 143: ...red keys Process ID Current Stmt Locks Not Granted2 Valid at server level No Required keys Process ID Current Stmt Logical Reads Valid at server level No Required keys Process ID Current Stmt Number 2...

Page 144: ...s ID Current Stmt Procedure Name2 Valid at server level No Required keys Process ID Current Stmt Procedure Owner Name2 Valid at server level No Required keys Process ID Current Stmt Procedure Text2 Va...

Page 145: ...at server level Yes Required keys None Demand Lock Valid at server level No Required keys Process ID Database ID Object ID Lock Status Page Number Optional keys Lock Type Device Hit Percent Valid at...

Page 146: ...uired keys None Optional keys Device Name Device Name KEY Device Reads Valid at server level Yes Required keys None Optional keys Device Name Device Writes Valid at server level Yes Required keys None...

Page 147: ...y combination Procedure Database ID Procedure ID Index Physical Reads Valid at server level Yes Required keys None Optional keys Process ID Database ID Object ID Engine Number Key combination Procedur...

Page 148: ...ested Valid at server level No Required keys Cache ID Lock Count Valid at server level Yes Required keys None Optional keys Process ID Lock Type Lock Result Lock Results Summarized Lock Hit Percent Va...

Page 149: ...r Wait3 Valid at server level Yes Required keys None Optional keys Process ID or Application Name Key Combination Database ID Object ID Key Combination Procedure Database ID Procedure ID Locks Granted...

Page 150: ...ct ID Key Combination Procedure Database ID Procedure ID Log Contention Percent Valid at server level Yes Required keys None Logical Page Reads Valid at server level Yes Required keys None Optional ke...

Page 151: ...evel Yes Required keys None Net Bytes Sent Valid at server level Yes Required keys None Net Default Packet Size Valid at server level Yes Required keys None Net I O Bytes Valid at server level Yes Req...

Page 152: ...nes Valid at server level Yes Required keys None Number of Processes2 Valid at server level Yes Required keys None Optional keys Application Name Object ID KEY Object Name1 Valid at server level No Re...

Page 153: ...Required keys None Optional keys Process ID Engine Number Key combination Database ID Object ID Key combination Procedure Database ID Procedure ID Page I O Valid at server level Yes Required keys Non...

Page 154: ...bination Database ID Object ID Key combination Procedure Database ID Procedure ID Physical Page Reads Valid at server level Yes Required keys None Optional keys Process ID Engine Number Key combinatio...

Page 155: ...Line Number Procedure Database ID KEY Procedure Database Name1 Valid at server level No Required keys Procedure Database ID Procedure Elapsed Time Valid at server level No Required keys Procedure Dat...

Page 156: ...eader Size Valid at server level Yes Required keys None Procedure Hit Percent Valid at server level Yes Required keys None Procedure ID KEY Procedure Line Number KEY Procedure Line Text Valid at serve...

Page 157: ...keys None Procedure Statement Number KEY Process ID KEY 3 Process State KEY Process State Count Valid at server level No Required keys Process State Rows Deleted Valid at server level Yes Required ke...

Page 158: ...red keys None Rows Updated Deferred Valid at server level Yes Required keys None Rows Updated Direct Valid at server level Yes Required keys None Rows Updated Expensive Valid at server level Yes Requi...

Page 159: ...n Valid at server level Yes Required keys None Thread Exceeded Max2 Valid at server level Yes Required keys None Thread Exceeded Max Percent2 Valid at server level Yes Required keys None Thread Max Us...

Page 160: ...infer names from IDs or status types They are valid in any view containing the required keys regardless of the other keys that might also be in the view For example Database Name is valid in any view...

Page 161: ...ms but keys are required for Current Engine and Login Name Table of valid statistic types for data items There are six valid statistic types Value for Sample Value for Session Rate for Sample Rate for...

Page 162: ...ficiency double double Cache Hit Pct double double Cache Hits long long double double Cache ID long Cache Misses long long double double Cache Name charp Cache Prefetch Efficiency double double Cache...

Page 163: ...long Current Stmt Batch Text Enabled long Current Stmt Context ID long Current Stmt CPU Time double double Current Stmt Elapsed Time double double Current Stmt Line Number long Current Stmt Locks Gra...

Page 164: ...double double Current Stmt Procedure Database ID long Current Stmt Procedure Database Name charp Current Stmt Procedure ID long Current Stmt Procedure Name charp Current Stmt Procedure Owner Name char...

Page 165: ...uble Device Writes long long double double Elapsed Time long long Engine Number long Host Name charp Index Logical Reads long long double double Index Physical Reads long long double double Kernel Pro...

Page 166: ...diately long long double double Locks Not Granted long long double double Log Contention Percent double double Logical Page Reads long long double double Login Name charp Most Active Device I O long l...

Page 167: ...of Processes long Object ID long Object Name charp Object Type ENUMS Owner Name charp Page Cache Size long Page Hit Percent double double Page I O long long double double Page Number long Page Writes...

Page 168: ...e Line Number long Procedure Line Text charp Procedure Logical Reads long long double double Procedure Name charp Procedure Owner Name charp Procedure Physical Reads long long double double Procedure...

Page 169: ...eferred long long double double Rows Updated Direct long long double double Rows Updated Expensive long long double double Rows Updated In Place long long double double Rows Updated Not In Place long...

Page 170: ...ceeded Max Percent double double Thread Max Used long Time Waited on Lock long Timestamp charp Timestamp Datim datim Transactions long long double double Statistic type data item Value for sample Valu...

Page 171: ...lay back with the same summarization level You define the summarization level of playback using the summarization_level parameter to the hs_create_playback_session command The summarization levels are...

Page 172: ...ording session has 10 minute samples with one starting at 2 55 p m the first sample in the playback is the one that starts at 3 05 p m If playback start_time is later than the input recording session...

Page 173: ...put recording session started at 1 p m but the playback start_time is 3 p m the data item Device I O Total for Session would be a prorated value for I O since 3 p m Entire playback This option plays b...

Page 174: ...S D H M S where S is seconds M is minutes H is hours D is days All components are numeric and can be one or two digits Some examples are 30 specifies sample intervals of 30 seconds 10 0 specifies sam...

Page 175: ...C 1 summarizes the playback features offered by each of the summarization_interval values Table C 1 Summarization interval features Feature Raw Actual Entire Defined interval All recorded data items...

Page 176: ...s by collecting data from 9 a m to 9 a m every day from Monday through Friday you could use the playback feature to create a new summarized weekly session Another way to eliminate the time gaps is to...

Page 177: ...d statistic type for a key is Value for Sample The result data items in the input view are optional in the playback view The statistic types for all data items in the playback view must be the same as...

Page 178: ...iew The statistic type for the data item must be Value for Sample in the input view and The playback session must be defined to allow estimations In the hs_create_playback_session command the allow_es...

Page 179: ...mated Cache Hit Pct Valid Calculated from Cache Hits Cache Misses Cache Hits Valid Full Cache ID KEY 1 Cache Misses Valid Full Cache Name1 Valid Value for Sample Cache Prefetch Efficiency Valid Estima...

Page 180: ...Physical Reads No Current Stmt Procedure Database ID No Current Stmt Procedure Database Name No Current Stmt Procedure ID No Current Stmt Procedure Name No Current Stmt Procedure Owner Name No Curren...

Page 181: ...ple Lock Results Summarized KEY Valid Value for Sample Lock Status KEY Valid Value for Sample Lock Status Count No Lock Type KEY Valid Value for Sample Locks Being Blocked Count No Locks Granted Immed...

Page 182: ...Sample Page Cache Size Valid Value for Sample Page Hit Percent Valid Calculated from Logical Reads Physical Reads Page I O Valid Full Page Number KEY Valid Value for Sample Page Writes Valid Full Phys...

Page 183: ...rocess State KEY Valid Value for Sample Process State Count No Rows Deleted Valid Full Rows Deleted Deferred Valid Full Rows Deleted Direct Valid Full Rows Inserted Valid Full Rows Inserted Clustered...

Page 184: ...mestamp and Timestamp Datim values are the same as those in the original data file which is the time as reported by Adaptive Server when the recording was originally made When summarization_level is a...

Page 185: ...n the session s summarization_level parameter is actual entire or user defined intervals These views return rows for all server processes included in any of the input views However since the Process I...

Page 186: ...ecution Count does not exist in the input view and Procedure Elapsed Time exists in the input view with the statistic type Avg for Sample and The playback session allows estimations That is in the hs_...

Page 187: ...nt because the data is sorted according to the key fields The first key field that appears in a view s definition acts as the primary sort key the second key field is the secondary sort key and so on...

Page 188: ...it Percent Value for Sample Device I O performance summary 179 Engine activity 179 Lock performance summary 179 Network activity for recording session 180 Network activity for sample interval 180 Netw...

Page 189: ...dy holds the lock are shown If instead the process already holds the lock the count of other processes waiting to acquire that lock is shown hs_create_view object_lock_status Database ID Value for Sam...

Page 190: ...ch named cache configured in the Adaptive Server and also for the default data cache this view collects the cache s name and the percentage of page reads for objects bound to the cache that were satis...

Page 191: ...ical Page Reads Value for Session Physical Page Reads Rate for Session Page Writes Value for Session Page Writes Rate for Session Data cache statistics for sample interval This view represents the ove...

Page 192: ...for Session Device I O Value for Session Device Reads Rate for Session Device Writes Rate for Session Device I O Rate for Session Device I O for sample interval This view represents the I O activity t...

Page 193: ...ws the level of activity for each active Adaptive Server engine during the most recent sample interval For each engine the percentage of the sample interval when that engine used the CPU is presented...

Page 194: ...alue for Session Net Packets Sent Rate for Session Net Packets Received Rate for Session Net Bytes Sent Value for Session Net Bytes Received Value for Session Net Bytes Sent Rate for Session Net Bytes...

Page 195: ...O This view enables you to determine which tables experienced the highest number of page reads over the duration of the recording session It also allows you to track activity against tables at differe...

Page 196: ...tored procedures since the start of the session hs_create_view session_procedure_cache_stats Procedure Hit Percent Value for Session Procedure Logical Reads Value for Session Procedure Logical Reads R...

Page 197: ...he Adaptive Server data caches and the number of logical page reads physical page reads and page writes generated while executing the stored procedures during the most recent sample interval hs_create...

Page 198: ...w also shows the object name object ID and the object s database name and ID plus the page I Os associated the object The view also shows the total page I Os the percentage of page I O requests that c...

Page 199: ...rocesses waiting to acquire that lock is shown hs_create_view process_detail_locks Login Name Value for Sample Process ID Value for Sample Kernel Process ID Value for Sample Database Name Value for Sa...

Page 200: ...he most recent sample interval hs_create_view process_lock Login Name Value for Sample Process ID Value for Sample Kernel Process ID Value for Sample Lock Count Value for Sample Process page I O This...

Page 201: ...and Kernel Process ID for each process that generated page I Os during the sample interval For each process and stored procedure that generated page I Os it shows the name and ID of the database that...

Page 202: ...Each statement of any stored procedure that was executed during the most recent sample interval is identified by the name and ID of the database that contains the procedure the name and ID of the proc...

Page 203: ...s Deleted Value for Sample Rows Inserted Value for Sample Rows Updated Value for Sample Rows Updated Directly Value for Sample Transactions Value for Session Rows Deleted Value for Session Rows Insert...

Page 204: ...Transaction activity 190 Historical Server...

Page 205: ...views 164 charsets directory 34 client connections See connections client playback 64 79 commands See also utilities histserver 13 20 33 histsrvr 20 31 37 hs_create_alarm 4 51 75 hs_create_filter 4 54...

Page 206: ...4 89 format of 95 location of 69 90 owner of 90 permissions on 10 11 24 71 90 data item control record 94 data items definitions of 103 procedure CPU time 171 procedure elapsed time 171 Process ID 171...

Page 207: ...5 97 hs_create_view command 4 72 75 hs_delete_data command 73 hs_initiate_playback command 5 64 65 74 hs_initiate_recording command 4 49 74 hs_list command 4 10 23 66 68 75 91 hs_playback_sample comma...

Page 208: ...iles 90 P P parameter 22 35 38 parameters D 10 21 69 90 i 21 l 21 34 n 22 P 22 35 38 S 22 47 start up 20 22 U 11 22 35 38 v 22 password in start up command 22 prompting for 22 permissions control file...

Page 209: ...1 for creating tables 65 71 for creating tables in Adaptive Server 97 for start up 12 server configuration utility 25 server level views 124 services list 27 session control record 92 session ID 4 ses...

Page 210: ...torical Server on 12 starting Historical Server 33 stopping on 34 user name 22 user defined sample intervals 160 utilities bcp 90 cut 101 dscp 13 dsedit 13 17 ocscfg 17 server configuration 25 V v par...