Index Analysis

The information for this feature is provided by new SQL Server system views.

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes.

You can invoke this feature with transaction DBACOCKPIT. Then choose Performance → Indexes → Index Analysis.

Features

The main screen shows a list of SQL Server tables where the SQL Server optimizer has had to create a suboptimal plan for some queries because an index may be missing. The list is sorted by Statement load, which is the total anticipated improvement that could be achieved by building the index.

You can enter the number of desired tables to select, the default is 50.

The output columns give you a guideline for how to possibly build a helpful index on the table. These columns are:

• equality_columns

  Comma separated list of columns of the table that occur in equality conditions such as table.column = constant_value.

• inequality_columns

  Comma separated list of columns of the table that occur in inequality conditions such as table.column > constant_value. Any condition other than "=" expresses inequality.

• included_columns

  Comma-separated list of columns needed as covering columns for the query. For more information about covering or included columns, see SQL Server Books Online.

This information can be used to decide how to build new indexes. For more details on using this information refer to SQL Server Books Online.

To analyze a given table further you can select the line and choose Table Detail. There you can view the current set of indexes on the table.

There are some limitations in the Missing Indexes feature. These limitations are described in the official SQL Server Books Online documentation. The information is gathered in SQL Server memory and only reflects the events that have occurred since the last SQL Server startup.