Page History
...
The following statement findings can help you tune your system:
- 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 or Batch Was Locked
- Statement Activity Consistently High
| Anchor | ||||
|---|---|---|---|---|
|
The statement is a major consumer of MS-SQL resources. By tuning the statement, you can free resources needed by other statements and processes.
...
| Column | Description |
|---|---|
| What to do next | Try to determine what is causing the statement’s high resource consumption. In the SQL tab, examine the text of the relevant statement, and its findings, execution plan, change data and statistics. |
| Advice | The following scenarios indicate what factors can lead to heavy resource consumption and what steps you can take:
|
| Anchor | ||||
|---|---|---|---|---|
|
A collapsed statement includes several statements that use the same text, but not the same constants.
...
| Column | Description |
|---|---|
| What to do next | In the SmarTune tab, examine the text of the relevant statements. Check their scalability and associated list of statements. Select the statement you want to analyze, and launch to the SQL tab with the statement in context. Examine its execution plan, change data and statistics to determine what is causing its high resource consumption. |
| Advice | The following scenarios indicate what factors can lead to heavy resource consumption and what steps you can take:
|
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
An increase of In MS-SQL time was identified. Use Precise for SQL Server to try and locate the cause. Check schema changes, major table growth and scalability changes.
Heavy Operators
The statement or batch has an operator that shows a high cost percentage.
...
■ Try to reduce the number of logical reads or writes.
Missing Indexes
During SQL Server optimization, missing indexes were identified for the statement 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 index recommendation for this statement or batch can be found in the Recommend tab of the SQL tab.
...
■ Use the What if feature in the Recommend tab to evaluate which statements or batches were affected, based on a large set of statements or batches that were active during the selected time frame. This feature lets you check whether it is possible to improve performance by creating one or several indexes.
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.
...
■ Once statistics are collected, remember that statistics should be periodically maintained.
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 execution time.
...
■ Compare statement or batch execution plans in the Compare tab.
Statement Is Not Scalable
Statement resource consumption was increased by n% as a result of an increase in its executions.
...
■ Eliminate concurrency by distributing the operation of the application over several MS-SQL
servers.
Table Growth May Increase Its Accessing Time
The average In MS-SQL time of the statement increased following a major change in table size.
...
■ Examine changes in the History tab and analyze these changes over time by launching to the
Objects tab.
Increase in Resource Consumption
The SQL Server resources consumed by the statement increased by n%.
...
Check instance behavior. An increase in instance execution over time, may affect the performance of regular statements.
Statement or Batch Was Locked
Much of the statement or batch time was spent waiting for a lock. Regular locks can be categorized as follows:
...
■ Try to identify lock patterns within a larger time frame.
Statement Activity Consistently High
Total In MS-SQL time of the statement was consistently high and reached the thresholds of the top statements.
...