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
- How many new entry we get in last 14 days,
- We list latest (today) entry in a Grid, so we query based on time and few data field.
- 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