Page History
...
The following object findings can help you tune your system:
...
...
...
...
- 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
...
- High Amount of Index Scans
...
- High Amount of Table Lookups
...
- High Amount of Unused Heap Pages
| Anchor |
|---|
...
|
...
|
Object is a major consumer of MS-SQL resources. By tuning the object, you can free resources needed by other statements and processes.
Table 13-13 Heavily 13 Heavily accessed object findings
...
■ Examine table activity in the Activity tab.
Column Description
Advice The following scenarios indicate which factors can lead to heavy resource consumption and what steps you can take:
...
heavy operators were identified. Check the information displayed in the Overview in the Statements tab in the SmarTune tab.
...
Try to use the INCLUDE (2005 only) option to add the needed columns to the index leafs to prevent unnecessary table lookups or in case of 2000 instance, use the index covering technique to eliminate extra table lookups
| Anchor | ||||
|---|---|---|---|---|
|
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.
Table 13-14 Heavy 14 Heavy Operators findings
Column Description
...
■ Examine the index and statistics recommendations for this object in the Recommend tab, in the Objects tab.
■ Explore table details in the Objects tab.
■ Examine table activity in the Activity tab.
Column Description
Advice One of the following operations (for example, table scan, index scan, clustered index scan or filter) was identified as a major cost indicator. Try to eliminate the major consuming operation by doing one of the following:
...
■ Analyze the recommendations provided in the Recommend tab in the SQL tab.
For a Filter:
Eliminate a filter by doing one of the following:
...
■ Consider performing an index on the computed column.
For a Sort:
■ Try reducing the space or memory required for Sort operations by reducing the number of sorted columns or by filtering the rows to be sorted.
...
■ Check the values of the Minimum memory per query (KB) and Minimum memory for index create sorts (KB) instance parameters. Use the memory for index create sorts (KB) configuration to control the amount of memory used by index creation sorts. The Minimum memory for index create sorts (KB) configuration is self-configuring and should work in most cases without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value. Query sorts are controlled through the Minimum memory per query (KB).
For a Hash:
Hash join is used where the joined tables or row sets are large or where there aren't adequate indexes or the lack of indexes at all.
■ Verify that there are no missing indexes or the indexes are adequate.
...
■ Check the values of the Minimum Size of Server Memory (MB) and Max Size of Server Memory (MB) instance parameters.
For a Index Spool, Table Spool and Row Count Spool:
...
■ Try to reduce the number of logical reads or writes.
| Anchor |
|---|
...
|
...
|
During SQL Server optimization, missing indexes were identified for one or more statements. This means that the SQL Server optimizer recommended creating indexes to improve the performance of the statement. Detailed information regarding index recommendation for this statement can be found in the Recommend tab of the Objects tab.
Table 13-15 Missing 15 Missing Indexes findings
Column Description
...
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 13-16 Missing 16 Missing Statistics findings
Column Description
...
The total In MS-SQL time of the object increased after changes were made to the schema.
Table 13-17 Table 17 Table Schema Change May Increase Its Accessing Time findings
...
The total In MS-SQL contribution of the object increased when the number of statements executions changed.
Table 13-18 Object 18 Object Is Not Scalable findings
...
The total In MS-SQL time of the object increased following a major change in the table size.
Table 13-19 Table 19 Table Growth May Increase Its Accessing Time findings
...
The total In MS-SQL time of the object has increased.
Table 13-20 Increase 20 Increase in Resource Consumption findings
...
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.
Table 13-21 Locked 21 Locked Object findings
Column Description
...
INSERT, DELETE, and UPDATE statements.
Table 13-22 Index 22 Index Overhead findings
Column Description
...
■ Try to identify index update patterns (such as, daily or nightly), in the In Oracle graph, in the Read/Write Operations tab.
...
The index is mainly used for scans and not for seeks which is the recommended method.
Table 13-23 High 23 High Amount of Index Scan findings
...
■ Examine the information displayed in the Recommend tab in the SQL tab to identify missing statistics and analyze index recommendations.
Table 13-23 High Amount of Index Scan findings
Column Description
Advice Perform one of the following options:
...
The table has a high amount of lookups which can cause excessive I/O wait.
Table 13-24 High 24 High Amount of Table Lookup findings
...
SQL Server reads a high amount of unused Heap Pages, which can lead to unnecessary I/O Wait.
Table 13-25 High 25 High amount of unused Heap Pages
...
The following instance findings can help you tune your system:
...
- Locked Instance
...
- Tempdb Bottleneck
...
- Buffer Cache Is Too Small
...
- Other Applications Influence SQL Server (Memory)
...
- Other Applications Influence SQL Server (CPU)
...
- Transaction Log Bottleneck
...
- Extensive Internal Wait
...
- Tempdb Major Growth
...
- High CPU Wait
...
...
Locked Instance
x% x% of the In MS-SQL time was spent waiting for locks. Regular locks can be categorized as follows:■ During
- 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.
...
Table 13-26 Locked 26 Locked Instance findings
Column Description
...
■ Concentrate on blocker sessions. Try to identify the locking statement in the Activity tab using a narrower time frame matching, the lock periods. Focus on the locked table and associated statements. The DML statement (and update queries) that are NOT waiting for locks can be the immediate suspects.
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.
Table 13-27 Tempdb 27 Tempdb bottleneck findings
Column Description
...
■ Check the load on the devices on which the tempdb files are created. You can view this in the Statistics tab.
Column Description.
Advice ■ Tune sorts or joins:
...
Verify that Hash and Merge joins occurred as the result of the proper join methodology. Hash and Merge joins are temporary storage consumers.
...
■ Examine tempdb usage when the applications explicitly create temporary tables. Try creating indexes on those tables after they are populated.
Buffer Cache Is Too Small
A SQL Server instance is configured to work with a small buffer cache. Your applications are experiencing x% x% of the In MS-SQL time for I/O wait. The application’s overall performance is therefore not optimal.
Table 13-28 Buffer 28 Buffer Cache Is Too Small findings
...
Advice Before increasing memory for the SQL Server, check that you are not going to adversely affect other applications running on the server by doing so. In addition, adding increasing the SQL Server memory too much, can decrease the performance, since because SQL Server will have to do paging.
...
SQL Server does not allow the configuration of different settings for each cache, so you must verify whether or not the buffer cache has enough memory.
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.
Table 13-29 Other 29 Other applications influence SQL Server (memory) findings
...
Prioritize the other applications running on the server and decide if any of the other processes can be migrated to another server, thereby freeing memory resources to the SQL Server.
Other Applications Influence SQL Server (CPU)
A SQL Server instance experienced CPU shortage, as a result of other processes running on the server.
Table 13-30 Other 30 Other applications influence SQL Server (CPU) resources findings
...
Prioritize the other applications running on the server and decide if any of the other processes can be migrated to another server, thereby freeing CPU resources to the SQL Server.
Transaction Log Bottleneck
Some of the databases suffer from transaction log bottleneck. Log wait consumes x% of the In MS-SQL time.
Table 13-31 Transaction 31 Transaction Log Bottleneck findings
Column Description
...
■ Examine Log Flush counters, in the Statistics tab.
Column Description
Advice The transaction log is the file that experiences many writes and few reads. Every update to the data is eventually written to the transaction log. Since every database has its own transaction log, the first thing to do is to try to pinpoint the specific database.
If all of databases are experiencing a transaction log bottleneck, try examining the recovery interval (min) configuration.
When you have identified which database is experiencing a transaction log bottleneck, examine the applications using this database.
...
You can also consider moving the transaction logs to different and faster devices.
Extensive Internal Wait
The SQL Server instance has spent much of its time waiting for Internal Wait.
Table 13-32 Extensive 32 Extensive Internal Wait findings
Column Description
...
■ Examine a specific Internal Wait in the Statistics tab (SQL Server 2005 only).
Column Description
Advice An Internal Wait is divided into the following substates:
Buffer Pool
Groups together the events that show contention on pages in the buffer pool. Buffer wait on tempdb pages are considered to be part of the tempdb wait.
...
The session is waiting for an internal lock to be released.
Parallel
The session is waiting for one of its sub-threads to complete its operation.
DTC
Aggregates waits that occur when Distributed Transaction Coordinator (DTC) sessions have to wait for each other.
This state is only available for SQL Server 2005 instances.
DB Mirror
Aggregates the new waits that occur when DB mirroring is performed, such as the waits that occur if the communication layer used by DB mirroring becomes backlogged.
This state is only available for SQL Server 2005 instances.
Profiler
Aggregates a number of states associated with the SQL Profiler and lets you see how much of the database resources it consumes.
This state is only available for SQL Server 2005 instances.
Memory
Aggregates several types of waits that indicate that a session is waiting for memory to be allocated to it. This state is only available for SQL Server 2005 instances.
...
Includes waits that occur when HTTP and SOAP operations are executing.
Query notifications
Aggregates a number of states associated with the synchronization of Query Notification sessions. This state is only available for SQL Server 2005 instances.
Tempdb Major Growth
The tempdb database has experienced major growth. The tempdb database is used for temporary storage for sorting, joining, and, in SQL Server 2005, for row versioning.
Table 13-33 Tempdb 33 Tempdb Major Growth findings
Column Description
...
■ Examine the Tempdb Usage to find out open transactions preventing tempdb to shrink.
High CPU Wait
The instance used SQL Server resources while waiting for CPU.
Table 13-34 High 34 High CPU Wait
Column Description
...
■ Examine the overall CPU usage (using Insight OS) to find out if other processes occupy the CPUs.
| Anchor | ||||
|---|---|---|---|---|
|
...
The following Storage findings can help you tune your system:
...
- Storage Contention on Device (Clariion)
...
...
- Storage Contention on Device (Symmetrix Thick)
...
...
- Storage Contention on Device (Symmetrix Thin)
...
...
- Storage Contention on Device (Symmetrix F.A.S.T. VP)
...
...
- Storage Contention between Log and Datafiles
...
- Unbalanced Storage Devices Activity
...
- Storage with Low Cache Hit Ratio
...
- Storage Contention on tempdb
...
...
Storage Contention on Device (Clariion)
...
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
Table 13-35 35 Storage Contention on Device findings
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Device (Symmetrix Thick)
...
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
Table 13-36 36 Storage Contention on Device findings
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Device (Symmetrix Thin)
The instance spent X% x% of its resources waiting for I/O on the specified storage device.
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
Table 13-37 37 Storage Contention on Device findings
...
■ Examine disks statistics.
Table 13-37 Storage Contention on Device findings
Column Description
Advice ■ If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Device (Symmetrix F.A.S.T. VP)
...
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
Table 13-38 38 Storage Contention on Device findings
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention between Log and Datafiles
...
Transaction Log files are frequently accessed by the database. The majority of the operations performed are writing commands, which cause a heavy load on the underlying disks.
As these files are considered heavy I/O consumers, it is highly recommended to place them on a separate disk without other database files. Separating the Transaction Log files by placing them on different volumes (e.g. E:/ and F:/) may not be enough, as the storage devices (LUNs) and physical disks may be shared between several file systems and volumes.
Table 13-39 Storage 39 Storage Contention between Log and Datafiles findings
...
Advice It has been detected that the Transaction Log files share the storage devices (LUNs) with other database files. Consult the storage administrator about provisioning the storage devices (LUNs) better to avoid this.
Unbalanced Storage Devices Activity
...
There are several storage devices (LUNs) allocated to the instance. However, the I/O activity is not spread evenly across these storage devices. The contention on the heavy storage devices increases the response time for the activities run on them. Such a situation can be caused by imbalanced internal database activity, contention on the storage device by other applications or an inefficient RAID policy.
Table 13-40 Unbalanced 40 Unbalanced Storage Device Activity findings
...
I/O load across the storage devices.
Storage with Low Cache Hit Ratio
...
I/O requests sent to the EMC storage array are processed on the storage internal cache. EMC runs a "prefetch" mechanism that predicts the future blocks to be requested and load them to the cache. Requests that are not being served by the cache access the underlying disks, and are significantly slower. The cache can be shared by different storage devices (LUNs), and may potentially become a layer of contention between I/O activities which are being served by those storage devices.
Table 13-41 Storage 41 Storage with Low Cache Hit Ratio findings
...
■ Enabling "EMC Cache Partitioning", to isolate the instance cache and avoid external contentions.
Storage Contention on tempdb
...
As these files are considered heavy I/O consumers, it is highly recommended to place them on a separate disk without other database files. Separating the tempdb datafiles by placing them on different volumes (e.g. E:/ and F:/) may not be enough, as the storage devices (LUNs) and physical disks may be shared between several file systems and volumes.
Table 13-42 Storage 42 Storage Contention on tempdb findings
...
Advice It has been detected that the undo tablespace files share the storage devices (LUNs) with other database files. Consult the storage administrator about provisioning the storage devices (LUNs) better to avoid this.
Precise. Performance intelligence from click to storage. Learn more > >