Microsoft SQL Server Index Performance


An index in SQL Server is a structure in association of which the speed of retrieval of rows from tables or views increases. It is comprised of keys that are associated or have been made from a single or multiple columns in tables and views. These keys get stored in a B-tree structure that facilitates SQL server to search for particular rows that are associated with the selected keys in a very quick manner.

This blog deals with an SQL Server Index Performance issue that was faced by one of our customers.

Issue:

The customer had an ETL package installed which was running for a longer time than usual. The package served a simple function- inserting multiple rows from a staging table to a large table containing million rows.

Reason:

It was revealed that the reason behind it was the OLEDB destination adapter. The adapter was consuming the maximum execution time. On further investigation of the issue, it came into light that the destination table comprised of a large number of indexes with some of them having a size larger than 200 GB. Moreover, the execution plan showed that the SQL Servr index updates were also responsible for the slow speed of the insert operations.

Solution:

In order to solve the SQL Server Index performance issue, firstly we thought of disabling the indexes of large size. However, it was found out that these large sized indexes were used for some kind of daily reports and disabling them would mean severe impact on the daily performance report.

The second solution which came to our mind was to disable the indexes before ETL and then enabling the afterwards. However, this solution also had it drawbacks. The index rebuild process consumed more amount of time and the reports on the indexes had to be run immediately, therefore this solution was also discarded.

Now the only solution, which suited every aspect, was the reducing of index size. This solution was based on the assumption that smaller sized indexes will consume less index updates, thus leading to better performance for update and data insert transactions.

Reduction of SQL Server Index Size

A deep analysis of the SQL database index revealed that irrespective of the fact that the large indexes had only a small number of key fields, they comprised of a large number of columns in INCLUDED columns logic.

SQL Server Index Optimization

In order to solve the problem in hand, optimizing the existing SQL Server indexes without doing any compromises with the read queries’ performance is the solution that can be preferred. In order to do so, we searched all the queries from the cache that had the reference for the large indexes. On analyzing the execution plan of the indexes, we came to know that no index was fully satisfying the queries as they had Lookup operators present in the execution plans. Therefore, we thought of removing a few columns from INCLUDE part as it would not change the query because the Lookup operator had to bring a lot more fields.

When a number of fields from INCLUDE portion of the covering indexes whilst not disturbing the main index, the index sizes dropped to 40%. Therefore, the query performance was drastically improved as the execution cost of the SELECT queries was reduced and they ran faster than before.

The most important point in the above process was that removing some columns from the INCLUDED part of the SQL Server index helped in the performance improvement of read and write queries. This is because the smaller indexes require a very less amount of memory and disk input/output.

Summary

Whilst covering indexes prove to be of great importance for improving read queries, they also play a crucial role in the performance degradation of read and write transactions in large tables. In cases of fully covering indexes, reducing its size may turn out to be problematic as it can make the queries less efficient and partially covering. However, if the SQL Server index is partially recovering and the queries and transactions are not performing well, downsizing is the most efficient solution.