Page History

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.

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.

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.

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 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.

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.

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.

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

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

The statement is a major consumer of MS-SQL resources. By tuning the statement, you can free resources needed by other statements and processes.

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.

The statement or batch has an operator that accesses the object and shows a high cost percentage.

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.

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.

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 resource consumption was increased by n% as a result of an increase in its executions.

The average In MS-SQL time of the statement increased following a major change in table size.

The SQL Server resources consumed by the statement increased by n%.

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.

Total In MS-SQL time of the statement was consistently high and reached the thresholds of the top statements.

A collapsed statement includes several statements that use the same text, but not the same constants.

A set of collapsed statements constitute a major consumer of MS-SQL resources.

Statement resource consumption was increased by n% as a result of an increase in its executions.

Object is a major consumer of MS-SQL resources. By tuning the object, you can free resources needed by other statements and processes.

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.

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.

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.

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.

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.

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.

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.

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.