This section includes the following topics:
The Dashboard tab provides an overview (approx. 70-80%) of the information displayed in other Precise for SQL Server tabs. The Dashboard is the starting point for your Precise analysis since it displays an overview of all the instances available in the selected application and information that is also available in some of the other tabs.
The Dashboard tab displays availability, performance findings, statistics and resource consumption of all SQL Server instances for the selected time period. The Current view in this tab can be used to monitor an instance's current state, display a list of its active sessions, and its resource consumption during the last minute. In addition you can view statistics, resource consumption and top resource consumers, for the selected time frame.
The Dashboard tab also displays information from the SmarTune tab. SmarTune can provide you with expert knowledge regarding a specific area of deterioration, and comprehensive advice on how to solve a specific problem and optimize performance. This is an important tab to begin with if you want to start performance tuning on a database.
The Dashboard tab provides answers to the following types of questions:
The All row in the All SQL Server Instances table located in the Main area (left pane) displays the average availability of all instances. When you select the All row a summary of the activities of all the instances and a comparison of application identifiers is displayed in the various tab pages viewed in the Instance Details area (right pane).
In general, launching to a tab from the All row focuses in on the Tier entity, which summarizes the data collected for all instances. Tabs that do not have an Tier entity, open on the default instance.
If you are working with SAP, PeopleSoft, or Siebel, and ERP support is installed, the Dashboard tab displays information on SQL Server activity in relation to the ERP application and users, and specific information on executed views and transactions. |
See About Precise for SQL Server tabs, About the Current tab, About the Activity tab, About the Objects tab, About the Statistics tab, About the SQL tab, About the Maintenance tab, and About the SmarTune tab.
The Dashboard tab is divided into two areas—the Main area and the Instance Details area. The Main area lists all the instances that are monitored by the Precise for SQL Server Tier. The Instance Details area provides comparative information regarding the selected instance.
The information displayed in the Dashboard tab is influenced by the time frame you selected. See Selecting a time frame.
The instance table displayed in the Main area lists all the instances that are monitored by the Precise for SQL Server Tier. Each row corresponds to an instance. The All row displays the average availability (%) of all instances. The following table shows the information displayed in the Instance table.
Table 1 Instance table
Column | Description |
---|---|
Icon | Indicates whether or not the instance is available. |
Instance | Name of the instance. |
In MS-SQL | Breakdown of In MS-SQL duration of the instance. |
Executions | Total statements executions. |
Availability | Average percentage of time the instance was available. |
Clicking on the various tabs in this area allows you to display overall activity, top performance findings, active sessions, current and statistical information for the selected instance. Clicking on the hyperlink for specific data items launches, in context, to the related tab from which the data was derived. See Configuring Findings settings.
The following table describes the information displayed in the Instance Details area.
Table 2 Information displayed in the Instance Details area
Tab | Information Displayed | |
---|---|---|
Overview | The Overview tab displays In MS-SQL data and performance findings for the selected instance. | |
Applications | The Applications tab displays the top resource-consuming databases, programs and machines. | |
More | The following options are available in the More tab:
|
If you select the All row, the information displayed reflects the average availability of all instances. The "Current" option and the Findings table will not be available. |
The Overview tab displays In MS-SQL data and performance findings for the selected instance. You can continue investigating a problem as follows:
The following table describes the information displayed in the Overview tab.
Table 3 General instance data
General Instance Data | Description |
---|---|
In MS-SQL | Displays a breakdown of the total In MS-SQL duration over time, into states such as, Using CPU, I/O wait, and Lock Wait. |
Findings | Lists all the statements, collapsed statements, tables or instance events that were detected for a specific instance. All problems are ranked by severity. The information displayed in the findings table is also displayed in the SmarTune tab. |
Details for ... Finding | Provides quick links to additional tabs and views that would be helpful in your analysis of a particular problem. |
The Applications tab displays the top resource-consuming databases, programs and machines. All entities in this display launch the Activity tab, in context of the instance.
The following table describes the information displayed in the Databases table.
Table 4 Database applications
Column | Description |
---|---|
Database | The name of the top resource-consuming databases. |
Sessions | Total number of sessions opened. |
In MS-SQL | The resource consumption of the database broken down into states such as, Using CPU, I/O wait, Lock wait states. Click on the respective icons to display the data in this column in a stacked graph, bar graph or numerical format. |
The following table describes the information displayed in the Programs table.
Table 5 Program applications
Column | Description |
---|---|
Program | The name of the top resource-consuming programs. |
Sessions | Total number of sessions opened. |
In MS-SQL | The resource consumption of the program broken down into states such as, Using CPU, I/O wait, Lock wait states. Click on the respective icons to display the data in this column in a stacked graph, bar graph or numerical format. |
The following table describes the information displayed in the Machines table.
Table 6 Machine applications
Column | Description |
---|---|
Machine | The name of the top resource-consuming machines. |
Sessions | Total number of sessions opened. |
In MS-SQL | The resource consumption of the machine broken down into states such as, Using CPU, I/O wait, Lock wait states. Click on the respective icons to display the data in this column in a stacked graph, bar graph or numerical format. |
The Statistics tab, accessed from the More tab, displays statistical information for the selected instance, as reported by the SQL Server and collected by the Precise for SQL Server Collector.
All items in this display launch the Statistics tab with an Instance or Precise for SQL Server Tier context. The following table describes the information displayed in the Statistics tab.
Table 7 Instance statistical data
Instance Statistical Data | Description |
---|---|
Server CPU | Displays the SQL Server instance CPU utilization for the selected instance with respect to the CPU utilization of other processes (non-SQL Server). |
Load | Displays instance load, for the selected instance, broken down into batch requests/sec, transactions/sec, and logins/sec. |
Page Faults | Displays memory paging of the selected instance vs. memory paging caused by other processes (non-SQL Server). |
The Activities tab, accessed from the More tab, displays the top resource-consuming statements, logical files and storage devices.
All entities in this display launch the Activity tab, in context of an instance or Tier. The following table describes the information displayed in the Statements table.
Table 8 Statement activities
Column | Description |
---|---|
Statement | Displays the ID of the top resource-consuming statements. |
Duration (Avg) | Displays the average duration of the statement. |
In MS-SQL | Displays the resource consumption of the statement broken down into states such as, Using CPU, I/O wait, and Lock wait states. Click on the respective icons to display the data in this column in a stacked graph, bar graph or numerical format. |
The following table describes the information displayed in the Logical Files table.
Table 9 Logical files activities
Column | Description |
---|---|
Database | Displays the name of the file's database. |
Logical File | Displays the logical name of the file. |
I/O Wait | Displays the total I/O waits registered for the file. Click on the respective icons to display the data in this column in a stacked graph, bar graph or numerical format. |
The following table describes the information displayed in the Storage Devices table.
This information is only displayed if storage points are installed. |
Table 10 Storage device activities
Column | Description |
---|---|
Device Type | Indicates a type of storage device, such as, Windows, HP, Symmetrix. |
Unit ID | Displays the ID of the device's unit. |
Device | Displays the name of the device. |
I/O Wait | Displays the total I/O waits registered for the device. Click on the respective icons to display the data in this column in a stacked graph, bar graph or numerical format. |
The Availability tab, accessed from the More tab, displays the average percentage of time the SQL Server instance was available (that is, it met the SLA Availability requirements configured in the SLA settings over the specified time period) and a detailed log of the changes in availability status.
All entities in this display launch the Activity tab, in context of the instance or Tier.
The following table describes the information displayed on instance availability.
Table 11 Instance availability
Item | Description |
---|---|
Availability | The average percentage of time the SQL Server instance was available. |
Log | A detailed log of the changes in availability status. |
The Current tab displays the overall activity and active sessions that occurred within the last minute for the selected instance.
All items in this display launch the Current tab, in context of an instance.
The following table describes the information displayed in the Session Activities area.
Table 12 Sessions
Item | Description |
---|---|
Sessions | Total number of sessions connected to the selected instance. |
Active sessions | Total number of sessions currently active. |
Locked sessions | Total number of locked sessions. |
The following table displays information on the active sessions that occurred within the last minute, for the selected instance.
Table 13 Active sessions (last minute)
Column | Description |
---|---|
Icon | Represents the current session's state (such as, Lock Wait, Request Wait). |
SPID | The session ID. |
Program | The name of the program that opened the session. |
State | The current state of the session. |
In MS-SQL | Total In MS-SQL entities for the session, occurring within the last minute, broken down into states (such as, Using CPU, I/O wait, and Lock Wait). |
The following table describes the information displayed in the Current Activity graphs.A
Table 14 Current activity graphs
Item | Description |
---|---|
Overall activity (Last minute) | Provides a graphical display of the overall activity that occurred within the last minute, for the selected instance, broken down into the following states: Idle Wait, Parallel Exchange Wait, Request Wait and In MS-SQL. |
In MS-SQL (Last minute) | Provides a graphical display of In MS-SQL activity that occurred within the last minute, for the selected instance, broken down into states, such as, Using CPU, I/O wait, and Lock Wait. |
The Findings area in the Dashboard tab displays performance problems detected in the select instance(s). The findings list is displayed according to severity (highest to lowest).
To view the details of a specific finding, hover over the finding name. The area for the selected finding will expand, displaying information and recommended actions. Click on the relevant link to launch the appropriate tab in context, or Learn more to open the help for that finding.
For more information about how you can identify specific performance problems, see How the Dashboard can help you identify performance problems.
You can identify a performance problem by doing one or more of the following:
Precise for SQL Server enables you to determine which are the most resource-consuming instances, indicating a potential performance problem.
To identify the most resource-consuming instance
The Dashboard tab helps you pinpoint performance problems by letting you observe the top problematic findings per instance, statement or object. You can then drill down on a finding and continue your analysis in the SmarTune tab, which provides important guidelines and clues as to what may be causing the performance problem.
To analyze top performance findings per instance
Precise for SQL Server enables you to determine which are the most resource-consuming application components (such as, Databases, Programs, and Statements).
To identify the highest resource consumers in various entities
The entities displayed in the Application tab will differ according to the ERP installed and will take into account that ERP's specific entities. For example, if you have SAP installed, the following types of entities are displayed: Work Types, Logins, SAP Users and Programs, and SAP Transactions. |
You can examine instance statistics over time to confirm that CPU and memory usage are within normal ranges. A continually high CPU usage may indicate the need to improve the application or to add more processes to the machine to enhance performance. In addition, a high number of page faults may indicate an inefficient SQL statement or lack of memory.
To examine instance statistics
Monitoring a session's activity during the last minute in SQL Server, can help you locate activities that deviate from the norm, such as locking and latch waits.
To monitor a session's activity during the last minute
It is possible to examine the availability of SQL Server instances and observe their current status as well as their level of availability over time. In addition, you can determine whether their level of availability meets the SLA standard defined in the SLA availability setting in AdminPoint.
For more information, see the Precise Administration Guide.
To check SQL Server instance availability