SQL connection timeout error with single query only in Azure webapp

Question

I'm working on an Azure web app. Using the entity framework to fetch the result. Everything is working fine. All queries are working as expected.

But the following one throwing the SQL connection timeout error. Most of the time it works fine but doesn't know when it starts giving the error and keep this error for more than 24 hours.

var logsCount = context.Logs.Where(l => l.StartDate >= startDate && l.StartDate <= endDate)
                            .GroupBy(l => l.KeywordID)
                            .ToDictionary(l => l.Key, l => l.Count());

I believe it's not an issue with the query because it keeps running fine for many days and starts giving problem suddenly.

It starts working fine itself. Not sure, why it's happening?

Can it be anything related to database or server only?

Answer 1

Please try creating an index like the following using a tool like SQL Server Management Studio:

CREATE INDEX IX ON [YourTable] (StartDate, KeywordID) INCLUDE ( list all columns returned separated by comma )