SQL Azure stored procedure timeout issue

Question

I have a stored procedure that when is executed directly against the database takes 1 second to run. It returns 46 rows of data.

The stored procedure is executed in code for my web site and returns data to create a report.

This works exactly as expected locally, takes a very short time (1/2 seconds) to load the report. When the web site is first deployed the report works as expected.

However, after a period of time (roughly half an hour) the report does not load because the stored procedure is timing out.

To get around this, if I drop and create the procedure it will start working again, but this is obviously not a solution to my problem.

The stored procedure is called in code like this:

DataSet content = new DataSet();

Database db = DatabaseFactory.CreateDatabase();
DbCommand cmdSearchQuestionLibrary = db.GetStoredProcCommand("SP_NAME");

db.AddInParameter(cmdSearchQuestionLibrary, "@Param1", DbType.Int64, Id);
db.AddInParameter(cmdSearchQuestionLibrary, "@StartDate", DbType.DateTime, dtStartDate);
db.AddInParameter(cmdSearchQuestionLibrary, "@EndDate", DbType.DateTime, dtEndDate);
content = db.ExecuteDataSet(cmdSearchQuestionLibrary);

Has anyone experienced this before? Or know what could be causing the issue?

Answer 1

2 things you may try 1. UPDATE STATISTICS (Transact-SQL) : https://msdn.microsoft.com/en-us/library/ms187348.aspx

2. sp_recompile (Transact-SQL) : https://msdn.microsoft.com/en-us/library/ms181647.aspx

Answer 2

Sometimes the best way to figure out what is going wrong is to take a look at what is happening in the database at the time. One common reason might be based on blocking queries.

If it helps, our Cotega database monitoring service provides a free capability to analyze a SQL Azure database which executes most of the common queries used to find issues such as this. This includes blocking queries which might provide some additional insight.

I hope this helps.

Answer 3

I had the same problem, the stored procedure used to work for years and then one day it started to time out. I re-compile the stored procedure and publish the web app again. And then the timeout problem doesn't show up again.