Precise for SQL Server basics

This section includes the following topics:

• About Precise for SQL Server tabs
• How most tabs are structured
• About drilling down in context
• About configuring your settings
• Tasks common to most tabs
• About role management in Precise for SQL Server
• Opening your Precise product from the Precise StartPoint

About Precise for SQL Server tabs

Precise for SQL Server continuously collects high-quality metrics from each supporting tier of your SQL Server-based custom applications infrastructure and correlates these metrics to build a clear picture of application performance from the end-user's perspective—that is, response time.

This information is displayed in the Precise for SQL Server tabs and provides you with the information necessary to successfully track your instance's performance and identify patterns in resource consumption.

The following table describes which tasks can be performed in each tab.

Table 1 Precise for SQL Server tabs

How most tabs are structured

Though each tab is structured differently, most tabs consist of two different areas. Each area can include different control elements, such as tabs and view controls, and displays information in various formats, such as tables, graphs, or charts. The various areas are related to each other in that performing an action in one area affects the information displayed in other areas on the page. For example, the Current tab contains an upper and a lower area. The lower area (Association area) shows information in a table format. Each row in the table represents an application. The upper area (Main area) displays general information on the selected entity as well as related applications.

The entities displayed in the Association area are associated with the selected entity or related application displayed in the Main area. At times, the relationship between the entity displayed in the Main area and those displayed in the Association area is that of parent to child , and sometimes it merely represents that there is a relationship between the selected entity and the entities displayed in the Association area (such as when a statement is the selected entity displayed in the Main area and the list of programs associated with the selected statement is displayed in the Association area).

It is possible to click a row and drill down to an additional level below the selected level or use the filter feature to determine which information is displayed in the table. When you perform an action on an element in the lower area (in this case the Association area), the information displayed in the upper area (in this case the Main area) changes to reflect the action you requested.

You can control the tab by doing any of the following:

• Keeping the selected entity but changing the associated entities by selecting a different entity from the Association controls in the Association area.
• Changing the selected entity by selecting a row in the Association area.
• Changing the view for the selected entity by selecting a different view in the Main area.

For example, when you want to view information on a specific application in the Current tab, choose Active Applications from the Association controls. The Association area changes to display applications-related information. The Tab heading and the Main area remain unchanged.

In the Association area, click the row of the application you want to view detailed information for. The Tab heading indicates the newly selected entity; the Main area displays information on the application you drilled down to, and the Association area shows information on statements executed by this application.

Figure 1 How a typical tab is structured

About the Precise bar

The Precise bar enables you to keep track of where you have been and provides various controls. The following table describes the function of each of the toolbar buttons.

Table 2 Precise bar functions

About the Main area

The Main area displays general information on the selected entity. The structure of this area depends on the selected entity and tab.

Information can be displayed as follows:

• As two over-time graphs that are displayed side-by-side and describe two data series.
• As a pie chart and a table that are displayed side-by-side. The table displays more details regarding a specific slice in the pie chart.

The View Control drop-down menu allows you to choose different view options. You can access this menu by clicking the arrow icon.

In some tabs, you can filter the contents of the view by clicking the Filter button. See Filtering data.

About the Association area

The Association area lists entities that have similar attributes and that are related to the selected entity named in the Tab heading. Besides the Tab bar, the Association area is the main navigation tool. It enables you to drill down from one entity to related entities (usually of a different type).

The entities displayed in this area depend on the selected tab and the selected entity. The same entity may be displayed in several tabs, with different data displayed for each one (for example, a SQL table will be displayed in the Objects tab with data from the SQL data dictionary, and in the Activity tab with data sampled by the Collector).

The Association control, accessed by the arrow icon, allows you to associate with different entities. The last option, More..., opens a dialog box that lets you carry out additional operations, such as to view additional associations, change the sort order, or control the number of returned rows.

The information displayed in the Association area is arranged in a tabular format. Each row in the table represents an entity, and each column describes an attribute of the entity, such as text or a graph. It is possible to change the sort order of the entities in the table by clicking a column title.

The Association tabs located above the table let you display different sets of attributes for the entities displayed in the table. The Association tabs are entity-dependent. For some entities, no tabs are displayed.

A numeric column can optionally display a graph, and at times, it can display a stacked bar graph as well. Switching to a graph view in these columns offers some level of breakdown to the information displayed in the column.

The following icons are used to indicate which format information is displayed.

 Displays the information in numerical format.

 Displays the information in a bar graph.

 Displays the information as a stacked bar graph.

 Displays the information in percentage format

The Association area lets you drill down to another entity by clicking a row. The information displayed in both the Main area and the Association area will change to reflect your selection.

See Filtering data and Associating entities with data that meets specific criteria.

About the Findings area

For selected tabs in Precise products, the association area includes the Findings area, displaying problematic findings for the application. The findings feature is a high level tool, designed to provide the user with an overview of performance issues within the monitored application and enable quick and efficient navigation to the relevant tab for further analysis and handling. The displayed performance findings may indicate performance deterioration as well as incorrect methods usage. Each finding appears as a row in the displayed table of findings. Hover the mouse indicator over the single-line displayed finding to expand the finding. When expanded, the finding details area provides important guidelines as to what may be the root cause for the reported problem, and what the recommended steps are to resolve this problem.

The table below describes the information that is displayed in the Findings area.

Table 3 Information displayed in the Findings area

Investigating a finding

Perform the following steps to investigate a finding.

To investigate a finding

1. In the Time Frame list, select the period of time you want to analyze.
2. In the All Instances table, select the instance you want to investigate.
3. In the Finding area, review the top Findings for the selected instance displayed in the Findings table. The findings displayed in this table are sorted by severity.
4. In the Findings table, select the finding you want to analyze further.
5. In the selected finding (the expanded view), read the data displayed for the finding and follow any links provided to view additional information (advice) or next steps (bullets) to resolve the problem.

For more information, see SQL Server Findings.

About drilling down in context

The term 'in-context' means that you can display additional information on a selected item by drilling down to another tab or view. The filter settings you defined (for example, the selected time frame you chose) and the entity you selected are carried over to the other view or tab, to allow you to continue analyzing your subject from a different perspective. This concept takes on slightly different meanings depending upon where you are attempting to drill down in context from.

For example, when viewing information on a database partition in the Dashboard tab, you can click a link in the Details area (right pane) to view additional information on the related tab, in context of your original selection.

Alternatively, when viewing a list of alerts for your product in Alerts, you can launch your product in context and continue investigating the factors that led the system to issue that alert.

In short, the information displayed when drilling down in context is always related to your original selection’s settings.

How a drill-down affects the Tab heading

The Tab heading displays the name of the currently selected entity on this screen. When you drill down to a new entity in the Association area, the Tab heading changes to reflect the name of the newly selected entity. See How most tabs are structured.

About configuring your settings

The following settings allow you to control the appearance and behavior of the user interface. They can be configured from the Settings menu on the Precise bar:

• General settings - Includes the General, Display, and Tree view tabs.
• SmarTune settings
• Time Frame settings

About configuring general settings

On the General tab of the General Settings dialog box, you can adjust the following:

• Number of items that will be displayed for each association. From the list, select the maximum number of items that will be displayed in a table. The default is 50 items. You can control the number of items returned for a specific association by using the More... option in the Association controls. This way, you can display up to 500 items.
• Maximum number of characters to display in a text ToolTip. When you move your cursor over text columns in a table listing Statements or PL/SQLs, a ToolTip appears, displaying more text than can normally fit into a table cell. This field controls the maximum number of characters that can be displayed. The default is 500 characters.
• How often the information in the Current tab is refreshed. If you select the Auto-refresh Current tab every check box, the Current tab automatically refreshes at the specified interval. By default, the tab is not refreshed automatically. If you select this option, the default refresh interval is every five minutes.

About configuring tuner settings

In the Tuner tab you can change the Login name and Password used when launching Microsoft® SQL Server Query Analyzer and the Recommend Indexes processes.

About configuring display settings

When you drill down to or choose a different entity, selecting the option on the Display tab of the General Settings dialog box allows you to maintain the selected view or tab when changing from one tab to another. This is sometimes referred to as sticky tabs.

About configuring Tree view settings (Only applies to Precise products that include a Tree view)

On the Tree View tab of the General Settings dialog box, you can adjust the following:

• Number of categories to display when expanding an item.
• Number of items to display when expanding a category.

About configuring SmarTune settings

You control the maximum number of findings to display in the Findings area and whether or not you want to ignore findings, in the SmarTune settings dialog box. See Configuring Findings settings.

About configuring Time Frame settings

You can determine the resolution of the data that is displayed in the overtime graphs using the Time Frame Settings dialog box. By using this dialog box you can define the default time frame to display.

Tasks common to most tabs

The following tasks are commonly performed in most tabs:

• Switching to a different tab
• Selecting a time frame
• Selecting an entity
• Filtering data
• Associating entities with data that meets specific criteria
• Focusing on information in overtime graphs
• Sending an email message
• Adding, viewing, and deleting Favorites
• Determining which table columns to display
• Copying data to the clipboard
• Exporting to the Precise Custom Portal
• Exporting the information

Switching to a different tab

You can easily switch between the different tabs using the Tab Selection bar. When you start your Precise product, the Dashboard tab opens by default. For other Precise products, another tab will open by default. The button of the selected tab is displayed in orange.

To select a tab, click a button on the Tab Selection bar to display information on the selected entity in a different tab.

Selecting a time frame

You can configure Precise for SQL Server to display transaction performance data for a specific time frame using the predefined time frame options or calendar icons.

Selecting a predefined time frame from the toolbar displays transaction performance data for the selected time period up to the current time. See Selecting a predefined time frame from the Precise for SQL Server toolbar.

Selecting the time frame using the calendar icon, you can choose to define a time range independent of the current time, or to define a time range up to the current time. See Selecting a time frame using the calendar icon. The predefined time frame options are:

• Last 8 hours (8h) (default)
• Last 1 day (1d)
• Last 2 days (2d)
• Last 2 weeks (2w)
• Last 5 weeks (5w)

The time frame selected affects all information displayed in Precise for SQL Server. Only data that falls within the selected time frame is shown in these areas.

Selecting a predefined time frame from the Precise for SQL Server toolbar

To select a predefined time frame, from the Precise for SQL Server toolbar, select one of the predefined time frames.

Selecting a time frame using the calendar icon

To select a time frame

1. Click the calendar icon. In the dialog box that is displayed perform one of the following:
   1. To define a time frame independent from the current time, select the ‘Time Range’ option and select the Start and End dates and times.
   2. To define a time frame up to the current time, select the ‘Last’ option and enter the desired time frame.
   3. To use one of the three previously used time frames, select the ‘Recently used’ option and from the drop-down menu select the desired time frame.
   4. To use a previously saved time frame, select Use a previously saved time frame and from the drop-down menu select the desired time frame.
2. To save your settings for future access, select Save these definitions for future use as: and enter a name in the corresponding field.
3. Click OK.

Selecting an entity

You can select the entity that you want to examine in more detail.

To select an entity, on the Main area of the tab, select the entity you want to examine.

Filtering data

You can focus on specific contents of the Dashboard and Activity tabs by showing a subset of the information. This shows the contribution of entities such as programs and users.

You can define flexible selection criteria in the Filter dialog box. To open the dialog box, you need to click the Filter Off icon next to the Time Frame selector. Filter On or Filter Off indicates the current state of the filtering mechanism. Filter Off means all information is shown; Filter On means a subset of information is shown.

When you apply your selections in the Filter dialog box, the information displayed in both the Main area and the Association area is modified to reflect your selections. Also, the filtering continues to apply when you drill down to associated entities.

To filter data

1. Click Filter is Off/Filter is On.
2. In the Filter dialog box, do the following for each entity you want to filter:
   1. From the left drop-down list, select an entity.
   2. From the middle drop-down list, select an operator, such as, Like, <>, Not Like, In, Not In.
   3. In the text box, type the criteria (case-sensitive) for the selected entity.
      If you select the operator Like or Not Like, you can use the % wildcard character to represent 0 or more characters, and the _ wildcard character to represent exactly one character. If you select the operator In or Not In, type a comma to separate values.
3. Click OK.

Associating entities with data that meets specific criteria

You can associate the displayed entity with specific data to focus your analysis.

To associate entities with data that meets specific criteria

1. Click the arrow located to the left of the Association controls and select More... (applies to Current and Objects tabs only)
2. In the Associate With dialog box, on the Entries tab, select the entity you want to associate data with from the Populate table with list.
3. In the Sort entries by list, determine according to which criteria you want the information to be sorted and in which order.
4. From the Display top list, select the number of rows to display.
5. On the Criteria tab, do the following for each entity you want to associate data with:
   1. From the left drop-down list, select an entity.
   2. From the middle drop-down list, select an operator, such as, Like, <>, Not Like, In, Not In.
   3. In the text box, type the criteria (case-sensitive) for the selected entity.
      If you select the operator Like or Not Like, you can use the % wildcard character to represent 0 or more characters, and the _ wildcard character to represent exactly one character. . If you select the operator In or Not In, type a comma to separate values.
6. Click OK.

Saving a statement

Save As allows you to save the statement or batch so that you can examine its access plan at a later date.

To save a statement

1. Select the statement you want to save in the Association area.
2. Click Save As.
3. In the Save Statement dialog box, enter the following properties:
   • Instance. The name of the instance that the statement belongs to.
   • Cabinet. The name of the cabinet that the statement is saved in (creates a cabinet if it does not already exist).
   • Folder. The name of the folder that the statement is saved in (creates a folder if it does not already exist).
   • Name. The name of the statement (generated randomly).
   • Database. The name of the database where the statement is running.
   • User. The statement's parsing user.
4. Enter the SQL text for the statement in the Text frame.
5. Click OK.

The new statement is saved in the PMDB in a logical cabinet and folder hierarchy.

Focusing on information in overtime graphs

Some entities display an overtime graph. The overtime graph displays entity statistics over a specified time period. Depending on the number of points displayed in the graph, you may need to zoom in or out. The text displayed on the x-axis varies according to the time frame. If there is a year or day change, x-axis labels will display accordingly.

To focus on information in overtime graphs, select the desired time frame on the overtime graph, click and drag the left or right handle to increase or decrease the time range. The small zoom (spyglass) icon will display on the upper right of the selected time range, and above the overtime graph legend. Click the zoom icon to zoom in according to the selected time frame.

Sending an email message

You can send an email message to one or more recipients from the Precise toolbar. The default subject for the message will be "Link to a Precise application."

The email will include a link to the Precise product in the current context (time frame and selected entries).

To send an email message

1. Click the email icon on the Precise toolbar. The default email program opens.
2. Fill in the required fields and click Send.

Adding, viewing, and deleting Favorites

The Favorites feature allows you to save a specific location in your application and to retrieve the same location later without having to navigate to it.

About the Favorites feature

The new Favorites feature includes the following options:

• Relative Time Frame. Saving a relative time frame instead of static date. For example, saving the last 7 days will always display the last 7 days, depending on the day entered.
• One click to specific location. Once you open Precise by launching a saved Favorite item, you will not have to enter a login credential nor click the login button.
• IE Favorites support. Adding a new Favorite item in Precise will also add it to the IE Favorites menu.
• Auto Complete. The Favorites dialog includes a new combo box which supports AutoComplete.
• Auto Naming. The Favorites dialog generates item names based on the current location.

UI description

A Favorites menu has been added to the Precise bar in each product including StartPoint.

An Add/Delete Favorites option under the Favorites menu allows you to save the current location or delete an existing one.

To add a new Favorite location

1. On the Add/Delete Favorites dialog box, enter the name of the new Favorites entry.
2. Click Add. The dialog box is closed and the new Favorite is added to the list.

To view a Favorites location

1. On the Precise bar, click Favorites.
2. Select the Favorites location you want to view.

To delete an existing Favorite location

1. On the Add/Delete Favorites dialog box, select the Favorite location to be deleted.
2. Click Delete. The dialog box closes and the selected Favorite is deleted from the list.

Determining which table columns to display

Tables are used to display information about a set of related entities in the Main and Association areas. It is possible to determine which columns to display in the Association area tables.

To determine which columns to display in the Association area

1. Click the Table icon on the upper right-hand side of a table and select Column Chooser.
2. In the Table columns dialog box, click the arrows to move the names of the columns that you want to display to the Visible box and the ones that you do not want to display to the Invisible box.
3. Click OK.

Copying data to the clipboard

At times you may want to save data displayed in the table area in a Microsoft Excel spreadsheet for further analysis or save an image of a graph to the clipboard.

To copy data displayed in the Association area to the clipboard, click the Table icon on the upper right-hand side of a table and select Copy to clipboard. The table can be pasted into Microsoft Excel or as an HTML file.

To copy a graph to the clipboard, right-click a graph and choose Copy to clipboard. You can now paste the image into any application that works with the clipboard.

Exporting to the Precise Custom Portal

The Export to the Precise Custom Portal Portlet feature enables you to export the view of the chosen table or graph and generate a portlet with that view in the Precise Custom Portal, so that it will provide you with another way of monitoring your application.

Prerequisites

To be able to use this feature, you need to have the View permissions to all Tiers in the application. If you do not have sufficient rights, you will get an error message when trying to execute this feature.

Exporting the information

You can either export a table view or a graph view.

To export a table view

1. Click the Column Chooser icon.
2. Select Export to the Precise Custom Portal Portlet.
3. Insert a name in the name field that clearly describes the table view.
4. Click OK.

To export a graph view

1. Right-click the graph.
2. Select Export to the Precise Custom Portal Portlet.
3. Insert a name in the name field that clearly describes the graph view.
4. Click OK.

About role management in Precise for SQL Server

The Roles management feature in Precise for SQL Server lets you assign roles to users and control whether they will be able to do the following operations:

• Whatif. Lets you run WhatIf simulations on statements that Precise for SQL Server has collected, without having to physically make the change (depends on what is configured for Gen_Estimated_Plan and Recommend).
• Recommend. Lets you generate index recommendations (depends on Gen_Estimated_Plan).
• Gen_Estimated_Plan (explain). Lets you generate a new execution plan.
• Launch_MS_Tool. Lets you launch external SQL tools (such as, Query Analyzer).

It is possible to grant permissions to all roles or to revoke permission from individual roles. Permission is a combination of permission type (such as: INSTALL, ADMIN, MONITOR) and operation (such as: VIEW, UPDATE).

Certain types of permission enforce permission dependencies. For example, if you grant a TUNE.RECOMMEND permission it will automatically grant TUNE.EXPLAIN. In the same manner, if you revoke TUNE.EXPLAIN then the TUNE.RECOMMEND permission is also revoked.

You can configure permissions and roles in AdminPoint.

For more information on configuring permissions and roles, see the Precise Administration Guide.

Opening your Precise product from the Precise StartPoint

Precise is a Web-based application. You can access the Precise user interface using the Internet Explorer browser, version 6.0 or later. The syntax of the Precise URL address is: http://<server>:<port>, where <server> refers to the Precise FocalPoint server and <port> refers to the port number used by the GUI Web server. By default, the port number is 20790 (20760 for J2EE). For example: http://beanotown:20790.

This URL provides secure access to the StartPoint using authorized roles. From here, you can launch all Precise products. It gives you a quick overview of the status of your applications and access to the AdminPoint, where you can perform various management tasks (see the Precise Administration Guide for details).

To open your product using Precise StartPoint

1. Type the address of the StartPoint user interface into the Address bar of your browser and click Enter. The Precise login page opens. The login page provides secure access to Precise and to your specific product.
2. Specify your authorized role name and password. By default, both role name and password are admin. For more information about role names, see the Precise Administration Guide.
3. Click Login. The StartPoint page opens. This is the Precise home page.
4. On the Product Selection bar, from the drop-down list, select the product you want to open.