How to identify when to change Indexes

Question

We are running a relative small database which when download through bacpac comes as 80MB. But when it runs on Azure it expand to 2GB. In this database, we have couple of main table, but for this question I am focusing on this one table that hold all Customer Record who apply/register with us. We get about 1000 entry in this database on average, 2000 when it is really busy. However the table is now 1.5M record old. Structure of table is:

CallID bigint Identity(1,1),
firstname varchar(50),
lastname varchar(50),
responsetime DateTime not null,
...
CompanyID bigint  
EventID bigint

We have 80 columns in database, mainly containing address and other data. Now, in our reports on web, we need to show

1. How many new entry we get in last 14 days,
2. We list latest (today) entry in a Grid, so we query based on time and few data field.
3. We list/fiter our Grid based on given date range and/or event Id and/or companyid.

For this we over time we add 5-6 indexes, all non-clustered with 1, 2 field (like one is for companyid, other is eventid, third one is for ResponseTime) including other ID field

so most indexes are like

Create Index idxname on Leads (ResponseTime) include (companyId, eventID)

Now, the problem: We are not getting Good Performance on Azure SQL (s1) database tier. On Local Installation of Database our queries usually take <3 sec, but on Azure S1 it is taking 18-20 second. When we were on business Plan which is now retired (almost). it use to take < 2/3 second there as well.

We want to optimize the database, and certainly I believe that Indexes are not in good shape. Most article/tutorial I found they says to create index, but not how to optimize the them. I spent last 8 hrs, trying to create new index and removing some old. but hardly can get a conclusive new index been create, because if I get performance benefit on one query it slows down other.

Now, is there any way, I can find out how to find best optimization of Indexes. I see azure/Sql server give missing index information, but it is asking to create 10 index on my table which already have 6 index, and most of them on single or two column and most of columns are repeated in multiple Suggested index.

Hope I make sense and might not get down vote for my question. Thanks.

EDIT: I found missing indexes using query from this article: https://msdn.microsoft.com/en-us/library/azure/dn369873.aspx

Answer 1

Since you are on S1 you can use two features from Azure:

• Query Store
• Index Advisor.

With Query Store you'll be able to identify the bottlenecks, the most heavily usage queries and the plans they use. This will allow you to prioritize what queries to optimize, and which existing indexes are important.

Index Advisor is more than missing indexes, see this video. It has some ML modeling that allows it to pick the most impactful index recommendation, eliminate duplicates, and it does A/B testing to measure the difference.

In general tables that already have 5-6 indexes and you find yourself adding more are good candidates for clustered columnstore indexes (supported on Azure and on premise post 2014). Segment elimination can do miracles for you.