This section includes the following topics:
- About the SmarTune tab
- About accessing the SmarTune tab
- How the Precise for SQL Server SmarTune tab is structured
- Configuring Findings settings
About the SmarTune tab
The SmarTune process detects performance deterioration by regularly analyzing SQL Server performance data, and identifying less than optimal behavior.
You can change the frequency of how often the processes are run and configure the process parameters in AdminPoint. See the Precise Administration Guide for details.
SmarTune displays performance findings that may indicate potential problems in the database. The list of problems includes the following:
- list of statements that require tuning
- list of objects displaying heavy activity or possessing an inefficient structure
- instance events
This is the tab to begin with, if you want to start performance tuning on a database. In the SmarTune tab, you can examine the SmarTune findings, read expert knowledge regarding the area of deterioration, and obtain comprehensive advice on how to solve a specific problem and optimize performance. The Findings area lists the problematic statements, objects, and instance events that are causing poor performance. Relevant information regarding each problem listed in the Findings area is displayed in the right pane.
As part of the guidance provided, SmarTune includes Quick Links to launch to other tabs in context, so that you can easily continue your investigation. For example, you may be directed to the Activity tab to view the performance of an offending SQL statement. From there, you can launch to the SQL tab to examine the statement's execution plan and view a recommendation on a new index.
About accessing the SmarTune tab
You can access the SmarTune tab from the following locations:
- From the Dashboard.
Select the SmarTune icon in the Findings table, for a given instance.
The SmarTune tab opens with the selected instance, displaying the findings for this instance, in the context of the selected instance and timeframe. - From the Activity tab.
Click SmarTune on the Tab Selection bar.
The SmarTune tab opens with the selected instance, displaying the findings for this instance, in the context of the selected instance and timeframe. - From the Current, Objects, or Maintenance tabs.
Click SmarTune on the Tab Selection bar.
The SmarTune tab opens with the selected instance, displaying the findings for this instance, in the context of the selected instance.
How the Precise for SQL Server SmarTune tab is structured
The SmarTune tab is divided into two areas—the Findings area and the Details area. The Findings area displays all findings returned by SmarTune for the selected time frame. The Details area provides detailed information regarding the selected finding.
About the Findings area in the SmarTune tab
The Findings area lists all the statements, collapsed statements, tables or instance events that were detected for a specific instance, within the selected time frame. All problems are ranked by severity. An Ignore icon, lets you decide if you want to remove a problem from the Findings table. This does not mean that SmarTune will stop checking these findings, rather that they will not be displayed in the Findings table. Clicking on an item in the Findings list refreshes the information displayed in the Details area.
The following table shows the information displayed in the Instance table.
Table 1 Findings table
| Column | Description |
|---|---|
| Severity Icon | Indicates how severe the detected problem really is. Calculates the severity of the finding in relation to the entire instance. |
| Type | Indicates the type of finding. Can be one of the following:
|
| Object | Depending on the type of finding, displays one of the following:
|
| In MS-SQL | Depending on the type of finding, displays one of the following:
|
| Database | Displays the name of the statement or object affected by the finding. The field is left empty in the case of an instance event finding. |
| User | Depending upon the type of finding, displays one of the following:
|
| Last Detected | Displays the last time the finding was found. |
| Ignore Icon | Click to ignore a finding. The finding is added to an Ignore list and is no longer displayed in the Findings table. You can always redisplay a finding by turning off the Ignore list or by specifying which findings you want to restore in the Ignore list. |
The following table shows the icons that signify the level of severity of a finding in the Findings table.
Table 2 About severity icons
| Severity Icon | Level of Severity |
|---|---|
 | Low |
 | Medium |
 | High |
About the Details area in the SmarTune tab
The Details area displays comprehensive information on the problem selected in the Findings area. A set of tabs lets you page through different views to gain additional information on the selected finding. The types of tabs, and the information displayed in them, changes according to the type of finding you chose to investigate in the Findings area.
The following table shows the information displayed for problematic statements.
Table 3 Information displayed in the Details area when a statement is selected
| Tab | Information Displayed |
|---|---|
| Highlights | Offers a quick overview of the major finding details. The Text area displays the actual text of the statement. The Statement Findings area presents an overview of all the findings detected within a time frame, for the selected statement.
The Details for Finding area points you in the direction of additional tuning and findings-related data. They direct you to other tabs that can supply additional information regarding the specific finding, or to additional views within the SmarTune tab, so that you can view additional information about the specific finding. |
| Objects | Displays the list of objects appearing in a statement, that are part of the performance finding. For example, Objects area can indicate if a table or index is part of the heavy operators finding, or if a table has missing indexes or statistics. |
| Over Time | Displays the following overtime graphs:
|
The following table shows the information displayed for problematic objects.
Table 4 Information displayed in the Details area when an object is selected
| Tab | Information Displayed |
|---|---|
| Highlights | Offers a quick overview of the major finding details, such as the table's details, and a description of the findings, including an estimate of the potential improvement to performance if you choose to make the suggested change.
The Details for Finding area points you in the direction of additional tuning and findings-related data. They direct you to other tabs that can supply additional information regarding the specific finding, or to additional views within the SmarTune tab, so that you can view additional information about the specific finding. |
| Statements | Displays a list of statements that accessed a table, during the selected time period. |
| Over Time | Displays the following overtime graphs:
|
The following table shows the information displayed for problematic objects.
Table 5 Information displayed in the Details area when a collapsed statement is selected
| Tab | Information Displayed |
|---|---|
| Highlights | The Text area displays the actual text of the collapsed statement. The Collapsed Statement Findings area presents an overview of all the findings detected within a time frame, for the selected collapsed statement.
The Details for Finding area points you in the direction of additional tuning and findings-related data. They direct you to other tabs that can supply additional information regarding the specific finding, or to additional views within the SmarTune tab, so that you can view additional information about the specific finding. |
| Statements | Displays the list of statements belonging to the same collapsed statement, that were executed during the selected time period. |
| Over Time | Displays the following overtime graphs:
|
The following table shows the information displayed on findings, at the instance level. Findings at the instance level check general behavior and statistics counters and give a general indication of the problems influencing instance performance.
Table 6 Information displayed in the Details area when an instance is selected
| Tab | Information Displayed |
|---|---|
| Highlights | Offers a quick overview of the major finding details. The text area describes the problem, displays thresholds and provides links to other tabs that can supply additional information regarding the specific finding, or to additional views within the SmarTune tab, so that you can view additional information about the specific finding. The graph presents information that is relevant to the specific finding or instance event. |
| Activities | For: Locked Instance
For: Tempdb bottleneck
|
| Statistics | For: The transaction log bottleneck
For: Buffer cache is too small
For Tempdb bottleneck, three graphs showing tempdb statistics:
|
About Statement findings
SmarTune displays the top statements occurring in a time slice. A SmarTune time slice is updated on an hourly basis. Top statements are the statements that have the highest In MS-SQL time, computed without lock time and remote wait time, that were running more than a specified number of minutes, during the time slice.
You can configure these parameters (how long they should be running to be considered top statements) in AdminPoint. You decide how many top statements to display.
The following table shows the statement behavior displayed on findings.
Table 7 Statement behavior displayed on findings
| Statement Behavior | Description |
|---|---|
| Heavy Statement | The statement is a major consumer of MS-SQL resources. By tuning the statement, you can free resources needed by other statements and processes. |
| Major Statement in Batch | The statement consumed more than 50% of MS-SQL batch resources. By tuning the statement, you can free resources needed by other statements and processes. |
| Heavy Operators | The statement or batch has an operator that accesses the object and shows a high cost percentage. |
| Missing Indexes | During SQL Server optimization, missing indexes were identified for one or more statements or batch. This means there can be one or more indexes, but they are not used because of a mismatch of column types. The SQL Server optimizer recommended creating indexes to improve the performance of the statement or batch. Detailed information regarding the index recommendation for this statement or batch can be found in the Recommend tab of the SQL tab. |
| Missing Statistics | The Missing Statistics warning was issued by the SQL Server optimizer when the access plan was created. This means that the SQL Server optimizer recommends creating and updating the statistics on objects accessed by a specific statement or batch. Detailed information on missing statistics for this statement or batch can be found in the Recommend tab in the SQL tab. |
| Table Schema Change May Increase Its Accessing Time | The average In MS-SQL time increased after an object change. Check the changes and how they affected the statement or batch execution time. |
| Statement Is Not Scalable | Statement resource consumption was increased by n% as a result of an increase in its executions. |
| Table Growth May Increase Its Accessing Time | The average In MS-SQL time of the statement increased following a major change in table size. |
| Increase In Resource Consumption | The SQL Server resources consumed by the statement increased by n%. |
| Statement Was Locked | Much of the statement or batch time was spent waiting for a lock. Regular locks can be categorized as follows:
|
| Statement Activity Consistently High | Total In MS-SQL time of the statement was consistently high and reached the thresholds of the top statements. |
| About Collapsed Statement Findings | A collapsed statement includes several statements that use the same text, but not the same constants. |
| Heavy Collapsed Statement | A set of collapsed statements constitute a major consumer of MS-SQL resources. |
| Statement Is Not Scalable | Statement resource consumption was increased by n% as a result of an increase in its executions. |
About Object findings
Object findings are checked for top objects. The parameters determining what is considered to be a top object can be configured in AdminPoint.
The following table shows the object behavior displayed on findings.
Table 8 Object behavior displayed on findings
| Object Behavior | Description |
|---|---|
| Heavily Access Object | Object is a major consumer of MS-SQL resources. By tuning the object, you can free resources needed by other statements and processes. |
| Heavy Operators | One or more statements access this object using heavy operators. Statement has major access plan operators for this object that scan a great deal of data and show a high cost percentage. |
| Missing Indexes | During SQL Server optimization, missing indexes were identified for one or more statements. This means that the SQL Server optimizer recommended creating and updating the statistics on objects accessed by a specific statement or batch. Detailed information regarding the index recommendation for this statement can be found in the Recommend tab of the Objects tab. |
| Missing Statistics | The Missing Statistics warning was issued by the SQL Server optimizer when the access plan was created. This means that the SQL Server optimizer recommends creating and updating the statistics on the object. Detailed information on missing statistics for this object can be found in the Recommend tab in the Object tab. |
| Table Schema Change May Increase Its Accessing Time | The total In MS-SQL time of the object increased after changes were made to the schema. |
| Object Is Not Scalable | The total In MS-SQL contribution of the object increased when the number of statements executions changed. |
| Table Growth May Increase Its Accessing Time | The total In MS-SQL time of the object increased following a major change in the table size. |
| Increase in Resource Consumption | The total In MS-SQL time of the object has increased. |
| Locked Object | Much of the In MS-SQL time was spent waiting for a lock on the table. Regular locks can be categorized as follows:
|
| Index Overhead | Most of the activity on the index is due to the fetching of index pages from the disk, reflect5ng changes made by INSERT, DELETE, and UPDATE statements. |
About Instance findings
SmarTune checks to which extent a finding affects an instance.
The following table shows the instance behavior displayed on findings.
Table 9 Instance behavior displayed on findings
| Instance Behavior | Description |
|---|---|
| Locked Instance | x% of the In MS-SQL time was spent waiting for locks. Regular locks can be categorized as follows:
|
| Tempdb Bottleneck | The instance is experiencing a bottleneck of the tempdb database. x% of the In MS SQL time was spent waiting for tempdb. The tempdb database is used for temporary storage for sorting, joining, and, in SQL Server 2005, for row versioning. |
| Buffer Cache Is Too Small | A SQL Server instance is configured to work with a small buffer cache. Your applications are experiencing x% of the In MS-SQL time for I/O wait. The application’s overall performance is therefore not optimal. |
| Other Applications Influence SQL Server (Memory) | A SQL Server instance experienced many page faults and lack of memory, while other processes on the same server did not experience any page faults problem. |
Other Applications Influence SQL Server (CPU) | A SQL Server instance experienced CPU shortage, as a result of other processes running on the server. |
The Transaction Log Bottleneck | Some of the databases suffer from transaction log bottleneck. Log wait consumes x% of the In MS-SQL time. |
Extensive Internal Wait | The SQL Server instance has spent much of its time waiting for Internal Wait. |
Configuring Findings settings
You determine the maximum number of findings to display in the Findings area.
Findings are added to the Findings table as they are detected by the SmarTune process. You can choose to ignore a finding by selecting the finding and clicking its ignore icon. Findings which you have decided to ignore can be returned to the Findings display by configuring the SmarTune settings.
The SmarTune settings control the findings displayed in both the Dashboard and SmarTune tabs.
To determine the maximum number of findings to display
- On the Settings menu, click SmarTune Settings.
- In the SmarTune Settings dialog box, in the Maximum number of findings to display, select the number of findings you want to display.
- Click OK.
To restore the display of a finding to the Findings table in the SmarTune tab
- On the Settings menu, click SmarTune Settings.
- In the SmarTune Settings dialog box, check the Ignore check box located next to the finding you want to ignore.
- Check the Enable Ignore List check box.
- Click OK.
To restore all findings detected by the SmarTune process to the Findings table in the SmarTune tab
- On the Settings menu, click SmarTune Settings.
- In the SmarTune Settings dialog box, clear the Activate Ignore List check box.
- Click OK.