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.
Note: 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 12-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:
■ Heavy statement
■ Heavily accessed object
■ Heavy collapsed statement
■ Instance event
Object Depending on the type of finding, displays one of the following:
■ For a heavy statement: the statement ID
■ For a heavily accessed object: the table name
■ For a heavy collapsed statement: the collapsed statement ID
■ For an instance event: empty field
In MS-SQL Depending on the type of finding, displays one of the following:
■ For a statement: total time spent In MS-SQL, during the selected time period.
■ For an object: total In MS-SQL contribution to the table, during the selected time period.
■ For a collapsed statement: total In MS-SQL time of all statements that are part of the collapsed statement.
■ For an instance event: the In MS-SQL time related to the event, when available.
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.
Table 12-1 Findings table
Column Description
User Depending upon the type of finding, displays one of the following:
■ for a statement: parsing user
■ for an object: owner
■ for an instance event: empty field
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 12-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 12-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.
■ Severity icon: indicates how serious a problem is, in relation to the statement's activity.
■ Launch icon: lets you launch, in context, to a different tab to continue your analysis. The tab you launch to depends upon SmarTune's recommendations.
■ Type: shows a description of the finding.
■ Table Name: displays the name of the table the finding is related to.
■ Potential Gain: calculates the possible gain obtained if the suggested change is implemented.
■ Last Detected: last time a problem was found for the specific finding.
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.
Table 12-3 Information displayed in the Details area when a statement is selected
Tab Information displayed
Over Time Displays the following overtime graphs:
■ Statement In MS-SQL
Displays the amount of time a statement spent in MS-SQL, during the selected time period.
■ Statement Scalability
Displays the number of times a statement was executed, over the selected time period.
■ Avg. Duration vs. Schema Changes
Displays the types of changes made to the schema, during the selected time period, such as whether an entity was dropped, created, or changed. This enables you to analyze how a schema change affected resource consumption and system performance.
The following table shows the information displayed for problematic objects.
Table 12-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.
■ Severity icon: indicates how serious a problem is, in relation to the statement's activity.
■ Launch icon: lets you launch, in context, to a different tab to continue your analysis. The tab you launch to depends upon SmarTune's recommendations.
■ Type: shows a description of the finding.
■ Potential Gain: calculates the possible gain obtained if the suggested change is implemented.
■ Last Detected: last time a problem was found for the specific finding.
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:
■ Storage
Displays an object's space usage, during the selected time period.
■ Table In MS-SQL
Displays the MS-SQL contribution of the table, during the selected time period.
The following table shows the information displayed for problematic objects.
Table 12-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.
■ Severity icon: indicates how serious a problem is, in relation to the collapsed statement's activity.
■ Launch icon: lets you launch, in context, to a different tab to continue your analysis. The tab you launch to depends upon SmarTune's recommendations.
■ Type: shows a description of the finding.
■ Table Name: displays the name of the table the finding is related to.
■ Potential Gain: calculates the possible gain obtained if the suggested change is implemented.
■ Last Detected: last time a problem was found for the specific finding.
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:
■ Statement In MS-SQL
Displays the amount of time a statement spent in MS-SQL, during the selected time period.
■ Statement Scalability
Displays the number of times a statement was executed, over the selected time period.
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 12-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
■ Table displaying information on top 10 blocked statements
■ Instance activity overtime graph
For: Tempdb bottleneck
■ Table displaying information on top 10 problematic statements
■ Tempdb files I/O requests pie chart
■ Tempdb I/O requests vs. other files I/O requests
■ Tempdb storage
Table 12-6 Information displayed in the Details area when an instance is selected
Tab Information displayed
Statistics For: The transaction log bottleneck
■ Table displaying information on top 10 statements suffering from transaction log bottlenecks. Click on a database in the table to launch it, in context, in the Statistics tab.
■ Table displaying top 10 statements waiting for log. For: Buffer cache is too small
■ Page life expectancy graph. Indicates the average number of seconds a page stays in
cache. If this value falls below the threshold set in AdminPoint, this can indicate that SQL Server may need more memory to boost performance.
■ Lazy writes/sec graph. Tracks how many times a second that the Lazy Writer process moves dirty pages from the buffer to disk to free up buffer space and make them available to user processes. Should be close to zero.
■ Page faults/sec graph. Indicates the overall rate at which the processor handles both hard and soft page faults.
For Tempdb bottleneck, three graphs showing tempdb statistics:
■ Temp tables creation rate
■ Temp tables for destruction
■ Active temp tables
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 types of findings on statement behavior are displayed:
■ Heavy Statement
■ Heavy Collapsed Statement
■ Major Statement in Batch
■ Heavy Operators
■ Missing Indexes
■ Missing Statistics
■ Table Schema Change May Increase Its Accessing Time
■ Statement Is Not Scalable
■ Table Growth May Increase Its Accessing Time
■ Increase in Resource Consumption
■ Statement Was Locked
■ Statement Activity Consistently High
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:
■ During the blocker session, a locked statement or batch ran for a short period of time. Afterwards the session was idle or continued running other statements or batches. In this case, it is possible to identify the blocker session, but not necessarily the blocker statement or batch.
■ During the blocker session, a locked statement or batch ran for a long period of time. Identifying the blocker statement or batch is easier in this case.
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 types of findings on object behavior are displayed:
■ Heavily Accessed Object
■ Heavy Operators
■ Missing Indexes
■ Missing Statistics
■ Table Schema Change May Increase Its Accessing Time
■ Object Is Not Scalable
■ Table Growth May Increase Its Accessing Time
■ Increase in Resource Consumption
■ Locked Object
■ Index Overhead
Heavily Accessed 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:
■ During the blocker session, a locked statement ran for a short period of time. Afterwards the session was idle or continued running other statements. In this case, it is possible to identify the blocker session, but not necessarily the blocker statement.
■ During the blocker session, a locked statement ran for a long period of time. Identifying the blocker statement is easier in this case.
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 types of findings on instance behavior are displayed:
■ Locked Instance
■ Tempdb Bottleneck
■ Buffer Cache Is Too Small
■ Other Applications Influence SQL Server (Memory)
■ Other Applications Influence SQL Server (CPU)
■ The Transaction Log Bottleneck
■ Extensive Internal Wait
Locked Instance
x% of the In MS-SQL time was spent waiting for locks. Regular locks can be categorized as follows:
■ During the blocker session, a locked statement or batch ran for a short period of time. Afterwards the session was idle or continued running other statements or batches. In this case, it is possible to identify the blocker session, but not necessarily the blocker statement or batch.
■ During the blocker session, a locked statement or batch ran for a long period of time. Identifying the blocker statement or batch is easier in this case.
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.
Note: The SmarTune settings control the findings displayed in both the Dashboard and SmarTune tabs.
To determine the maximum number of findings to display
1 On the Settings menu, click SmarTune Settings.
2 In the SmarTune Settings dialog box, in the Maximum number of findings to display, select the number of findings you want to display.
3 Click OK.
To restore the display of a finding to the Findings table in the SmarTune tab
1 On the Settings menu, click SmarTune Settings.
2 In the SmarTune Settings dialog box, check the Ignore check box located next to the finding you want to ignore.
3 Check the Enable Ignore List check box.
4 Click OK.
To restore all findings detected by the SmarTune process to the Findings table in the SmarTune tab
1 On the Settings menu, click SmarTune Settings.
2 In the SmarTune Settings dialog box, clear the Activate Ignore List check box.
3 Click OK.