Index performance tuning in AzureSQLDB

Question

Hi Team,

We are in the process of index performance tuning in one of our AzureSQLDB.

How to fix Non-Clustered indexes with High Writes and low Reads?

Answer 1

If the nonclustered indexes are showing high writes and high reads, it means that they are heavily used but also that the table is involved in data ingestion. My suggestion in this case:

1. Consolidate indexes to improve the number of reads and improve insert performance by updating a smaller number of clustered indexes during data ingestion.
2. Look for tables that have 10 or more non-clustered indexes and try to consolidate indexes there. 10 is very likely too many indexes for read-write tables in most databases. The more indexes a table has the more writes are needed.
3. Make sure all columns on the index definition section and on the Include section are used by queries.
4. Indexes with a ratio of 10x more writes than reads should be consolidated on another index and be removed

Answer 2

Hi @Vijay Kumar

This is not that easy to answer for us... it depends...

You know your application and workload best, so you might know which statement needs which index to run fast.

Usually, I would suggest dropping that index if the numbers are really different => 100.000 writes vs. 1.000 reads.
If you have so many writes in a table/index, the fragmentation of that index might also be high and in need of at least daily index optimization... so your workload on that table might rise for unneeded indexes.

What are the benefits of this index for your queries and workload? Do you really need THIS index, or are there other indexes on that table that might support your queries in a similar way?

And it is not about the optimization of indexes. It is about optimizing the queries and supporting them with correct indexes.
So you have to investigate these using queries and make a decision if you need that index.