Indexes Analysis

Indexes Analysis shows poorly-used indexes from the restart of the SQL server and the missing indexes, based on their acquisition, which are suggested by the SQL server.

The Indexes analysis feature is available on the Performance panel in the Administration panel.

CASE SUMMARY The IT department received a report from users, about the slower performance of the SI_TECTA database. The person responsible from the IT department carries out several performance monitorings. One of those is also indexes analysis. The person responsible does this in the following way: Selecting the database on server Analysing the poorly-used indexes Analysing the missing indexes

1. Selecting the database on server

The person responsible selects the desired database from the list of databases, in this case SI_TECTA.

The person responsible clicks the Analyse button.

HINT A detailed description is provided in chapter Input/Output Analyses.

2. Analysing the poorly-used indexes

Index analysis is displayed in the Index analysis panel.

The person responsible sees a table with data about the poorly-used indexes since the restart of the SQL server.

In the field of all the readings, the person responsible sees how many times an index was used. In this case, the person responsible pays attention to the indexes that have never been used and have the value 0.

With indexes with the value of Total reads being 0, there is a high value (difference between the Total writes and Total reads fields) in the Difference field. An example of this is the IX_tPA_SysExclusive index in the PA_SysExclusive table, which has the value 3160 in the Difference field.

WARNING If there are a lot of indexes with a big difference between the Total reads and Total writes fields, the person responsible labels those indexes as possible reasons for the slowed performance of the database. Such indexes can be removed.

The person responsible realizes that there are a lot of poorly-used indexes, which is why they do XXX. A detailed description is provided in chapter XXX.

3- Analysing the missing indexes

In the second part of the results of the index analyses, the person responsible sees a table with data about the missing indexes from the restart of the server, suggested by the SQL server.

In the inequality_columns column, the person responsible sees which indexes are missing. In the last_user_seek column, the person responsible sees when the missing indexes were last used and within which database scheme, which is written in the DatabaseSchemeTable column.

In this case, one of the missing indexes is acTypeWeb, which was last used 24.6.2024 at 7:47:07 within the SI_TECTA.dbo.tZE_SetDash database scheme.

WARNING If the missing indexes are the cause for the slow performance of the database, they must be returned. For this purpose, a procedure for restoring a missing index in a data scheme is available in the ShortQueryText field.

The person responsible sees that there are a lot of missing indexes, which is why they carry out XXX. A detailed description is provided in chapter XXX.