Page History

...

The Activity tab was designed to answer questions such as, "What happened yesterday, as well as a month ago and a year ago?" and "What has been the performance level of my applications over time.".

Data in the Activity tab is available up to the last time slice that ended (information on the last minute is available in the Current tab). The Activity tab is usually the place to identify and investigate tuning problems.

...

The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. For example, you can focus on an In MS-SQL overtime graph for an Instance entity to observe its resource consumption over time or you can focus on the Locking view to investigate all the locks performed on the instance.

See “About About drilling down in context” on page 27, “About context, About the Precise bar” on page 24, “About bar, About the Main area” on page 25, “About area, About the Association area” on page 25area, and “Focusing Focusing on information in overtime graphs” on page 32graphs.

About navigating in the Activity tab

...

For example, when you want to drill down to an associated entity, such as a specific program, to view additional information, choose Programs from the Association controls. Note that the Tab heading and the Main area remain unchanged. Click the row of the program you want to view detailed information for. The Tab heading indicates the newly selected program; the Main area displays In MS-SQL information on the program you drilled down to, and the Association area shows information on statements executed by this program.

See “About About drilling down in context” on page 27, “About context, About the Precise bar” on page 24, “About bar, About the Main area” on page 25, “About area, About the Association area” on page 25, “Focusing area, and Focusing on information in overtime graphs” on page 32. See “How most tabs are structured” on page 23, “Tasks common to most tabs” on page 28, and “About the Dashboard tab” on page 46graphs.

Anchor
Abouttheentitiesyoucanexamine Abouttheentitiesyoucanexamine

About the entities you can examine

...

• Buffer Pool
• Latch
• Parallel
• Distributed Transaction Coordinator (DTC)
• DB Mirror
• Profiler
• Memory
• Backup
• Other Internal waits

See “About internal waits” on page 37 About internal waits.

About viewing Instance Grouping for All Instances

...

For an explanation on how to define instance groups, see the Precise Administration Guide.

About Instance and Collector entities

...

See “Precise Precise for SQL Server agents” on page 19agents.

The following views are available for the Instance and Collector entities:

...

• Load. Displays information on the following session-related items, as follows:
  • Sessions. Number of sessions that occurred during the specified time period of the selected collector entity. For example, if you are viewing the Login entity, this value represents the number of sessions that are connected to the selected Login.
  • Duration (Avg). Average duration for a single session, in the selected collector entity, during the selected time period, calculated by dividing the Duration by the number of Sessions.
  • Duration (Summed). Total amount of time that elapsed for all of the selected collector entity's sessions, during the selected time period.
  • Statement Executions. Number of statements executed within the selected time period that were generated by the selected collector entity's sessions.
  • Statement Duration (Avg). Average duration for a single statement execution, in the selected collector entity, during the selected time period. This value is calculated by dividing the Duration (Summed) parameter by the number of statement executions.
• Statistics. Displays the following session-related statistics, as follows:
  • CPU Time. Cumulative CPU time for the collector entity or instance, during the selected time period. This counter is not accurate when SQL Server is configured to use fiber mode (lightweight pooling option).
  • Physical I/O Operations. Total number of physical I/O operations performed by all sessions connected to the database during the selected time period.
• In MS-SQL breakdown. Displays collector entity resource consumption (table format) and shows instance breakdown, within the selected time period, as follows:
  • State. Displays the In MS-SQL state.
  • In MS-SQL. Displays a graphical representation of the Time column.
  • Time. Amount of time the selected collector entity was in this state.
  • %. Percentage of time the selected collector entity was in this state.
    See “About session states” on page 36 About session states.

About viewing Instance and Collector In MS-SQL data

The In MS-SQL view displays the resource consumption of the selected collector entity over the selected time period. This graph enables you to analyze performance trends for the collector entity, over time.

See “About session states” on page 36 and “Focusing See About session states and Focusing on information in overtime graphs” on page 32graphs.

About viewing Instance and Collector locking data

...

• Lock wait graph. The Lock Wait graph shows a breakdown of the Lock Waits, and when they occurred, over the selected time period.

See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About the lock tree structure

...

• Waitfor Command. The amount of time applications were waiting because they were waiting for command.
• Parallel Exchange Wait. The amount of time the thread in a parallel session is waiting for data exchange from another thread.
• Request Wait. The amount of time applications were in a request wait state, defined as the state wherein the server is waiting for a client request.
• In MS-SQL. Represents total amount of time spent in SQL Server. See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About viewing Instance and Collector Scalability

...

• Sessions duration (Avg) vs. sessions. Compares the average duration of the selected collector entity with the number of sessions, over the selected time period. For example, if the selected collector entity is Login the graph will show the number of sessions connected with the selected login versus the average duration of each session over time. Use this graph to analyze if the average session duration increases when the number of sessions increase.
• I/O wait vs. physical I/O Operations. Compares the amount of time the collector entity waited for the I/O to be completed with the actual physical I/O requests performed on behalf of the collector entity. Use this graph to analyze how an increase of physical I/O requests affects the I/O wait of the collector entity.

See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About viewing Instance and Collector System Scalability

...

• In MS-SQL vs. SQL server CPU usage (%). Compares the In MS-SQL of the selected collector entity with SQL Server CPU usage over the selected time period. For example, if the selected collector entity is Login the graph will show the In MS-SQL time consumed by the selected login and SQL Server CPU utilization over time. Use this graph to evaluate the correlation between collector entity performance and the SQL Server CPU usage. For example, you can determine if high CPU utilization is a result of collector entity's activity.
• Physical I/O Operations vs. queue length (Avg). Compares the physical I/O requests performed on behalf of the collector entity with the average queue length of the available disks over the selected time period. Since an increase in queue length affects application performance, this graph enables you to analyze how an increase in physical I/O requests affects queue length.

See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About viewing Instance and Collector Availability

...

• Availability. Displays the percentage of time the SQL Server was available (including planned down time; for more information see the Precise Administration Guide).
• Unavailability times. Displays how long the SQL Server was down. If the Precise for SQL Server Collector was down, the status will be labeled "Unknown Time".
• Log. Provides general information regarding each period that the SQL Server was down.
  Provide information on the status of SQL Server instance, over the selected time period. If the instance was down or if the status was unknown (the Collector was down) during the selected time period, only information on the unavailability of the instance is displayed (time range of the unavailability period).

See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About the Instance and Collector entities Association area

...

The In MS-SQL tab contains the In MS-SQL counter and the breakdown of the In MS-SQL into its states. The In MS-SQL is displayed with a normalized view while all the other counters display the time each state was consumed. See “About session states” on page 36See About session states.

About viewing procedure cache data for Instance and Collector entities

...

• Statistics. Displays the following statement-related statistics:
  • CPU Time. Cumulative CPU time for the entity during the selected time period. This value is calculated by the Collector as follows (this value is an approximation)—the CPU Time field in sysprocesses is sampled at the start point and end point. The value sampled at the start point is subtracted from the value sampled at the end point resulting in the CPU Time reported. The start and end points are determined according to values that have changed. For example, a statement is sampled by the Collector for five seconds, but the start and end points were calculated according to when a value has changed. As a result, the calculated CPU Time in this example is only three out of the sampled five seconds since this is takes into consideration when the value has changed.
  • Physical I/O Operations. Total number of physical I/O requests during statement execution, within the selected time period.
  • Parallel Sessions (Min). Certain statements are executed using parallelisms. This counter displays the minimum number of threads used to execute the statement and is calculated by examining the ecid column in sysprocesses. Check this counter to verify that the SQL Server is implementing the best execution plan for the statement.
  • Parallel Sessions (Max). Certain statements are executed using parallelisms. This counter displays the maximum number of threads used to execute the statement and is calculated by examining the ecid column in sysprocesses. Check this counter to verify that the SQL Server is implementing the best execution plan for the statement.
• In MS-SQL breakdown. Displays resource consumption broken down into states, such as, Using CPU, I/O Wait, Lock Wait, etc., within the specified time period. This allows you to identify the highest resource consumers.
• Text. Displays statement or batch text (formatted for easy readability).
  When viewing a batch entity, the entire text of the adhoc ad-hoc batch or stored object is displayed.
  When viewing a statement, only the part of the batch that generates the statement is displayed.
  When viewing a collapsed statement or batch, the literals are replaced with parameter markers.

...

The In MS-SQL view displays the resource consumption of the selected entity over the selected time period. This graph enables you to analyze performance trends for the entity, over time.

See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About viewing Statement, Collapsed Statement, Batch and Collapsed Batch entities locking

...

• Locks. Displays which sessions were involved in a blocking situation (either locking or blocking). It is important to understand which sessions are involved in each blocking chain, as there may be several sessions involved at the same time. It is then important to determine which session is blocking others, which object is being blocked, and what type of lock has been applied.
  A yellow lock indicates that the session is currently blocking other sessions but is not itself blocked.
  A red lock indicates the session is currently blocked by another session. Note that a session may also block other sessions while being blocked by a different session.
• Lock wait graph. The Lock Wait graph shows a breakdown of the Lock Waits, and when they occurred, over the selected time period.

See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About the lock tree structure

...

• Physical I/O Operations vs. queue length (Avg). Compares the physical I/O requests performed on behalf of the entity with the average queue length of the available disks. Since an increase in queue length affects application performance, this graph enables you to analyze how an increase in physical I/O requests affects queue length.

See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About the Statement and Batch entities Association area

...

See “About session states” on page 36 and “Focusing See About session states and Focusing on information in overtime graphs” on page 32graphs.

About viewing Session and Blocker Session Locking

...

Every time a change occurs, a new lock tree is opened, as for example, when a new session enters into the lock. It is therefore possible that there will be sub-trees of several locks that may appear identical. See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About viewing internal waits for session and blocker session entities

...

See “Focusing Focusing on information in overtime graphs” on page 32graphs.

About the Session and Blocker Session entities Association area

...

See “About session states” on page 36See About session states.

About the Locked Object entity

...