Page History
...
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 12-6 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. |
...
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 table shows the 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
...
on findings.
Table 12-7 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 types of findings on table shows the 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
...
on findings.
Table 12-7 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. |
| Anchor | ||||
|---|---|---|---|---|
|
...
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:
| Info |
|---|
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.
Precise. Performance intelligence from click to storage. Learn more > >
...