Investigate inefficient queries running against a table

Inefficient queries—that is, queries that perform a large number of reads but return a relatively small number of rows—can lead to database performance issues. These queries do a large amount of work for little return. This type of inefficiency results in higher I/O, longer wait times, greater amounts of blocking, and increased resource contention.

Possible solutions include tuning the query, adding an index, or adding columns to an existing index. DPA's table advisors help you make informed decisions about the best course of action.

See the following sections for tips on using the information in each table advisor:

• What are table tuning advisors?
• Open a table tuning advisor
• Quick start
• Examine the list of inefficient queries
• Examine query details
• Examine table statistics
• Examine index details

What are table advisors?

At the end of each day, DPA runs an analysis to identify tables that had inefficient queries run against them during that day. For each of these tables, the Table Tuning Advisor page displays aggregated information about the table, the inefficient queries that ran against it, and any existing indexes. This information helps you optimize query performance while taking indexing trade-offs into account.

Open a table tuning advisor

The Tuning tab lists all table tuning advisors for the selected database instance. Click a table tuning advisor to open it.

Quick start

Each table tuning advisor provides detailed information, as described in the following sections. To get started, use these suggestions:

1. Update statistics.

   In the Existing Indexes section, look at the age of the index statistics. If the statistics are stale, especially if table churn is high, the optimizer does not have the best information to make good plan choices. Updating statistics is often a good first step before you do any further analysis.

2. Evaluate indexes.

   Click on several of the top inefficient queries and do the following:

   • Review the SQL text to learn more about the WHERE clauses and JOIN conditions that can affect query performance.
   • (SQL Server and Azure only) If plans with SQL Server's index recommendations are provided, consider adding them or extending existing indexes to satisfy them.
   • If plans with inefficient table or index access steps are provided:
     • Review each plan section and the predicates for each step. The columns in the predicates are candidates for indexes.
     • Check for warnings (shown as links below the step if they are detected) and consider their recommendations.
     • Consider indexing the candidate columns found across the SQL statements examined:
       • Is there an index that might benefit several queries?
       • Is there an existing index that could be extended to benefit one or more queries?

3. Resolve fragmentation.

   Review the table's row count, churn, and index fragmentation. For larger tables, consider the following:

   • If fragmentation is high, defragmenting the indexes might help resolve performance problems when plan steps are using scan operations.
   • If churn is also high, consider defragmenting the index more frequently.

Examine the list of inefficient queries

The upper-left pane lists the inefficient queries that ran against the table on the selected day. DPA assigns a relative efficiency score to each query and uses this score to sort the list.

Select a query from this list to display detailed information about it.

Tips for using this information

• Focus your tuning efforts on the queries at the top of the list, which are driving the most inefficient workload against this table.

• A large number of queries in the list could indicate a more widespread performance issue. Perhaps one good index could improve the performance of several similar queries.

Examine query details

The upper-right pane displays information about the selected query that can help you determine the source of read inefficiencies against this table.

	The performance statistics at the top of the pane show the extent of the query's inefficiency: Reads per Exec is the number of read I/O operations per execution, which indicates how much work the query is doing. Rows per Exec is the number of rows the query returns. The Reads per Row ratio is the number of reads the query needed to do to arrive at each row in the query's result set. Statements with the highest Reads per Row ratios could potentially benefit most from tuning. For more information about the query, click the SQL name or hash value to view DPA's query performance analysis, which shows when the query ran, the execution statistics, and the most relevant metrics charts.
	DPA lists each execution plan that it finds. You can click the link to examine the full plan, but DPA lists the steps most likely to need attention below.
	(SQL Server and Azure only) Index recommendations made by the SQL Server optimizer, if any, are listed. The Projected Impact is the cost reduction that the optimizer estimates the recommended index will have. Click Show index DDL to see the CREATE INDEX statement for the recommendation.
	DPA analyzes the plan and lists steps with the most inefficient access paths. These steps read data to be processed by subsequent "consumer" plan steps. While consumer steps (for example, sorts) can have a high plan cost, they are usually affected by a preceding step that read too much data. Information about each step includes: The step number and the type of operation being performed in the step (for example, INDEX SCAN). The index this step uses, if it uses an index. Any predicates. These are snippets of the SQL that the plan step is acting on. They are typically portions of JOIN or WHERE clauses in which a table's column is being compared to another column or value. Any warnings that apply to the step. The number of rows the optimizer estimates this step will read. Critical and warning icons identify steps that read a high percentage of the table or index rows, and therefore have a greater need for tuning or an index.

Tips for using this information

• Before you add an index, weigh the projected impact or potential performance improvement against indexing trade-offs. Also consider the indexing needs of other queries.

• Click any step to get detailed information about the operation and recommendations for potentially reducing the amount of I/O.

  

• If predicates are listed, they often indicate which columns need to be indexed, or where the optimizer is not using an existing index. For example, if the query calls a function on the column, the plan will not use an index.

• If warnings are listed, click the warning for a detailed description of the condition that DPA has identified as a potential reason for concern.

Warnings

DPA provides the following warnings:

• A predicate warning occurs when a column needs to be converted to a different data type before it can be used. For example, if a query has a JOIN clause that equates a numeric column to a varchar column, one of the columns will be implicitly converted to the other's type. The optimizer typically does not use an index on an implicitly converted column. This is often why the optimizer doesn't use an existing index that the query's author expected it to use.

• A lookup warning typically indicates that the database is doing an index lookup to identify the target rows, then doing an extra table access to get data not found in the indexed columns. To get better performance, consider adding a covering index, or extending an existing index to include columns needed to avoid the table lookup. However, remember that adding a large number of columns can increase the index size and maintenance overhead.

• A spool warning indicates that the step's result set is being stored for reuse later in the query's execution. While spool operations are often beneficial, the intermediate data storage can cause disk overhead and contention.

• A parallel warning indicates that DPA has detected a parallelism step later in this query's execution, implying that this step's intermediate result set is likely large enough to exceed parallel processing cost thresholds. Look for ways to rewrite the query to reduce the size of intermediate result sets earlier in the query. For example, look for a sub-select that could produce fewer rows or the nested loop join order if more than two tables are involved.

Examine table statistics

At the top of the Current table information section, DPA provides table statistics, such as the size of the table and the amount of churn.

Tips for using this information

	Best practices: If the table or its indexes do not fulfill all of DPA's best practice recommendations, click the info icon to find out which recommendations are not met. Click here for information about correcting any violations.
	Size and Rows: For large tables, indexing is often critical to good query performance, although an index on a large table uses large amounts of disk space. For small tables, full table scans sometimes offer better performance than the use of indexes.
	Stats Generated: If the statistics are old and data churn is high, statistics should be updated frequently to provide the optimizer with the information it needs to make better plan decisions.
	Churn: A table's churn is the daily number of insert and delete operation expressed as a percentage of the total number of table rows. Each insert and delete statement, as well as some update statements, incur a performance hit due to index maintenance. Generally, the higher the churn, the more caution you should take when adding an index. Before you add a new index, weigh the query execution time saved against the time spent on index maintenance.

Examine index details

DPA displays information about all existing indexes on the table, including the structure, the amount of fragmentation, how long ago the statistics were generated, and when the index was last used.

Where does DPA get the last used value for an index?

For SQL Server database instances, DPA shows when the index was last used for a seek, scan, or lookup operation, which is recorded in the sys.dm_db_index_usage_stats table. This value is not updated as a result of system activity.

For Oracle databases, DPA shows when the index was last included in an Oracle execution plan for a select, update, insert, or delete statement.

For PostgreSQL database instances, DPA shows when the index was last included in PostgreSQL execution plan for a select, update, insert, or delete statement.

Tips for using this information

Before you make any indexing decisions, first review the existing indexes. Consider the following questions.

Take indexing trade-offs into account when you are considering adding or extending an index.

• Are the statistics stale? If the statistics are old and data churn is high, statistics should be updated frequently to provide the optimizer with the information it needs to make better plan decisions.

  If statistics are old and churn is high, consider updating the statistics before adding or modifying indexes.

• Is there an existing index that an inefficient query should be using?

  Look for ways to adjust the query so that it uses the index.

• If an inefficient query is using an existing index, are there inefficient table or index access steps on columns that aren't included in the index?

  Consider adding those columns to the existing index.

• If an inefficient query is using an existing index, are there inefficient table or index access steps that indicate a lookup warning?

  Consider adding those columns to the existing index to make it a covering index for the query.

• Is there no existing index that would improve an inefficient query's performance?

  Consider adding a new index.

• (Oracle and SQL Server) Are indexes fragmented? Fragmentation occurs as a result of numerous insert and delete statements. Fragmentation causes index data to become out of order on the disk, with gaps between index data. This is not a major concern for small tables, but for large tables this can cause slow performance when the index is read using a scan operation.

  Consider defragmenting your indexes on a regular basis for large tables, especially if data churn is high and many scans are occurring.

• (PostgreSQL) Is the Index Bloat Metrics percentage high? Bloated indexes can make inserts slower and negatively affect lookup performance.

  Consider rebuilding the index if the bloat percentage is high.

Correcting common index problems

After you determine what indexes are needed to improve query performance, look for additional benefits by identifying poor index usage, such as:

• Unused indexes: Can indexes be removed without negatively affecting query performance? To help you find unused indexes, DPA lists how long ago each index was used. However, before you remove an index:

  • Be aware that sometimes the Last Used value can show only the date since the monitored database instance was last started.
  • Consider whether queries that run infrequently (for example, monthly or quarterly) might use the index.

• Too many indexes: A large number of indexes on a table might be necessary for important queries to run quickly. However, you should also consider the performance overhead of index maintenance on other DML statements. Look for opportunities to:

  • Combine similar indexes.
  • Remove unused or rarely used indexes.
  • Remove indexes that were added for queries that are not performance sensitive.

• Overlapping indexes: Two indexes overlap if they both have the same leading edge columns in the exact same order, but one index has at least one additional column at the end. In this case, the larger index (with more columns) is all that you need, and you can remove the smaller, redundant index. Alternatively, you might choose to remove the larger one if the additional columns are not being used, or if the additional columns offer little benefit compared to the cost of index maintenance.

• Questionable index structure: The following might indicate a poorly constructed index:

  • Many columns: Indexes with many columns require more storage, and increase the cost of index maintenance. Perhaps the index was defined this way to make it a "covering index" for some queries. If not, consider removing trailing edge columns.

  • Wide columns: Some DBAs question the benefit of adding wide columns (for example, long varchars) to an index, because of the high amounts of storage needed for the index and the maintenance overhead. With this in mind, if your queries do a lot of searching on any column, consider indexing it.

Indexing trade-offs

While indexes can provide performance benefits for some queries, consider the following trade-offs when making indexing decisions:

• Index maintenance: When a table row is inserted or deleted, the corresponding entry in each index must also be inserted or deleted. If an indexed column is updated, the associated entries in the index must also be updated. These operations on indexes increase the time an insert, delete, or update statement takes to run. The cost of index maintenance increases as the amount of data churn increases.

• Disk space: Indexes consume disk space. The larger the table and the more columns in the index, the more disk space it needs.