Database Timeout Issue

Mohamed Rashed asked on May 8, 2016 21:27

I have an issue that happens randomly on our servers. After several calls to Kentico database through the Kentico API CustomTableItemProvider and ObjectQuery<CustomTableItem> we receive an exception and the connection to database is blocked.

Kentico assemblies are referenced by an ASP.Net Web API project. Kentico version: v9.0.17

Initiailzation function:

public bool Init()
    {
        var physicalPath = ConfigurationManager.AppSettings["KenticoPhysicalPath"];
        CMS.Base.SystemContext.WebApplicationPhysicalPath = physicalPath;
        return CMS.DataEngine.CMSApplication.Init();
    }

Sample Data Access Code:

private List<EventInfo> Get(int emirateID, DateTime startDate, DateTime endDate)
    {
        return CustomTableItemProvider.GetItems("RYD.Event")
                .Source(s => s.Join(new QuerySourceTable("RYD_EventCategory"), "RYD_Event.CategoryID", "EventCategoryID"))
                .WhereEquals("RYD_Event.IsDeleted", false)
                .WhereEquals("EmirateID", emirateID)
                .WhereGreaterOrEquals("EventDate", startDate)
                .WhereLessOrEquals("EventDate", endDate)
                .Columns("EventID", "EventTitle", "RYD_Event.CategoryID", "RYD_EventCategory.CategoryTitle",
                            "EventContent", "EventDate", "EventImage", "EventImageID", "EventLocation")
                .ResultList(this.Map);
    }

and I got this exception

Event ID: 5017 Event type: Error Event time: 5/4/2016 10:53:08 AM Source: System.Data.ProviderBase.DbConnectionFactory.TryGetConnection Event code: b3771c45-e374-4923-be21-00d46e70d525 User ID: 65 User name: public IP address: 192.168.87.25 Description:

Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Exception type: System.InvalidOperationException Stack Trace: at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at CMS.DataEngine.AbstractDataConnection.Open() at CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query) at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query) at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords) at CMS.DataEngine.DataQueryBase1.GetDataFromDBInternal() at CMS.DataEngine.DataQueryBase1.GetDataFromDB() at CMS.DataEngine.DataQueryBase1.GetData() at CMS.DataEngine.DataQueryBase1.get_Result() at CMS.DataEngine.ObjectQueryBase2.GetResults(IDataQuery query, Int32& totalRecords) at CMS.DataEngine.DataQueryBase1.GetDataFromDB() at CMS.DataEngine.DataQueryBase1.GetData() at CMS.DataEngine.ObjectQueryBase2.GetData() at CMS.DataEngine.DataQueryBase1.get_Result() at CMS.DataEngine.ObjectQueryBase2.EnsureTypedResult() at CMS.DataEngine.ObjectQueryBase2.System.Collections.IEnumerable.GetEnumerator() at CMS.DataEngine.EnumerableWrapper1..GetEnumerator>d__0.MoveNext() at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable1 source) at System.Linq.EnumerableExecutor1.ExecuteBoxed() at CMS.DataEngine.CMSQueryProvider1.Execute[TResult](Expression expression) at RYD.IPad.Data.Kentico.IpadApiSessionRepository.GetSession(String pTokenHash)

Machine name: TE1-EFD-WV-IS01 Event URL: /api/v1/events/1437 URL referrer: User agent: Riyadah/1.1 CFNetwork/758.2.8 Darwin/15.0.0

Recent Answers

Bryan Soltis answered on May 9, 2016 16:50

Hi Mohammed,

Your code looks correct and the ObjectQuery API is designed to be much more efficient that a straight database call. A couple things I would be curious about:

  1. What is the database timneout set in your connection string?

  2. Is the site doing a ton of other things when you get this error?

  3. How many records are in this custom table?

  4. I'm curious if the QuerySourceTable is cause of the issue. Your code looks correct, and if it works usually then it's probably not it.

  5. Bryan

0 votesVote for this answer Mark as a Correct answer

Mohamed Rashed answered on May 9, 2016 20:39

  1. I'm using the default connection string come with the installation,

  2. No it's simple site and still not in production

  3. Less than 100 record

  4. Yes, it's working usually and this issue happens randomly

0 votesVote for this answer Mark as a Correct answer

Bryan Soltis answered on May 9, 2016 22:27

The default timeout for SQL server is 60 seconds, which is usually plenty of time. Your record count certainly isn't large so I suspect the delay is coming from the join in some way. You could do one of the following to isolate the cause:

  1. Enable SQL Debugging and isolate the call the above code creates. you may need to add the code to a web part or something in order to isolate it.

  2. Set up SQL profiling on your SQL server and test the generated SQL call (you should be able to get that form the Debug information). It may indicate an index or restructuring of your data is needed.

  3. Look into adding an index on one / both of your tables to help SQL find values faster.

  4. Increase your SQL timeout setting. Given that your site is only experiencing this issue occasionally, there may be something else going on that is causing the delay. Increasing the timeout would give it more time to work, however, you're not really addressing the issue.

  5. Ensure all of your custom code is optimized. Another possible cause of timeouts is too many connections to the SQL server. If are using objects, use "using" statements to make sure they are disposed.

Hope this helps.

0 votesVote for this answer Mark as a Correct answer

Mohamed Rashed answered on May 11, 2016 00:05

Thank you Brayan, for your detailed reply.

I'll apply all possible solutions from your reply, and I'll keep thread updated with the solution once I got it.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.