Sometimes Stored Procedures takes too much time to return result

Question

_{Thursday, September 29, 2011 10:56 AM}

Hi,

My Azure Web Application (live) has a large set of data stored in SQL Azure. For which I've created many SPs to fetch up to around 2000 records which matches the filter criteria.

Many of the SPs are having 3 to 9 Joins in Select statements.

Problem scenario is little strange and as following:

1. When I create a new SP, it takes around 6-10 seconds in fetching result records. And this works fine for few weeks or for few months.

2. Suddenly it starts taking long time to return the results, and now execution time is around 2 - 3 minutes. I've tried to execute SP from both, MS SQL Management Studio and from my application; it takes almost same long time.

3. BUT, if I simply ALTER the SP from SQL Mgmt Studio ( Without making any change in the logic of SP ), it suddenly starts returning result in 6 - 10 seconds again.

Until unless I use to ALTER the SP, it keeps taking long time for the whole day or two. I've created more than 25 SPs, and this happens with all the SPs at same time. I have to ALTER them to get the result on time as my Web Application is live, hosted on cloud.

Strange, isn't?

I'm not able to find the exact reason behind this. Can anyone put some light on this issue and its permanent solution..??

Thanks in advance..

Kaushal H. Patel
MCPD-EA, MCTS, MCA

All replies (3)

_{Friday, September 30, 2011 3:27 AM ✅Answered}

Hi Kal Patel,

According to your description, I think your problem can be caused by inefficient query plans. The stored procedure may use a cached query plan that is not inefficient for the current parameter values.

As we know, SQL Server stores the plan for the procedure into cache, so that the plan can be reused next time to save the overhead of recompiling the SQL statement. I think SQL Azure should do the same. The optimal plan differs depending on passed parameters. Sometimes, the cached plan is not good for the current parameters so that decrease the performance. ALTER PROCEDURE will make the plan out of cache. So when the stored procedure is executed next time, a new good query plan is compiled and used, then cached.

The following link demonstrate a similar scenario, I think you can also look into it:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Usually, there are several ways to solve the issue in SQL Server, some of them should also work in SQL Azure like dynamic SQL or replacing parameter values with local variables, please see:

http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

http://www.sommarskog.se/query-plan-mysteries.html (It’s quite long but exhaustive.)

References:

Execution Plan Caching and Reuse
http://msdn.microsoft.com/en-us/library/ms181055.aspx

DBCC FREEPROCCACHE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms174283.aspx

Jian Kang
Forum Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [email protected].

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

_{Tuesday, October 4, 2011 1:14 PM}

Hi Jian Kang,

Thanks for quick reply.

I tried to go th.' the articles you've listed. With DBCC FREEPROCCACHE, it looks like it can solve the issue but unfortunately, it is not supported by SQL Azure (http://msdn.microsoft.com/en-us/library/windowsazure/ee336253.aspx).

I'm trying to go with other articles you've listed to find the solution.

Thanks..

Kaushal H. Patel
MCPD-EA, MCTS, MCA

_{Thursday, October 6, 2011 2:09 PM}

Hi Kal Patel,

Try adding OPTION (RECOMPILE) at the end of the query, this will ensure that query is compiled on each execution and use the right plan.

We solved some issues in our datawarehouse and reporting projects using this option.

This was suggested to us by one of the Microsoft Support member itself.

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"