SQL Server Index Analysis and Optimization

Get acquainted with the key aspects of analysis and optimization of indexes in SQL Server database

Quite often there is a need to optimize indexes and statistics for quicker search for the necessary data and better construction of a query execution plan by an optimizer.

The following areas of work can be understood as index optimization:

• creation of missing indexes
• removal of overlapping indexes
• removal of unused indexes
• change of existing indexes in order to fit operating conditions changed over time (structures of key columns, structures of included columns, and properties of an index itself)
• removal of those indexes for which servicing costs are significantly greater than their benefit for optimization under the operating conditions that have changed over time
• indexes reorganization and rebuilding.

SQL Server Index Analysis

The level of index fragmentation in a database can be analyzed with the help of the following query:

The following columns are returned:

1. db — database name
2. schema — schema object (table, view)
3. tb — table/view where the index is located
4. idx — index ID
5. database_id — database ID
6. index_name — index name
7. index_type_desc — index type description
8. object_id — object ID (table, view) where the index is located
9. frag_num — the number of fragments at the final level of the allocation unit
10. frag — average percentage of available disk space used by all pages
11. frag_page — average number of pages in one fragment at the final level of the allocation unit IN_ROW_DATA
12. page — total number of index pages or data

This shows the level of fragmentation of the indexes which size is no less than 1 extent (8 pages) and which fragmentation is more than 10%. It works only for tables with a clustered index, and only root indexes are taken into account. This query uses two system views:

1. sys.dm_db_index_physical_stats — returns information about the size and fragmentation of data and indexes of the specified table or view in SQL Server.
2. sys.indexes — contains a row for each index or heap of a table object, such as a table, a view, or a table-valued function.

Next, let’s consider when index optimization is recommended and how to perform it.

SQL Server Indexes Optimization

In the previous query, two indicators are particularly important:

1. frag — the level of index fragmentation in percent
2. page — index size in total number of its pages

There are different approaches to the interpretation of the level of index fragmentation and index optimization methods. One of them will be considered in this article.

The index needs to be optimized if:

• its size exceeds 8 pages and its level of fragmentation is more than 30%.
• its size exceeds 64 pages and its level of fragmentation is more than 25%.
• its size exceeds 1 000 pages and its level of fragmentation is more than 20%.
• its size exceeds 10 000 pages and its level of fragmentation is more than 15%.
• its size exceeds 100 000 pages and its level of fragmentation is more than 10%.

Two approaches can be used for index optimization:

1. Index reorganization

Index reorganization requires a minimum amount of system resources. During reorganization, the leaf level of clustered and nonclustered indexes in tables and views is defragmented by means of physical reorganization of the leaf level pages. As a result, they become arranged in accordance with the logical order of the leaf nodes (from left to right). In addition, the reorganization compresses the index pages. Their compression is performed in accordance with the current value of the fill factor.
You can perform index reorganization with the help of the following command:

ALTER INDEX < index_name> ON <schema>.<table> REORGANIZE;

2. Index rebuilding

Rebuilding removes an old index and creates a new one. This eliminates fragmentation, restores disk space by means of compressing pages to the specified or existing fill factor, reorders index rows in consecutive pages, and updates the new index statistics.
You can perform index rebuilding with the help of the following command:

ALTER INDEX < index_name> ON <schema>.<table> REBUILD;

If your edition of the MS SQL Server supports that, the index rebuilding can be done online:

ALTER INDEX <index_name> ON <schema>.<table> REBUILD WITH(ONLINE=ON);

More information about ALTER INDEX command can be found here.

There are many index optimization tools both free and fee-based. For example, Sergey Syrovatchenko is developing a fairly powerful and free tool for optimizing indexes.
The advantages of this tool are as follows:

• optimized algorithm for obtaining fragmented indexes
• ability to serve multiple databases at once in one process
• automatic action selection for indexes based on selected settings
• support for global search and advanced filtering for better analytics
• a lot of settings and useful information about indexes
• automatic generation of index maintenance scripts
• support for heap and column index maintenance
• ability to enable index compression and statistics update instead of rebuilding
• support for all editions of SQL Server 2008 and later, as well as the Azure SQL Database.

The detailed discussion about the tool can be found here.

Statistics Analysis and Optimization

Let’s consider one of the methods to determine obsolete statistics:

In the method given, obsolete statistics is determined by the following indicators:

1. If the data has been changed significantly.
2. If statistics has not been updated for a long time.
3. If the object size is less than the specified maximum or this maximum is not specified.
4. If the number of rows in the section is less than the specified maximum or this maximum is not specified.

The following system views are used in the query:

1. sys.dm_db_partition_stats — returns the page and row count information for all sections of the current database.
2. sys.objects — database objects.
3. sys.stats — statistics for tables, indexes and indexed views.
4. sys.indexes — indexes.
5. sys.dm_db_stats_properties — returns the statistical properties of the specified database object (table or indexed view) from the current SQL Server database.
6. sys.stats_columns — contains one row for each column that is part of the sys.stats statistics.
7. sys.columns — columns of all objects with columns.
8. sys.types — types of data.

Statistics can be optimized further using the following commands:

IF (EXISTS(SELECT TOP(1) 1 FROM sys.stats AS s WHERE s.[object_id]=<object_id> AND s.[stats_id]=<stats_id>))
UPDATE STATISTICS <SchemaName>.<ObjectName> (<StatName>) WITH FULLSCAN;

More information on UPDATE STATISTICS command can be found here.

An Example of Analysis and Optimization of Indices in dbForge Studio for SQL Server

In dbForge Studio for SQL Server, it is possible to analyze and optimize the level of index fragmentation. The product dbForge Index Manager also has this functionality.

In this example, we will consider the SRV database, which is designed to serve MS SQL Server DBMS.

This SRV database is distributed freely for any purpose. After opening the studio, click the “Manage Index Fragmentation …” button on the “Administration” tab:

“Manage Index Fragmentation…” selection on the “Administration” tab.

In the window that opens, select the server and click “Options” to configure the settings:

Server selection and settings configuration.

In the options window that appears, set the desired parameters:

Indexes optimization options.

The results of the settings can be saved as a bat-file by clicking the lower-left button “Save Command Line …”. And you can reset to default settings by clicking the lower-right button “Restore defaults”.

Next, click “OK”.

Now select the database you need:

Database selection.

After that, the analysis will start. You can also update the analysis by clicking the “Reanalyze” button.

When the analysis is finished, select the desired indexes for optimization:

Selection of indexes for optimization.

Please note that the analysis result can be downloaded as a CSV file by clicking the button “Export to CSV …”.

Further on, optimization can be carried out in several ways:

1. generating a script in a new studio window (in “Script Changes” menu select “To New SQL Window”).
2. generating a script to clipboard (in “Script Changes” menu select “To Clipboard”).
3. running the optimization directly (click the “Fix” button).

Let’s choose the third method — click the “Fix” button .

After the optimization process is completed, you need to click the “Reanalyze” button again:

Analysis after indexes optimization.

Conclusion

The analysis of indexes fragmentation level and the extent of statistics obsolescence with subsequent optimization was conducted. It is also clear from the example above that the dbForge Index Manager tool allows you to analyze quickly the level of index fragmentation, as well as generate a database index optimization script.